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 number and percentage in excel bar chart.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Simple Methods to Show Number and Percentage in Excel Bar Chart
In the following, I have shared 2 simple methods to show numbers and percentages in 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 number and percentage, 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 excel bar chart.
Step 1:
- Choose a cell. Here I have selected cell (F5).
- Enter the below formula-
=D5*1.15
- Press the Enter.
- Pull the “fill handle” down to fill.
- Here we have our first helper column ready to show in excel chart.
Step 2:
- Select a cell (G5) to write the formula.
- Put the formula down-
=D5&CHAR(10)&" ("&TEXT(E5,"0%")&")"
- 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.
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.
- 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.
Step 4:
- Right-click the mouse button to select the chart.
- Choose “Select Data” form options.
- 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.
- 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.
Step 5:
- Select the new bars and again click the right button on the mouse.
- Press “Select Data”.
- This time choose “Series 2” and press “Edit” to change it.
- 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”.
Step 6:
- Now we will change the format from the “Format Data Labels”.
- 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.
Read More: How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
Similar Readings
- How to Color Bar Chart by Category in Excel (2 Easy Methods)
- How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
- Excel Add Line to Bar Chart (4 Ideal Examples)
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 years. We also have the values in percentages. Now utilizing the format chart we will show both numbers and percentages in a 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.
- 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”.
- 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.
Step 2:
- Again click the right button on the mouse and choose “Add Data Labels” to get the numbers inside the bars.
- Now open the options window by selecting the values from the bars and choose the “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.
- Change the “Separator” to “New line” from the drop-down list to get percentage values just below the numbers.
- 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.
Read More: How to Make a Percentage Bar Graph in Excel (5 Methods)
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.
Conclusion
In this article, I have tried to cover all the simple methods to show number and percentage in 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. Lastly, We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- How to Combine Two Bar Graphs in Excel (5 Ways)
- How to Make a Grouped Bar Chart in Excel (With Easy Steps)
- What is the Difference Between Bar Graph and Histogram?
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- Show Difference Between Two Series in Excel Bar Chart (2 Ways)
- How to Create Stacked and Clustered Bar Chart in Excel (with Easy Steps)