These are great for drawing attention to a single metric like the total sales in your dashboards.
In this post we’re going to get extra fancy and add some little additions to the cards like a percentage change over the previous year and a small line chart in the background. To build these fancy KPI cards, we are going to pull out pivot tables, pivot charts, GetPivotData, shapes and custom number formatting from our toolbox of Excel tricks.
Data Setup
In this example I’m going to be using the AdventureWorks data set which has order data for a fictitious bike shop. This data contains the Order Date and Order Total which we will be using for the data cards.
Pivot Table Setup
We will then need to create two pivot tables with this order data. This can be done by selecting a cell inside the order data and going to the Insert tab of the ribbon and selecting the PivotTable command.
For the first pivot table, we need to add the Order Date field into the Rows area and two instances of the Order Total field into the Values area. This should group the order date into years, quarters and months automatically. We will only need the year and month grouping, so we can remove the quarter grouping from the pivot table.
If Excel does not group our order dates automatically, the dates will be listed down the rows of the pivot table and we can right click on them and choose the Group command from the menu. The first instance of the Order Total in the Values area of our pivot table will be a regular SUM. For the second instance we will create a percent difference calculation.
We can right click on the numbers in our second Order Total field and then choose Show Values As ➜ % Difference From.
Then we can choose Years as our base field and Previous as our base item. This will calculate the percent difference from the current year to the previous year for each month.
For our second pivot table, we will add the Order Date Month grouping to the Rows area, the Order Date Year grouping to the Columns area and the Order Total field to the Values area. Both pivot tables can then be filtered on the years to show only the two years of data which we want to compare.
We will also need subtotals in the first pivot table. We can add these by selecting the pivot table and going to the Analyze tab ➜ Subtotals ➜ Show all Subtotals at the Top of Group.
Extract The KPI With GetPivotData
Now we can use the GetPivotData formula to extract the numbers from our pivot tables which will feature as the key performance indicator. In our case this will be the total orders for 2016 and the percent difference from 2016 to 2015.
First, we need to turn on the GetPivotData feature. This is the (sometimes annoying) feature that automatically creates a GetPivotData formula when trying to reference a cell inside a pivot table. This should be enabled by default, but in case it is not, go to the Analyze tab and click on the small arrow next to the Options then select Generate GetPivotData (it should have a small check mark next to it when it’s on).
In a cell outside of the pivot table, we can create a formula that references the 2016 subtotal in the pivot table. This will automatically create a GetPivotData formula to reference the value inside the pivot table.
In another cell outside of the pivot table, we can create another GetPivotData reference to the 2016 percent difference subtotal.
We will need to format these two values as the formatting in the cells is how they will be formatted in our data cards. The order subtotal can be formatted as a currency with no decimal places. Go to the Home tab and select Currency from the Number section of the ribbon and adjust the decimal places to show none.
The percent difference subtotal we will add a custom format with up and down arrow symbols for positive and negative values. Press Ctrl + 1 to open the Format Cells dialog box.
Creating The Data Card
We will need to add in 4 rectangle shapes to our Excel worksheet. One is for the background, two will be to display the subtotals and one will be to display a title heading. Go to the Insert tab and click on the Shapes button to select from all the various shapes. Select the Rectangle shape and then click and drag in the sheet to draw the shape.
With the 4 shapes we will format the fill and outline based on its purpose. If we create the shapes in this order then we won’t need to adjust the layering order later on. The background shape will be underneath the other shapes.
Subtotal And Percent Difference Subtotal Data Cards
Now we can add the subtotal to our rectangle with no fill or outline. We need to do the same with our second rectangle with no fill or outline and our percent difference subtotal value.
Create A Data Card Title Heading
Our last rectangle shape will contain our data card title heading. We can simply select the shape and start typing our title. Then we can go to the Home tab to increase the font size, change the font colour, middle and center align the text.
Arrange Align And Group The Shapes
Now that we’ve got all the shapes, we can select and drag them around to arrange them like the above picture. When we’re satisfied with the approximate location, we can align them perfectly with the alignment tools. Select all the shapes (select one then press Ctrl + A), then go to Drawing Tools Design tab ➜ Arrange section ➜ Align ➜ Align Center. We can also group them together so we don’t accidentally mess up the alignment. With all the shapes selected go to the Drawing Tools Design tab ➜ Arrange section ➜ Group ➜ Group.
Adding A Line Chart
This is where we’re going to use our second pivot table. Select it and go to the Analyze tab and press the PivotChart command.
Then choose a line chart from the options.
We also want to remove all the buttons, axis, grid lines, move the legend to the bottom, and remove the fill and outline.
Arrange Line Chart Over The Data Card
Now, we can place the chart over the data card. Select the chart and go to the PivotChart Tools Format tab ➜ Align ➜ Snap to Shape. This will make it easy to line up the edges of the chart to the edges of the data card background. Now drag the chart edges so they are lined up with the background. The chart will be the top layered object since it was created last. We want this to be just underneath the large total order number. With only the chart selected go to the PivotChart Tools Format tab ➜ Send Backwards ➜ Send Backwards until it is behind the order total number. Now we can group everything together. Select every object and go to Drawing Tools Format tab ➜ Group ➜ Group.
We now have a fancy data card which can be moved around and resized as one object.
Add Slicers To Control The Data Card
Since the data card is all based on pivot tables, we can now add slicers to control the data card!
Select either pivot table and go to the Analyze tab and select the Insert Slicer command.
We can select any field(s) in our data for a slicer. In this example we’ll add a slicer for our products.
We also need to connect the new slicer(s) to the other pivot table. Right click on the slicer and select Report Connections from the menu, then check off both pivot tables in the Report Connections dialog box. Now the data card will show the 2016 total orders for any set of products selected in the slicer as well as the percent change from 2015.
Conclusions
With a little work and creativity, we can make some pretty cool stuff for our Excel dashboards. Setting up the pivot tables correctly to extract the metrics we want to display in the data cards is essential to the whole process. The pivot tables take care of summarizing the key metrics of our data. The shapes can’t directly reference the values in our pivot tables though, so it’s necessary to use the GetPivotData formula as an intermediate step to reference the values. I’d love to hear about what kind of cool dashboard visuals you’ve been able to make. Let me know in the comments!
