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

`=SUM(C5:C9)`

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.

`=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*.

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

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

`="$"&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 theargument while the*value***“#%”**refers to theargument.*format_text* - Lastly, all the values are combined using the
**Ampersand**(**&**) operator.

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

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

`=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**.

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

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

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

`=MAX(C5:D5)`

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

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

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

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.

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