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:

**=YEAR (F5)**

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:

**The Analytics**

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

**and the YTD field as a**

*Values box***. With MS Excel 2010 and higher versions, once you insert a given column, say Sales, in a Pivot Table,**

*Filter**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 “

**” to**

*Show values As***”. (3) from the dialog box that pops up, choose ‘Year’ as the base field and ‘Previous’ as the base item.**

*“% Difference From…***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

**for MS Excel 2010 and 2007) select**

*Options menu***as seen in the figure that follows. Type “Margin” as the field name and insert the following formula (**

*Calculated Field…**Double click a given field to include it in the formula*).

**=Sales-‘Purchase price’**

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

**icon…**

*Insert Slicer*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:
- Shapes
- 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
inserted above -cut and paste it in this worksheet*Year Slicer* - In the
**Insert Menu**, insert variousfrom the*Shapes*group. For this example, we have used these shapes:*Illustrations**rectangle*, the*pentagon*, a*text box*and an*isosceles triangle*. The idea is to__layer the shapes cleverly__by adjusting each shape’s “” positions. You will love inspirations by*Bring to Front / Send to Back*for stylish Excel dashboards using shapes!__datapigtechnologies__

- 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 chooseas seen below.*Linked Picture*

- 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. This helps to move the shapes without interfering with the feel of the dashboard.*Group*

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.

0