Excel Basics #8: Cell References Relative & Absolute

Download file: excelisfun.net/files/ExcelBas...
Download file: people.highline.edu/mgirvin/Y...
The Excel Basics Series shows a systematic description of what Excel can do from beginning to end.
#8 Video topics:
1)Cell references in formulas
2)F4 key to select the type of cell reference
3)Relative Cell Reference
4)Absolute Cell Reference
5)Mixed Cell Reference with column locked (Absolute Column)
6)Mixed Cell Reference with row locked (Absolute Row)
7)Fill handle double click trick for copying
8)Ctrl + Enter keyboard shortcut for entering many formulas at once
9)Formula for Percentage
10)Formula for periodic increase (increase each successive amount by 10%)
11)Formula function for Future Value

[@excelisfun]

Making the world a better place by sharing the little that I know is its own reward. That's it.

[@excelisfun]

For me, life is simple: try to make the world a better place and try to have some fun doing it! You must remember: In the definition of finance & accounting & economics an essential requirement is that the thing being considered must be expressed in monetary terms. The happiness that I get knowing that the 2000 videos I have posted help people with there jobs cannot be expressed in monetary terms. However, if I were to guess, the happiness I gain is greater than the opportunity costs. more...

[@hellogolfy]

GREAT Tutorial, I can't believe this is free on youtube. Easy to understand with simple example. You're better than my professor in college! Thank you for the video.

[@excelisfun]

Watch the next three in the series, #9, #10, #11 and you can learn. In this particular case, though, it does not matter because you never copy the formula across the columns, only the rows.

[@excelisfun]

I am glad that you liked it!

[@excelisfun]

Not a king, just a guy having fun with Excel!!

[@MerryZumwalt]

The idea of free education and folks like you sharing knowledge this way is just awesome. Thank you. :)

[@excelisfun]

Try these other video titles: Excel Magic Trick # 267: Percentage Change Formula & Chart Excel Busn Math 33: Stock Values Web Query & Rate Of Change Excel Busn Math 29: Rate Of Change Formulas

[@sophiashah6051]

You are a kind of angel to those who wants to learn Excel effectively including me... thanks a million

[@excelisfun]

Absolute means that the reference is locked. For example, if the formula is in cell C2 and the cell reference in the formula is A$2, the formula sees A2 as you copy it down - so when you copy it down the A2 stays A2 all the way down. Search for this video title for tons more on this topic: Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation

[@excelisfun]

You are welcome! I am glad that the videos are helpful!

[@excelisfun]

I am glad that you like them!

[@excelisfun]

Dear killera, I am glad that it helped!! I'll keep making videos! --excelisfun

[@excelisfun]

If your cells are pre-formatted, then remove the number formatting by applying the General formatting. Here is another video to watch: Excel Magic Trick 450: Number Formatting Abbreviated Notes (House Paint or Halloween Mask)

[@excelisfun]

That is what this video is about. In brief: Realtive Cell Reference looks at a cell reference relatively. So if the formula is in cell C2 and the cell reference in the formula is A2, the formula sees "Two cells to the left" - so when you copy it down the A2 changes to A3, then A4, etc. ... more below...

[@excelisfun]

I am glad that you like it!

[@excelisfun]

I do not have any videos for certification. I am sorry about that.

[@JagoBridgland]

literally man you are my newest hero, i think i may have died from some kind of spontaneous combustion or gamma ray burst in the near future if i didn't find this out

[@excelisfun]

Glad the videos help!

[@excelisfun]

Dear hellogolfy, I am glad that the videos are useful! --excelisfun

[@excelisfun]

Ctrl + 1 (open Format Cells Dialog box) Click on the Number tab Click on the General format. Keep watching this series because I have some good videos that talk about this topic of Number Formatting and how it is a Facade.

[@LettyBee]

Thanks! You explained this better than my instructor, easy and through step by step instructions, will recommend.

[@excelisfun]

My teaching philosophy is to always use the minimum number of $ for any cell reference. This is so that people learning Excel think about whether or not we need to lock the row or column. If you learn this way, then when you get to a formula that requires a "Mixed Cell Reference", you are already thinking along these lines. more below...

[@excelisfun]

Dear RockwellHL, You could $B$2 and $F$4 directly. I was illustrating that you do not need both. I always try and use the minimum # dollar signs so that when I have to use a Mixed Cell reference, I already am thinking in that way. Many advanced tricks require that you are fluent with Mixed cell references and so this is my way of training myself to think in "minimum # dollar signs". A formula like this generates 10, 20, 30: =10+(ROWS(A$1:A1)-1)*5 --excelisfun

[@TV-vu9yo]

Thank you for offering me a awesome video! Im a korean, high school last grades. Tomorrow is my "Information" subject examination vut I didn't know about the Excel of Cell Reference. I just write that word to the KZbin's searching engine, saying "what if there is a video for me?" At first I was surprised because The video make me Information's Information! Once again, thank u for giving me a nice video!

[@excelisfun]

Dear simonw1485, I am glad that you like them! You are right about how important cell references are for formulas! --excelisfun

[@excelisfun]

Keep up the good work! And use the rewind button a lot!!!

[@jamesperry3837]

Thank u Mike for the fun u can have with the F4 key. Hitting F4 the first time to lock-in the cells with $ signs. Hitting F4 the second time will get the $ sign in front of the row reference. Hitting F4 the third time will put the $ sign in front of the column reference. And finally when u hit the $ sign the fourth time, ur back to relative. That was simply super.

[@excelisfun]

...from above: An examples of a feature that cannot be fully utilized without the knowledge of Mixed Cell References is Conditional Formatting (like formatting whole row). An example of an efficient spreadsheet construction techniques that cannot be used unless you use Mixed Cell References is a table of Future Value amounts created with the FV function and Mixed Cell references (instead of populating all 144 cells with a single formula, maybe you have to create 12 different ones).

[@excelisfun]

You are Welcome!

[@bjwm1982]

EXCELlent explanation of absolute vs relative cell references. Thank you so much for providing this EXCELlent resource! :-)

[@excelisfun]

Dear JenicekUK, It depends on what you want to do. Tell me what you want to do (Add, Average, etc...) with the cells with zeros (0), blanks (empty cells) and numbers, and I will see if I can help. --excelisfun

[@m.mahesh2155]

wow.... superb sir... How many days have you been gone sir We are lucky to find you sir thank you thank you thank you ........................... sir We are lucky to find you sir Thanks alot sir

[@excelisfun]

You are welcome alot, M M!!!!

[@excelisfun]

You are welcome!

[@excelisfun]

Dear killera, I am glad that the video helped! --excelisfun

[@excelisfun]

You are welcome!!

[@excelisfun]

I just use the built-in settings for High Definition videos for KZbin when i produce the videos in Camtasia. Not much thinking on my part, Camtasia does all the good work.

[@excelisfun]

You are welcome! Good luck with the test!

[@thelionspet]

Beautifully explained. Thank you so much! Am really enjoying delving deeper into Excel...

[@excelisfun]

Glad the videos help!

[@Education_tv11]

ʜᴇʟʟᴏ

[@excelisfun]

You are welcome! Good luck on your Test!

[@excelisfun]

Watching the Excel Basic Playlist and then the Highline Excel Class Playlist I have posted at KZbin is a great way to learn most of what Excel can do. Watch this video to see how to find the playlists: youtube[dot]com/excelisfun replace the [dot] with a .

[@excelisfun]

I glad that it helps! Good luck tomorrow!

[@excelisfun]

Dear huyenng, Thanks. I am glad that videos are useful! --excelisfun

[@jamesperry3837]

Also many thanks Mike for a basic introduction to FV. I like when u say: "Now here's the magic...... " And Ctrl+enter does it all. Thank u Mike for sharing.

[@excelisfun]

Dear mWb006, I am not sure what you mean. Do you want A4 to look at A1 and A5 to look at A2, etc.? If that is what you want, you do not need absolute, relative will work fine. If I do not understand what you want, clarify and I will try to help. --excelisfun

[@excelisfun]

@amodhsinha , I am Glad that the video helps!

[@excelisfun]

Dear noobu, It is quite difficult. Over the many years that I have been teaching Excel, this is the topic that is most difficult for people. However, if you don't become fluent in the four types of cell references, then many of the advanced tricks that are possible in Excel cannot be done. Practice makes perfect. When I was learning this topic I practiced with the multiplication spreadhseet. See this video: Excel Basics #9: Mixed Cell References in Formulas --excelisfun --excelisfun

[@Mormodes]

really great video. I like that you showed examples from every angle, it really helped. It also helped that you spoke about everything you were doing, even when you were deleting examples. Thanks!

[@Montegoman66]

Excellent! I was so confused reading this in my school text book, but you really broke it down. NOW it makes sense...Much appreciated my friend!

[@tahiyabegum27]

That really helped me a lot on my understanding of the different types of references! Thank you!

[@rishigupta2342]

One of the best explanations for cell references.

[@excelisfun]

Glad it helps, Rishi!!!

[@excelisfun]

Dear JenicekUK, I am still not sure what you are trying to do. But if cell A1 contains the blank or 1, in cell A2 you could use a formula like this: =IF(A1="","",IF(A1=0,1,A1)) Then if blank is in cell A1, nothing will be in cell A2. Then if 0 is in cell A1, 1 will be in cell A2. Then if the number 10 is in cell A1, 10 will be in cell A2. Maybe that will help. --excelisfun

[@lelouch111]

Thanks Man! I was really worried about my exam but you saved me!

[@excelisfun]

...from above: Over the years of teaching 100s of beginning Excel classes, without exception, the hardest topic for people is Cell References. So my philosophy is to teach the idea of row locked and column locked from the very beginning. more below...

[@excelisfun]

You are welcome soooo much!

[@excelisfun]

What error message are you getting? What does the formula look like?

[@excelisfun]

That character is the asterisk: * Use Shift + 8 to get the asterisk character. Or use the * (asterisk) on the number pad.

[@Ramkumarjaga]

That was really helpful. short and perfect. Thanks a ton

[@excelisfun]

It has Percentage Number Formatting applied to the cell.

[@thesithxv]

If i watched this video 2 hours ago i would have saved two hours of my life thanks!

[@excelisfun]

I am glad that it helps!

[@excelisfun]

try: Start, Control Panel, Settings, Regional and Language and Date options

[@excelisfun]

Cool! I am glad that this helps!

[@excelisfun]

Dear noobu, I am glad that it was useful! --excelisfun

[@excelisfun]

If it were the 2 numbers being multiplied: 0.95*3 you would not get a value error. Maybe the decimals are considered text by Excel. A #VALUE! error comes when you have the wrong operator or argument (* is ok so it is probably not that) or when you have a word (text)*number. Look to see if there is an apostrophe in the cell or if the TEXT number format is applied.

[@excelisfun]

Dear RockwellHL, To generate 10, 20, 30: 1) type 10 2) right-click and drag fill handle 3) point to "Series" 4) enter "Step Value' 5 --excelisfun --excelisfun

[@Von-dee]

thank you so much! I always come to your channel for help in excel

[@excelisfun]

Cool, I am glad it worked!

[@excelisfun]

You are welcome a million!

[@cyprianmoye5382]

Thank you so much, the easiest method out by far. Love it.

[@debratorres-reyes3088]

Thank you for sharing. I always enjoy your videos.

[@excelisfun]

Dear finbar826, Search for and watch this video: Excel Basics #13: Percentage Number Format --excelisfun

[@excelisfun]

the real numbers are in the cell, but the cell has Percentage Number Formatting. To remove the Percentage Number Formatting: 1) Ctrl + 1 to open Format Cells dialog box 2) On Number tab, select General.

[@excelisfun]

It sounds like the cells are pre-formatted with Date Number Format. Search for thistopics: Excel Basics #12: Stylistic & Number Formatting Excel Basics #13: Percentage Number Format Excel Magic Trick 450: Number Formatting Abbreviated Notes (House Paint or Halloween Mask)

[@jefficy1]

Hey, thank you so much! just have a quick question, when I hold ctrl and press enter, it didnt work out for me. It didnt show the entire numbers like you had. May I ask why?

[@mallet999]

You Sir ...are a legend, a whole day on the book/help section, still couldnt grasp it...2 mins on here ...BINGO.!!! ILL BE SUBSCRIBING

[@MrSeagull16]

struggled with doing this for 20 min, watched the vid, easy as pie. fantastic video.

[@agdollsparkle]

Thankyou, i have a final after tomorrow! This explained it perfectly:)

[@excelisfun]

I don't understand your question. Can you re-state it?

[@allchangesintime]

best tutorials...simply incredible :)

[@johnryan1069]

Another great vid. Thank you so much.

[@excelisfun]

Try this: 1) Highlight everything 2) To highlight only Visible Cells use: Alt + ; (Alt + semi-colon) 3) Ctrl + C (Ctrl + C) 4) Ctrl + V (Paste)

[@vidpeon]

Great job on the videos. Clear and concise.

[@vanniesha1]

thanks you so much . you have no idea how helpful your videos are. you should e very proud.

[@blackgaming5961]

Can you left the link of this video , please ? i really want to download and practice by my own

[@excelisfun]

...from above: Further, since almost 100% of the textbooks that are used in beginning and advanced Excel classes do not teach Mixed Cell References, this means that most people never learn these important topics. Then, because people dont know about Mixed Cell References, they are not capable of utilizing many of Excels greatest tricks and Excels most efficient spreadsheet construction techniques. more below...

[@excelisfun]

Dear bjwm1982, I am glad that the video was helpful! --excelisfun

[@StilettoKix]

Very helpful for a spreadsheet assignment I have coming up! Now I just need to figure out how to calculate column totals for a five year period after assigning % growth to each year. It would be similar to an an income statement. Do you have a video that helps explain that? I had an Excel class in the summer of 2007, but it was an accelerated summer session. Now I'm having to refresh my memory. Great video and thank you!

[@excelisfun]

Search for and watch this video title (it shows how to use the excelisfun channel for teaching yourself Excel): excelisfun Search for Excel Videos, Playlists & Download Workbooks I will send link by e-mail since they don't let us post links in comments...

[@wvoider09]

I love these videos! Thank you x a million...

[@erkatana6163]

It helped me a lot! Thanks!

[@techpacks4153]

Great Job explaining absolute and relative cell references!

[@lennywatson4799]

Dude you are awesome!

[@fabricianoacosta7011]

Thanks for the video, now I understand more about the magic Key F4.

[@junaidahmad8120]

Man, You are awesome, exactly what im looking for. literally, i have done F4 many times and it works always but did not understand what that is Lol... now i understand its an Absolute reference. Thank you so much for amazing clear explanation

[@excelisfun]

You are weclome!

[@junaidahmad8120]

Hey, i am stuck in minor problem. i have a column of " PASS/FAIL Evaluation " . how can i count that column?

[@excelisfun]

You are welcome ssssooo much!

[@unitedcomputers]

By the way, your tips are great...I have subscribed!!

[@Kozli1985]

Awesome explanation sir,thank you!

[@burakkahraman7344]

Always short and perfect explanation...thanks

[@jasnookybb5430]

Thank u...am doing online classes. This is sooo much help!!

[@excelisfun]

Dear JenicekUK, EXCELlent!!!!! --excelisfun