How to Show Percentage Change in Excel Graph (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

This article will illustrate how to show the percentage change in an Excel graph. Using an Excel graph you can present the relation between the data in an eye-catching way. Showing partial numbers as percentages is easy to understand while analyzing data. In the following dataset, we have a company’s Profit during the period March to September.

how to show percentage change in excel graph


How to Show Percentage Change in Excel Graph: 2 Ways

1. Using Column Chart to Show Percentage Change in Excel

For the dataset we have, we are going to scrutinize the percentage change in monthly profit by a Column Chart.

Steps:

  • First, make a new column for the profits of the following months and type the following formula in cell D5.
=C6

  • Then press ENTER and you will see the profit of April month appear in cell D5.

how to show percentage change in excel graph

  • Now make some columns for Variance in profits, Positive Variance, Negative Variance, and Percentage Change.
  • Type the following formula in cell E5, hit the ENTER button, and use Fill Handle to AutoFill lower cells as you did in the Next Profit column (To see the AutoFill procedure, go through the First Formula).
=D5-C5

how to show percentage change in excel graph

  • Again, type this formula in cell F5, hit the ENTER button, and use Fill Handle to AutoFill lower cells like you did in the Next Profit column (To see AutoFill procedure, go through the First Formula).
=IF(E5>0,-E5,"")

The IF function stores the increase in profits in column F.

  • After that, write down the formula given below in G5, hit the ENTER button, and use Fill Handle to AutoFill lower cells like you did in the Next Profit column (To see the AutoFill procedure, go through the First Formula).
=IF(E5<0,-E5,"")

how to show percentage change in excel graph

This time, the IF function stores the decrease in profits in column G.

  • Finally, type the following formula in cell H5, hit the ENTER button, and use Fill Handle to AutoFill lower cells as you did in the Next Profit column (To see the AutoFill procedure, go through the First Formula).
=E5/C5

Now we are going to show this information in a chart. Our main focus is on percentage change. Let’s see how we make a chart for this.

  • Select the range B4:D11 (Month, Profit, and Next Profit columns) and then go to Insert >> Clustered Column Chart.

how to show percentage change in excel graph

A chart will appear.

  • Double-click on the Next Profit column (Orange colored bars) in the graph. Then click on the Plus (+) icon and uncheck the Legend and Gridlines option (This is optional, I’m doing this to get a better view of the chart).
  • Select Error Bars >> More Options…

how to show percentage change in excel graph

  • You will see the Format Error Bars window.
  • Ensure 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.

  • A dialog box will appear.
  • Select range F5:F11 and G5:G11 for the Positive Error Value and Negative Error Value sections respectively.
  • Click OK.

how to show percentage change in excel graph

  • Now right-click on any of the Profit Column Bars (Blue colored bars) and select Format Data Series…

  • Make Series Overlap and Gap Width to 0% in Format Data Series window.
  • Also, make sure that Plot Series remains on Primary Axis.

how to show percentage change in excel graph

  • To change the Fill Color of Next Profit Bars to no Fill, right-click on any of the Orange Bars after selecting the Chart.
  • Then click on the drop-down list of the Fill and choose No Fill.

  • You will see the Next Profit Bars become neat and clean.
  • Now click on the Plus (+) symbol of the chart and select Data Labels >> More Options…

how to show percentage change in excel graph

  • Then the Format Data Labels will appear. Uncheck Value and check Value From Cells.

  • You will see a dialog box.
  • Select the range H5:H10 (Percentage column) for the Select Data Label Range section and click OK.

how to show percentage change in excel graph

After that, you will see the increase and decrease in monthly profits in percentage in the chart.

This is a very nice and effective way to show the percentage change in Excel Graph.

Read More: Calculate Percentage Difference Between Two Numbers in Excel


2. Showing Percentage Change by Using Line Chart in Excel

You can also represent percentage change by using a line diagram.

Steps:

  • Select the range B4:C11 and then go to Insert >> Line With Markers.

how to show percentage change in excel graph

  • You will see the Line Chart. But you won’t see the percentage change in that graph.

  • To see the percentage change, please go through the steps of Section 1 of this article to generate the Line Graph. The process of showing percentage change is completely similar to the first section. Only this time, we don’t need the Error Bars, so we uncheck Error Bars. After repeating those steps, you will finally get a Line Diagram of the percentage change.

how to show percentage change in excel graph

So, this is another simple way to show the percentage change in an Excel graph.

Read More: How to Calculate Percentage Change with Negative Numbers in Excel


Practice Section

Here I present to you the dataset that we used to show the percentage change in an Excel graph so that you can practice on your own.


Download Practice Workbook


Conclusion

My goal was to give you some basic tips on how to show the percentage change in an Excel graph. This is very important when you want to understand the business condition of your organization and for that to predict what kind of action needs to be taken. If you have any questions or feedback, please feel free to share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Percentage ChangeCalculating Percentages | Calculate in Excel | 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.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo