How to Display Percentage in an Excel Graph (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


Excel Graph Percentage: 3 Methods to Display

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:

Display Percentage in an Excel Graph: Choose a graph

❹ After that navigate to Chart Design > Add Chart Element > Data Labels > Center.

Display Percentage in Excel Graph: 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 are 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.

Display Percentage in Excel Graph

Read More: How to Make an X-Y Graph in Excel


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.

Format Graph Axis to Percentage in Excel

❹ Now double-click on the chart axis that you want to change to percentage.

Change Graph Axis to Percentage in Excel

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:

Read More: How to Create a Chart from Selected Range of Cells in Excel


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 why 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 in 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.

=C6

❷ Now press ENTER.

❸ Drag the Fill Handle icon to the end of the Helper column.

Show Percentage Change in Excel Graph: Create Data Table

After that create another column called difference using the following formula:

=C6-C5

Use the following formula to create the Positive column. This column will contain only the positive difference values.

=IF(E5>0,-E5,"")

Lastly you need to create another column, called the Negative using the following formula:

=IF(E5<0,-E5,"")

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.

Show Percentage Change in Excel Graph: Generate a 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.

Show Percentage Change in Excel Graph

❹ 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.

Format Error Bars: Show Percentage Change in Excel Graph

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.

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.

Format the columns in Excel Graph

Display Percentage in Graph

❶ Select the Helper columns and click on the plus icon. Then go to 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.

Display Percentage in Excel Graph

❹ For this purpose, create a column called Percentage using the following formula:

=E5/C5

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:

The Final Graph: Display Percentage in Graph

Read More: How to Make a Graph from a Table in Excel


Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

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.


Related Articles


<< Go Back To How to Create a Chart in ExcelExcel ChartsLearn 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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo