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 Interactive excel dashboard comes in. The first question that we must answer before we delve into the depths of it is what is a excel Dashboard? A dashboard may be described as a visual display of the important information that an individual needs to convey to the client in order to achieve one or more objectives which 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 a excel dashboard can be tedious, time consuming as well as 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 as compared to the more liner as well as 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 as well as 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, the look and feel of it. You will also need to keep in mind how much they are familiar with the data and how much context they have. For example, a monthly report for your boss who is already familiar with everything is going to look very different from the one which 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 a excel dashboard of your own without it being tedious or difficult! We provide you with a step by step analysis below:
-
Bringing in data
Sure, Excel is very useful and flexible. But to create a excel dashboard you cannot just paste some data and add a few charts. You need to maintain it, update it and you must impose some kind of 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.
-
Select a background
Select an appropriate background which will bring your excel dashboard appear attractive without taking focus 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.
4.9 (2,356 ratings)
View Course
-
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 formula after formula. Fewer formulas mean a safer and a 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.
-
Use Dynamic Charting
Dashboards that a user can’t interact with don’t make much sense. All your excel dashboards should have controls which will enable you to change the markets, product details as well as 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 a large number of sites online which 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 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:
- Define what cells should be selected, using the RANGE function;
- Use a variable (i) instead of row number;
- 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.
Recommended courses
-
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. What you can do instead is create a hierarchy of focus and contextual data that is relevant, and create a form of basic interact if not much.
-
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 a 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 in 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.
-
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.
- Bar Charts
Bar charts as we all know are bars on the x axis. One of the most common misgiving 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.
- 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 proportions of a whole. Use with extreme frugality.
- 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.
- Tables
Tables are great if you have detailed information with different measuring units, which may be difficult to represent through other charts or graphs.
- 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.
-
Colour theory
I love colours. Who doesn’t? Colours in a 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 dine. 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% or women are colour blind.
Most people can perceive a colour, but cannot correctly distinguish between two shades of the same colour. These people can perceive changes in brightness though, just like me and you. 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.
-
Dashboard Design
So now you know how and when to use each charts, 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.
-
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 to Create Interactive Excel Dashboard so just go through the link.
- 10 Awesome Basic Excel Formulas Useful For Any Professionals
- Important Of Powerful Excel macros – Easy to Learn (Advance)
- Important of Excel Functions- Charts and Graphs
- 8 Awesome & Useful Features of 2016 Excel Workbook
- Top 25 Useful Advanced Excel Formulas and Functions
- 10 Easy Steps to Create a Pivot Chart in Excel 2016
- 9 Amazing Steps on How to Use Microsoft Excel Skills
