While working in Excel, we often have to compute percentage of entries in a dataset using absolute cell references. This absolute cell reference means the locking of a cell value to reference it while applying the same formula to a large range of data. This article will talk about how we can calculate percentage using absolute cell reference of such dataset entries in excel.

## ๐ Download Excel File

Download the Excel file below.

## Overview of Absolute Cell Reference

When **copying a formula** from one cell to another in Excel, an absolute cell reference is a cell along which the row and column values remain constant. It is represented by a **$** sign. For example, to get the absolute reference for cell **C2**, write **$C$2** on the formula bar.

An absolute cell reference is required because, by default, when we copy a formula in Excel and paste it into another cell, the cell references change. To keep it constant, we must use an absolute cell reference.

## Learn to Calculate Percentage in Excel Using Absolute Cell Reference with These 4 Suitable Approaches

To demonstrate things easily, we will consider a dataset where we have different products with different sales amounts. We will use this dataset to calculate percentage values utilizing absolute cell reference by the following methods in Excel. The dataset is shown below:

**Approach**

### 1. Applying SUMIF Function

Suppose we have a target sale of about 10000 units and we need to find out some particular productsโ percentage of total sales. We can do this by using the **SUMIF** Function. The steps for this approach are as follows:

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- At first, we select cell
**F6**. - After that, we go over to the formula tab and write the following formula, and press
**Enter**.

`=SUMIF($B$6:$B$13,B10,$C$6:$C$13)/$C$15`

- The ratio of sports shoe sales with total sales will come out as the output.
- Now, we drag the
**Fill Handle**and drag it up to the next cell**F7**.

- We will get the ratio of sales of sports shoes and boots to total sales. Now we select
**F6:F7**and go to**Home**tab and in**Number**section, we select the**%ย**sign.

- We will subsequently get the sales percentage of Boots as Boots lie after Sports shoes in the catalog. The final output will look like the following image.

**ย Read More: ****5 Easy Methods to Add 10 Percent to a Number in Excel**

**Approach**

### 2. Using Conventional Method

The Conventional Method to calculate percentage in Excel using absolute cell reference includes the formula to be written with & signs in places where we need to absolute reference the cells. The breakdown of these steps are as follows:

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- At first, we select cell
**D6**. - Then we write the following formula in the formula bar.

`=C6/$C$14`

- Next, we place our cursor on the bottom right of cell
**D6**and drag the**Fill Handle**up to cell**D13**.

- The results will come out as seen in the following image.

- After that, we select
**D6:D13**and go to**Homeย**tab and then on the**Number**section of the Ribbon. We select the percentage sign as shown in the figure.

- Thus, the entries in
**D6:D13**will convert into percentages. The final output is shown as follows:

**ย Read More: ****3 Quick Ways to Add 20 Percent to a Price in Excel**

**Approach**

### 3. Applying Keyboard Shortcut

This method is almost similar to the previous method. But here, we absolute reference cells using a keyboard shortcut. The steps are discussed as follows:

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- At the beginning, we select the cell
**D6**. - Then we write the following formula in the formula bar.

`=C6/C14`

- Then we press the
**F4**key while our cursor remains after**C14**in the formula bar. This shortcut key**F4**will absolute reference the noted cell**C14**to**$C$14**. Then we press**Enter**.

- Consequently, we place our cursor on the bottom right of cell
**D6**and drag the**Fill Handle**up to cell**D13**.

- All the entries of the percentage of total column will look like the following image.

- Next, we select
**D6:D13**and go to**Home**tab and then on the**Number**section of the Ribbon. We select the percentage sign as shown in the figure.

- Thus, the entries in
**D6:D13**will convert into percentages. The final output will be like the following image.

**ย Read More: ****3 Ways to Add Percentage to Price in Excel with Formula**

**Approach**

### 4. Utilizing Absolute Cell Reference for Estimating Percentage Difference

We can also calculate percent difference in Excel by absolute referencing cells. Sometimes, companies have a target sale and a percent difference can be useful to find how each of the products are doing in terms of their sale. The steps for this process are as follows:

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- Firstly, we select cell
**D6**and write down the following formula in the formula bar. - We will see that the sales ratio difference of that particular product with respect to target sales will be visible as the cell value.

- Then we drag the
**Fill Handle**and get all the values up to cell**D13**.

- The difference with regard to target sales will look like the image below.

- Now, we will select the cell range
**D6:D13**and go to the**Number**section under the**Home**tab and click on the percentage (**%**) sign.

- We will see that the percent difference of all the products will come as output in the following image.

**ย Read More: ****Calculate Percentage Change with Negative Numbers in Excel**

## ๐ย Important Notes

๐๏ธย We should take care while pressing the **F4** key in the Keyboard Shortcut approach. Pressing **F4 **once absolute reference a cell. Pressing the key twice and thrice makes relative referencing based on column and row individually. At the fourth press, the cell becomes fully relative again.

๐๏ธย After pressing the percentage sign, we might see whole numbers. To get percentage values with decimal points more accurately, we can choose the increase or decrease decimal points from the **Number** section of the Ribbon under **Home** tab.

## ๐ย Takeaways from This Article

You have taken the following summed-up inputs from the article:

๐ **ย **We can calculate percentage by absolute referencing a cell by using **$ **sign before the column heading and number. By alternating the sign, we can **relative reference** a cell. We can also do this by pressing the **F4** key different times.

๐ย Using functions like **SUMIF** Function can help us find percentage values by absolute referencing cells.

๐ย Percent differences can also be calculated by modifying formula and absolute referencing a target cell.

## Conclusion

In conclusion, I hope that this article has provided you with insights about how we can calculate in Excel. If you want to know more about Excel, please visit our site **ExcelDen** and if you need to ask any queries regarding this topic, feel free to let us know down below in the following comment section.

## Related Articles

**3 Easy Ways to Calculate Accuracy Percentage in Excel****4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel****Calculate Percentage in Excel Based on Conditional Formatting****4 Easy Ways to Calculate Percentage in Excel Based on Cell Color****6 Ways to Forecast Growth Rate in Excel**