Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard
Ever feel like you would have more use for Excel if you had a better way to present your data? Excel can be a very powerful program in the right hands, but sometimes a simple spreadsheet format isn’t engaging enough to make your data accessible to a reader. One way around this is producing a Dashboard; an environment that takes all the most important information from your document and presents it in an easy-to-digest format.
What Can I Use An Excel Dashboard For?
The main function of an Excel Dashboard is to transform a great deal of information into one manageable screen. What you choose to put on that screen is up to you, but this guide will instruct you how to best draw together different types of Excel content into a single environment. From there, you might choose to implement it to keep an eye on project progress at your place of business, or you might use it at home to track your finances — the same techniques can be applied to a broad spectrum of uses.
The first thing you’ll need at your disposal is the data you want to present. As an example, this particular Dashboard will function as a way for a student to look over the entirety of their assignments over a school year — but, again, your Dashboard can present whatever information you choose — so this is the first lot of data I’ll need.
As you can see, I’ve used a simple function to make the ‘Progress’ column display the figure entered in ‘Wordcount Progress’ over the ‘Total Wordcount’. This allows the user to quickly amend their figures as they continue to make progress, which will then be reflected in the final dashboard. We also need to use the SUM function to work out totals for ‘Wordcount Progress’ and ‘Total Wordcount’ — to do so, enter the following formula in cell C15 without quotation marks “=SUM(C3,C4,C5,C7,C8,C9,C11,C12,C13)”, then drag out from the bottom right corner of the cell so it populates D15 with a similar formula for ‘Total Wordcount’.
Add Some Color
Now it’s time to make this information presentable. The point of this dashboard is to give you immediate access to a range of high-level information, so a simple ‘traffic lights’ method works rather well. First, you need to right-click the top of the column bar that contains ‘Wordcount Progress’ and select ‘Insert’ to add in an extra, blank column.
This is where we’ll place the traffic lights. To do this, convert the ‘Wordcount Progress’ figure into a percentage using a simple formula. Enter “=(D3/E3)*100” without the quotation marks into cell C3, then drag the bottom-right corner of the cell down to cell C13 populate the rest of the percentages we’ll need — cells C6 and C10 won’t work correctly as they are title rows of the table, so just remove the formula from those individual cells. Test out your formulas by changing the ‘Wordcount Progress’ values in the D column and making sure that your C column changes accordingly.
Now you will use conditional formatting to change these percentages into legible icons. Select all of column C, then click on ‘Conditional Formatting’ in the ‘Styles’ section of the ‘Home’ ribbon. Then choose ‘Icon Sets’ from the drop-down menu, and select one of the three-tiered sets of colored icons. Data Bars and Color Scales could work too, but it all depends on what sort of information you’re wanting to show. Here, the important details are whether the individual essays are finished, in progress or haven’t yet been started, so a ‘traffic light’ format works well.
Now, we just need to make some tweaks to the rule that’s formatting your icons. With column C selected, click on ‘Conditional Formatting’ and then ‘Manage Rules’ in the drop-down. There should only be one rule there, so select it and click ‘Edit Rule’. Here, change the value assigned to the green icon to 100, and the lower boundary of the amber light to 1 — this will mean that any completed essay will display a green light, any essay in progress will display an amber light and any essay that hasn’t been started will display a red light. Finally, check the ‘Show Icon Only’ box so that the percentage itself isn’t displayed.
Once you’ve done this, the C column should be displaying appropriate icons for each value in the B column. It’s worth centering the column to make the icons look a little neater, as well as resizing it to fit the icon better, but this can be done at the end, when we bring everything together.
Next, create a version of a Thermometer Chart that will allow someone looking at this dashboard to get an idea of how much of the entire year’s work has been completed at a glance. There are many ways of creating a chart like this, but the following method will allow it to continually update according to changes in the ‘Wordcount Progress’ values. First, you’ll need to set up a data pool for the chart as in the following image.
The figures on the right represent the percentage increments that our thermometer will go up in, and are simply entered into the spreadsheet as integers. The lefthand column works out the corresponding word totals to those percentage values — and, as such, the formula pictured should be entered once in the top cell and then copied to the nine underneath it by dragging the bottom-right corner of the cell down, as before.
Next, enter “=$D$15” without the quotation marks into cell I3, and drag from the bottom right corner so that all the cells in this column down to I13 also contain this value. The cells should populate with the current ‘Wordcount Progress’ figure collated from all the individual values in the D column. Next, we’ll use conditional formatting once again to turn these values into a Thermometer Chart.
Highlight cells I4 to I13 — ignoring I3 for the moment — then select the ‘Greater Than’ option from ‘Highlight Cells Rules’ in ‘Conditional Formatting’. Type “=G4” into the ‘Greater Than’ dialog that you’re presented with, then choose ‘Custom Format’ from the drop-down to its right. At the next screen, choose the ‘Fill’ tab, and then a bright red swatch — although any color will do, really .
Now, the bottom few cells that you have selected should have turned red — but there are a few more steps before our thermometer will be finished. First, select cell I3 only and repeat what you did for the cells beneath it, this time choosing ‘Highlight Cells Rules’ then ‘More Rules’. There, you should select ‘greater than or equal to’ from the dropdown, enter “=G3” without the quotation marks into the field to its right and format the cell with a red fill as you did above. This ensures that it will be possible to ‘fill’ your thermometer.
Next, stop the values themselves from displaying in these cells. Highlight from I3 to I13, right click and select ‘Format Cells’ . Choose ‘Custom’ from the list and enter “;;;” without the quotation marks in the field marked ‘Type’. Press OK and your number values should have disappeared, leaving only the red of the thermometer.
However, we can do more than simply have the cells form a colored bar. Select the ‘Shapes’ tool from the ‘Insert’ ribbon, the choose the ‘Freeform’ solid shape from the ‘Lines’ subgroup. Use this to draw the outline of a thermometer to contain the red bar.
Draw a similar shape to the one above with the tool, linking it up to form a complete shape rather than leaving a gap as I have. Notice that we’re not drawing the thermometer bar itself, but everything else — our shape is simply going to block out the red coloring that we don’t want to see. Use the ‘Shape Styles’ menu to change the fill color to white, and the outline color and weight to something more appropriate.
Putting it All Together
Once you have all these elements in place, it’s a simple matter of compiling them all into your dashboard. First, rename the Sheet that you’ve been working on so far as ‘Data’ or something similar, then switch over to a different Sheet and rename that ‘Dashboard’. Next we’ll be using the Camera function, so if you haven’t already added that to your Quick Access Toolbar , it’s worth doing now so it’s handy.
To do this, access Excel Options and select ‘Customize’. From here, add the Camera command from the left column into the right column. Now you’ll have the Camera easily accessible, so we can use to to put the dashboard together.
The Camera itself is very simple to use; simply highlight the cells that you want to display somewhere else, then click the Camera icon and you’ll copy them across next time you click on a cell. From here on out, that ‘photograph’ of those cells will update as they change.
Use the Camera tool to take snapshots of the progress charts with traffic lights and your thermometer, transferring them over to the Sheet that you named ‘Dashboard’. Now it’s just a case of arranging and formatting things in a way that’s pleasing to you, as well as adding any other elements you might want. I added in a to-do list simply by creating it on the ‘Data’ Sheet and using the Camera to transfer it across.
Using these techniques, you can make a similar dashboard to cater to just about any sort of task. Once you become comfortable with the basics of advanced Excel techniques like Conditional Formatting and have a grasp of tools like the Camera, you just have to think about what information you need at your fingertips, and what’s the best way of presenting that information. Excel is more than just an office tool — there’s plenty it can do for you at home, too.
Do you have a tip of your own for how to take an Excel Dashboard to the next level? Or are you looking for more information about a particular part of the process? Get in touch by leaving a comment below.