• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Formula Friday – Using Formulas To Add Custom Data Labels To Your Excel Chart

Formula Friday – Using Formulas To Add Custom Data Labels To Your Excel Chart

July 21, 2016 by Barbara

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.

Custom data labels Pin it! Share on Facebook

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!!

Custom data labels1 Pin it! Share on Facebook

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.

data labels3 Pin it! Share on Facebook

…and the extra two columns we need to add.

 

data labels9 Pin it! Share on Facebook

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.

data labels10

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.

data labels11

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

data labels12 Pin it! Share on Facebook

Have you ever used these types of charting labels?
More Excel Tips_New1

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.

 

th
Excel Expert Course

 

  • 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

MR Xl 40 formulas

excel-formulas-ad-5

Filed Under: Blog, Excel Charting Tagged With: Blog, Excel Formulas, formula friday

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2019 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in


Share

Tweet

Additional Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel? Click the link below to receive more Excel tips' and my Free Ebook

New To Excel? This Is A Great Place To Start!!

Learn Excel Dashboard Course Excel Basics
Scrollbars
Colour of Sheet Tabs
Conditional Formatting
Calculations Introduction

Advanced Excel

Power BI Webinar Excel Expert Course Excel Dashboard Course
Data Validation Lists
Create A Timestamp
Create Table Of Contents

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

Video Tutorials-If You Like To Learn By Watching

Subscribe to YouTube to get notified of of new Excel videos.


Self Expanding Chart
Delete Obsolete Items From A Pivot Table-Excel 2007
What Are Pivot Tables- Create Your First One
Enable The DeveloperTab In Excel

Some Fun Stuff

Pivot Table Song

Site Links

  • Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • Top Menu
© copyright 2019 howtoexcelatexcel.com
  • Privacy Policy