How-to Use Data Labels from a Range in an Excel Chart

Excel 2013 has some cool features. If you were not aware, here is an awesome Chart Data Label option that you now have when you upgrade to Excel 2013. I know that this wasn’t something that I was looking for and therefore didn’t notice it until someone pointed it out to me. So now I want to show the world :). The awesome new feature is that you can now add data labels to chart from a range on the spreadsheet as a standard option in Excel. No more tricking Excel. The developers got this one right if you ask me. Even though this is a cool technique, I don’t find the use for it as often as I thought. Do you use it? If so, let me know in the comments below. If you don’t know about it, check out the tutorial and the video how-to here:

 

The Breakdown

1) Create Chart Data Range and Data Label Range

2) Create Chart





3) Add Data Labels

4) Select Data Label Range

 

Step-by-Step

1) Create Chart Data Range and Data Label Range



First we need to create two (2) different data ranges in our Excel Spreadsheet. First, create your chart data range, like you see in columns A and B. Next, create the data label series in equal length of our data points, like you see in column C. Custom Chart Data Labels Pic 1

2) Create Chart

Now we are ready to create our chart. For this step, we only want to chart columns A and B and NOT C. So select the chart data range from A1:B6Custom Chart Data Labels Pic 2

Then go to your Insert Ribbon and select a 2D Clustered Column Chart from the chart buttons like you see here:Custom Chart Data Labels Pic 3

Your chart should now look like this:Custom Chart Data Labels Pic 4





3) Add Data Labels

Now we can add our data labels. To do this, select your chart, then select the Design Ribbon. Then select the Add Chart Element button on the left and select the Data Labels Menu and the “More Data Label Options…” from the menu:Custom Chart Data Labels Pic 5

Now you will see a dialog box of Data Label options pop up. Uncheck the “Value” check box and check the “Value From Cells” check box as you see here:Custom Chart Data Labels Pic 6

4) Select Data Label Range

When you have done that, you will then get another dialog box that will pop up so that you can input your data range for your chart labels. Simply highlight the range that represented your custom data labels. In our case, it was cells C2:C6. Then press the Ok button.



Custom Chart Data Labels Pic 7

Your final chart should now look like this with the new data labels from an alternate label range:Custom Chart Data Labels Pic 8

Here is a link if you want to do this same thing with your data labels in Excel 2007 or Excel 2010:

How-to Add Custom Labels that Dynamically Change in Excel Charts

 





Video Tutorial

http://youtu.be/-bZxQA1LVZQ

 

Free File Download

How-to-Use-Data-Labels-from-a-Range-in-an-Excel-Chart.xlsx

 



I hope this makes you reconsider migrating to Excel 2013 as one feature that is just what we wanted.

Steve=True





8 COMMENTS

  1. Uso bastante esse método para nomear gráficos de bolhas e de pontos. Continue sempre compartilhando seu conhecimento conosco!!!

    ——————————————————————–

    I use this trick a lot on labels of bubble and scatter charts. Keep sharing your knowledge with us!!!

    • It took M.S. long enough to include the labeler in 2013. I just wanted to remind everyone that John Walkenbach had a chart data labeler in his add-in as far back to at least Excel 97, if memory serves me right.

  2. I definitely use this feature at work when I can; however it’s difficult because I’m the only one here that has Office 2013. And as I’m sure you know, opening an excel file with this feature in Excel 2010/2007 returns a “CellValue” error to each label.

    Anyways, I work in the sales department of a logistics company and I like to show clients which of our company warehouses deliver to which Wal-Mart Distribution Centers. I show this by laying a bubble chart over a map of the U.S., creating a series by origin, etc. My final step though is populating all the bubbles with the Distribution # that I pull from a spreadsheet. This is helpful, because in previous versions the size of the bubble would be dependent on the actual DC #.

LEAVE A REPLY

Please enter your comment!
Please enter your name here