Once you uncover the mystery of Absolute References copying and pasting formulas becomes child’s play.

You might have seen cell references in formulas surrounded by ‘$’ signs. For example $D$3:$D$10.  What’s that all about?

Well, the ‘$’ before the column or row reference instructs excel to keep the reference absolute.  Huh, I hear you say.  Ok, I’ll explain in English.

Looking at the table below we have a Commission Rate of 3% in cell G3.  In column E we want to calculate the commission as Total $k x Commission Rate 3%.

Excel Absolute References

I could simply enter the formula as =D3*3% and copy it down column E, but there are two problems with this that make it not ideal:

1)      I can’t easily see what the Commission Rate is without looking in the formula bar.  I could put it as part of the column heading e.g. ‘Commission $ @ 3%’, but that makes my heading bigger than it needs to be, and if I change the rate at a later date I have to go back and change the heading too, which is easily overlooked.

2)      If I change the rate I need to change the formula and copy it down the column again. Ok, so it might only take a few seconds to do this, but if you’re playing with scenarios and you want to change the rate a few times to see what the results are it’s much quicker and easier to just type a new figure in cell G3 and let Excel do the work to change the formula.

So assuming I’ve convinced you that referencing one cell for the commission rate is the best method let’s look at how absolute references work.

Excel Absolute References

First let’s look at what happens if you don’t use an absolute reference.  If you entered in cell E3 the formula =D3*G3 you would get the correct answer.  But if you were to copy that formula down the rest of column E Excel would dynamically update the formula to increase by one row as it goes down the page.  You can see this to the left where the reference to G3 goes G4, G5, G6 and so on.

How Absolute References Work in Excel

What we want Excel to do is dynamically update the cell reference to column D, but to keep the Commission Rate reference on cell G3. To do this we would use the ‘$’ signs to instruct Excel that this is an absolute reference, like this =D3*$G$3. Then when we copy the formula down the column it will be entered like example on the left here.

Other ways to use absolute references

  • Make a whole range of cells an absolute reference: $D$1:$F$1
  • Make only the column absolute $D3
  • Make only the row absolute D$3

As you can see in the examples above, whatever the ‘$’ sign prefixes is absolute. i.e. as you copy the formula anywhere in the spreadsheet the reference prefixed by the ‘$’ sign will not change.

Shortcut to entering Absolute References

The magical F4 key instantly enters the ‘$’ signs for you.  You can do it while you’re building your formula, or you can go back and edit the formula and enter them.  Of course you can also type them in but it’s quicker to use F4.

Let’s look at the different ways you can enter absolute references using the F4 key in more detail

1)      While you’re building your formula; as you can see below I have started to type a formula into the cell E3.  I have just selected cell G3, as you can see by the marching ants (dashed line) surrounding the cell.

How to enter Absolute References in Excel

At this point, before I press enter to finish the formula, I can press the F4 key and Excel will automatically put the ‘$’ signs around G3 for me like this.

Excel Absolute Reference Lesson

2)      Or I can go back to a cell at any time and press the F2 key to edit the cell.  I can then put my cursor anywhere in the cell reference I want absolute and press F4.  See below.

Excel Absolute References Tutorial

3)      If you want to absolute a range you have to highlight the cell range like the example below before pressing F4.

Excel absolute references tutorial

4)      The above examples show you how to apply an absolute reference to the column and the row, but if you keep pressing F4 Excel will scroll through your options. Using =D3*G3 as an example, I want to absolute G3:

a.       With the first press of F4 you will get =D3*$G$3

b.      With the second press of F4 you will get =D3*G$3

c.       With the third press of F4 you will get =D3*$G3

d.      With the fourth press of F4 you will get =D3*G3

So, now you know how Absolute References work in Excel, and how to apply them quickly using the F4 key, you can start to build better spreadsheets that you can dynamically update.

Download the workbook to practice here.

Share the knowlege with your friends and colleagues.  Click the icons below for Twitter, Facebook, Stumbleupon and many more.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 36 comments… read them below or add one }

maggie pont May 28, 2014 at 1:04 pm

Always sensational

Every group I train, I refer them to your web site.

Kind regards

maggie

Reply

Mynda Treacy May 28, 2014 at 1:09 pm

Wow, thanks Maggie :-)

Reply

rahul May 8, 2014 at 11:33 am

Thnx…its a nice tutorial…

Reply

Mynda Treacy May 8, 2014 at 11:37 am

Cheers, Rahul.

Reply

George Mousavi February 21, 2014 at 4:26 pm

I really enjoyed reading your blog.

Reply

Mynda Treacy February 21, 2014 at 8:48 pm

Cheers, George :-)

Reply

Dell February 11, 2014 at 6:38 am

I read your article regarding drop-down boxes and absolute validations. I didn’t quite get it. I have a formula in the SOURCE box of the Data Validation field that keeps moving with the subsequent boxes in the column. My formula is =data!A1:A12. My goal is to have that value in every cell of the column. When I check “apply these changes” it cuts off A1 and only leaves A2-A12 in the cell. The next cell cuts off A2 leaving A3-A12 and so on. I put in a new formula =data!$A1:$A12 (absolute reference) and it did the same thing! I don’t know what I’m doing wrong. Can you assist?

Reply

Mynda Treacy February 11, 2014 at 9:56 am

Hi Dell,

There are two components to every cell/range reference that you can absolute which are the Column reference and the Row reference.

The reference $A1 only has the column reference absolute i.e. the $ sign is before the A, where as A$1 only has the row reference absolute. If you want to absolute everything then you need to put the $ sign before both the column and row references like this: $A$1.

Likewise when referencing a range: $A$1:$A$12

Let me know if you get stuck.

Kind regards,

Mynda.

Reply

Patty August 4, 2013 at 2:20 am

I know a few excel basics, am trying to learn more. I am confused about the formula above using the absolute value. It looks like the running total will be the sum of the first number(the absolute value) and the last value entered. Skipping the ones in between. What am I missing?

Reply

Mynda Treacy August 4, 2013 at 3:56 pm

Hi Patty,

I’m not sure which formula you’re referring to. Can you please clarify.

Cheers,

Mynda.

Reply

khalid July 2, 2013 at 5:11 pm

Dear Sir,

Thx a lot for such informative mails.May GOD bless you.It help me a lot.

Reply

Mynda Treacy July 2, 2013 at 8:16 pm

You’re welcome, Khalid :)

Reply

ahsan April 27, 2013 at 1:23 am

Hi

thanks for the nice article.

I understand that absolute reference can be added to each cell using F4. But is it possible to convert more then one cell to absolute reference at the same time / simultaneously. cause i have a column of cells which are linked to cells on another sheet and i want to convert them to absolute reference

Reply

Mynda Treacy April 27, 2013 at 9:00 pm

Hi Ahsan,

You can convert a whole formula to absolute by first selecting the whole formula while in edit mode and then pressing F4, but you can’t absolute more than one formula at a time.

Kind regards,

Mynda.

Reply

Jay Jain April 3, 2013 at 1:34 pm

Thank you. very well illustrated and explained.

Reply

Carlo Estopia April 4, 2013 at 11:31 am

Hi Jay,

On behalf of Mynda,
You’re welcome!

Cheers,

CarloE

Reply

Narasimha Rao December 21, 2012 at 3:23 am

Really I Got a good friend thats you. After learning the lessons of Excel from you I got good name in my Department, while applying the formulas teaching by you. In view of this I won’t leave you upto learnt about the Excel completely. Do you give support to my interest?

Reply

Mahipal Singh December 14, 2012 at 2:40 am

it helped me a lot. it really makes everything easiest.
thanks for the great help…

Reply

Mynda Treacy December 14, 2012 at 9:58 am

You’re welcome, Mahipal :)

Reply

patricia November 1, 2012 at 8:48 pm

i just love how you teach..thank you.. and God bless..

Reply

Mynda Treacy November 2, 2012 at 9:50 pm

:) wow. Thanks, Patricia.

Reply

Kumar Subramanian August 17, 2012 at 3:27 am

I have no word to express my gratitude towards your service free service to those aspiring in this field. Receive my kudos.

Reply

Mynda Treacy August 17, 2012 at 3:05 pm

Thanks, Kumar :)

Reply

Kumar Subramanian August 17, 2012 at 3:24 am

I have no word to express my gratitude. very good effort towards the humanity. Those who wanted to strive hard in this ms excel need not do any more like that but very easily they can prolong their work in this chapter because your spoonfeeding worksheets.

Reply

Ramamoorthy June 5, 2012 at 9:29 pm

The tip is simple and easy to understand.
Thank you
Ram

Reply

Mynda Treacy June 6, 2012 at 2:52 pm

Thanks. Glad you liked it :)

Reply

Agatha Amadhila May 4, 2012 at 8:07 pm

Huh, it was so wonderful to have a new knowledge of an F4 trick. It really helped me. am going to write my test on the excel, am certain I will pass

Thanks for your help

Reply

Mynda Treacy May 4, 2012 at 9:00 pm

Thanks Agatha. Glad you liked it.

Good luck with your test :)

Kind regards,

Mynda.

Reply

Bill May 3, 2011 at 12:11 am

I didn’t know about the F4 trick, but the tutorial was a good review. I always like to know how to use functions that have real examples. Keep it up.

Reply

Mynda May 3, 2011 at 1:41 pm

Thanks, Bill. Glad you liked it :)

Reply

Click here November 26, 2010 at 12:26 pm

Nice article and i want to know more on this blog.

Reply

aparadekto October 26, 2010 at 1:25 am

= Comment moved into Tech Support =

Reply

Philip Treacy October 26, 2010 at 8:11 pm

Hi,

Thanks for your comment aparadekto

If you have any other technical questions or issues please use the Contact Us form or the Helpdesk to let us know.

Regards

Phil

Reply

Philip Treacy November 1, 2010 at 12:08 pm

Hi apardekto,

Upon investigation it may be that you have “Fit to width” turned off in Opera.

If you are seeing text beside the large images above, or text in tables, like on the the syllabus pages running together or overlapping, please click at the bottom right of your Opera screen and make sure “Fit to width” is turned on.

Regards

Phil

Reply

Janine September 29, 2010 at 1:31 pm

I feel so silly I didn’t know about this before. thanks for the workbook download it made it much clearer

Reply

Mynda September 29, 2010 at 8:25 pm

You’re welcome @Janine. I’m glad it helped you out.

Reply

Previous post:

Next post: