XL: Finding the Percentage of Change Between Values

 Last reviewed: February 2, 1998
Article ID: Q101168
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
  • Microsoft Excel for OS/2, versions 2.2, 3.0
  • Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, there is no built-in function to calculate the percentage of change from one value to another. However, you can use the following formula to calculate the percentage change: 

   =(new_value-original_value)/ABS(original_value)
Original_value represents the value that the percentage of change is based on and new_value represents the value that has changed.

The following alternate formula may also be used: 

   =(new_value/original_value)-1
To display the resulting value as a percentage value, format the cell containing the formula with a percentage format.

MORE INFORMATION

Example

To see an example, follow these steps:

  1. In a new worksheet, enter the following values: 

          A1: Sales for 1990     B1: Sales for 1991
      A2: $100,000           B2: $175,000
      A4: Percent Change     B4: =(B2-A2)/ABS(A2)

   If you use the alternate formula, enter the formula "=(B2/A2)-1"
   (without the quotation marks) in cell B4.

  2. With cell B4 selected, choose Number from the Format menu and choose a percent format. (In Microsoft Excel 5.0 and later, click Cells on the Format menu, select the Number tab, and select a percent format.)

You will see that the 1991 sales increased 75-percent over the sales for 1990. This is the percent of change.

Additional query words: 2.00 2.01 2.10 2.20 2.21 3.00 4.00 4.00a decrease
difference 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98 XL97 XL7 XL5 XL4 XL3
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.2,3.0,4.0,5.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.