How to Show Percentage in Bar Chart in Excel (3 Handy Methods) 

Bar Charts help to visualize and analyze data more quickly and effectively. In fact, you can display the percentages and values in your Bar Chart to make it more meaningful. With this in mind, this article demonstrates 3 useful methods on how to show percentage in a bar chart in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Methods to Show Percentage in Bar Chart in Excel 

Let’s say we have the following dataset shown in the B4:E9 cells below. The dataset shows the sales in each Store for the 3 consecutive Years.

Dataset 1

Fortunately, you can easily represent this dataset in a Bar Chart using Microsoft Excel. So, without further delay, let’s dive into the methods!


1. Show Percentage in a Stacked Bar Chart

In the beginning, you can generate a Stacked Column Chart in Excel and display percentage values by following these steps.

📌 Step 01: Calculate Percentage Values

  • At the very beginning, add a row for the Total.
  • Then, use the SUM function to add up the values in the C5:C9 cells.

=SUM(C5:C9)

Here, the range of cells from C5 to C9 represents the sales amount of each Store respectively.

Using SUM Function

  • Next, compute the percentage using the formula below.

=C5/C$10

In the above formula, the C5 cell refers to the 2019 sales amount of Store 1 while the C10 cell indicates the Total sales amount for 2019.

Calculating Percentage

  • In turn, use the Fill Handle tool to copy the formula to the other cells.

Data Table

📌 Step 02: Insert Stacked Column Chart

  • Secondly, select the dataset and go to Insert > Insert Column or Bar Chart.

Insert Chart

  • Now, choose the Stacked Column chart option from the drop-down.

Insert Chart

  • Next, right-click on the mouse while selecting the chart and go to the Select Data option.

How to Show Percentage in Bar Chart in Excel Show Percentage

  • Then, click on the Switch Row/Column button and then click on the Edit button.

How to Show Percentage in Bar Chart in Excel Show Percentage

  • Following that, choose the Years as the x-axis label.

How to Show Percentage in Bar Chart in Excel Show Percentage

📌 Step 03: Add Percentage Labels

  • Thirdly, go to Chart Element > Data Labels.

How to Show Percentage in Bar Chart in Excel Show Percentage

  • Next, double-click on the label, following, type an Equal (=) sign on the Formula Bar, and select the percentage value for that bar. In this case, we chose the C13 cell.

How to Show Percentage in Bar Chart in Excel Show Percentage

In a similar fashion, repeat the process for the other values and finally, the results should look like the following.

How to Show Percentage in Bar Chart in Excel Show Percentage


2. Show Percentage and Value in a Stacked Bar Chart

In case you’re wondering how to add both values and their percentage of the total, then you’ve come to the right place. Allow me to demonstrate the process bit by bit.

📌 Step 01: Obtain Percentage Values

  • Initially, add a row for the Total and sum up the values, for instance, the Total of the values of C5:C9 cells is shown.

Using SUM Function

  • Next, insert the given formula to get both the value and the percentage in the same cell.

="$"&C5&","&" "&TEXT(C5/C$10,"#%")

In this expression, the C5 cell refers to the 2019 value of Store 1, and the C10 cell points to the Total for 2019.

 Formula Breakdown:

  • Firstly, the TEXT function formats the appearance of the value using the Pound (#) sign.
  • Here, the Pound sign ensures that there is at least 1 number before the Percent sign.
  • Next, the C5/C$10 refers to the value argument while the “#%” refers to the format_text argument.
  • Lastly, all the values are combined using the Ampersand (&) operator.

Using TEXT Function

  • Then, copy the formula to the other cells.

Data Table

📌 Step 02: Insert Stacked Column Chart and Add Labels

  • Secondly, select the dataset and navigate to Insert > Insert Column or Bar Chart > Stacked Column Chart.

Inserting Chart

  • Similar to the previous method, switch the rows and columns and choose the Years as the x-axis labels.
  • Next, go to Chart Element > Data Labels.

How to Show Percentage in Bar Chart in Excel Show Value and Percentage

  • Following, double-click to select the label and select the cell reference corresponding to that bar. In the picture below, we chose the C13 cell.

How to Show Percentage in Bar Chart in Excel Show Value and Percentage

Finally, you should get the following results.

How to Show Percentage in Bar Chart in Excel Show Value and Percentage

You can explore more ways to add percentages to your Excel charts if you wish.


3. Show Percentage Change in Bar Chart

What if you want to display percentage change in your Bar Chart? Well, our method has you covered. In this method, we’ll utilize the IF and MAX functions to create helper columns and then show the percentage change in the Bar Chart. So let’s see it in action.

Considering we have the dataset shown in the B4:E9 cells. Here, the dataset shows the Month and the Sales amount in USD for 2021 and 2022 respectively.

Dataset 2

📌 Step 01: Create Data Table

  • Firstly, we need to create a helper column and insert the following expression.

=IF(D5>C5, D5-C5,0)

In this formula, the C5 and the D5 cells refer to the Sales in 2021 and 2022 respectively.

Here, the IF function checks if the value of the C5 cell is greater than the D5 cell. If the comparison is True, then it returns the value of D5-C5, else it returns 0.

Using IF Function

  • Similarly, we insert a second column and use the formula given below.

=IF(C5>D5,C5-D5,0)

Using IF Function

  • Next, we use the MAX function to obtain the larger of the two values in the C5 and D5 cells.

=MAX(C5:D5)

Using MAX Function

  • Then, we create a Data Labels (+) column.

=IF(D5>C5,E5/D5,"")

In this formula, the E5 cell points to the difference between the 2021 and 2022 Sales amounts.

Here, the IF function compares D5 and C5 cells, if the value of D5 is greater than C5 then the value of E5 is divided by D5, otherwise it remains blank.

Using IF Function

  • Likewise, we create a Data Labels (-) column.

=IF(D5>C5,E5/D5,"")

Using IF Function

After completing the above steps the data table should look like the image shown below.

Data Table

📌 Step 02: Insert Clustered Bar Chart and Add Formatting

  • Secondly, select the dataset and go to Insert > Insert Column or Bar Chart > Clustered Column Chart.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

The chart appears as shown below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Next, we select the Sales in 2021 bars and specify Solid Fill and Color.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Then we format the Border of the bars by choosing a Solid Line and Color.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • In turn, we add the Series Overlap and Gap Width of 25% and 100% respectively.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Additionally, we provide the following formatting for the Sales in 2022 series.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

📌 Step 03: Add and Format Error Bar

  • Thirdly, select the dataset and go to Chart Element > Error Bars.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Now, from the Chart Options drop-down choose Sales in 2021 series.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Next, select the options as shown in the screenshot below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Then, provide the Direction and End Style of the Error Bars.
  • In turn, check the Custom option and click Specify Value button.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Following, select the E5 to E10 cells for the Positive Error Value field and leave the default value for the Negative Error Value field.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • This time, choose the Error Bars for the Sales in 2022 series.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Then, apply the formatting as shown in the picture below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • In the same way, enter the Direction, End Style, and set a Custom value.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • This time, however, select the F5 to F10 cells for the Positive Error Value.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

📌 Step 04: Include New Series for Data Labels

  • Fourthly, select the chart and right-click on the mouse to go to the Select Data option.

Adding Data Series

  • Next, click the Add button to include a new series.

Adding Series

  • Now, enter the name Max 1, and select the G5:G10 cells as shown below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Similarly, add the series Max 2, and again choose the G5:G10 cells.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

The new series appear as shown below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

📌 Step 05: Format New Series and Data Labels

  • In the fifth step, choose Max 1 series from the Series Options drop-down.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • In turn, select the formatting as shown below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Moreover, check the Secondary Axis option.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Next, select a Sales in 2021 bar and right-click on the mouse to go to the Add Data Labels option.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

The labels appear as shown in the picture below.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Then, double-click the data label to select it and check the Values From Cells option. As a note, you should un-check the Value option.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Now, select the H5:H10 cells and press OK.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

In a similar fashion, repeat the same procedure for the Max 2 series.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

  • Lastly, you can delete the Secondary Axis and the Max 1 and Max 2 legends by selecting them and pressing the DELETE key.

How to Show Percentage in Bar Chart in Excel Show Percentage Change

Eventually, you’ll get the following results.

Bar Chart with Percentage Change


Conclusion

I hope this article helped you understand how to show percentage in Bar Chart in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo