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

Get FREE Advanced Excel Exercises with Solutions!

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.


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


Calculate Percentage Using Absolute Cell Reference in Excel: 4 Methods


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: Make an Excel Spreadsheet Automatically Calculate Percentage


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.


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.


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 didn’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 a 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.


Download Practice Workbook

To practice by yourself, download the following workbook.


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


<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo