Categorized | excel dashboard
Tags |

How to Create a Dashboard in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

How do I create a dashboard in Excel?

how-to-create-a-dashboard-in-excelTo 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.

Format data

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.

Organize data

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

Named range when used with data validation is an amazing way of presenting and organizing data in an excel dashboard. You can read about data validation and named range here.

VBA code

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.

Adding charts

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.

Sample Dashboard

Here’s is are a couple of dashboard screenshots that you can use:

Sample Dashboard 1

excel-dashboard-screenshot-1-small
(Click on image to enlarge)

Sample Dashboard 2


(Click on image to enlarge)

You can also download this a sample excel dashboard here or click on the button below:

sample-excel-dashboard

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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=""> <strike> <strong>


Comments and Trackbacks

  1. sisense wrote:

    Creating dashboards in excel is a good practice but its very limited.

    You can’t connect items to each other and for every excel sheet you need to do all work again.

    Also Filtering options in excel are very limited.

  2. Ajay wrote:

    I don’t know if I could agree with that. I have used Excel for virtually everything. The charting capabilities in Excel are awesome (once you know how to make them work), its widely available and requires no additional training.

    But if you show me something that can’t be done in Excel, I am willing to change that rigid stance of mine :-)

    Ajay

  3. John Kyle wrote:

    IThis is a great article and really shows that dashboards in Excel are not only NOT limited (as was suggested by the first comment), but are actually very powerful. The range of visuals that you show in the sample dashboards is great – everything from standard charts and graphs to Sparklines (now available in Excel 2010) and heatmaps.

    Another great thing about using Excel for dashboards is that end users are truly empowered to explore not only the data but also the formulas. With web-based dashboard tools, most users are not allowed to experiment with new and different formulas because they only have have limited “viewer” functionality. Excel is a superior solution in this case in that the end user is truly empowered to try new things and to explore what-if with both data and formulas.

  4. Victor wrote:

    Beautiful work man, keep rockin!

Subscribe

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

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel