Dashboards in Excel
All About Dashboards in Excel
The article will cover everything you need to know about Excel Dashboards and their applications and guide you in creating your own Excel Dashboard. But first, let us have a look at what a Dashboard is before we jump on to a Dashboard in Excel.
What is a Dashboard and what is it used for?
A Dashboard is a single screen tool that helps the user make sense of the complex and vast amount of data using data visualization. Raw data, especially if it is available in a huge amount, as is the case with most businesses, is very difficult to understand. Making sense of raw data without any kind of visualization tool is tedious and almost impossible.
A Dashboard does the crucial task of transforming raw data into meaningful information. Its task is to organize and display raw data from multiple sources on one single screen for an effective overview.
The data visualization which is done using tables, line charts, bar graphs, pie charts, and other metrics tools is highly effective for a user as well as for the layman to whom the user presents the data. By the effective use of dashboards, one can:
- Track multiple sources of data
- Monitor the business or activity
- Analyze/ Evaluate key performance indices
- Shortlist the variables with the most effect
- Make quick decisions targeting fixed results
Dashboards have become pre-requisites to almost all organizations/ businesses in monitoring their performance, growth statistics, projected operations, etc. and company meetings targeting the operations and progress are almost exclusively delivered through the medium of tailored dashboards. Now let us take a look at what Excel Dashboards are.
Dashboards in Excel
Excel is an incredibly useful tool for those looking for any sort of numerical representation. However, the Dashboard feature in Excel makes it not just useful, but indispensable in a business organization. An Excel Dashboard not only has all the necessary features desired in Business Dashboards but also possesses the advantage of being highly interactive.
What the Excel Dashboard effectively does is to transform the reams of complex data in your spreadsheet into a compact visual through the visualization tool of your choice be it a line chart, bar graph or a table; so that the data becomes highly understandable and can be efficiently utilized.
Excel Dashboards have as many uses as you can imagine, from keeping tabs on KPIs, comparing two different business models, presenting a data-driven business operation to predicting business growth in the next few years.
The various areas in which these Dashboards would help include; Data/ Business Analytics, Marketing, Sales, Call Centres, Social Media, and Healthcare.
Through a Dashboard, the user will instantly be able to answer questions regarding growth, sales projections, regions performing better or poorly, areas that need to be managed, etc.
The Excel Dashboards can also be made interactive such that, on changing the elements, the data would dynamically get updated. This is of immense benefit in designing long-term business plans or making projections about x years into the future.
Prerequisites to Dashboard Creation in Excel
In order to begin creating your own excel dashboard, it is recommended that you have a working knowledge of Excel and that you should familiarise yourself with the components in excel that will be listed below. Creating an Excel Dashboard is not very difficult and can be accomplished even by a layman, provided that he/ she is able to use the following components in excel:
- Excel Charts
- Excel Data Model
- Excel Pivot Table
- Excel Workbooks
- Excel Power Pivot Tables and Pivot Charts
- Excel Power View Reports
Creating Your Own Excel Dashboard
In order to be effective at data analysis, you must be an expert at the creation of Excel Dashboards. It is a much-needed skill in today’s data-driven business scenario. Here’s a step by step process in Creating your own Excel Dashboard. You will be able to create Excel Dashboards in all versions from 2007 onwards, in the manner specified below. The Slicers feature, however, is available from Excel 2010 onwards and the Map Charts Feature is available in Excel 2016 (Office 365). However, even if you don’t have these features, there is a lot that you can accomplish. Let’s begin:
Step 1: Readying the Data
- The primary task is to get all the data that you need to represent in an excel workbook. Copy and paste it into the excel workbook.
- Next, you need to organize this excel data systematically into correct rows and columns that such as Monthly Expenses for Travel, Food, and Accommodation as shown below.
- Now you need to create two additional tabs apart from the currently existing one. The current tab is to be named as ‘RAW DATA’. The second tab can be named ‘USABLE’, which will contain only that data out of the total raw data which is to be used for the creation of the Dashboard. The third tab is where the Dashboard will be and it is to be named as ‘DASHBOARD’.
Step 2: Deciding Upon Your Visualization Tools
- There are numerous visualization tools available in Excel which include; scatter plots, bar graphs, line charts, column charts, pie charts, waterfall charts, etc.
- The choice of which tool to use depends upon what you intend to do with your data. Whether you want to give a sales projection or demonstrate the rising demand of your competitor in the local market, the choice of data visualization tool makes a great difference, during presentation hour.
- It is up to you to choose the tools that would highlight your data in the best possible way.
- If you are unable to decide, go with a short sample of data and try using different tools one after the other. You are bound to get the tool of your choice.
Step 3: Building your Representation (Chart/ Graph, etc.)
- Let’s suppose that you decide to use a chart. The first step is to filter your raw data so that you can get the appropriate data for putting into your chart.
- To filter, highlight your entire data set, in the Raw Data tab, go to the ‘Data’ option in your Excel toolbar and then click on ‘Filter’. You can now filer the required data, and copy it onto the ‘USABLE’ tab that you have created.
- Now, to create your chart, go to the ‘DASHBOARD’ tab that you have created, click on the ‘Insert’ option in the toolbar, and select the type of chart that you want from the menu. You may choose a pie chart, line chart, scatter chart, Radar chart, etc.
- If you are unsure and want recommendations, Excel can even do that for you. You simply need to select all the data that you need to represent, go to the ‘Insert’ option in the toolbar and click on ‘Recommended Charts’. Having chosen a line chart, for example, you will get a blank box.
Step 4: Putting in the Data
- The next step is to plug in your data. Right-click the blank box that you got in the DASHBOARD tab and choose ‘Select Data’.
- Now select the data from your ‘USABLE’ tab and you will instantly get your line chart representing the data columns.
- For other chart types involving horizontal and Vertical Axis, you can plug in the data appropriately to get the desired representation.
Step 5: Putting in the Final Touches
- Each chart needs some fort of final touch-up for better visualization.
- Choose a different color for each variable involved. Choose contrasting colors when comparing the trend lines of different data points.
- Put in headings, appropriate naming and also the units of the data.
- Finally, crosscheck the data for any discrepancies that may have been the result of manual errors.
Step 6: Create Multiple Data Representations to Complete Your Dashboard
- The above process needs to be repeated with other parts of your raw data to obtain a complete representative report.
- An ideal Dashboard has various visualization tools on a single screen to effectively represent a large volume of data at a single excel tab.
- Use Pivot Tables to insert especially required data from among your raw data points. Macros can be used to automate the tasks and Slicers are used as one-click visual filters. All these advanced tools may be used to make the Dashboard more effective and powerful.
- At this stage, you can also create a rough map of the various data representations that you will need on your Dashboard.
- After you have added all the elements to your ‘DASHBOARD’ tab, do the final customization part, adding colors and animation, as required.
An Excel Dashboard is a very powerful skill to add to your assets and with it, you may literally Excel at your work/ business. Using this powerful technique is a sure shot way to not only make sense of large data before you but also to make a favorable impression on your superiors.