Data Labels in Excel Pivot Chart (Detailed Analysis)

Get FREE Advanced Excel Exercises with Solutions!

Data Labels in Pivot Chart are an extremely useful feature of Excel. Using this feature users can manipulate the data table as their wish. If you are curious to know how you can do various things with Data Labels in Excel Pivot Chart, this article may come in handy for you. In this article, we are going to show how you can use Data Labels in Excel Pivot Chart with elaborate explanations.


What Do Data Labels Mean in an Excel Chart?

The data label is a marker on the Excel Chart, where this marker is linked with the data in the Table and updates when the data is updated.

A data label is such a useful feature using which can give you the info about the data or data series instantly. Which part of the chart denotes which data can be easily distinguished through the data labels.


Overview of Excel Pivot Chart

As stated before, a Pivot Table is a strong tool to analyze data. And making a Pivot Chart adds more to it. A Pivot Table allows users to modify the existing data Table according to their wishes. Then we can summarize and present our analysis using this Pivot Chart made from Pivot Table. Before we delve into showing the Pivot Chart with the data label. We need to get comfortable with the Pivot Table and Pivot Chart environment.

  • After we initiate the Pivot Table, we will see three separate tabs in the ribbon instantly. Power Pivot, PivotTable Analyze, and Design.
  • We also have a side panel of Fields. The fields are basically the columns that we had in the main dataset.

Overview of Excel Pivot Chart

  • Dragging the fields on the row will show them on the leftmost column as rows in the Pivot Table.
  • And adding them to the columns will place the values in the column in the Pivot Table.
  • You can change the value field settings, whether you want to show the Average value/Maximum/Minimum value etc. In the Value field area.

Next, we will discuss the Pivot Chart environment.

  • In order to create the Pivot Chart, we need to create a Pivot Table first.
  • Considering we already have that Pivot Table, we can hover over to the PivotTable Analyze tab.
  • In the PivotTable Analyze tab, click on the Pivot Chart.

Overview of Excel Pivot Chart

  • After clicking on the Pivot Chart, you will notice that there are a couple of pivot charts that can be made from the information suggested.
  • You can also Recommend Pivot Chart for the best chart recommendations.

Data Labels in Excel Pivot Chart Considering All Factors: 7 Suitable Examples

For the demonstration purpose, we are going to use the below dataset. This is hypothetical sales data of an imaginary organization, where we can see the ID of the products they sold so far, which Region they sold, their Type, Quantity, Cost of production, Ratings, Revenue, and Profit Margin. We will implement a Pivot Table to analyze and extract important insights about the data table.


1. Adding Data Labels in Pivot Chart

In this example, we will show you how to create a pivot chart and add data labels to the pivot chart.

Steps

Before adding the data labels, we need to create the Pivot Chart in the beginning. We can create a Pivot Chart from the Insert tab.

  • To do this, go to Insert tab > Tables group.

  • Then in the dialog box, select the range of cells of the primary dataset., here the range of cells is B4:J23.
  • And select the New Worksheet in the next option.
  • Click OK after this.
  • This will open a new worksheet and in that worksheet, you will have the Pivot table fields on the right side.
  • Now from the Pivot Table fields, drag the region in the Row area below.
  • And drag the Quantity in the Values area.

Adding Data Labels in Pivot Chart in Excel

  • After that from the PivotTable Analyze tab, click on the PivotChart.

  • Then in the Insert Chart dialog box, select the Clustered Column option.
  • Click OK after this.

  • After this, there will be a column chart without any data label.

  • Then click on the Plus sign right next to the chart.
  • From the menu, notice the Data Labels check box.

Then check the Data labels box, after then you will see the Data Labels showing over the columns.

Adding Data Labels in Pivot Chart in Excel


2. Set Cell Values as Data Labels

By default, the Data Labels are the data itself. But we can manipulate and add the data table manually by setting cell values as Data Labels.

Steps

  • Create the Pivot Table just like before and then drag the Region in the Axis area and Quantity in the Values area.

  • Then add a Pivot Chart from the PivotTable Analyze tab.
  • Next, you will notice that there is a data label option, but we want to add it manually from a range of cells.
  • Click on the Plus sign right next to the Chart, then from the Data labels, click on the More Options.

  • After that, in the Format Data Labels, click on the Value From Cells. And click on the Select Range.

Set Cell Values as Data Labels in Pivot Chart in Excel

  • In the next step, select the range of cells B5:B11.
  • Click OK after this.

Set Cell Values as Data Labels in Pivot Chart in Excel

  • After this, there will be Data Labels from the range of cells B5:B11 on top of each column.

Set Cell Values as Data Labels in Pivot Chart in Excel


3. Showing Percentages as Data Labels

We can show the percentage distribution of the data instead of the data itself in the Pivot Chart.

Steps

  • Create a similar Pivot Table just like the previous methods dragging the Region in the Rows area and Quantity in the Values area.
  • And create a Pivot Chart from this table using the PivotTable Analyze in a similar way before.

  • Right after this, copy the range of cells B5:B11 to the range of cells C5:C11.

Showing Percentages as Data Labels in Pivot Chart in Excel

  • Next, select cell D5 and enter the following formula:

=C5/$C$12

Showing Percentages as Data Labels in Pivot Chart in Excel

  • The drag the Fill Handle to cell D11.
  • Doing this will fill the range of cells C5:C11 with the percentages of each cell with respect to the Total values.

Showing Percentages as Data Labels in Pivot Chart in Excel

  • Add a Pivot Chart from the PivotTable Analyze tab.
  • Then press on the Plus right next to the Chart.
  • Next open Format Data Labels by pressing the More options in the Data Labels.
  • Then on the side panel, click on the Value From Cells.

  • Next, in the dialog box, Select D5:D11, and click OK.

  • Right after clicking OK, you will notice that there are percentage signs showing on top of the columns.

Showing Percentages as Data Labels in Pivot Chart in Excel


4. Changing Appearance of Pivot Chart Labels

We can change how the data label looks in order to have more clarity in the Pivot Chart.

Steps

  • Next, we will try to change the way the Data Labels look.
  • We need to create the Pivot Table and the Chart the same way before.
  • Then we click on the Plus sign top right corner of the Chart.
  • From there, click on the Data Labels.
  • After then, you will notice that the Data Labels are now showing on top of each column.

  • Clicking on any Data labels one time will select all of the Data Labels simultaneously.
  • Then right-click on the Data Table and from the context menu, click on the Format Data Labels.

Changing Appearance of Pivot Chart Data Labels in Excel

  • Then in the Format Data Labels, go to the Size and Properties.
  • From there, click on the Text Directions. And from the drop-down menu, click on the Rotate all text 270.

  • Doing this will instantly rotate the text 270 degrees.
  • Next, go to Fill and Line, and from there, select the color.

  • While selecting the data label, right-click on it again and then select Font.

  • Then select the Font style to be Bold and the Font color to be White.
  • Click OK after this.

  • The Chart will finally look like the below image.

Changing Appearance of Pivot Chart Data Labels in Excel

Read More: How to Edit Pivot Chart in Excel


5. Changing Background of Data Labels

Manipulating the background of the data labels can make the reading of the data labels easier.

Steps

  • Create the Pivot Table and the Chart just like the previous steps.
  • Then right-click on any data labels and click on the Gradient fill.
  • Then choose the color gradient as you wish and adjust settings accordingly.

Changing Background of Data Label of Pivot Chart in Excel

  • Next, go to the Size and Properties and click on the Text Direction.
  • And select Rotate all texts 270 degrees from the dropdown menu.

Changing Background of Data Label of Pivot Chart in Excel

  • Next, go to Fill and Line, and from the Border, click on Solid Line.
  • Then in the Width, select 1.25 pt.

Changing Appearance of Pivot Chart Data Labels in Excel

  • The background of the data labels on the Pivot Chart is now altered.
  • And this is how we changed the background of the data labels in the Excel Pivot Chart.

Read More: Use Excel VBA to Create Chart from Pivot Table


6. Dynamic Pivot Chart Data Labels with Slicers

Dynamic Pivot Chart means that f the data changes or updates, the chart will also update and the labels also change dynamically. We accomplish this by using TEXT, CHOOSE, and IFERROR functions.

Steps

  • Create Pivot Table just like the previous examples, but with a small change.
  • Now we will add the category in the column area in the chart.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • Next copy the newly created table (range of cells A5:H13) in the range of cells A16:H24.

  • Then create the same table with the same table header without the values in the range of cells A27:A35.
  • Then select cell B28 and enter the following formula:

=TEXT(B17,"$#,###")

  • Then drag the Fill Handle horizontally to cell H28.
  • Then drag it vertically to cell H35.

  • Next, select the cell B29, and enter the following formula:

=TEXT(IFERROR(B17/B$24,0),"0.0%")

Dynamic Pivot Chart Data Label with Slicers in Excel

Breakdown of the Formula

  • IFERROR(B17/B$24,0): This part of the formula will check whether the division of cell B17 by cell B24 is valid, or shows any error. It will return 0 if the division result returns an error. Otherwise, it will be as it is.
  • TEXT(IFERROR(B17/B$24,0),“0.0%”): This formula will take the output of the previous function as input. And return as one decimal place percentage formatted text.
  • Then drag the Fill Handle first in the horizontal direction to cell H39.
  • Next, drag the Fill Handle first in the vertical direction to cell H45.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • In the next step, create the table in the same format shown below. Then select cell C49, and enter the following formula:

=TEXT(IFERROR((C17-E17)/E17,0),"0.0%")

Dynamic Pivot Chart Data Label with Slicers in Excel

Breakdown of the Formula

  • IFERROR((C17-E17)/E17,0): This part of the formula will check whether the division of (C17-E17) by the cell E17 is valid, or shows any error. It will return 0 if the division result returns an error. Otherwise, it will show as it is.
  • TEXT(IFERROR((C17-E17)/E17,0),“0.0%”): This formula will take the output of the previous function as input. And return as one decimal place percentage formatted text.
  • Then drag the Fill Handle first in the horizontal direction to cell H49.
  • Next, drag the Fill Handle first in the vertical direction to cell H55.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • In the next step, select the cell B60, and enter the following formula:

=CHOOSE($B$59,B28,B39,B49)

  • Then drag the Fill Handle first in the horizontal direction to cell H60.
  • Next, drag the Fill Handle first in the vertical direction to cell H66.

  • Then in a new sheet, create the following table as shown here, this table will help us to switch the data labels easily.
  • From the data labels table, create a Pivot Chart.
  • And while creating the Chart, add Type, Sign, and Serial in the Row area.
  • After that, add a slicer from the PivotTable Analyze.

  • Then go back to the previous Chart and select cell B59 and enter the “=” first, to initiate the formula. Then go to the newly created sheet and select cell G3.
  • This way this cell is now linked with the value of cell G3.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • Click on the Insert tab, and then from the Charts, select 2-D Column.

  • There will be a new Chart with the 2-D columns.
  • But we need to make the table Data Labels dynamic.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • To do this, select any Data Labels and right-click on them.
  • Then from the context menu, click on the Format Data Labels.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • After that, from the side panel, click on the Value From Cells.

Dynamic Pivot Chart Data Label with Slicers in Excel

  • Then in the range box, select the range of cells B60:B66.

  • Then select the data label named 15 and right-click on it.
  • Then from the context menu, click on the Format Data Labels.
  • After that, from the side panel, click on the Value From Cells.
  • Then in the range box, select the range of cells C60:C66.

  • Repeat the same process for the rest of the columns.
  • Then finally the Chart will look like the below image with the slicer.

Dynamic Pivot Chart Data Label with Slicers in Excel

That is how we created the dynamic data labels in the Excel Pivot Chart.


7. Removing Data Labels in Pivot Chart

Just like adding, we can also remove the data label in the Pivot Chart with quick steps.

Steps

  • A Pivot Table alongside the Pivot Chart is created in the same way in previous examples.
  • Notice where all the data labels are showing.
  • And we want to get rid of them.

  • Select the data label and right-click on the mouse.
  • Then click on the Plus sign on the right most corner of the chart.
  • From there you can see that the Data Labels box is checked.

  • Uncheck the data labels box.
  • After that, you will notice that the data labels are no longer visible right now.

Removing Data Labels in Pivot Chart in Excel

Read More: How to Refresh Pivot Chart in Excel


Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the topic Data Labels in Excel Pivot Chart is described here with 7 different examples Those processes can make any kind of data analysis project seamless with the Pivot Chart with the data labels. For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.


Related Articles


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo