Show Percentage in 100 Stacked Column Chart in Excel

Looking for ways to show the percentage in 100 Stacked Column Chart in Excel? Then this is the right article for you. You can show the percentage easily in a Pie Chart. However, it is not so straightforward for a Column Chart. Do not worry, we got you. In this article, we are going to show you 2 easy methods to show the percentage in the 100 Stacked Column Chart in Excel.


Download Practice Workbook


2 Suitable Examples to Show Percentage in 100 Stacked Column Chart in Excel

To demonstrate our methods, we have selected a dataset with 3 columns: “Country“, “Sales Q1“, and “Sales Q2“. Basically, we are showing the sales data by country for the first two quarters of the year 2022. We will use this data to construct a 100 Stacked Column Chart and show the percentage in Excel. Here is a snapshot of the ultimate outcome of this article.

100 Stacked Column Chart Excel Show Percentage


1. Using Value From Cells Feature to Show Percentage in 100 Stacked Column Chart

In this section, we will first find the total amount of sales using the SUM function. Then, we insert a basic 100 Stacked Column Chart, which shows only values. After that, we will calculate the percentage of the sales. Lastly, using the Value From Cells feature, we will achieve the goal of showing the percentage in the 100 Stacked Column Chart.

Steps:

  • At first, select the cell range C9:D9 and type the following formula.

=SUM(C5:C8)

100 Stacked Column Chart Excel Show Percentage 2

  • Then, press CTRL+ENTER.
  • This will AutoFill the formula and show us the total amount.
  • Alternatively, you can select the cell range and press ALT+= to get the total.

100 Stacked Column Chart Excel Show Percentage 3

  • Next, select the cell range B4:D8.
  • Afterward, from the Insert tab → Insert Column or Bar Chart → select 100% Stacked Column.

100 Stacked Column Chart Excel Show Percentage 4

  • So, we will get this basic Chart.

100 Stacked Column Chart Excel Show Percentage 5

  • Now, we will swap the row with the column.
  • To do so, select the Chart, and from the Chart Design tab, select Switch Row/Column.

100 Stacked Column Chart Excel Show Percentage 6

  • Thus, our Chart will change like this.

100 Stacked Column Chart Excel Show Percentage 7

  • Then, select the Chart again and select Chart Elements.
  • From the Axes, deselect Primary Vertical. This will hide the Vertical Axis.
  • After that, select Data Labels.
  • Then, deselect Gridlines. So, the Gridlines will disappear.
  • Now, we can see there are Data Labels but it does not show any percentage values. We are going to fix this next.

100 Stacked Column Chart Excel Show Percentage 8

  • Then, we copy and paste the original dataset to the cell range B11:D16 and delete all the numerical values.

100 Stacked Column Chart Excel Show Percentage 9

  • After that, type the following formula in cell C12.

=C5/C$9

  • Then, press ENTER and use the Fill Handle to AutoFill the formula to the rest of the cells. Remember to change these cell formats to percentages.

100 Stacked Column Chart Excel Show Percentage 9 GIF 2

  • Now, we move back to the Chart and double-click on the first Data Label.

100 Stacked Column Chart Excel Show Percentage 10

  • Consequently, this will bring up the Format Data Labels box.
  • Then, deselect Value and select Value From Cells.

100 Stacked Column Chart Excel Show Percentage 11

  • So, another dialog box will pop up.
  • The first Data Label is for Honduras, hence, we select the cell range C15:D15.
  • After that, press OK.

100 Stacked Column Chart Excel Show Percentage 12

  • By doing so, we will see the first Data Label showing percentages.

100 Stacked Column Chart Excel Show Percentage 13

  • Similarly, we will do for the rest of the Data Labels.
  • Then, we added a Title to the Chart and increased the font size.
  • Finally, our final step should look like this image.

100 Stacked Column Chart Excel Show Percentage 14

Read More: Column Chart vs Bar Chart in Excel (6 Useful Examples)


2. Applying VBA to Show Percentage in 100 Stacked Column Chart

For the last method, we are going to apply an Excel VBA Macro to show the percentage in 100 Stacked Column Chart. Moreover, we can see that our dataset is in the “VBAWorksheet and we are using the same percentage dataset that we calculated in method 1.

VBA Dataset

Steps:

  • To begin with, press ALT+F11 to bring up the VBA window.
  • Alternatively, we can do it by selecting Visual Basic from the Developer tab.

Developer Tab

  • Then, from Insert → select Module. We’ll type our code here.

Insert Module

  • Then, type the following code.
Sub Show_Percentage_100_Stacked_Column_Chart()
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .SetSourceData Source:=Range("VBA!$B$11:$D$15")
        .ChartType = xlColumnStacked100
        .PlotBy = xlRows
        .SetElement (msoElementPrimaryValueGridLinesNone)
        .SetElement (msoElementDataLabelCenter)
        .SetElement (msoElementPrimaryValueAxisNone)
        .HasTitle = True
        .ChartTitle.Text = "Applying VBA"
    End With
End Sub

VBA Code

VBA Code Breakdown

  • First, we are calling our Sub procedure  Show_Percentage_100_Stacked_Column_Chart.
  • Next, we insert a Chart in the Active Sheet.
  • Then, we use the VBA With statement to set the properties of the Chart.
  • Here, our data range is B11:D15, you need to change it according to your needs.
  • Afterward, we make the Gridlines from the graph disappear, swap rows with columns in the Chart, and hide the Vertical Axis.
  • After that, we add a Title to the Chart.
  • Thus, this code works to create a 100 Stacked Column Chart.
  • Afterward, Save the Module.
  • Then, put the cursor inside the first Sub procedure and press Run.

Code Run

  • So, our code will execute and it will create a 100 Stacked Column Chart that shows the percentage.

VBA Output


Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Practice Dataset


Conclusion

We have shown you 2 handy approaches to how to show the percentage in 100 Stacked Column Chart in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo