Video Lesson: Absolute & Relative Referencing

Absolute and Relative Cell Referencing

Note: This video was originally the 3rd lesson in the plan, but due to the comments regarding it being too confusing at that level, I have moved it to the Intermediate section effective 3/23/17.

This video tutorial focuses on Absolute vs. Relative cell referencing within Excel.  You likely know it as the dollar sign (\$) used in functions/formulas (for example, C5 vs \$C\$5).

There is also a quick demonstration of the video using a VLOOKUP formula.  The VLOOKUP formula is explained in more detail in these two lessons here and here, but the example in this video gives a basic understanding of how it works in Excel.

Update 5-22: I’ve noticed some users (especially those with Macs) have had difficulty viewing this video.  Since it is one of the older videos, I will be re-recording it and adding information so that it is comparable to more recent videos.

If you’re following the Lesson Guide, next up is Math & Statistical Functions (e.g. SUM, AVERAGE)

This entry was posted in Workbook/Reference Lessons and tagged , , , , , , . Bookmark the permalink.

58 Responses to Video Lesson: Absolute & Relative Referencing

1. Jude Okocha says:

Hello ben, your videos are really helpful and I appreciate what you are doing. I tried the vlookup function but I still get this results #REF and I was mindful of the possible errors that might occur as you explained in this video.

2. Anonymous says:

Hi M8

3. luciano says:

Thank you very much for these vídeos! Many people charge for this some thousands of dollars! The quality is good enough for those who are at least self learners. Congratulations!!

4. Sam says:

Great , Cell referencing surely will help me lot at time of preparing reports

5. John Hotzfield says:

You totally lost me on this video.

• Anonymous says:

I completely agree! He totally lost me when he went into VLookup.

• Suzanne Lieberman says:

me too!

• Anonymous says:

What lesson in the workbook are we supposed to be following here?? Thanks

• Anonymous says:

Same here!!

• I have now moved this lesson to the ‘Intermediate’ section based on the above information about it being too confusing this early on in training.

6. James says:

Great video. For those who got value out of the Vlookup lesson, also research INDEX MATCH

7. Shiqi says:

Hi Ben,

Thank you for the videos. As a graduating college student, I feel so weak in excel. I don’t know much about it. The lessons have given me a nice look into it, and I feel good about following through your lessons! This will potentially land me a job in finance, which is what I am looking for. Thank you!!!
I am just a little curious, why are you giving free excel lessons?

8. andrea says:

Hi, thanks for your tutorials. I am an absolute beginner, and the first two were fantastic. This video, however, as the third in the suggested series, was like being thrown into the deep end not knowing how to swim. I didn’t have a point of reference from which to grasp the information, had no idea how this was applicable to anything (i.e. why you’d want to autofill the same numbers over and over with the \$ method), and the steps moved entirely too fast. I’d love to see a third video in the series that follows the “beginner” methodology more, like you did in the first two. This one was just too confusing. Sorry 🙁

• Hervin says:

I agree too. The first two videos were perfect. But this video leaves me very confused. If I knew why I was doing some of these functions I might understand this a lot better. But this one eludes me. I am going to check out some other books to see what they say.

• I’m having the same issues. It seems like this video has taken a leap of logic that I can’t follow as easily.

• Irene says:

What??? Could you repeat this about 100 times please? LOL Great video until this point. I am totally confused.

• Hi, I’ve noticed that this lesson in particular loses people sometimes. I will be going through and fixing it, but I can say that if you skip the video and move on to further training and it will hopefully click later. Sorry for the inconvenience!

• Anonymous says:

dank

9. Anonymous says:

Hello Ben,

Thanks for sharing the videos

i have a question ” Why do we need to use False in Vlookup”

10. Anonymous says:

Hello…

u r really good in excel..
i have a question “why do we need to use False in vlookup”

11. Rita Smith says:

Hi!

You do a great job and are very knowledgeable! You have given me many good, tangible ideas for teaching my students. I really like your videos overall with the exception of the placement of this one. I think this one should belongs in the advanced area.

Thanks for doing this!

12. Reblogged this on Pablo Milandu.

13. awliabazar.com says:

suppose you added a little content? I am not suggesting your content isn’t good, however what if you added a post title
that grabbed people’s attention? I mean Video Lesson: Absolute & Relative Referencing |
Free Excel Training! is a little plain. You ought to peek at Yahoo’s
to click. You might try adding a video or a related pic or two to grab readers interested about everything’ve written. Just my opinion, it could bring

14. Anonymous says:

I don’t understand why you are using a vlookup in a beginner’s course (you even said that most people don’t even know what a vlookup is) and why you are therafter spending 5 minutes messing up your vlookup.

• Hey Anonymous,

Thanks for the constructive criticism. Actually this is one of the oldest / first videos I recorded and at the time I wasn’t entirely sure which way the course was going so I probably should have used a different method to show Absolute and Relative cell referencing.

Sorry if you felt your time was wasted with this lesson, but hopefully the message about cell referencing comes across.

Here’s another site that goes over the difference between Absolute and Relative cell referencing:
http://www.howtogeek.com/school/microsoft-excel-formulas-and-functions/lesson3/

Cheers,

Ben

• Anonymous says:

On the flip side, it never hurts to get exposed to something advanced you want to eventually understand, even if you don’t get it now.

That way, when it’s introduced later, you’ll already have had that exposure, and things will click even more.

Always a positive side to something.

Cheers,

Harrison

15. Anonymous says:

Awesome!!!! I really accumulated a lot Excel knowledge from the series videos! Thank U very much!!!

16. If somе one desires tߋ be updated with most up-tօ-date technologies afterward he must
Ƅe visit this web site ɑnԀ bе սp to date daily.

17. Hi, I do believe this is a great web site. I stumbledupon it 😉 I am going
to come back yet again since I bookmarked it. Money and freedom is the greatest way to change, may you be rich and continue
to guide other people.

michael kors handbags outlet

• Shiqi says:

Same here. Bookmarked to follow through the lessons one at a time!

18. Anonymous says:

Why don’t you use Youtube? I have a PC, chrome, everything updated and half these videos freeze a few minutes in for no reason.

• Anonymous says:

Freeze on a video can be a hardware video card problem, a video driver problem, or a internet speed problem, good luck fixing it – this is a very good training video on Excel to watch

• Anonymous says:

Just go to Vimeo (click Vimeo) and download the video. It’s definitely your computer/internet connection at fault here.

19. Anonymous says:

Hi,
Your videos are very well done. I could not open the current video with IE but it worked with Google Chrome.

Thank you for sharing your knowledge and taking the time and effort to create such useful videos.
Josée

20. Anonymous says:

Why did u make the totals of the vlookup table 1 row short of the source table? Just curious if it wasn’t accidental or some mysterious clever reason.

21. Anonymous says:

Can you explain, where this can be useful?

22. K. says:

The latest version of Firefox appears unstable with the flash plugins. Couldn’t view this video in Firefox, but it works just fine in IE.

23. Anonymous says:

Thank you for this! Appreciate this very much!

24. Felix says:

I love this website! But I didn’t fully understand this lesson. I did a VLOOKUP course on Microsoft’s support website, I thought I got it. But in this lesson, I don’t understand what you are trying to do with the second table. I have looked at the formula in the downloadable table so I see the arguments but the goal of the second table is still not clear to me.

25. Andreas says:

I still have problems… it crashes.

• Catherine says:

Same here. So disappointed. I am really hoping I can watch this video!

• I don’t have any problems playing it. It seems the people who I’ve heard have a problem are using Mac’s.

I’ll try to re-upload and/or re-render the video and see if that fixes it.

In the meantime, you can try going to the Vimeo page and downloading the video file. It can be found here:

Hope that helps!

Ben

26. Anonymous says:

kudos! good job

27. majid says:

great training well done

28. C says:

Great videos, they’re very much appreciated!

29. Anonymous says:

Dood…Awesome!

30. shan says:

Great video…. thanks a lot

31. marcsmith says:

Happy to have found your series, Ben! Great stuff! I’m not having problems viewing the videos, except this one keeps crashing my firefox browser a second after hitting play.

• Hey Mark!

The video seems to play fine for me, hope it’s still not giving you issue! Could be a problem with vimeo…

Cheers,

Ben

• Erika Birdsley says:

I am having trouble viewing this video, also. It either crashes, or when it finally did play (the next day), it is blurry and looks like it is reversed from a normal view.

• monicalee says:

It crashes my Firefox browser as well Running firefox 12.0 on a Macbook 10.5.8

• It works fine on me. I don’t have a Mac to try it on so I’m not sure what the issue is unfortunately.

You can try going to vimeo.com/excelexposure and see if it works on their site.

• Anonymous says:

Had the same problem… says adobe flush p[lug in crashed…. same with vimeo…

I tried a different browser like google chrome.. IT WORKED… thanks BEN…

32. quick question, are the worksheets you use on the videos available in the master book? if not, not big deal, i just wanted to see if i could use them to test out the new stuff i learn

Yes, the Excel workbook that is reviewed in the video can be found in the Master Workbook. Here’s a screenshot where you can see that it is the spreadsheet entitled “9 – Referencing”:

http://i.imgur.com/cp2Up.png

Let me know if you still have problems.

Ben

33. KosherDog says:

Thank you! Toggling between this video and the naming video until I really get it all into my head, and move it to my bag of Excel tools!