Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Create stacked column chart with percentage

Author Xiaoyang Last modified

In Excel, it is easy for us to create a stacked column chart with data value labels. But, sometimes, you may need the stacked column chart with percentage values instead of the normal values, and display the total values for each column at the top of the bar as below screenshot shown. How could you create this type of chart in Excel?

stacked column chart with percentage


Create a stacked column chart with percentage values

For displaying both total values and percentage values in the stacked column chart, please do with the following steps:

1. First, please calculate the total values for each column data, enter the below formula into cell B6, and then drag the fill handle from right side of the cells you want to apply the formula, see screenshot:

=SUM(B2:B5)

steps of creating a tacked column chart with percentage in Excel

2. Then, you should calculate the percentage of each values for the products separately, please apply the below formula into cell B9, and then drag the fill handle over other cells to calculate the percentage values. See screenshot:

=B2/B$6

steps of creating a tacked column chart with percentage in Excel

3. Now, you need to change the decimal values to percentage values, please select the formula cells, and then click Home > Percentage from the General drop down list, see screenshot:

steps of creating a tacked column chart with percentage in Excel

4. Now, select the original data including the total value cells, and then click Insert > Insert Column or Bar Chart > Stacked Column, see screenshot:

steps of creating a tacked column chart with percentage in Excel

5. A stacked chart has been inserted as below screenshot shown:

steps of creating a tacked column chart with percentage in Excel

6. Click to select the chart, and then click Design > Switch Row/Column, and you will get a chart as below screenshot shown:

steps of creating a tacked column chart with percentage in Excel

7. Then, you can delete the unwanted elements from the chart, such as chart title, gridlines, see screenshot:

steps of creating a tacked column chart with percentage in Excel

8. And then, click to select the chart, and click the Chart Elements icon to expand the Chart Elements list box, in the list box, check Data Labels to insert the data labels, see screenshot:

steps of creating a tacked column chart with percentage in Excel

9. Then, click to select the total series data, and then right click, choose Change Series Chart Type from the context menu, see screenshot:

steps of creating a tacked column chart with percentage in Excel

10. In the popped out Change Chart Type dialog box, in the Choose the chart type and axis for your data series list box, select Line chart type for the Total data series, see screenshot:

steps of creating a tacked column chart with percentage in Excel

11. And then click OK button to close the dialog box, and the chart has been displayed as below screenshot shown:

steps of creating a tacked column chart with percentage in Excel

12. Then, right click the line, and choose No Outline from the Outline drop down list to hide the line in the chart, see screenshot:

steps of creating a tacked column chart with percentage in Excel

13. And then, click to select the total data values, and then click Chart Elements to expand the list box, in the Chart Elements list box, select Above from the Data Labels drop down, see screenshot:

steps of creating a tacked column chart with percentage in Excel

14. From now on, you should convert the data values to the percentage values, please right click the first series data labels (the data labels of the Pear, the data in the golden bars), and choose Format Data Labels, see screenshot:

steps of creating a tacked column chart with percentage in Excel

15. In the opened Format Data Labels pane, under the Label Options tab, check Value From Cells, see screenshot:

steps of creating a tacked column chart with percentage in Excel

16. In the popped out Data Label Rage dialog box, select the percentage values which are consistent with the data series (percentage values of Pear), see screenshot:

steps of creating a tacked column chart with percentage in Excel

17. Then click OK to close the dialog, still in the Format Data Labels pane, uncheck the Value box in the Label Options section, and only the percentage values are displayed in the chart, see screenshot:

steps of creating a tacked column chart with percentage in Excel

Note: If you want to display both the data values and percentage values as the data labels, please check Value option, and then choose the (New Line) from the Separator drop down list, see screenshot:
steps of creating a tacked column chart with percentage in Excel

18. And then, you just need to repeat the above steps 14- 17 to convert the other data values to their own corresponding percentage values, and you will get the chart you need, see screenshot:

steps of creating a tacked column chart with percentage in Excel

19. At last, format the Legend to your need, and delete the Total legend as well. The stacked chart with percentage values has been created successfully, see screenshot:

steps of creating a tacked column chart with percentage in Excel


Create a stacked column chart with percentage values with an amazing feature

Kutools for Excel provides dozens of special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy tool- Stacked Chart with Percentage, you can create a stacked chart with both percentage values and total values with ease. Click to download Kutools for Excel for free trial!


Download Stacked Column Chart With Percentage Values sample file

sample of creating a tacked column chart with percentage in Excel


Video: Create a stacked column chart with percentage values


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.