Calculate Percentage in Excel Using Absolute Cell Reference

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:

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.

• 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

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.

• 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

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.

• 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

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