Highline Excel 2013 Class Video 05: Cell References: Relative, Absolute & Mixed (8 Examples)

Download workbook: people.highline...
This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel.
Topics in this video:
1. Relative Reference Example, Net Income Formula (01:09 min)
Ctrl + Enter to populate a range of cells with a formula (02:02 min)
2. Relative and Absolute Reference Example, Increase Price Formula (02:20 min)
Ctrl + Period ( . ) to jump to each corner of a highlighted range (04:29 min)
3. Relative and Absolute Reference Example, Commission Formula (05:00 min)
4. A. Relative and Absolute Reference Range Example, SUMIFS formula for adding with one condition. Top of sheet CR(4). (05:51 min)
5. Mixed Cell Reference Example, Income Statement Budget Example (07:56 min)
6. Mixed Cell Reference Example, Multiplication Table Example (19:23 min)
4. B. Mixed Cell Reference Example, Cross Tabulated Table with SUMIFS formula for adding with two conditions (criteria). Bottom of sheet CR(4). (22:47 min)
7. Mixed Cell Reference Example, Future Value (Value of pension example) (28:34 min)

[@excelisfun]

Search for this title: VLOOKUP function Beginner to Advanced 26 Examples (Excel VLOOKUP WEEK Video #1) two solutions in this video. one at 34:40 and one at 38:50.

[@excelisfun]

You are welcome a lot!

[@alonariel8170]

Michael Girvin you are a great teacher! great channel.

[@excelisfun]

I am so glad that the videos help! :)

[@faizaashfaq9377]

The coolest thing about your videos is Home Work section.... My most favourite part.... love it!

[@excelisfun]

I wish that F4 worked on table. Check out this video (it shows how to lock a table referecne): Highline Excel 2013 Class Video 08: Excel Table Formula Nomenclature / Structured References 22 Ex. at the 20:58 minute mark - it shows a trick for locking in a table... BTW, you are welcome for the videos!

[@rishabhmehta3364]

Thanks a lot, this is the first time m using excel. Before watching your vidoes i thought learning excel would a tough task for me, but u have pleasantly proven me wrong. I have never come across such a teacher who takes pain to make something complex appear fun to learn. Thanks once again.

[@excelisfun]

+Rishabh Mehta You are welcome!

[@KksSquad2016]

+ExcelIsFun How do you swap rows in a spreadsheet and keep the formulas that you have linked from another worksheet?

[@TheBuffalodiver]

Thank your for your time and presentation

[@jayeshsaroj3944]

Sir i can see your effort..may god bless you always.l’m very new to excel but you teach in so detail which is making me learn how excel is thinking which will make me use my logics later.once again god bless you

[@excelisfun]

You are welcome, JAYESH!!!! Thank you for helping support this free education with your comment, thumbs up and of course your Sub : )

[@ryanwilson9283]

This is a legit playlist to start learning excel from scratch. Thanks !

[@excelisfun]

+Ryan Wilson Glad you like it! Thanks for the Likes and comments!

[@ORDERGATIpsp]

Dear Michael you are fantastic awesome teacher. Thank you! Thank you! Thank you!

[@SamerDebs]

Great Tutorial, thanks.

[@muditace111]

man this is really a social service u r doing without charging anything its really amazing

[@excelisfun]

You are welcome, no charge at all!

[@alicerezacova1450]

Michael, you are amazing! :)

[@excelisfun]

+Alice Rezacova I am glad that the videos help!

[@scj1997]

WOWW!!! Iam late in this Platform....Thank you so muchh

[@mohammedjawad3918]

Thank you Mr. Michael that was a very fantastic and fab video. much appreciated.

[@excelisfun]

Glad you liked it!

[@lavitaneja4161]

you are really great boss......speechless.

[@lavitaneja4161]

i am

[@zoeyz996]

it is more clear than I learned in classroom:)

[@boraig1109]

That is absolute and relative amazing

[@726June]

Great Formula~~~ Thanks.

[@edbrocklehurst]

Hi.... Ive been looking for a specific formula and has driven me crazy for a week now I need excel to look up a date from a tray of data, which is easy, my problem is, it needs to consider 2 columns (employee ID's and training program theyve attended) Thank u in advance

[@juanfernandoariaszuluaga5069]

great explanation. I really appreciate it

[@MsCodeofficer]

Great. It helped me a lot. Thank you.

[@excelisfun]

You are welcome!

[@waqaskhan-zi4ub]

wooo!Excellsfun You Are Teaching us Imazing Excel Thanks.

[@excelisfun]

You are welcome!

[@LanguagedesignLearn]

it's very helpful,, thank you a loot

[@איגורשוויגר]

Hello, first of all thanks for your videos ! I found some error in your calculations at sheet cr2 and cr2(ans), at cell k25 when you trying to show how to find the sell price your formula is not correct. You wrote =B8&" - "&B8&" * "&B2&" = "&B8-B18, and it's has to be =B8&" - "&B8&" * "&1-B2&" = "&B8-B18 because 50-50*0.4≠20 and it's has to be 50-50(1-0.4)=20

[@sam5605]

THANK YOU SO MUCH SIR, I WOULD HV FAIL MY FINALS WITHOUT TIS VIDEO

[@excelisfun]

Glad you like it!

[@1CBAITY]

i have a question> WHY CANT YOU USE ABSOLUTE VALUE ON A NAMED TABLE.....THE TABLE NAME WILL COME UP BUT YOU CAN'T USE F4 TO MAKE IT ABSOLUTE.....BY THE WAY I LOVE YOUR CHANEL IT HAS TAUGHT ME ALOT....KEEP THE VIDS A COMMING!!!!

[@lavitaneja4161]

i am keen to read that book

[@triciadonovan6818]

If I wanted to reference a cell in a table such as a title, not a cell, and lock it... How do I do that? I seem to only be able to use the F4 option with cells. I am specifically talking about when using SUMIFS or AVERAGEIFS statements.

[@lavitaneja4161]

please don't forget to tell me the name of book you have written about excel.

[@AlexLeijen]

Ok, great explanation. One question though, what if u have a table. We have the formula like [@[id]:[ID]] to lock the row for a formula.. The uestion is how do i do the same for a column.. [[ID]:@[ID]] does not work.. Any ideas?

[@excelisfun]

I think for a column you have to do this for absolute: [[column]:[column]] Video about Tables (later in this class): Highline Excel 2013 Class Video 08: Excel Table Formula Nomenclature / Structured References 22 Ex.

[@Kevinsf]

that SUMIFS with multiple criterias are so confusing. It's gonna take a little time to let it sink in.

[@66koolkb]

so I keep trying to use this F4 shortcut to lock the cell, but all that does for my computer is give me options to change projector settings...can anyone please tell me what I am doing wrong?? It is quite tedious to manually add the $ sign in front every time. I have windows 7 and am working on a PC. Much help would be appreciated.

[@haseybur]

+66koolkb I have a toshiba laptop and it does the same thing. If your keyboard is like mine, look at the FN key next to the CTRL button on the left side and press FN + Function Key , that is, press FN+F4. That should do it