10 Useful Steps to Create Interactive Excel Dashboard (Easy)

Create Interactive Excel Dashboard – Most of us probably rely on our trusted MS Excel dashboard for the day to day running of our businesses, but like many, we struggle to turn that data into something that will actually interest people and want them to know more about it. So how do you attain this seemingly impossible goal? This is where the Interactive excel dashboard comes in. The first question that we must answer before we delve into the depths of it is what an excel Dashboard is? A dashboard may be described as a visual display of the important information that an individual needs to convey to the client to achieve one or more objectives that can fit entirely on a single computer screen and hence can be monitored at a glance.

It is a comprehensive as well as complete visual report or analysis of your project, which can be shared with other people concerned. Creating an excel dashboard can be tedious, time-consuming, and difficult if you do not have the proper knowledge about how to go about doing it. But fret now; that’s where we enter.

Dashboards are not native to Excel, as they can be created on PowerPoint as well. Excel Dashboards offer a more dynamic approach to presenting data than the more linear and un moving nature of PowerPoint dashboards. An interactive dashboard in excel is basically slices of visualization which enables your data to tell a story. A dashboard is only useful if they are dynamic, easy to use, and compatible with the PC you are using. Before making a dashboard, you need to consider the decisions the end-user will make based on the data, look, and feel. You will also need to keep in mind how much they are familiar with the data and its context. For example, a monthly report for your boss who is already familiar with everything will look very different from the one you make to pitch a new idea to a potential client.

Another thing to remember is that the data should be the star of the excel dashboard. There is no need to clutter the screen with unnecessary components, so keeping it simple is the best way to go. You will also want to strike a perfect balance between making it look striking (so that it holds your audience’s attention) but not so stylized so that it takes away from the data to be presented. When we tell a story, we must always consider the tastes and distastes of the audience and adapt our presentation accordingly. For example, if you are presenting to a very formal organisation, you should do your best to keep the excel dashboard as simple as possible without compromising on subdued attractiveness.

Armed with the right knowledge about how to go on about creating a stunning excel dashboard, you can create an excel dashboard of your own without it being tedious or difficult! We provide you with a step by step analysis below:

1. Bringing in data

Sure, Excel is very useful and flexible. But to create an excel dashboard, you cannot just paste some data and add a few charts. You need to maintain it, update it, and impose some structure to that data. Usually, you don’t have to enter the data directly into the spreadsheet. You can copy-paste the data, but the best option is to bring in data via an external source. You can use it to connect your excel dashboard to Access or Oracle. A good practice is to limit the amount of data you bring in. As we’ve seen before, data can be brought in with two basic structures: a flat file and a pivot table. A flat file is generally smaller, where as a pivot table is a large file (As a thumb rule). Both have their pros and cons, which one must figure out only through experience.

2. Select a background

Select an appropriate background that will bring your excel dashboard to appear attractive without focusing away from the data. Your data should be the star. You can go with subdued shades like blue, grey and black, or you can take it up a notch like orange, green and purple. It is your choice, but keep in mind the audience you will be presenting it to. I suggest you stick to subdued hues if it is for official purposes.

3. Manage your data and link it to your excel dashboard

If you are using a pivot table, use the GETPIVOTDATA function. If you use a flat-file, there are a number of formulae you can use like DSUM, DGET, VLOOKUP, MATCH, INDEX or even a dew math formulas like SUM, SUMIF, etc

But be careful here, do not punch in the formula after formula. Fewer formulas mean a safer and more reliable excel dashboard which is also easier to maintain. You can automatically reduce the formula number by using pivot tables.

Also, another important point is that you should name all your ranges. Always, always document your work. Simplify your work by making your excel dashboard formulas cleaner.

4. Use Dynamic Charting

Dashboards that a user can’t interact with don’t make much sense. All your excel dashboards should have controls that will enable you to change the markets, product details, and other nitty critters. What is most important is that the user must be able to be in complete charge of his or her own excel dashboard and make changes whenever and wherever they want.

If you are creating interactive charts, you will need dynamic ranges. You can do this by using the OFFSET() function. You can also add a few cool things to your excel dashboard, like greeting the user and selecting the corresponding profile when they open the excel dashboard. All this can be done using macros. All you need to do is record a macro, add a FOR NEXT or a FOR EACH loop. If you have never recorded a macro before, there are many sites online that give you perfectly tailored macros as per your needs.

Creating a macro is easier than it seems. To do so, you much click right anywhere on the Ribbon. Click customize Ribbon. In the drop-down menu that opens up, select main tabs. Check the Developer check box. Click OK. You can now find the Developer Tab next to your last tab on the Ribbon. Now click on the Developer tab – Insert. In the ActiveX Control, click on the Command Button. Drag this button to your active cell.

This is all you have to do, most of the time:

1. Define what cells should be selected, using the RANGE function;
2. Use a variable (i) instead of row number;
3. Add a loop.

It may not seem very simple, but rest assured that it is not rocket science either. You need not be a programmer to know Excel macros. Search the internet, and you will find it. Suppose you want your excel dashboard to display a particular set of content when a certain user accesses it; you can use a set of particular macros for the same.

5. Design your excel dashboard report

If you are still using Excel 2003 or 2007, their default charts are not very attractive, so I suggest you avoid them like the plague but make sure to use acceptable formats. Excel 2010 and 2013 are a lot better, but they still need some work. Keep this in mind, a chart is used to discover actionable patterns in the data, and you should do your best to bring out most of it. This also means that you should remove all the jazzy, glittery stuff which adds no value to your excel dashboard. Instead, you can create a hierarchy of focus and contextual data that is relevant and create a form of basic interaction, if not much.

6. Dashboard Storytelling

Storytelling which is pregnant with data, is the best kind that there is. With better access to data and better tools to make a point, we are able to recover a lot of data types. However, even though data is good, it is great, but you must not reveal all of it at once. When deciding how to make an excel dashboard, start by reviewing the purpose of the said dashboard. The goal shouldn’t be to overwhelm the audience with data but to provide data in such a form so that it gives them the insight you want them to have. I think this is true for all data-based projects.

Let your audience explore the data on their own by offering them their own filters and controls. This is where interactive visuals come into the picture. If you are a newcomer to interactive excel dashboards, you can still spot trends and learn how to build up a stunning dashboard. If you are a pro at it, you can drill down deeper into the data for better charts.

7. Select the right kind of chart type

Before we decide which chart to use in our excel dashboard, let us have a review of all the charts used in dashboards and when to use what.

1. Bar Charts

Bar charts, as we all know, are bars on the x-axis. One of the most common misgivings about excel dashboards is that the more is better; the truth is, that is seldom true. Bar charts are simple and very effective. They are particularly useful to compare one concept to another as well as trends.

2. Pie Charts

These charts, in my personal opinion, should be used very carefully and sparingly. Well, no matter how you feel about pie charts, you should only use them when you need a graph representing the proportions of a whole. Use with extreme frugality.

3. Line Charts

These are one of my favorites. They are so simplistic. These charts include a serious of data points that are connected by a line. These are best used to show developments over a certain period of time.

4. Tables

Tables are great if you have detailed information with different measuring units, which may be difficult to represent through other charts or graphs.

5. Area charts

Area charts are very useful for multiple data series, which may or may not be related to each other (partially or wholly). They are also useful for an individual series that represents a physically countable set.

So choose wisely, and you will be good.

8. Colour theory

I love colours. Who doesn’t? Colours in an excel dashboard make it livelier as opposed to the drab and overused grey, black and white. I could write an entire book on how colour theory works, but well, that’s already fine. You must know which colours work together and which do not. For example, you cannot pair bright pink and red together unless you want an assault on the eyes. One thing you must keep in mind while selecting a colour coding, that 8% of men and 0.5% of women are colour blind.

Most people can perceive colour but cannot correctly distinguish between two shades of the same colour. These people can perceive changes in brightness, though, just like you and me. Avoid having shades that overlap, like the example I gave above. That would not only look ugly but also be completely useless for users we discussed above.

9. Dashboard Design

So now you know how and when to use each chart and the colours to pair them with. But one more thing that is critical to this is where you place everything on the excel dashboard. It is essential that everything is strategically placed. Data that you want to compare must be arranged with that in mind.

10. Have fun and let your creativity flow

Once you have understood your target audience, the purpose of your excel dashboard and have selected the correct charts and designs, you should be good. By the end of this step by step informative article, you can go from a blank spreadsheet to a fully functional spreadsheet with stunning interactions. So go ahead and give it a try. Good luck!

Recommended Articles

Here are some articles that will help you to get more detail about Create Interactive Excel Dashboard, so just go through the link.

1. Best Basic Excel Formulas
2. Excel macros
3. Features of 2016 Excel Workbook
4. Advanced Excel Formulas and Functions