When working in Google Sheets, 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. There are instances when we want a cell reference to stay the same and not adjust. When this happens, we need to use an absolute cell reference.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
- To begin copying the practice workbook in a new browser window,
Right-Click Copy practice workbook, Click Open link in new window
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
- To copy the workbook file, in the browser window,
Click 
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
This Google Sheets 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, in the toolbar,
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, in the toolbar,
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
- To format the value in cell J2 as a percentage, on the toolbar,
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. Google Sheets 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.