Learn how to create dynamic chart data labels that can be changed with a slicer to display different metrics or calculations. Download the sample file to follow along.
Bottom line: Learn how to create this interactive chart where the data label metrics change based on a slicer. Skill level: Advanced I have written before about how I am NOT a big fan of stacked charts, and shared some alternatives to the stacked bar or column chart. Basically, it's hard to make comparisons between the series in the bars because of the uneven baseline. However, these types of charts are very popular and I am guilty of using them too. 🙂 But the stacked charts always seem to leave us with more questions…
These are great questions that can help identify trends and the need to analyze the data further.
So this chart attempts to help answer some of those questions. It allows the user to change the data label metrics with a slicer.
The user can then quickly see the amount, percentage of total, or percentage change for each series in each bar.
Of course you could put all these metrics in one label, but that typically leads to a very cluttered chart.
Setting up the dynamic labels isn't too complex, but it does require a bit of labor.
So the ingredients for this chart are:
In this article I will provide high level instructions on how to create this. You can download the file to follow along or modify for your own use.
Download the example file to follow along.
Warning: This file works best in Excel 2013 or 2016 for Windows. It will work in 2007/2010 but you will have to reassign the labels using a method mentioned in step 5 below. It also works in 2016 for Mac, but not 2011.
The first step is to create a regular stacked column chart with grand totals above the columns. Jon Peltier has an article that explains how to add the grand totals to the stacked column chart.
The source data for the stacked chart looks like the following. We will need to calculate the different metrics for the labels as well.
I created a section on the sheet for each metric: $Amount, % of Total, and %Change.
This is pretty easy and I won't go into the details of each calculation.
Typically a chart will display data labels based on the underlying source data for the chart. In Excel 2013 a new feature called “Value from Cells” was introduced. This feature allows us to specify the a range that we want to use for the labels.
Since our data labels will change between a currency ($) and percentage (%) formats, we need a way to also change the number formatting in the chart. Otherwise it will display a decimal number instead of percentage.
Fortunately we can use the TEXT function for this. The TEXT function allows you to feed it a value and specify the number format that you want to display that value in.
The TEXT function will actually return a text value to the cell, even though it looks like a number. So we can use this as the source of our data label and the chart will display the correct formatting.
We now have three sections that contain different metrics. Next we want to create one section that will contain the chart labels.
We will need to use some kind of lookup function to return the correct metric based on the slicer selection.
The CHOOSE function works great for this. CHOOSE allows us to specify an index number (1,2,3,…) and it will return a value based on the index number.
For now we will just add a cell that contains the index number, and point to the three metrics for each value in the CHOOSE formula. Eventually the slicer will control the index number.
The next step is to change the data labels so they display the values in the cells that contain our CHOOSE formulas.
As I mentioned before, we can use the “Value from Cells” feature in Excel 2013 or 2016 to make this easier.
You basically need to select a label series, then press the Value from Cells button in the Format Data Labels menu. Then select the range that contains the metrics for that series.
Repeat this step for each series in the chart.
If you are using Excel 2010 or earlier the chart will look like the following when you open the file.
This is because Excel 2010 does not contain the Value from Cells feature. Jon Peltier has a great article with some workarounds for applying custom data labels. This includes using the XY Chart Labeler Add-in, which is a free download for Windows or Mac.
The final step is to make the data labels interactive. We do this with a pivot table and slicer.
The source data for the pivot table is the Table on the left side in the image below. This table contains the three options for the different data labels.
It also includes the Index number that will be referenced in the CHOOSE formulas (step 4).
Add the Name, Index, and Symbol fields to the Rows area of the pivot table.
Then insert a slicer for the Symbol field. Checkout my free video series on pivot tables and dashboards to learn how to create a pivot table and add slicers.
The slicer can be formatted to match the theme of your chart. I also changed the Columns to 3 to show the slicer buttons horizontally.
The last step is to reference the Index number in the pivot table (cell F2) in the Index Number of the CHOOSE formulas.
When the user selects a slicer button, cell F2 in the pivot table will display the index number of the selected item. The CHOOSE formulas will automatically return the label metric of the selected item.
There are a lot of steps that go into creating this interactive chart. Fortunately you can download the sample file and modify for your use.
The example below shows a lighter gradient color theme. You can move the slicer into the chart area and also add instructions so the user understands how it works.
There are a lot of possibilities with this technique. This could be applied to other chart types besides the stacked chart.
What other charts or label metrics would you use for this dynamic chart? Please leave a comment below with your suggestions or questions.
