Happy Friday Excellers….today let’s have a look at using a few types of formulas to add some really cool customised data labels to our Excel charts.
Chart labels can really just help with giving clarity the message you are looking to communicate within your data to your audiences, so you may want to add even information to them, and get some added value without overcluttering your chart.
The chart in our example shows April 2016 Sales Values for our 6 products we sell. Ok that is fair enough, we can see our sales values for April 2016, but not much more.
What we could do is create some custom chart labels to not only show the value sales but the difference in sales from the previous month. Let’s see how much more detail we can pack into the chart for our audience.
I have added the extra information into the chart labels below. Not only in this chart can we see the current sales we can eaily see that QRY1 is up 25% vs last month sales. Nice. Let’s create these labels then!!
Firstly we need a helper column, or 2! These are custom labels so we need to do a little bit of manipulation of our data. Here is the original data set that I created my chart out of.
…and the extra two columns we need to add.
In Column D I added is the difference between April 2016 sales and the previous months sales, whereas in Column E is where I have my custom labels that appear on my chart.
The Formula is D is a standard % difference formula, taking the difference between the two numbers and dividing it by the original number.
In Column E, I used the CONCATENATE Function to join the Sales figures from April 2016 and add the resultant percentage difference between April 2016 and the previous months sales figures (from Column D). This generates the custom labels I need. You can check my blog posts for more information on CONCATENATE and here for CONCATENATE for NUMBERS & PERCENTAGES.
We are now ready to generate the custom labels. This is now a manual process but I think it is worth it for those charts where real estate space is of a premium and you want your chart to tell visually as much of the story as possible.
- Select the chart label you want to change
- In the formula bar hit = (equals) and select the cell reference that contains the data for your chart label
- In this case the first label is in cell E2
- Repeat for all all your labels
Have you ever used these types of charting labels?
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
- Use TRIM for a speedy data clean up
