Recently I was thinking that sometimes when you make a Stacked Column Chart in your Excel Dashboard, you may want to hide some of the smaller values. This is especially true when some of the values are really small and the chart data labels may overlap. For example, in this chart you see that we are not showing a value for Kittens in January and also not showing a value for Puppies in March.
So it got me thinking about how we can do this in Excel. I also thought, how can we make a the labels conditional based on a value that I enter in the spreadsheet?
And I came up with this solution…
Breakdown
1) Create a New Conditional Data Label Range
2) Create Chart and Add Data Labels
3) Change Data Labels to the New Conditional Data Label Range
Step-by-Step
1) After you create your Chart Data Range, you now need to create the conditional labels data range. Given my example below, I have put the chart data in cells A1:C4 and then I put this formula in cell
E2 =IF(B2>=$B$6,B2,””)
Then copy this formula down and over into cells E2:F4
Then put your Conditional Label Threshold in Cell B6. In this case, I used 25.
2) Create your chart by highlighting cells A1:C4. Then go to the Insert Ribbon and Chose the Conditional Stacked Column Chart.
3) Select the chart and then insert data labels by going to the Layout Ribbon and choosing Data Labels from the Labels group.
Then select each individual data label by clicking on each label 2 times. Once you have selected each individual data label, press the “=” (equals sign) and then select the corresponding New Data Label that you created in cells E2:F4.
Repeat this process for each of the individual data labels in the Excel Dashboard Chart.
Now each of the data labels that are greater or equal then your Label Threshold (cell B6) will now conditionally appear in the chart as you change the threshold.
See a detailed video tutorial below. Also, please check out this additional demonstration of how to make your own custom data labels.
How-to Add Custom Labels that Dynamically Change in Excel Charts
Video Tutorial:
Please leave me a comment and let me know what you think of this post. Also, please sign up for the blog RSS feed so you get the latest post.
Steve=True
I wanted to comment and say thank you.
You are quite welcome. Thanks for being a reader and leaving a comment.
Hi, this taught me an aspect of data labels I didn’t know existed, thanks! However, I noticed that if your data labels are formatted with (say) a white box and an outline, this solution will successfully prevent the number from appearing, but the box will still appear! I wonder if there’s a way to keep that from happening? (Other than not to use boxes for your labels?)
You can choose to format labels anyway you desire. Simply select the label range and then right click on it and choose “Format Data Labels” and then choose Border Color = No Line. That should change your formatting
Is there a way to do that without having to click on Each individual data label?
Hi Todd, if you have Excel 2013 or 2016, you can use this tutorial:
https://www.exceldashboardtemplates.com/how-to-use-data-labels-from-a-range-in-an-excel-chart-2/
Also, if you are not using a stacked column, you can use this for standard column charts:
https://www.exceldashboardtemplates.com/create-dynamic-excel-chart-conditional-labels-and-callouts/