TUTORIAL

Excel Tips n Tricks -Tip 8 (Applying Chart Data Labels From a Range in a Excel 2013)

Posted by Sunny Kumar Tutorials | Office 2013 July 30, 2013
This article will help you to learn how to add chart Data labels from a range in a Excel.
Reader Level:
Download Files:
 

Hey there, what's up? Another trick is on the way that you will love to learn and share. We all know how to add a chart and show chart data in data labels, but have you ever wanted to set the Data Label from a given Range in Excel? I mean set the Text as data labels instead of Numbers. I'm sure you would like to, since this feature has been in the wishlist for 15 years to be added to the Microsoft Office Package. Use the following procedure for that.

Procedure

1. Prepare some data
2. Select the data and go to "Insert" > "In Charts" section, select a chart type (I'd prefer scatter chart for the sake of brevity)

Excel-Tips-n-Tricks.jpg
Picture 1

Excel-Tips-n-Tricks-2.jpg
Picture 2


As you see, I've a nice chart plotted with a nice dark theme and it's cool. I must say I really love the formatting enhancements done by Microsoft in Microsoft Office 2013. Choose another one if you want to.  Anyway, the chart is bare without any label showing over there with data points. You can see it shows three buttons with the chart when you click on the chart. They're formatting menus. The first one, the plus symbol,  lets you play with various options like show/hide Axis Title, Data Labels, Error Bars, Legend, Trendline and so on. The second one is for styling your chart with some predefined format by Microsoft that are awesome. The last one is a filter that will let you filter the data for the chart. So, now we need to show up the data Labels.

Excel-Tips-n-Tricks-3.jpg
Picture 3


3. Click on the plus symbol, the first icon, and check "Data Labels". Now you will see them added to your chart. You can also click on the right arrow on "Data Labels" and select where you want the data labels to be aligned, in other words center, right, top, bottom and so on.

Excel-Tips-n-Tricks-4.jpg
Picture 4


4. I modified the chart and axis titles to look good. Now we have data labels on the data points but still they're numbers.  Click on any of the data labels, all of them will be selected automatically.

Excel-Tips-n-Tricks-5.jpg
Picture 5

5. As soon as you select the labels, "Format Data Labels" will be enabled in the right panel of the active window offering you various options to set as per your preference. Check the first option,"Value from Cells".

Excel-Tips-n-Tricks-6.jpg
Picture 6

It will popup a Range Selector dialog box. Select the Column containing the text for the chart data and click "OK". Remember that the text should belong to the cell adjacent to the label data.

Excel-Tips-n-Tricks-7.jpg
Picture 7


6. All done, just hit Enter or click "Ok" and you have your chart decorated with the text for data labels like this:

Excel-Tips-n-Tricks-8.jpg
Picture 8

Of course I've changed a little bit in format that you can see here in the picture above to make it look better. You're free to go beyond the creativity and create some awesome stunning charts.

Remember, this feature is not available in earlier version versions of Excel, so even if you open the Excel document with this kind of chart with Range as Data Labels, you will get a "[Data Range]" instead of actual data.
 
A sample is attached with this article. I hope you enjoyed reading this article :)

You may want to read my other articles on Excel Tips, please go through the following:

Feedback and comments are highly appreciated. Follow for more!


COMMENT USING