Excel graph is an amazing feature that assists in analyzing data a lot quicker. You can display the total as well as the part numbers of your data table using an Excel graph. Illustrating partial numbers through the usage of percentages is a great convenient way to analyze data. In this article, I will show you how to display a percentage in an Excel Graph using 3 relevant methods.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
3 Methods to Display Percentage in Excel Graph
1. Show Percentage in a Stacked Column Chart in Excel
You can generate a stacked column chart using general numbers in Excel. However, if you want to display a percentage in a stacked column chart instead of showing them using general numerical values, then follow the steps below.
❶ Select the range of cells that you want to consider while plotting a stacked column chart.
❷ Then go to the Insert ribbon.
❸ After that from the Charts group, select a stacked column chart as shown in the screenshot below:
❹ After that navigate to Chart Design > Add Chart Element > Data Labels > Center.
At this point, you will have data labeled in the stacked column chart. To display percentage instead of the general numerical value,
❺ Create one secondary data table and convert all the general numerical values into percentages.
❻ Then click one of the data labels of the stacked column chart, go to the formula bar, type equal (=), and then click on the cell of its percentage equivalent.
❼ After that hit the ENTER button.
Then you will see percentages is showing instead of numerical values.
❽ Now repeat the same process to convert all of the numbers into their corresponding percentages.
When you are done, you will see percentages showing up in the stacked column chart instead of showing the general numerical values.
2. Format Graph Axis to Percentage in Excel
If you want to change the graph axis format from the numbers to percentages, then follow the steps below:
❶ First of all, select the cell ranges.
❷ Then go to the Insert tab from the main ribbon.
❸ From the Charts group, select any one of the graph samples.
❹ Now double click on the chart axis that you want to change to percentage.
Then you will see a dialog box appear from the right side of your computer screen.
❺ Select Axis Options. Then go to Chart.
❻ Navigate to Number.
❼ From the Category box select Percentage.
❽ If you want to adjust the decimal places then tweak it from the next box below then Category.
So, finally, you will see your graph axis is set to a percentage as in the image below:
3. Show Percentage Change in Excel Graph
In this section, I will show you how to display percentage change in Excel Graph. This method is quite lengthy, that’s I have divided the whole process into several small parts. So without having any further discussion, let’s get started.
Create the Data Table
Our primary target is to display the percentage change of the monthly revenue.
So the Month and the Revenue are the main columns. But for our convenience, you need to create another column, the Helper column. To do that,
❶ Type the following formula in cell D5.
❷ Now press ENTER.
❸ Drag the Fill Handle icon to the end of the Helper column.
After that create another column called difference using the following formula:
Use the following formula to create the Positive column. This column will contain only the positive difference values.
Lastly you need to create another column, called the Negative using the following formula:
Generate a Graph
❶ Select the Month, Revenue, and Helper columns. Then go to Insert and select the Clustered Column command to insert a column graph.
❷ Double-click on the Helper columns in the graph. Then click on the plus icon and uncheck the Legend option.
❸ Go to More Options from the arrow at the right side of the Error Bars option.
❹ Then Format Error Bars dialog box will appear. Make sure that the direction is set to Both and the End Style is Cap. After that from the Error Amount options, select Custom and click on Specify Value.
❺ Custom Error Bars will appear. Select the whole Positive column cell ranges in the Positive Error Value box. Also, select the whole Negative column cell ranges in the Negative Error Value box. Then hit the OK button.
❻ Now right-click on the Blue columns in the graph. Which are originally the Revenue column series. From the pop-up list select Format Data Series.
❼ Select the Graph in the Format Data Series dialog box. Select the Series Overlap to 0% and Gap Width also to 0%.
❽ Now select all the Helper columns in the graph. Go to the Format tab. Navigate to Shape Fill and choose No Fill.
Display Percentage in Graph
❶ Select the Helper columns and click on the plus icon. Then go to the More Options via the right arrow beside the Data Labels.
❷ Select Chart on the Format Data Labels dialog box.
❸ Uncheck the Value option. Check the Value From Cells option. Then you have to select cell ranges to extract percentage values.
❹ For this purpose, create a column called Percentage using the following formula:
The Final Graph with Percentage Change
So after following all the steps above, you will see a graph with percentage change as in the picture below:
To sum up, we have discussed 3 methods to display percentages in Excel graphs. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.