Absolute value means the actual value of a number without any sign. Mathematically the absolute value is the modulus of a number. In this article, we will show you how to get absolute value in Excel.

There are multiple ways to get absolute value in Excel. Of them, using the **ABS **function is the simplest way to get absolute value in Excel.

We will also discuss other ways consisting of functions, **Pivot Table**, **Power Query**, and **VBA** to get absolute value in Excel.

Look at the following image to get the brief idea of how to calculate an absolute value in Excel.

**What Does Absolute Value Mean?**

Absolute value means the distance of a number from zero in the number line. Whether the number is positive or negative, the absolute value is always positive. The absolute value of **0** and positive numbers remain unchanged. Absolute values do not contain any sign. Mathematically we can say this is the modulus of any number and represent it as **|X|**. Here, **X** can be any positive or negative number and the result will be an absolute value is **X** without any sign.

**Note:**

Absolute value and **absolute cell reference** are two different things. Do not mix up them. The absolute cell reference is a special type of cell addressing in Excel, that does not change by dragging or copying that cell.

## How to Get Absolute Value in Excel: 8 Suitable Ways

In this article, we will discuss **8** different ways how to get absolute value in Excel for any number. For that, we will consider the following dataset consisting of the name of the **Brand**, sales of the present day, and the previous day.

Now, we will find out the variance of sales using the following formula on **cell E5** and after that use **the Fill Handle icon** to execute that formula in the rest of the cells.

`=D5-C5`

We get both positive and negative values in the **Variance** column. We will show how to get the absolute value of variance in the below discussion.

**1. Use of Excel ABS Function to Get Absolute Value**

**The ABS function** is most widely used to get absolute value in Excel. This is the easiest way for getting absolute value.

- Go to
**cell F5**and insert the following formula based on the**ABS**function.

`=ABS(E5)`

- Then drag the
**Fill Handle**icon downwards.

The **ABS **function always returns the absolute value of any given reference.

**Read More:** **Opposite of ABS Function in Excel**

**2. Apply Condition Using the IF Function to Get Absolute Value**

In this section, we will apply a condition using **the IF function** to determine whether the given number is positive or negative. If the given number is smaller than zero means negative, it will return the opposite positive value, otherwise will remain the same.

- Insert the following formula on
**cell F5**and pull the**Fill Handle**icon,

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

We get the absolute value for variances in **column F**.

**3. Combination of SQRT and POWER Functions to Find Absolute Value**

This is another way to get absolute value in Excel. Here, we will use the combination of the **SQRT** and **POWER** functions. The **SQRT **function returns the positive square root value, and the **POWER **function results based on a specific power.

In this section, we will consider power **2** of the given number. After that, the **SQRT **function returns the square root value which is the absolute value of the given number. So, raising the power of a number by **2** and then calculating the square root of the result will return the absolute value of the initial number.

- Input the following formula on
**cell F5**and drag the**Fill Handle**icon.

`=SQRT(POWER(E5,2))`

We can also use the **circumflex** (**^**)” operator in place of **POWER **function and the formula will look like this.

`=SQRT(E5^2))`

**4. Excel MAX Function to Get Absolute Value by Comparing the Opposite Value**

We can also use **the MAX function** to determine the absolute value of any number. Here, **1st** we multiply the given number by **-1**. Then, the **MAX **function compares the given number and its opposite number multiplied by **-1** and returns the maximum number of them.

- Apply the formula below on
**cell F5**and pull the**Fill Handle**icon.

`=MAX(-1*E5,E5)`

**5. Use of SIGN Function to Multiply the Given Number with its Sign for Absolute Value**

**The SIGN function** determines the sign of a number. For positive and negative numbers, it will return **1** and **-1** respectively. Here, we used the **SIGN **function to get the sign of the given number, then multiply the given number by that sign. So, whatever the number is positive or negative always returns a positive number.

- Move to
**cell F5**and put in the following formula.

`=SIGN(E5)*E5`

- Then, pull the
**Fill Handle**icon.

This converts the negative number into a positive, and the positive number does not change.

**6. How to Get Absolute Value in Excel Using Pivot Table**

**Pivot Table** is an interesting feature of Excel. This feature can also be used how to get absolute value in Excel. Go to the below section for details.

**📌 ****Steps:**

- Select
**range B4:E9 >> Insert**tab**>>**click drop-down of the**PivotTable****>>**select**From Table/Range**.

- The
**PivotTable from table or range**window appears. - Select the
**Existing Worksheet**option. - Move to the
**Location**section and select a cell (**cell G4**) in the dataset. - Finally, press the
**OK**button.

- The
**PivotTable1**has been inserted in the dataset. We can see the**PivotTable Fields**tab in the upper-right section.

- Now, customize the
**PivotTable1**. - Drag
**Brand**into**Rows**and**Variance**into**Values**section. - On the left side, we can see the customized
**Pivot Table**.

- Go to the
**PivotTable Analyze**tab**>>**click into**Field, Items, & Sets**option**>>**click on the**Calculated Field**option from the list.

**Insert Calculated Field**window appears.- Input a customized name in the
**Name**section and insert the following formula in the**Formula**section.

`=if(Variance<0,-1*Variance, Variance)`

- After that, click on the
**Add**button and finally click on**OK**.

- Look at the
**Pivot Table**of the dataset.

We get absolute values for the **Variance **column into the **Sum of Absolute Variance** column.

**7. How to Get Absolute Value in Excel Using Power Query**

**The Power Query** is another way to get absolute values for a large dataset in Excel. For details look at the below discussion.

**📌 ****Steps:**

- Select
**cell B4:E9 >>**go to**Data**tab**>>**choose**From Table/Range**option.

- The
**Create Table**window appears. Mark the**My table has headers**option. - Then, press the
**OK**button.

- We enter the
**Power Query Editor**. - Go to
**Add Column**tab**>>**click on the**Custom Column**option from the**General**group.

- The
**Custom Column**window appears. Insert a name in the**New column name**box. - Insert the following formula in the
**Custom column formula**box.

**=if [Variance] < 0 the -[Variance] else [Variance]**

- Next, press the
**OK**button.

- Look at the
**Power Query**window.

We get the absolute values on the **Absolute Variance** column.

**8. VBA Macro to Get Absolute Values for Large Excel Dataset**

**VBA** code is an excellent way to perform a predetermined task repeatedly. According to this article, we can get absolute values for a large dataset using **VBA** macro. Look at the below section for details.

**📌 ****Steps:**

- Go to the
**Sheet Name**section and press the right button of the mouse. - Choose the
**View Code**option from the**Context Menu**.

- We enter the
**VBA**window. - Choose the
**Module**option from the**Insert**tab. - Then, paste the following
**VBA**code on the**Module**.

```
Sub Calculating_Absolute_Value()
Dim Variance, V_cell As Range
Set Variance = Application.InputBox(Prompt:="Select Input Range", Type:=8)
For Each V_cell In Variance
V_cell.Offset(0, 1).Value = Abs(V_cell.Value)
Next V_cell
End Sub
```

**Code Breakdown**

**Dim Variance, V_cell As Range**

Declares two variables of the **range **type.

**Set Variance = Application.InputBox(Prompt:=”Select Input Range”, Type:=8)**

Set the value of **Variance** using an **InputBox **of the **range** type.

**For Each V_cell In Variance**

** V_cell.Offset(0, 1).Value = Abs(V_cell.Value)**

** Next V_cell**

Apply a loop for each cell of **Variance**. Abs function determines the absolute value for **V_cell** and inserts that value into the next column of the same row. Then, move to the next cell of **V_cell**.

- Run the
**VBA**code by pressing the**F5**button. - Select the desired range (
**E5:E9**) in the**Input**window. - Then, press the
**OK**button.

- Look at the dataset.

We can see the absolute values are on the **Absolute Variance** column.

## How to Get Absolute Value in Excel: 5 More Examples

The **ABS **function is widely used to get absolute value in Excel. Here, we will see some practical examples based on the **ABS **function to calculate absolute values in Excel.

### 1. Calculate the Absolute Value for the Conditional Sum

In this example, we find out the absolute value of the conditional sum. We have two series of data in **columns B** and **C**. We want to get the sum of values small than **0** from **Series 1** and the sum of values greater than **0** from **Series 2**.

For that, we applied the following formula based on the combination of **ABS** and **SUMIF **functions on **cell F4** to get the absolute value for the conditional sum.

`=ABS(SUMIF(B5:B9,"<0")+SUMIF(C5:C9,">0"))`

**Formula Breakdown**

**SUMIF(B5:B9,”<0″)**

Determines the sum of values lower than **0** from **Series 1**.

**Result: -55**

**SUMIF(C5:C9,”>0″)**

Determines the sum of values greater than **0** from **Series 2**.

**Result: 50**

**ABS(SUMIF(B5:B9,”<0″)+SUMIF(C5:C9,”>0″))**

The **ABS **function determines the absolute value of the sum of the conditional sum of previous **SUMIF **functions.

**Result: 5**

**Read More:** **Changing Negative Numbers to Positive in Excel**

### 2. Calculate the Absolute Sum Value for the Array Formula

In this example, we want to get the absolute value for an array sum. We can use the combination of **SUM **and **ABS **functions. The **ABS **function determines the absolute value of the array, and the** SUM **function adds the absolute values.

- Insert the following formula on
**cell E11**to get the absolute array sum of**Variance**.

`=SUM(ABS(E5:E9))`

We can also use the below formula based on the combination of **SUMPRODUCT **and **ABS **functions.

`=SUMPRODUCT(ABS(E5:E9))`

**Read More:** **How to Sum Absolute Value in Excel**

### 3. Find the Maximum/Minimum Absolute Value

In this example, we will find out the maximum and minimum absolute values in Excel. The combination of **MAX **and **ABS** is used for maximum and **MIN **and **ABS **for minimum absolute value.

We insert the following two formulas for maximum and minimum values at cells **E11 **and **E12 **respectively.

**For maximum value:**

`=MAX(ABS(E5:E9))`

**For minimum value:**

`=MIN(ABS(E5:E9))`

### 4. Average Absolute Values in Excel

Here, we will show how to get the average of absolute values. We have to insert **the AVERAGE function** with the **ABS** function for that.

- Insert the formula on
**cell E11**to get the average.

`=AVERAGE(ABS(E5:E9))`

### 5. Calculating Tolerance of Variance

In this example, we will show the tolerance for the absolute value of variance using conditional formatting.

**📌 ****Steps:**

- First, we will find out the tolerance of variance using the following formula on
**cell F5**.

`=(--(ABS(E5)<120))`

- Then, drag
**Fill Handle**icon downwards.

We can see if the variance is below the tolerance level showing **1**, otherwise **0**.

- Select
**range F5:F9 >>**go to the**Conditional Formatting****>>**choose**Icon Sets >>**choose the desired symbol from**Indicators**.

- Finally, we get the dataset with marking.

Right for a variance under tolerance level and cross for above tolerance level.

**Read More: How to Calculate Absolute Difference between Two Numbers in Excel**

## Frequently Asked Questions

**1. Can I get the absolute value of a complex number in Excel?**

Ans: Yes, we can use the **ABS **function to get the absolute value of a complex number. The formula may look like this, **ABS(x)**. Here, **x** is a complex number.

**2. Can I use the ABS function to calculate the distance between two numbers on a number line?**

Ans: We can use the **ABS **function to get calculate the distance between two numbers on a number. The distance between two numbers in the number line is the absolute value of the difference between two numbers. You can use the following formula: **ABS(A1-A2)**. Here, **A1**, and **A2** contain any two numbers.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we showed **8** different ways how to get absolute value in Excel. We also added some practical examples to get absolute values in Excel. I hope this will satisfy your needs. Don’t forget to give your suggestions in the comment box.