Tech Tips Home
The Best Tech Tips And Daily Deals

WorldStart Tech Tip And Store Search

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

# OpenOffice.org Calc: Relative vs Absolute References

Wednesday, June 15th, 2011 by | Filed Under: OpenOffice

When you create a formula in a Calc spreadsheet you use cell locations in the formula to tell Calc where to find the data to use.

Take the following example:

In cell A7 I told Calc to add the values in cells A2 & A3.

Based on the way they’re written they are relative cell references… meaning that the way Calc understands the references, is to add the values in the cells that are 5 and 4 cells above the formula.

If we copy and paste that formula into cell A9 we would now see this:

Since we moved the formula 2 rows down the cells it pulls from are also 2 rows down… meaning that the actual numbers added are still exactly 5 and 4 cells above the formula.

When the formula is pasted into cell B7 we get this result:

It still adds the values that are exactly 5 and 4 cells above the formula but since it’s now in column B it’s adding the cells in column B too.

There is a way to reference a cell so that no matter how many times or where you copy / paste it, the data in the same cells will always be used.

This is called an absolute cell reference and to create one you simply have to use a strategically placed \$ or two.

Each cell location is made up of 2 parts – the column letter and the row number.

If you place a \$ in front of the column letter then no matter where the formula is moved it will always pull data from that column… however, the rows will still be treated as relative. You will find that the row numbers will change as appropriate even though the column letter didn’t.

=\$A2+\$A3

Placing a \$ in front of the row number will hold it constant as the formula is pasted but the column will change.

=A\$2+A\$3

And, as you may have already guessed, inserting two \$‘s (in front of both the column letter and the row number) will hold that cell reference as is no matter where you paste the formula. It will Absolutely reference that cell – end of story.

=\$A\$2+\$A\$3

Oh, and one more thing… you don’t actually have to hand-type all those dollar signs into your formulas.

Simply select a cell reference within a formula and use Shift + F4 to rotate between the four possible settings. (no \$, \$ on column letter only, \$ on row number only or \$ on both)

Understanding the power of the the \$ in your formula references is an invaluable tool that will prevent a lot of frustration when formulas are moved or copied elsewhere.

~ April

• Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

• WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

• Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Subscribe

We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.

Subscribe

Categories:
Archives: