Have you ever wanted to have a different data point label in your Excel Chart than what is offered by the Standard Excel Dialog box choices?
Well you can do that in Excel and not only can you do that, you can make it dynamically update. Read on if you want to learn how-to do this in your next Excel Graph.
This might even be a trick that you employ in your next Excel Company Dashboard to highlight information that you wish to convey.
The Breakdown
1) Create your chart data
2) Add Data Labels
3) Link the Data Labels to Cells in the Spreadsheet
4) Sit back and impress your boss, company board, teachers, coworkers and friends.
Step-by-Step Instructions
1) Create the data for your chart and for your custom labels.
In this case, we have the standard data table for our chart, but we also need to create the information for the data labels. In column C, you will see that I have created a calculation that is the percent difference between the prior month and the new month’s data. There is no data point for the first month because there is no comparison to a previous month.
2) Create your chart just using your normal chart data. In our case, it is just A1 to B5. After cleaning up the chart a little bit, it will look like this:
Then after you have created your basic chart, you need to add data labels. You can do this by selecting the chart, then going to your Layout Ribbon and then selecting Data Labels and then choose Above.
Your chart will now look like this with Data Labels included:
3) Click on the left most label 2 times and then hit your delete key to get rid of it. Next click on the next label (17) then press the Equals (“=”) sign and then choose Cell C3 and then press enter. Repeat this step for the next label =C4 and the next label should be =C5.
After completing this final step, your chart will look like this:
Now when you change your data points, the calculated fields will dynamically update the chart labels.
When you know awesome charting techniques like this, you will become the envy of all and will be able to secure your position as your Excel Company Dashboard Designer.
Video Tutorial
Here is a free video tutorial excel learning session about how-to make a dynamic custom data label.
Steve=True
Thank you so much my friend…….
Thank you! I have so many applications for this!
great information. Now what i want to achieve is for the data labels to change automatically when i exclude January from the series am working with. it always stay the same which means i have to go through the same process again. Thanks
Hi Alanda, I think this can be done, but can you explain more of what you are doing? i.e. How are you excluding January? Thanks Steve=True
Hi Steve, so i do a weekly report that is supposed to show the percentage growth. for every week i add i have to take out the first week off week off which in this case i would say January. now my issue is anytime i take the first week out, the percentages do not correspond and i have to repeat the manual process again. if i could have a way where when i change the chart range the data labels also changes it would make my work easier.
Hi Alanda, I sent you an email if you need to send me a file. I would think that you could do the following:
1) Keep you raw data and categories in another place.
2) Copy and paste the data to a chart data area
3) Create your formulas for the +/- percentages
4) Create your chart and make your custom data point labels
5) When you get new data, put it in the raw data area
6) Copy and paste your data from the raw data area and overlay the chart data area.
Then your chart should just update with the new data and your labels will stay in the same place.
Let me know if this helps. Steve=True
Alanda,
Another option, albeit with a little more work up front but with time savings overall, is to create a dynamic named range based on two dates you choose to define the size of the range. With this method, your data model doesn’t change at all, you just input a start data and an end date and the chart will automatically adjust to show your desired range. This can be done with an INDEX and MATCH combination. The setup would be a named range formula something like this (lets call it “Dates”): =INDEX([List of Dates],MATCH([Start Date],[List of Dates],0)):INDEX([List of Dates],MATCH([End Date],[List of Dates],0))
And then for your values, you can use another named range formula “Values”: =OFFSET(Dates,,1)
I hope this helps, and I can explain in more detail if you like.
-Pete
Pete, that may be the best solution. Apparently this is in a Pivot Table.
Alanda, let us know if this helps.
Steve=True