Creating an Excel Dashboard for data visualization is one of the key skills analysts in vast majority of organizations require. The good news is that Microsoft Excel has continued to make improvements that allow the process and available visualizations (charts) easy to generate. For instance, with Excel 2016, you have a Waterfall chart as a standard chart. With Excel 2013 and prior versions, creating a waterfall chart would require some tweaking, but yes, it is achievable (follow the link to learn the 4 steps). I will discuss some key concepts among the undermentioned. You can download a copy of the file used in this example at the bottom of this article.
- Custom calculations in Pivot Tables
- Adding interaction to dashboards with slicers
- Custom number formats and conditional formatting
- Using shapes for a prettier excel dashboard
- Applications of VLOOKUP + MATCH and named ranges
Given the data set above (sample), we would be interested in showing the Year-to-Date (YTD) sales and sales margin performance for a given (user) selected year as seen in the figure below. Further, in a dashboard, it is good practice to provide context for the various metrics one chooses to display. In this respect, we shall also indicate the YTD sales for prior year and the Year-on-Year (YoY) growth. There are myriad ways to achieve this but we shall be employing Pivot Table options to achieve the user interaction.
Prepping your data
To start with, given the sales date, we can generate the column for years using the following formula:
For the year-to-date (YTD) filter for our report, we would need to identify the dates that fall between “1-Jan- ‘year’” and today’s date (or equivalent of today’s date for prior years). We will therefore endeavour to identify a lower limit date (“1-Jan- ‘year’”) and an upper limit date. We shall apply DATE Functions as follows:
- First generate the start date, i.e. “1-Jan- ‘year’”. For instance, if the sales date is for year 2014, then you will have “1-1-2014”, and so on. This is the formula in cell J5.
=DATE ($I5, 1, 1)
- Generate a date (End date for this example) that is equivalent to today’s date for the various years. We’ll use this formula in cell K5.
=DATE ($I5, MONTH (TODAY ()), DAY (TODAY ()))
- We then combine (1) and (2) ab0ve using an IF and the AND function to generate a column that will be used as an YTD filter/slicer.
=IF(AND($F5>=$J5, $F5<=$K5), “YTD”, “Other”)
We will end up with four extra columns as shown below:
Having prepped the data as guided in the section above, we shall then compute these metrics (YTD):
(a) YTD Sales, Profit Margin & Margin as a percentage of sales –user selected year
(b) YTD Sales, Profit Margin & Margin as a percentage of sales –prior year
(c) Year on Year Growth (YoY%)
To achieve this, we insert a Pivot Table [from Insert Menu on the Ribbon] with the Year field in the Row labels of the pivot report grid, Sales field to the Values box and the YTD field as a Filter. With MS Excel 2010 and higher versions, once you insert a given column, say Sales, in a Pivot Table, you can rename it to a more meaningful name in the report. In this example, we have renamed it as Turnover [i.e. from Sum of Sales to Turnover]. We shall be doing this more often in our report to enhance how the report looks.
YoY Growth field:
In the report above, we have dragged sales column (the second time) to the Values section in the field list drop zone. Then as shown in the figure below, (1) right click the values in the report and (2) change the display under “Show values As” to “% Difference From…”. (3) from the dialog box that pops up, choose ‘Year’ as the base field and ‘Previous’ as the base item.
Profit margin and Margin as % of Sales
These two fields are computed as Calculated fields in Pivot Reports. To create the two fields, select any cell in the pivot report you have already created. From the Analyze menu (or Options menu for MS Excel 2010 and 2007) select Calculated Field… as seen in the figure that follows. Type “Margin” as the field name and insert the following formula (Double click a given field to include it in the formula).
In a similar manner, insert a calculated field for the margin as a percent of sales.
The Dashboard Display Page
For interactivity of the dashboard, we cannot link directly to the report generated in the previous section. We need to extract the info (using Excel formulas) based on the user selection on the dashboard. In our example, the main driver or report slicer is the year.
- Year Slicer:
Insert another pivot report but this time round we include the Year column only in the Report Filter. Further, go to Analyze menu and insert a slicer by clicking the Insert Slicer icon…
The cell with the year (R5) in the figure above is named selYear (I will write on how to name a cell or a range of cells in a different article).
- In another section, we shall use formulas to extract data to be displayed in the dashboard. When a user selects a given year from the slicer on the dashboard page, we will display the Turnover, YoY%, Margin, and Margin as a % of sales -for the selected year versus prior year.
- In cell Q23, we type “=selYear” so that this is controlled by the user. In cell Q24, we use the formula “=selYear-1”
- At this point, we shall employ VLOOKUP and the MATCH function to retrieve the above metrics based on the selected year. The formula in cell R23 is as follows:
=IFERROR (VLOOKUP ($Q23, $Q$14:$U$18, MATCH(R$22,$Q$14:$U$14,0), FALSE), “n.a”)
The MATCH function is used here to provide the column-index required by the VLOOKUP. We have standardized the labels/headers (YoY Growth, etc.) to aid the MATCH function. The IFERROR function traps errors that may arise and displays ‘n.a.’ instead.
- Having used the formula in the step 4 above to return the metrics to be displayed in the dashboard, we’ll use the following to build the dashboard page:
- Picture Links and/or camera tool
- Colour coding
The Dashboard Page
Open a new worksheet and do the following (details on various techniques will be discussed in a separate article):
- Go to View Menu and remove gridlines (uncheck the Gridlines box)
- Select the Year Slicer inserted above -cut and paste it in this worksheet
- In the Insert Menu, insert various Shapes from the Illustrations group. For this example, we have used these shapes: rectangle, the pentagon, a text box and an isosceles triangle. The idea is to layer the shapes cleverly by adjusting each shape’s “Bring to Front / Send to Back” positions. You will love inspirations by datapigtechnologies for stylish Excel dashboards using shapes!
- The YoY Growth is a linked picture. To do this, go to your analytics page and copy the cells you want to link. On the dashboard page choose Linked Picture as seen below.
- Once you have neatly arranged the shapes and linked them to the relevant numbers, SELECT ALL the shapes (this will activate the contextual Format menu for shapes), then choose Group. This helps to move the shapes without interfering with the feel of the dashboard.
Take a look at the completed Excel Dashboard and play with it!…
Do you use shapes for Excel Dashboards? Share your experience and tips using comments.