How do I create a dashboard in Excel?
To create a dashboard in Excel you organize the data used by the dashboard in an excel workbook, create flexible models out of the data and then finally present the data as a properly formatted report or a dashboard. Before we begin, always keep in mind that if the dashboard you are creating is going to be used (and oftentimes maintained) by other people in your company, it is always a good idea to simplify things as much as possible. You don’t want to spent time creating something that goes out of the window just because somebody else could not comprehend how you created the dashboard.
In case you want try it out yourself, you can use the raw data for creating a dashboard in excel here.
Now lets get back to business.
Proper organization of data in Excel – the first step in creating a dashboard
More likely than not, you will have raw data coming in from a database, an existing worksheet or in many cases, a flat file. Excel provides a very easy interface to load data and it can accommodate text files, xml files, html files, database queries or even some database files. On the menu bar, use the ‘Data’ -> ‘Import External Data’ option to load data onto a workbook.
Check and clean data for errors, missing values and trailing spaces
Once you have the data loaded onto an Excel workbook, the first thing that you need to check is if each of the fields has been inserted into a proper column. Many a times, you may have a data field populated correctly in a column only for the first few rows. Once you are certain that you have all the data field properly populated in columns (you may have to use the ‘Data’ -> ‘Text to columns’ option to separate out the fields in a comma separated string, especially if your data come from a flat text file), let’s move over and ‘eyeball’ the data in your Excel dashboard. Are some fields blank and data missing in some other field? Does a field which contains numbers behave like text? Does a field contain leading or trailing spaces? Check for all these and ensure that you eliminate any sources of errors.
It is always a good practice to format all the data points once you have loaded data onto a workbook and before you add other elements to your dashboard. Formatting can also help bring out sources of errors in your data. For example, if you format a column in the date format ‘dd-mmm-yyyy’ and some of the fields do not format properly, it may indicate that some of the data fields may be getting treated by Excel as text strings. I typically add lighter borders to my data sheet.
It is always advisable, both for aesthetic and reasons of efficiency to organize data in the dashboard in such a way that the unique key fields (employee ID, serial numbers etc) are towards the left of other data fields. The same is the case with fields that help aggregate data – say for example, country of origin, department name etc. Organizing data in this way can help improve speed of calculation of formulas in your Excel dashboard. To know more about ways to improve the speed of calculation in excel read this article.
Create flexible data models for the dashboard
Once you have the data laid out nice and pretty, you need to move onto the next step which is to create flexible models using the data. It is always advisable to pack as much data in as small a space as possible rather have the readers go through worksheet upon worksheet of graphs and tables in the dashboard. However you also need to ensure that everything is intuitive and proper separation is maintained for unrelated data elements. Balancing both the criteria can be a challenge at first but you can master them with experience.
Creating these data model is way to pack as much data in as small a space as possible in your Excel dashboard. For example, you can show say, sales from all 20 divisions in 20 different charts or simply have one chart updated as the user chooses from a drop-down with the list of 20 divisions.
Creating pivot tables
Pivot tables are one of the easiest and most useful features of excel. You can create one using the ‘Data’ -> ‘PivotTable and PivotChart Report’ option from the menubar. You can read more about pivot tables and VBA here.
Named range and data validation
Though I would not suggest using VBA code when other means can do the job equally well in your excel dashboard, at times there is no other option to use it. When you write code for your excel dashboard, comment profusely and write easily understandable code. You can learn more about the VBA For Next and For Each in Next loop here and do until loop here.
Other data models
In my article about alternative to pivot tables, I detailed out a method to drill-down data in a manner that is simpler for the user than can be achieved by a pivot table. There are quite a few other methods that can help you create these data models.
Present data as a proper dashboard in Excel
Once you have your data models in place, all you have left to do now is to place and organize various data elements in such a manner that the most useful information is made most easily available to the reader of the dashboard. Also bear in mind that the task here is to make readers life simpler by providing him the ability to see as much information as he/she wants to without dumping everything on him/her all at one go. A good thumb rule to follow is that the option that surprises the reader the least is always the best option. Before you add any data point, VBA control or graph to the dashboard, ask whether it makes the user life simpler.
What data points to include in a dashboard
Start with making a list of data points that you want to show the user. Start with the most obvious ones. Then move over to ones that add a little less value. Once you have a list of all the data points, remove those the reader can do without. Now sort the list of data points in order of importance to the reader.
If it can simply be represented by a number, don’t use a chart. However charts are great to show trends. Use a chart that compliments the data point you want to show. If you want to show the profit and loss statement, use a waterfall chart (if needed). If it’s a project timeline, use a gantt chart. While using a chart in your Excel dashboard, follow these simple guidelines:
1. Keep it simple. Remove everything that does not help. Excel is notorious for inserting data elements in a chart which is popularly known as chartjunk. These include gridlines, area outlines and other junk.
2. Prefer line and bar charts over others fancy ones and two dimensional charts ones over 3-dimensional or 1-dimensional ones.
3. Do not use colors if you can avoid them.
4. Replace the jarring Excel default chart colors with soft shades.
5. Ensure that you have the proper scale and formatting assigned to the x and y axis.
Excel is fairly versatile at making charts and I haven’t yet come across a reasonable chart that can’t be created in Excel. They come in all shape and sized can be made to fit into a tiny cell and scale all the way up to an entire sheet.
Adding VBA controls
You can add VBA controls to your excel sheets. Turn on the Forms toolbar by clicking the ‘View’ -> ‘Toolbar’ -> ‘Forms’ in the menu. Once you have it turned on, you can click and drag and control that you want onto a spreadsheet on your dashboard.
Formatting the Excel dashboard
The final step in the creation of a dashboard in excel is to properly format the final worksheet/s that form your dashboard. Remove cell borders from the cells of your excel dashboard, hide unwanted rows and columns, provide proper spacing to various data elements, choose a proper font and provide shading and borders to those elements that do require them.
Here’s is are a couple of dashboard screenshots that you can use:
Sample Dashboard 1
Sample Dashboard 2
You can also download this a sample excel dashboard here or click on the button below: