The formula in the table calculates the percentage deviations
How to show percentage difference of two numbers in Excel spreadsheet
Percentages are required for many Excel calculations and evaluations. In the business environment, for example, it can be important to know the percentage deviations in sales between two months. The comparison of several months in the year can be just as interesting. Many Microsoft Excel users shy away from building complex formulas that determine the percentage deviation with auxiliary cells or many intermediate steps.
Mainly because of the error susceptibility of complicated Excel formulas, it makes sense to use a simpler and more effective way. An effective solution in this case can be the use of the ABS function. The ABS function is integrated into a formula and returns the absolute value of a number as the result.
In practice, this means that the number is returned as a positive value, regardless of its sign. The only argument ABS needs is the number whose absolute value you want to determine. You can pass this directly as a reference to a cell.
Video instruction – Microsoft Excel: Calculate percentage change of two values with Excel formula
Example for determining the percentage deviation
example table
To determine the percentage deviation with little effort, proceed as in the following example:
In the example table, cells A2:A13 contain the names of the months of a calendar year. In the adjacent column B, the associated sales figures for each month are entered. The figure below shows the structure of the table:
In column C, the percentage deviations from the previous month should now be determined. To do this, enter the following formula in cell C3:
= (cell month 1 – cell month 2) / ABS(cell month 2)
=(B3-B2)/ABS(B2)
How to designate cells with percentage format
This formula with the ABS function returns the decimal deviation. To display it as a percentage, assign a percentage format to the cell. To designate cells with percent format:
-
Highlight the appropriate cell
-
press the key combination “CTRL + 1” in all Excel versions
-
and select a format from the PERCENTAGE category.
Format Cells: Percent
-
Then copy the formula from cell C2 to the cells below to calculate the remaining variances. Alternatively, you can select the first cell with a built-in formula and copy the formula by dragging it down.
The following figure shows the use of the formula in the sample table:
Example table with percent deviation
Basics of percentage calculation in Microsoft Excel
Basically, you calculate the difference between two values by subtracting the starting value from the final value. The result is then divided by the initial value and multiplied by 100%.
When entering data, Microsoft Excel generally distinguishes between the value and the formatting of a cell. Changing the formatting, for example changing from STANDARD to PERCENTAGE, does not change the value of the Excel cell at any time. 1 percent of a number is defined as the hundredth part of that number.
Summary and Conclusion: The ABS function is effective to calculate percentage deviation
Instead of complex and difficult to understand formulas, the ABS function in Microsoft Excel can be helpful and effective to calculate the percentage deviation between two values. The Excel function is fitted into a formula and independently calculates the deviation. A formula with the integrated function ABS can easily be adapted and used for different calculation scenarios.
FAQ
How is the percentage calculated?
Basically, percentages are calculated by finding the difference between the final value and the starting value. In the next step, the result is divided by the initial value and multiplied by 100%.
Why is it important to know percentage deviations?
In both the professional and private environment, there are countless situations in which it is important to know the percentage deviation. With the knowledge of percentage deviations, measures can be taken in the purchasing or sales department of a company. Privately, one is interested, among other things, in the percentage deviation of the personal weight in the context of a fasting cure or a change in diet.
How to use the ABS function?
The ABS function returns the absolute value of a number, where the number is returned as a positive value regardless of its sign. In addition to being used in spreadsheets, ABS is used as a function to take the square root of a calculated number.
How to create a chart in Excel
In order to display the data from Excel spreadsheets, the user can create various charts. These visually represent the data and results…
