How to Show Number and Percentage in Excel Bar Chart

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel we create bar charts or different types of charts to visualize data in a graphical way. But while showing data, you might need to show both numbers and percentages in the chart. Today in this article, I will show you how to show numbers and percentages in an Excel bar chart.


How to Show Number and Percentage in Excel Bar Chart: 2 Simple Methods

In the following, I have shared 2 simple methods to show numbers and percentages in an Excel bar chart. Stay tuned to learn these easy and quick methods.


1. Use Helper Column to Show Number and Percentage in Bar Chart

In order to show both numbers and percentages, you can use an extra helper column.

Suppose we have a dataset of some Products, Sales Order, and Total Market Share. Using helper columns we will show numbers and percentages in an Excel bar chart.

Show Number and Percentage in Excel Bar Chart

Step 1:

  • Choose a cell. Here I have selected cell (F5).
  • Enter the below formula-
=D5*1.15

Show Number and Percentage in Excel Bar Chart

  • Press the Enter.
  • Pull the “fill handle” down to fill.

  • Here we have our first helper column ready to show in the Excel chart.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Step 2:

  • Select a cell (G5) to write the formula.
  • Put the formula down-
=D5&CHAR(10)&" ("&TEXT(E5,"0%")&")"

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • Hit the Enter button to get the value.
  • Drag down the “fill handle”.

  • Below you can see we have our 2nd helper column ready showing values and percentages.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Now we will create the bar chart. To create the bar chart start by selecting columns from the data table.

Step 3:

  • Choose data from the Product and Sales Order.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • From the “Insert” option choose “2-D Stacked Column”.

  • After selecting a new bar chart will be created just like the following.
  • Now we will edit the chart to show both numbers and percentages inside the chart.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Step 4:

  • Right-click the mouse button to select the chart.
  • Choose the “Select Data” form option.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • In the “Select Data Source” click “Add”.

  • After that in the “Series values” section choose data from the “Helper 1” column.
  • Press OK to continue.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • Now selecting the new bars click the right button on the mouse to get options.
  • From the options choose “Format Data Series”.

  • Click the “Secondary Axis” and your chart will look like the below screenshot.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Step 5:

  • Select the new bars and again click the right button on the mouse.
  • Press “Select Data”.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • This time choose “Series 2” and press “Edit” to change it.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • A new window will appear named “Axis labels”.
  • Select data from the “helper 2” column and press OK to continue.

  • Open options by right-clicking the mouse button.
  • From the options click “Add Data Labels”.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Step 6:

  • Now we will change the format from the “Format Data Labels”.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

  • Check mark the “Category Name” from the list and choose “Inside End” to visualize data at the end.

  • After deleting all the unnecessary elements your final chart will look just like the following.
  • Thus we can easily show numbers and percentages in an Excel bar chart.

Use Helper Column to Show Number and Percentage in Excel Bar Chart

Read More: How to Make a Percentage Bar Graph in Excel


2. Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

You can show numbers and percentages without using the helper column, too. In this method, we will use the built-in feature of an Excel format chart to show numbers and percentages in a bar chart.

Suppose we have a dataset of Sales in a region year. We also have the values in percentages. Now utilizing the format chart we will show both numbers and percentages in a bar chart.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

Step 1:

  • Firstly, select data from the list. Here I have selected cell (B5:F5).

  • While the dataset is selected choose the “2-D Stacked Column” chart from the “Insert” option.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

  • A chart will be created.
  • Choose the bar from the chart and press the right button of the mouse to appear options.
  • From the options choose “Select Data”.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

  • A new window will appear named “Select Data Source”.
  • Click “Edit” from the “Horizontal Axis Labels”.

  • Choose years from your dataset and press OK to continue.
  • This way years will be shown horizontally in the chart.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

Step 2:

  • Again click the right button on the mouse and choose “Add Data Labels” to get the numbers inside the bars.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

  • Now open the options window by selecting the values from the bars and choose “Format Data Labels”.

  • From the right pane go to “Label Options” and check mark the “Value from cells”.

  • A new window will appear asking for the range from your table.
  • Henceforward, Choose the percentage values from the dataset and click OK.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

  • Change the “Separator” to “New line” from the drop-down list to get percentage values just below the numbers.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

  • Thus your chart will look like the following screenshot.

  • Finally, Remove all the unnecessary elements and thus you will get your final chart ready showing both numbers and percentages in the chart.

Utilize Format Chart to Show Number and Percentage in Excel Bar Chart

Read More: How to Show Difference Between Two Series in Excel Bar Chart


Things to Remember

  • In the second method, I have shown separating the percentages in a new line. This works if your Excel version is 2013 or above. Otherwise, you can use a comma (,) or semicolon (;) to separate.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the simple methods to show numbers and percentages in an Excel bar chart. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


Related Articles


<< Go Back to Excel Bar ChartExcel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo