Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard

Brad Jones 10-11-2014

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 15 Excel Spreadsheet Templates for Managing Your Finances Always keep track of your financial health. These free Excel spreadsheet templates are the tools you need to manage your money. Read More  — the same techniques can be applied to a broad spectrum of uses.

Getting Started

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 Excel-Formulas: Ask Your Excel Questions To Experts Read More 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 How to Save Time in Excel by Using the Fill Handle Mastering the Fill Handle is the first step to becoming an Excel wizard. Read More 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 3 Crazy Excel Formulas That Do Amazing Things Excel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel. Read More 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.



Thermometer Gauge

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 How to Create Self-Updating Microsoft Excel Charts in 3 Easy Steps Self-updating Excel charts are huge timesavers. Use these steps to add new data and watch them automatically show up in a chart. Read More 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 How To Pick A Color Scheme Like A Pro Colors are tricky. It might seem easy enough to pick a handful of colors that look good together, but as any designer who has spent hours tweaking shades will tell you: it's tricky. Read More .


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’ 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office "Get that report to me, ASAP!" A core set of Microsoft Excel and Word tricks can make this task less stressful and help you whiz up a stylish report in no time. Read More . 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 How to Customize the MS Office 2010 Quick Access Toolbar The Quick Access Toolbar has been a part of MS Office since the 2003 version, but it has come to the forefront since the introduction of MS Office 2007 and more prominently with MS Office... Read More , 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 How to Create a Pivot Table for Data Analysis in Microsoft Excel The pivot table is one of the single most powerful tools in Microsoft Excel. Learn how to use the pivot table for data analysis. Read More 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 10 Excel Templates To Track Your Health and Fitness Read More — 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.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *