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

In this article, we’ll demonstrate the easiest ways to calculate the percentage in Excel using absolute cell references.

What are Absolute Cell References?

An absolute cell reference in Excel is a cell reference that remains constant regardless of where it is copied or filled to other cells. When used in a formula, the formula will always refer to the same cell, rather than adjusting its reference based on its new location. The Dollar Symbol ($) usually denotes an absolute cell reference in Excel.

What is Percentage?

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

Percentage = (Part/Whole)*100

Dataset Introduction

To illustrate our methods, we’ll use the sample dataset below, containing the Salesman, Product, and Net Sales of a company. We’ll determine each Salesman’s percentage of Total Net Sales using absolute cell references.

Calculating Percentage Using Absolute Cell References in Excel: 4 Methods

Method 1 – Manually Calculate Percentage in Excel Using Absolute Cell References

There are two ways to input the Dollar Symbol, which denotes absolute cell references in Excel. We’ll start by manually typing the dollar sign ‘$’ in our percentage calculation formula.

Steps:

In cell E5, enter the following formula:

=D5/$D$11

We have applied the absolute cell reference in cell D11, the Divisor. Now, if we use the AutoFill tool to complete the rest of the series, only the Dividend will get changed, while the divisor D11 will remain constant.

Press Enter and use the AutoFill tool to fill the series.

Select the ‘%’ icon in the Number group under the Home tab.

The desired percentage outputs are displayed in column E.

Method 2 – Using the Keyboard Shortcut for Absolute Cell Reference

Instead of entering $ signs manually in our formulas, we can insert them by using the F4 key.

Steps:

In cell E5, enter the following formula:

=D5/D11

Press F4.

The Divisor is now an absolute reference ($ signs have been added).

To clarify, the F4 key inserted the dollar symbol in cell D11, converting it into an absolute cell reference.

Press Enter, and use the AutoFill tool to complete the series.

In the Number group under the Home tab, select the ‘%’ icon.

The respective percentages are returned.

Method 3 – Using Absolute Cell Reference for Percentage Difference Calculations

Suppose we have set a Target sales amount of 10,000, and want to determine the percentage difference between this target and the net sales amount of each Salesman.

Steps:

Select cell E5 and enter the following formula:

=(C5-$D$12)/$D$12

Press Enter and use the AutoFill tool for the rest of the series.

Select the ‘%’ icon in the Number group under the Home tab.

The required percentage difference will appear in column E.

Method 4 – Using the SUMIF Function to Calculate Percentage with Absolute Cell References

In our last method, we’ll apply the SUMIF function to find out percentage of total sales contributed by the salesmen Wilham and Simon.

Steps:

Select cell G5 and enter the following 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 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 criterion is Simon.