Excel Basics #8: Cell References Relative & Absolute

[amodhsinha]

great Sir thanks a toooon it was really a learning u delivered it fab

[ExcelIsFun]

@amodhsinha , I am Glad that the video helps!

[jefficy1]

nvm, I figured it out! THX

[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?

[IWantSoundKnowledge]

great!

[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!

[hatke159]

=82/100 should give 0.82 but how did it show 82% ????

[ExcelIsFun]

It has Percentage Number Formatting applied to the cell.

[nf255]

Thank you sssssssoooooooo much

$kiss$ u

[ExcelIsFun]

You are welcome ssssooo much!

[brodieyo]

THANKYOU!!!

[ExcelIsFun]

You are welcome!

[ASTiGBA]

@ExcelIsFun I love you man No homo!

[madduppz]

I have Dell Laptop for some reason any of the shortcuts referencing F2 or F4 do not work. Is anyone aware to enable these keys so it will work as stated in the video?

[brodieyo]

@madduppz hold down the Fn key while you press F4.

[ExcelIsFun]

Sorry. I do not.

[ExcelIsFun]

brodieyo sent this message:

hold down the Fn key while you press F4.

[oo0cry]

amazing, useful...thx

[ExcelIsFun]

You are welcome!

[allchangesintime]

best tutorials...simply incredible :)

[ExcelIsFun]

I am glad that you like them!

[saifulalam1]

Thanks you!!!!

[ExcelIsFun]

You are welcome!

[Brooke684]

Thank you!

[ExcelIsFun]

You are welcome!

[Brooke684]

@ExcelIsFun - I had a TOUGH test today, so I was "cramming" last evening. I did well ... I think! I need more tutorials because I'd like to get certified in Excel. Do you know of any? If so, will you respond with the information? I'd surely appreciate it!

Regards,

B. ♥

[ExcelIsFun]

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

[Brooke684]

No, no, that's not what I meant. I'm curious to know if there are any other tutorial sites for learning Excel, in addition to watching your videos. I can get certified at my school, but because Excel is a little tricky for me, I would love extra help, and I know the Internet is a gold mine for information.

This video, for example, was extremely helpful. I'm looking for different ways to boost my know-how, in "lay" terminology.

Languages, not numbers, are my thing, but I'm still persevering!

[ExcelIsFun]

Watching the Excel Basic Playlist and then the Highline Excel Class Playlist I have posted at YouTube 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 .

[SuperAhmedrashad]

Just amazing!

[ExcelIsFun]

I am glad that you like it!

[fighterbugy]

very helpful, thank you!

[ExcelIsFun]

You are welcome!

[quaziloko]

why dont you use $f$4 instead of using $f4 thats the only part i can't get my head around if they both give you the same answer? thanks

[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.

[summerstar333]

Ok so where is your youTube link to get to your website to download the spreadsheet...I guess I am blind today cuz I just dont see it..

[ExcelIsFun]

Click on the blue excelisfun link, then watch the video that auto plays - it will show how to download and more. Or search for excelisfun, go to chanel, watch auto video...

[summerstar333]

@ExcelIsFun Thanks so much... I think my brain has gone to mush...In the middle of Mid-terms....Very helpful vidoes.....Thanks

[ExcelIsFun]

You are welcome!

[wvoider09]

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

[ExcelIsFun]

You are welcome!

[adrian17cy]

hi! how can the following be created:

for example when you click on tina to take you on a new worksheet named tina, or sue, or anything else. And this without hyperlinking them. just click on tina and create new sheet named tina. tks

[ExcelIsFun]

Try this site for Excel VBA help:

mrexcel..com/forum

[taylorjoyschuetz]

thank you so much! you help me so much with my computer class!

[ExcelIsFun]

You are welcome!

[DeeCatWolf7]

Thankyou helped heaps !!!!

[ExcelIsFun]

You are welcome!

[R3alidad23]

hey I thought that / was a division that gives you the answers en real number not in percentages.... can you explain??

[R3alidad23]

Question? I thought that when you use the / is a division and the results is given in real number not in percentages. 

[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.

[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]

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

[bertfreez]

thanks my exam is tomorrow and this is gonna help huuuuuge!!!!!!

[ExcelIsFun]

I glad that it helps! Good luck tomorrow!

[Skux32]

Thanks a Million !

[ExcelIsFun]

You are welcome a million!

[christopherwmoody]

thanks a ton for posting this!

[ExcelIsFun]

You are welcome!!

[christopherwmoody]

Thank you so much for this video!

[darrencornell1]

in 7 days i have learnt so much its incredible, fanx very moooooch

[ExcelIsFun]

You are welcome soooo much!

[aman1245singh]

as always i am obliged, god bless :)

[ExcelIsFun]

You are welcome!

[4abregas]

it is just amazing!

[ExcelIsFun]

I am glad that you like them!

[mnava663]

thanks for posting this useful explanatory videos! they are so great!

thanks for the help!

[ExcelIsFun]

You are welcome!

I am glad that the videos are helpful!

[13gne1]

why you wrote excel 2003 ?!?!?!?

[ExcelIsFun]

I do not understand your question. Can you restate your question?

[libexzz]

I've got a question, on the first example with the 100%, when you locked both coloumns and rows , it did work, so why did u delete it and locked it only along rows??

Can u just lock it both across rows and columns as it works??

[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]

...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]

...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]

...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).

[raikkonenowns1]

Thanks

[ExcelIsFun]

Tiy are welcome!

[ExcelIsFun]

You are Welcome!

[zahawk007]

Please tell me that in the Excel Basic video ( in the Second Example u wrote the statement as

=E2(?)$F4.

After E2 which Sign u use, how i cant it found, i hv search that Sign alot but couldnot pick.

please how can i write time sign.

thnks

Zarshad from Pakistan NWFP Mardan.

[ExcelIsFun]

That character is the asterisk:

*

Use Shift + 8 to get the asterisk character.

Or use the * (asterisk) on the number pad.

[zahawk007]

Thanks alot for your kind reply...

actually i was just confused..

tell me how can i get all of ur Excel 2007 video and books.

also tell me ur index of series...

i means that from the Start which Series i need to study, i.e series wise......i m learning excel for the first time..suggest me which series i need to study first..

thnks once again for ur kind rply

Zarshad Khan

Pakistan (NWFP) Mardan

[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...

[lexingtonlp]

You are extremely helpful and truly gifted in teaching. Keep up the fantastic work!

[ExcelIsFun]

I am glad that you liked it!

[amauta5]

Very awesome videos. Thank you very much.

[ExcelIsFun]

You are welcome!

[hayleydisca]

oh i mean...wht's the difference between Absolute reference and relative reference

[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]

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

[biruot]

Why when I'm multiplying by decimals gives me error message in the formula cell, but it works well for the clear numbers like 1, 2, 3, 4

[ExcelIsFun]

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

[biruot]

Sorry for not being clear enough in my question. What i meant is in a regular excel workbook, when multiplying one cell by another if one cell contains decimals less than 1, ex: 0.95*3 I'm getting #value! in the result cell. But it works fine for numbers without decimals. And without any cell pre-formating when I'm typing 1.2 it automatically converts to the date/month format. I never had such problems in 2003's version. Know I'm using 2007's version.

[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]

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)

[biruot]

Unless I'm formatting cells in TEXT number format, I cannot type a number like 1.3 or 2.3 etc...it converts to date/month format immediately. And that's the root of my problem. When I'm multiplying it by another cell it gives me #value error, I think it is something related to my excel configurationn. Because when I'm opening anew excel window it is the same without any pre-formatting converts numbers to date/month format. I mean I'm typing 1.3 it converts to date/month format.

[ExcelIsFun]

try:

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

[biruot]

Yes it worked for me. In Regional and Language standards changed the format from Russia to USA and the problem been solved.

Many thanks,

[ExcelIsFun]

EXCELlent!!!!

[smallbot1]

LOL smoooottthhhh

[hayleydisca]

absolute and relavtive reference...got any differences?

[ExcelIsFun]

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

[biruot]

I'm trying to copy rows in one worksheet and paste them to another worksheet, holding Ctrl key selecting them but when I'm pasting them excel paste all the rows in between, How can i do that please . If there is a video on this please point me to it. Many thanks.

[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)

[ExcelIsFun]

This video shows this trick about 4 minute mark:

Excel Basics #19: Data Analysis Subtotals

[biruot]

Man I don't know how to thank you, you just saved me tons of time. You are the King of excel. Many many thanks.

[ExcelIsFun]

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

[biruot]

Thanks a lot for the quick an adequate answer.

You are doing great.

[biruot]

very helpful

[biruot]

You have been very helpful, thanks. I have a problem entering numbers like 1.2 or 2.3 etc. it automatically converts in excel 2007 to month /date format, how to stop that auto converting please? thanks in advance,

[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.

[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)

[hvovovivhofvhv]

That really helped because i forgot what teahcer told me about those references so thnks!

[ExcelIsFun]

You are welcome!

[uniqueHAITIANdiva]

Thank you much, u just save me from a F!!! my prof doesnt know how to explain at all

[ExcelIsFun]

You are welcome!

[JessMenu]

Very helpful, thanks alot!

[ExcelIsFun]

You are welcome!

[MegaMagicBus]

thank you very much

merci merci meri

[ExcelIsFun]

You are welcome!

[ChrisAdams1985]

It is facinating, thank you very much....

[ExcelIsFun]

You are welcome!

[MsWarren12]

Great video a little fast talking for me to follow but im learning slowly thank you

[ExcelIsFun]

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

[RockwellHL]

two quick questions

1. in the first two examples, why not use $B$2 and $F$4 directly? I got the exact same results as ones you got using mixed cell reference.

2. I noticed the number of years increase in a constant 5 year period (10,15,20~~~~) in the third example. Is there a way automate the process? (I just need to input 10, and drag down to cell F21, it automatically shows 50)

Thanks for your patience

[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

[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

[rehd]

I was about to ask that same question, very good answer and definitely not something I knew. I think it would of made a great addition to this video though that little post you made.

[ExcelIsFun]

I have three other videos on this topic. Do you know how to get to the excelisfun list of all Playlists? If so, the name of the Playlist you want to see is:

Various Excel Incrementing Sequencing Numbers in Formulas

If you don't know how to find Playlists, search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

[bjwm1982]

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

[ExcelIsFun]

Dear bjwm1982,

I am glad that the video was helpful!

--excelisfun

[slaves2sin]

my version of excel did not allow for the CRTL+ENTER function for some strand reason. I had to drag out the formula using my mouse.

strange

[ExcelIsFun]

Dear slaves2sin,

What version?

--excelisfun

[slaves2sin]

F4 is powerful. wow. usually i would type in the $ signs.

[ExcelIsFun]

Dear slaves2sin,

That is so cool - that moment that you learn something efficient!!

F4 key is efficient!

--excelisfun

[finbar826]

Dear ExcelIsFun,

How do you get the numbers in the "Percentage" section to have the "%" sign. I can get the correct numbers, but I am unable to get the "%" to follow the numbers.

For example, I might get the number "72" but not "72%".

Great Videos by the way!

Thank you very much!

[ExcelIsFun]

Dear finbar826,

Search for and watch this video:

Excel Basics #13: Percentage Number Format

--excelisfun

[JenicekUK]

Dear Excelifun,

I need to help with little thing.. Cells in Excell always equals 0, but in football ressults there can be result 0:0 which equals draw (1 point). How to make excel understand that empty box mean nothing and once you wrote 0 it means ressult. I hope you understand, my english isn't best..

[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

[JenicekUK]

Dear Excelifun,

I have found out. I am trying to do System, which makes draws, results and tables for Footy.. I am almost done, just few things need to be made.. And I was so annoid of that thing that i had to come back to my teacher (you).. I have tried to look at it from your point of view and finaly get that sollution.. Otherwise i would like to thanks for all your work you have done on tube.. Thanks to that i have much more easy job and belive me, without your advices it was impossible..

[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

[JenicekUK]

Dear Excelisfun,

That is what i need.. Thank you for your advice and vids..

[ExcelIsFun]

Dear JenicekUK,

EXCELlent!!!!!

--excelisfun

[vidpeon]

Great job on the videos. Clear and concise.

[ExcelIsFun]

Dear vidpeon,

Thanks!

--excelisfun

[vanniesha1]

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

[ExcelIsFun]

Dear vanniesha1,

I am glad that they help!

--excelisfun

[simonw1485]

Wow I'm really learning heaps from these videos!

Good work, you are explaining them really well. Particularly this one which I think is the most important.

Keep them coming...

[ExcelIsFun]

Dear simonw1485,

I am glad that you like them!

You are right about how important cell references are for formulas!

--excelisfun

[mWb006]

Bloody hell, you're right, Excel IS fun! :D

You're vids better (and way more entertaining) than the $900 course I took at Uni last year that was pretty much only on Excel!!

Question:

If you have cells A1:C3 already filled with numbers, and you wanted cells A4:C6 to be an absolute reference to A1:C3, how do you go about this? I am having to use relative references then manually go to each cell and hit F4.

Cheers mate. ;)

[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

[superchess101]

thanx a LOT for this video. U solved questions that I had for a long time!

[ExcelIsFun]

Dear superchess101 ,

You are welocme!

--excelisfun

[huyenng]

absolutely LOVE your videos!! you're a fantastic teacher :D

[ExcelIsFun]

Dear huyenng,

Thanks. I am glad that videos are useful!

--excelisfun

[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]

Dear hellogolfy,

I am glad that the videos are useful!

--excelisfun

[killera]

Thnx for ur info man xD

i wud rate this vid 1000/5 if i could,

im sure this will be helpfull with my examn ;)

keep the good work up ( for the sake of ppl who dont know how to use it xD )

THNX!

[ExcelIsFun]

Dear killera,

I am glad that the video helped!

--excelisfun

[killera]

i had the exam today and it sure it help =p ;)

Keep the good work up!

[ExcelIsFun]

Dear killera,

I am glad that it helped!!

I'll keep making videos!

--excelisfun

[noobu]

very detailed explanation, a good tutorial indeed!

[ExcelIsFun]

Dear noobu,

I am glad that it was useful!

--excelisfun

[noobu]

quite complicated -.-:

[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