Excel Dashboard : Data Organization

Having our first view of the data
More often that not, dashboards get prepared with a large amount of data. Even if that’s not the case, it always makes send to have your data nicely laid out. That way, whenever you have to go back and check the accuracy of the data, it becomes so much easier.

Let’s work with an example. Suppose we’ve just been provided with one year’s worth of sales and target numbers for the regional and divisional managers for a company called ABC Limited which sells pens and books.

Sales figures for ABC Limited

Sales figures for ABC Limited

To download the sample database click here

The first thing to do is to select the entire worksheet (Remember Ctrl+A) and change the font to “Arial” size 10 and left align everything. That will set up the entire worksheet evenly in same format. (Arial belongs to sans serif fonts group which should preferably be used while working with data). Adjust the column/row heights/widths by right clicking on the column/row headers and clicking Column Width/Row Height.

As you can see, that will have our dataset is beginning to look good.
However, as you can see there are still gaps and omissions.

Text and Numbers ConversionsAn interesting to watch out for is numbers which have been wrongly formatted as text. For example in our sample database, the year column seems to have been wrongly formatted at places resulting in excel showing a large string of #########;s in those cells. To overcome this, select the entire column by clicking on the column header, and right click and press Format Cells.



In the box that appears, under the Number tab, change the Cateogory to “Number” and “Decimal Places” to 0. Press ok.

Another error that is often encountered when working with *raw* data is that often numbers will be prefixed with a single quote ( ‘ ). In Excel a number entered as ‘100 may still appear as 100. But they are not the same thing. To overcome this, select the specific column (one column at a time please incase of multiple columns having the same issue) and click Data -> Text to Columns. This will rectify the formatting for all the cells in the column.

Rename ColumnsA good prictice is to rename columns and provide them with more descriptive names. That way you and anybody else who gets to see it later, would know exactly where everything is located. In our sample database we may want to rename some of the columns as
Name = DivisionalManagerName
Name = RegionalManagerName
Reg = Region
Pdt = Product
Mth = Month
Yr = Year
MthSal = MonthlySales
CustN = CustomerName

Turning Filters OnJust so that you can glance the entire data at one go, turn all the filters on. To do so, select the entire sheet and click Data -> Filter -> AutoFilter. For some of the important columns you may want to do a quick check by looking at the filter drop downs and “eye balling” the data for non-text characters, junk and blanks.

Filling the dataIf you may have noticed, whenever you select a cell or a range of cells, a fill handle appears at the bottom-right end. You can stretch this handle to automatically fill data into the adjacent cells. For example, stretching a range of cells each of which contain numbers 1, 2, 3 respectively can give you a continous series of numbers extendig beyond 3.

Fill Handle

Fill Handle

If required, use any of the excel formulas at your disposal to fill up the blank cells. You’ll find = and vlookup() to be a particularly useful . However, when the base data is truly large and stretches your processor to its limits, it may be wise to turn calculations to manual mode. (See how to turn calculation to manual mode here) Just be sure to switch on to the automatic calculation mode later.

In our sample database, we can see that the company and division names are blank in many of the cells. We will fill these up using the fill handle.

Combining fields
As you can see, columns G and H contain the month and the respective year of the sales figures. It would be so much better if we can combine them in to a single column and then eliminate the two.
Insert a column to the right of column H. Then in cell I2, enter the formula =EOMONTH(“1″&”-“&G2&”-“&H2,1). Select the entire column (Ctrl+Spacebar), right click and press Format Cells. In the category box choose “Date” and in the Type box choose “Mar-01”. (We choose a monthly format since our data has monthly breaks. For data daily frequency, you may want to choose “14-Mar-01”. Press ok. Copy this formula to all the cells below. Rename this column MonthYear.

Format Cells to Date

Entering Formula

Format Cells to Date

Format Cells to Date

Once you have a new column, you may need the remaining two. Before we do that you’ll need to make convert the values in he newly inserted column from formula referencing others cells to a static standalone value. Select the entire column. Copy (Ctrl+c) and then right click and press Paste Special… In the Paste option, select the radio button from labelled “values” and press ok. That will convert all the cells formulae to static values. Now you can safely delete those columns by Click on Edit -> Delete or simply by pressing Ctrl+-.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Janet Embrey wrote:

    I think this site is WONDERFUL. An excellent resource and very well documented.
    I read through about 6 segments so far. Only found one problem. Excel Dashboard: Data Organization. towards the end you use a formula that is wrong. It should be =EOMONTH(G2&H2,0) Also, the last illustration is missing.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel