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 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 
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: 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.
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.
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
- 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.
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
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.
- 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
- Calculate Percentage in Excel VBA
- How to Calculate Percentage for Multiple Rows in Excel
- How to Calculate Percentage of Filled Cells in Excel
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- How to Apply Percentage Formula for Multiple Cells in Excel
- IF Percentage Formula in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Use Food Cost Percentage Formula in Excel
- How to Calculate Variance Percentage in Excel
<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
