When working in Microsoft Excel, by default, cell references are relative. This means when a formula is entered and then copied to a new location, the cell references used in the formula adjust to reflect the new location. Most of the time, this is exactly what we want to happen. We want to copy a formula down a column and know it is still correct. However, there are some instances when we want a cell reference to stay the same and not adjust. When this happens, we need to use an absolutecell reference.
This Microsoft Excel worksheet has been created to track sales for a fundraiser. At the end of the fundraiser, a certain percentage of the sales will be donated to a worthy cause. We can use an absolute cell reference in a formula to calculate how much money from each items' sales will be donated.
Before creating the formula, let's add some new column headers to the worksheet.
Adding text data
Let's add some new column headers to the worksheet. We're going to calculate how much money will be donated for each item sold, so we'll need a column labeled "Donation." Let's make column E the Donation column.
To make cell E1 the active cell,
Click in cell E1
To add the column header, in cell E1, type:
Donation Enter key
To return to cell E1, on the keyboard, press:
Up Arrow key
To format cell E1, if necessary, on the left side of the Home tab,
Click
Now let's add a column header for the amount of our donation. This information doesn't necessarily need to be next to the donation column but needs to have a cell reference we can use to create the formula. We'll put it in column J.
To make cell J1 the active cell,
Click in cell J1
To add the column header, in cell J1, type:
Donation Amount Enter key
To return to cell J1, on the keyboard, press:
Up Arrow key
To format cell J1, on the left side of the Home tab,
Click
To place the cursor to widen column J,
Point to the border between columns J and K
Your cursor should look like the following example:
To widen column J,
Double-Click the border between columns J and K
Calculating a percentage
Now that the worksheet has been set up to calculate the donations, let's enter the donation amount and the formula. Our fundraiser is going to donate 19% of the profits to a worthy cause.
To make cell J2 the active cell,
Click in cell J2
To enter the donation amount, .19, in cell J2, type:
.19 Enter key
To return to cell J2, on the keyboard, press:
Up Arrow key
To format the value in cell J2 as a percentage, in the middle of the Home tab,
Click
Cell J2 now shows the donation amount of 19%. We will use this cell reference to calculate the amount of donations from each item. The formula will multiply the total Item Sales in column D by the percentage in cell J2.
To make cell E2 the active cell,
Click in cell E2
To add the formula, type:
=D2*J2 Enter key
The fundraiser will donate $76.95 from the sales of tote bags.
Now let's copy that formula down column E.
To make cell E2 the active cell,
Click in cell E2
To copy the formula using the auto-fill handle, in cell E2,
Point to the autofill handle, Press & Drag the autofill handle to cell E7
The remaining cells of column E fill in with zeroes. This is not the expected result. Let's examine why this happened.
Using an absolute reference in a formula
When the formula in cell E2 was copied down column E, the cell references were adjusted for each row. Typically this works well, but in this case, we wanted the reference to cell J2 to remain as cell J2. We did not want it to adjust. We can fix this by using an absolute reference.
To make cell E3 the active cell,
Click in cell E3
With cell E3 as the active cell, we can see the formula in the Formula bar is =D3*J3. When copying this formula, we wanted the reference to column D to adjust for each row; meaning we wanted D2 to become D3, then D4, and so on. However, we do not want the reference to cell J2 to adjust relative to the row. The reference to J2 was adjusted to cell J3. Cell J3 is blank. Microsoft Excel then multiplied cell D3 by nothing and returned the result of $0.00.
We can make the reference to cell J2 an absolute reference. By doing this, we ensure that when the formula is copied, the reference to cell J2 remains.
To make a cell reference absolute, we add a dollar sign ($) before the row and column. The new formula will read: =D2*$J$2.
To make cell E2 the active cell,
Click in cell E2
To select the reference to cell J2, in the Formula bar,
Press & Drag J2
To make the reference to cell J2 absolute, type:
$J$2 Enter key
The value in cell E3, $76.95, does not change, but the formula will now work correctly when copied to the remaining cells in column E.
To make cell E2 the active cell,
Click in cell E2
To copy the formula using the auto-fill handle, in cell E2,
Point to the autofill handle, Press & Drag the autofill handle to cell E7
To examine the formula in column E, on the keyboard, press:
Down Arrow key
As you move down column E, the reference to the Item Sales adjusts relative to the row, but the reference to the Donation Amount stays $J$2.
