In this article:

Absolute Reference in Google Sheets: A Beginner's Guide

How to Add an Absolute Reference in Google Sheets

It's easy to add an absolute reference in Google Sheets. Here are the steps:

There are many times when you don't want a cell reference to change when copying or filling cells. You can use an absolute reference to always keep that row or cell constant in the formula. For example, maybe you have a list of numbers in Column A and a list of numbers in Column B. You want to multiply all of them by the same value in cell B1 but when you drag the formula down it starts multiplying A2 by B2 and A3 by B3 etc. This is when you would want to use an absolute reference. The B1 cell would be updated to $B$1 in this multiplication formula. Now when the formula is dragged down the column it will only multiply the values in column A by the Value in B1.


We use cell referencing in Google Sheets to help our formulas look for the values they need. These values are oftentimes not fixed; sometimes they are not even known at the time the formulas are added. Once the values are added or changed, the Google Sheets will automatically update the formula to get the new value. As a simple example, consider these set of values:

A table with values, percentage column empty


To calculate the percentages, we have to divide the scores in Test 1 by the total maximum score written above:

Percentage formula added and calculated


If we try to copy the equation to the rest of the items, however, the formula automatically adjusts. This is not always good, as we need the cell C1 reference fixed when we copy the formulas by dragging down the small blue box at the lower right. How can we do so? The solution is simple: we add $$ to the cell reference. So for our example, instead of writing C1, we write $C$1:

Percentage formula copied to other cells, reference fixed to a cell


The cell reference $C$1 makes an absolute reference to the cell located in Column C and Row 1. 

That is how you make an absolute reference to a specific cell. Pretty simple, right?

A Keyboard Shortcut

A quicker way, however, is to press F4 immediately after selecting a cell to add to your formula. (For Mac Users, press fn + F4!)

Before: cell reference is relative. After pressing F4: cell reference is absolute


The cell reference C1 now becomes $C$1.  If you press F4 again, however, you would notice that it does not easily revert to C1, but instead becomes C$1 then $C1. What do they mean?

Additional Tricks

Since $C$1 locks down the reference to column C and row 1, referencing cell C1, then similarly:

  1. C$1 locks down only row 1, but the column can change. Look at example below:
Top: Formula in Row 16 has absolute reference to specific row and column. Bottom: Formula in Row 17 has absolute reference only to a specific row.


For cell C16, we multiply the numbers at B16 and C15. Since we fixed the reference at B16, we see that the only reference that changed is at C15, which updated itself to point at D15 for cell D16, and so on. For cell C17, we multiply the numbers at B17 and C15. But we only fixed the row reference at row 17, so the column of the cell it is referencing also changed.


  1. $C1 locks down only row 1, but the column can change. Look at example below:
Left: Formula in Column C has absolute reference to specific row and column. Right: Formula in Column D has absolute reference only to a specific column.

For cell C16, we multiply the numbers at B16 and C15. Since we fixed the reference at C15, we see that the only reference that changed is at B16, which updated itself to point at B17 for cell C17, and so on. For cell D16, we multiply the numbers at B16 and D15. But we only fixed the row reference at column C, so the row of the cell it is referencing also changed.

‍Check out our invoice approval software and learn how you can manage all of your invoice approvals directly from a spreadsheet.

We hope this article has helped you and given you a better understanding of how to add an absolute reference in Google Sheets. You might also like our articles on how to reference another workbook in Google Sheets and how to create a drop-down list in Google Sheets.

To optimize your workflow, we recommend reading our guide on how to mail merge with Google Sheets and trying our software for insurance renewal reminders.

Related Articles

-How to Reference Another Sheet

-How to Use Named Ranges in Google Sheets

-Cell References

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started