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.

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.

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

• Next, compute the percentage using the formula below.

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.

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

📌 Step 02: Insert Stacked Column Chart

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

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

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

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

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

📌 Step 03: Add Percentage Labels

• Thirdly, go to Chart Element > Data Labels.

• 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.

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

Read More: How to Make a Stacked Bar Chart in Excel (2 Quick Methods)

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.

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

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

• Then, copy the formula to the other cells.

📌 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.

• 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.

• 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.

Finally, you should get the following results.

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

Read More: How to Show Number and Percentage in Excel Bar Chart

Similar Readings

• How to Combine Two Bar Graphs in Excel (5 Ways)
• How to Change Bar Chart Width Based on Data in Excel (with Easy Steps)
• Excel Bar Chart with Line Overlay (Create with Easy Steps)
• Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
• Excel Add Line to Bar Chart (4 Ideal Examples)

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.

📌 Step 01: Create Data Table

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

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.

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

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

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

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.

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

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

📌 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.

The chart appears as shown below.

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

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

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

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

📌 Step 03: Add and Format Error Bar

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

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

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

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

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

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

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

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

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

📌 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.

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

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

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

The new series appear as shown below.

📌 Step 05: Format New Series and Data Labels

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

• In turn, select the formatting as shown below.

• Moreover, check the Secondary Axis option.

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

The labels appear as shown in the picture below.

• 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.

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

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

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

Eventually, you’ll get the following results.

Read More: How to Make a Percentage Bar Graph in Excel (5 Methods)

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.

Related Articles

• How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
• How to Make a Bar Graph in Excel without Numbers
• Excel Chart Bar Width Too Thin (2 Quick Solutions)
• How to Change Bar Chart Color Based on Category in Excel (3 Methods)
• How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)
• Excel Bar Graph Color with Conditional Formatting (3 Suitable Examples)