Dashboards are reporting tools meant to help users or decision makers make business decisions. They provide a simple view that presents all information to understand the key results, show trends and risky areas. By the way, the term “dashboard” is a metaphor for a car dashboard or the cockpit area at the front of an aircraft, from where a pilot controls the vehicle.
The first thing you need to know before creating a dashboard is to know why. You have to ask yourself the key questions: what do you want to show? who will be the audience? what are your KPIs? where will the data come from? Discuss the expectations with every stakeholder to agree on the content of the dashboard before you create it. Also, you will have to investigate where to find the data for it.
Some of the basic rules for creating a good dashboard would be as follows;
- Sketch and visualize – Before starting with the dashboard, it would be a good idea to create a rough sketch based on the audience, message, story to convey the message and data. This way, we can decide on which visualization to communicate the data effectively.
- Make it simple and visually appealing – Don’t include too much information & fancy formatting. Let the key message be delivered instantly.
- Proper formatting – Adjust column & row width along with white spaces so that it fits into one page ,i.e, either for display/print. Place the vital component to the upper left always and use descriptive titles to each of the component. Proper alignment and grouping also matters.
- Make it highly dynamic – This will allow the users to dig into your data easily. In case the user is looking for the statistics of a specific week or month or a product, he should be able to navigate to the data easily and quickly.
- Make it easy to update – See to it that you don’t spend many hours a week on the updation of the dashboard. Make it almost instantaneous.
In order to design a dashboard, mainly we will need the following three types of sheets;
- Data Source sheets would be in a database format that contains raw data and your calculated columns if any in a tabular format.
- Pivot Table sheets aggregate your database to prepare them for different visualization ,i.e, mainly for the pivot charts.
- The Dashboard sheet is the only sheet needed for visualization and it is where you will have your pivot charts and/or pivot tables, slicers etc.
Let’s look into a dashboard based on the above structure. This is a dashboard sheet which highlights the analytics data about a website. The details can be seen in the below picture.
Data Source sheets
The data source sheets contain the data extracted from an excel/csv/text file, access db, sql db or the web. The initial raw data will have inconsistencies which needs to be filtered to the final tabular format so that we can start working on the other sheets. The importance of tabular format is that your data will be easy to update. A couple of productivity tips on tables would be to use Ctrl+T key to create a new table from raw data, Ctrl+End key to move to the end of table, Formulas tab>Name Manager to check the name & range of a table. Refer to my blog here to read more about filtering the raw data.
Pivot Table sheets
The same source data can be analyzed on different parameters using separate pivot tables in separate sheets and converting them to pivot charts in the dashboard sheet or one can just display the pivot table values in the dashboard sheet. Here everything depends on how we have designed our dashboard to start with. The analysis of data in the pivot tables may include adding an additional column using Analyze>Fields, Items, & Sets and making use of the formula feature. Also make sure your pivot table refers to the name of the table you have created for your data source, so you have the benefice of flexibility for future updates to the data source. Any changes to the data source will be reflected in the pivot tables instantaneously, thereby making the dashboard very easy to update. Refer to my blog here to read more about pivot tables.
The Dashboard sheet
The dashboard sheet will be dependent on a lot of other sheets, namely the source data sheet, other derived sheets with tables or charts. The main thing about designing a dashboard is that we need to structure it for the kind of screen or printing paper where it will end up. Starting with a rough sketch or an excel dashboard grid would be a good idea. Some simple dashboards example ideas are; create rectangular shapes in the dashboard sheet and link it to the totals in the pivot table sheet, create a single pivot table in the dashboard sheet with various slicers etc.,
Charts form an important component of dashboard. Choosing the right chart to convey the information regarding data will make the dashboard look appealing to the audience. For example; bar charts are used to compare values across multiple categories, histograms are used to display data grouped into bins, scatter charts are used to detect relationships between two separate values, line charts are used to display pattern of measures overtime.
In our dashboard sheet above, we can see that it has been divided into sections; namely the upper chart area, the lower table area and the slicer on the right to make the dashboard dynamic. The upper line charts are derived from the pivot tables in other sheets and the lower tables are directly linked to the pivot table cell values in other sheets. The sparklines also present in the lower area of the dashboard is inserted using Insert>Line or Column option, and is linked to the pivot tables in the other sheets. Another way of displaying a table in the dashboard is to copy the pivot table in the other sheet and use the Paste Special>Linked Picture option to paste the whole pivot table in the dashboard that will get updated automatically in case of any changes to data in the other sheet.