Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)

Sometimes, in our Excel worksheet, we need to calculate the percentage of multiple cells with reference to a specific cell value. But the AutoFill tool changes the relative cell locations automatically. So, in this article, we’ll show you the easiest ways to Calculate the Percentage in Excel using Absolute Cell Reference.

Download Practice Workbook

To practice by yourself, download the following workbook.

What is Absolute Cell Reference?

The Absolute Cell Reference in Excel is to use one particular cell in a calculation and keep that cell constant by not moving to relative locations in further calculations. The Dollar Symbol ‘$’ usually represents the Absolute Cell Reference in Excel.

What is Percentage?

A Percentage is a number or ratio when expressed as a fraction of 100. The sign of percentage is ‘%’. The formula for basic percentage calculation is:

Percentage = (Part/Whole)*100

Dataset Introduction

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. In this case, we’ll show you how to find out the percentage of the net sales of each salesman to the Total Net Sales using absolute cell reference.

4 Methods to Calculate Percentage Using Absolute Cell Reference in Excel

1. Manually Calculate Percentage in Excel Using Absolute Cell Reference

There are two ways to input the Dollar Symbol which represents the absolute cell reference in Excel. At first, we’ll manually type the dollar sign, ‘$’ in our percentage calculation formula. Additionally, we’ll use the Divisor Symbol, which is a Forward Slash (/), and the Excel Percentage format to determine the net sales percentage of each Salesman to the Total sales. Therefore, follow the steps below to know how to perform the operation.

STEPS:

• Firstly, select cell E5. Here, type the formula:

Here, we have applied the absolute cell reference for cell D11 which is our Divisor. Now, if we use the AutoFill tool to complete the rest of the series, the Dividend will get changed only, while keeping the divisor D11 constant.

• Then, press Enter and use the AutoFill tool to fill the series.

• After that, select the ‘%’ icon in the Number group under the Home tab.

• Finally, the desired percentage outputs will appear in column E.

Related Content: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)

2. Keyboard Shortcut for Absolute Cell Reference in Calculating Percentage

Another way to apply the absolute cell reference in Excel formulas is by using the keyboard shortcut. In this method, we’ll use the key ‘F4’ to input the dollar sign in our formula for percentage calculation. So, follow along to learn the process.

STEPS:

• At first, select cell E5. Here, type the formula:

• Then, press the key F4. As a result, you’ll see the formula as shown in the following picture.

To clarify, the F4 key inputs the dollar symbol in cell D11. And thus, cell D11 has become an absolute cell reference. Using the AutoFill tool to fill the series won’t change D11 to relative locations anymore.

• After that, press Enter. And then, use the AutoFill tool to complete the series.

• Now, in the Number group under the Home tab, select the ‘%’ icon.

• Eventually, it’ll return the respective percentages.

Read More: Percentage Formula in Excel (6 Examples)

Similar Readings:

• Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
• How to Calculate Percentage of a Number in Excel (5 Easy Ways)
• Use the Exponential Growth Formula in Excel (2 Methods)
• Subtract a Percentage in Excel (Easy Way)
• Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)

3. Using Absolute Cell Reference for Percentage Difference Calculation

Sometimes, we have to perform analysis in our Excel datasheet to get a clear perspective about how the product or service is making an impact on the market or to see improvements. In this case, we have set a Target sales amount of 10,000, and from here, we’ll determine the percentage difference between this target and the net sales amount of each Salesman. Hence, learn the steps below to perform the task.

STEPS:

• Firstly, select cell E5 and type the formula:

• Then, press Enter and use the AutoFill tool for the rest.

• Subsequently, select the ‘%’ icon in the Number group under the Home tab.

• At last, the required percentage difference will appear in column E.

Read More: Percentage Difference between Two Percentages Excel (2 Easy Ways)

4. SUMIF Function to Calculate Percentage with Absolute Cell Reference

At times, we may need to calculate the contribution of a particular person or product to the total sales amount. For this purpose, we can use the Excel SUMIF function along with absolute cell reference. So, in our last method, we’ll apply the SUMIF function to find out the impact in percentage on the total sales made by the salesman Wilham and Simon. Therefore, follow the steps given below in order to compute the percentage.

STEPS:

• In the beginning, select cell G5 and type the formula:

Here, B5:B10 is the criteria range, B5 is the criteria, and D5:D10 is the sum range. We applied the absolute cell reference in the criteria range and the sum range as we don’t want the ranges to change in our next calculation. But, B5 will change to B6 when we use the AutoFill tool as our next desired criteria are Simon.

• Then, press Enter and apply the AutoFill tool to get the contribution of Simon.

• After that, under the Home tab, select the ‘%’ icon in the Number tab.

• Finally, you’ll get the contribution of each Salesman in percentage.

Related Content: How to Calculate Reverse Percentage in Excel (4 Easy Examples)

Conclusion

Henceforth, you will be able to Calculate Percentage in Excel using Absolute Cell Reference with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Calculate Average Percentage Increase for Marks in Excel Formula
• Calculate Percentage Increase from Zero in Excel (4 Methods)
• How to Calculate Percentage Change with Negative Numbers in Excel
• Apply Percentage Formula in Excel for Marksheet (7 Applications)
• How to Forecast Growth Rate in Excel (2 Methods)