Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Calculate Percentage in Excel 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:
=D5/$D$11

Manually Calculate Percentage in Excel Using Absolute Cell ReferenceHere, 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.

Manually Calculate Percentage in Excel Using Absolute Cell Reference

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

Manually Calculate Percentage in Excel Using Absolute Cell Reference

  • 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:
=D5/D11
  • Then, press the key F4. As a result, you’ll see the formula as shown in the following picture.

Keyboard Shortcut for Absolute Cell Reference in Calculating Percentage

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.

Keyboard Shortcut for Absolute Cell Reference in Calculating Percentage

  • 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:


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:
=(C5-$D$12)/$D$12

Using Absolute Cell Reference for Percentage Difference Calculation

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

Using Absolute Cell Reference for Percentage Difference Calculation

  • 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:
=SUMIF($B$5:$B$10,B5,$D$5:$D$10)/$D$11

SUMIF Function to Calculate Percentage with Absolute Cell Reference

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.

SUMIF Function to Calculate Percentage with Absolute Cell Reference

  • 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

Aung

Aung

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo