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

This article will illustrate how to show the percentage change in an Excel graph. Using an Excel graph can present you 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


Download Practice Workbook


2 Ways to Show Percentage Change in Excel Graph

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. Let’s make some simple discussion below.

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

  • Use the Fill Handle to AutoFill the lower cells.

  • 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

  • Then 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 of 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. Especially 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. 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.

  • A dialog box will appear. Select the 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 Data Label Range 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.


2. Showing Percentage Change by Using Line Chart in Excel

You can also represent percentage change by using a line diagram. To do this, follow the description below.

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 then. But you won’t see the percentage change in that graph.

  • So, 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.


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.


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.

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo