If you have followed this till now (silly me, if you’re reading this, you likely might’ve :-)), you’ve completed the first of the three important phases in the development of your dashboard. You may want to use these formulas to get your data up to scratch for the next important step – Data Modeling and the generation of pivot tables.
Generating a Pivot Table
Once your data has been properly scrubbed, cleaned for errors and filled up, its time to us to make a data model out of it. Though there are many ways to do it in excel, most likely you’d want to use the pivot table functionality provided by excel. Pivot tables, in short, are God’s gift to people like me whose life depends on data :-). Not only do they sum up the data in a concise manner, they also add interactivity to your data model.
Now, making dashboards dynamic/interactive is optional, but I would argue that it is generally a good idea. One, it saves screen space…..you can present multiple data points in the same area, and secondly if the amount of data is truly large and complex, interactivity allows the user to build a mental map by varying a set of data elements. On the flip, if your audience needs to print the dashboard, you’ll be better off reducing the interactivity. And interactivity, unfortunately, is not something that a sheet of paper is naturally equipped to handle!
Now to the skunkworks, in order to create a pivot table, you must first need to identify the data elements that you would want to show to the users. More often than not, there will be fields (columns) in your data that serve the purpose of classification or record keeping, for example the names and addresses of customers who bought stuff from a departmental store last month. These, though useful, serve limited or no purpose if presented to your audience.
Now that our base data looks nice and dapper, lets start working on generating pivots from our sample database. Click on Data -> Pivot Table and PivotChart Report. The box that appears shows, Step 1 of 3. Click on next. Excel will automatically pick up the entire range of cells for you. If it doesn’t, you can enter the range yourself. In our case it picked up the range as A1:J97. (If any of the column names are blank or duplicate, excel reserves the right to throw the error “The pivot table field name is not valid”.) Click on Next. Then click on Layout. This opens up the Pivot Layout window. As you can see, this is divided into four major areas. In the center in the portion called the data field. Apart from this, there are three more portions. Towards to top-right is a placeholder for page fields. Then there are column fields and row fields. The page, column and row fields help you organize the data better. How do you decide which ones to put where?
The data that pertains to performance (in our case the sales figures), needs to go to the data field in the center. Typically the time horizon comes on the column field. This will lay out the time horizon in your pivot from left to right. The fields to whose performance the data pertains to (ex. Regional managers and Divisional managers) appear in the page and row fields. A simple rule of thumb:
“Ascertain the parent-child or superior-subordinate relationship between the fields. Put the junior most field in the row or column fields and move all others to the page field.”
This will provide you with a neat and concise pivot to begin with. Use your imagination to make modifications.
An interesting thing to note is that you may see duplicate fields in your pivot. These may be due to leading or trailing empty spaces or incorrect names. If you look carefully at your pivot, you’ll see a duplication of some of the names, Billy W for Bill Wanton and T. Spenser Terri Spenser. A rose by any other name may smell as sweet and in an Excel, it can smell twice as sweet Go back to your base data correct all the names. Then right click on the pivot and Refresh Data.
Pivot Field Settings and Table Options
Right click on the pivot and choose Pivot Field Settings. In the panel that open up, cllick on “Number”. If you recall, this would allow you to format the field you choose in a particular fashion.
Right click on the pivot and choose Table Options. This panel allows you to control how the pivot is diaplayed. If you do not want the pivot to display the totals for the row and column, turn off Grand total for columns and grand total for rows. While you’re at it, also turn off AutoFormat Table option as well. This is particularly important if you are planning to change the look and feel of the pivot on your own. If this option is left turned on, the pivot will lose all formatting that you carry out on this the next time you refresh the pivot.
If you do not see the pivot toolbar, you may want to turn it on by clicking View -> Toolbars -> PivotTable.
Formatting the pivot
Select the entire sheet and choose white as the Fill Color for all cells. Similarly click on the Borders button and choose No Border.
If you are feeling a little more adventureous, you can try formatting your pivot. Right click on the pivot and click and Select -> EntireTable. Then right click again and click FormatCells. Move to the “Border” tab. In the section labelled Line Style pick up the line with the least ink. Move over to the section labelled “Presets” and click outline. Press ok.
Your pivot now looks like it has just come out of a war wash!!!
Just incase you were wondering why did we want to create a pivot? Well…..they allow the user to isolate and combine data by a few clicks and ascertain trends. Click on any page field and choose Pens from the Product under the Page fields (top right). The data in the pivot changes to reflect only to that pertaining to Pens. Play around this until you become a pro.
Note about Pivots
Everytime the base data changes, the pivot would need to be refreshed….until unless told to do so, the pivot will not get refreshed automatically. Change some part of the base data and right click on the pivot and then choose refresh data. However as a point of caution, when you add additional data points to your base data, on refreshing a pivot, it can expand to take up extra space below and to the right of its original position. That can sometimes have disastrous and potentially “fatal” consequences if you have important data elements present nearby You can always undo your past mistakes, but it’s always beneficial to get the learning before the experience! There’s little benefit in learning how to the make a chicken sandwich if you happen to be the chicken.
One interesting feature about the pivot tables is that once created, they retain a copy of the base data with them in their cache. Infact you can go ahead and delete the base data and the pivot would still work as if it were reading all the information from the base data. However, I would suggest we save that for later.
With this you’ve reached exactly 2/3rd of your way to creating an excel masterpiece. You may save or ignore the pivot we created…..in the next section we’ll be switching over a more complicated set of data, one that allows us to fully grasp the various options to present data that excel provides. The good news is that the rest of the journey is very exciting!