Excel Basics #8: Cell References Relative & Absolute
ฝัง
- เผยแพร่เมื่อ 27 ส.ค. 2024
- Download file: excelisfun.net...
Download file: people.highlin...
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
Making the world a better place by sharing the little that I know is its own reward. That's it.
Not a king, just a guy having fun with Excel!!
Dear killera,
I am glad that it helped!!
I'll keep making videos!
--excelisfun
Dear hellogolfy,
I am glad that the videos are useful!
--excelisfun
Dear killera,
I am glad that the video helped!
--excelisfun
Keep up the good work! And use the rewind button a lot!!!
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.
Watching the Excel Basic Playlist and then the Highline Excel Class Playlist I have posted at TH-cam 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 .
I am glad that you like them!
The idea of free education and folks like you sharing knowledge this way is just awesome. Thank you. :)
Dear huyenng,
Thanks. I am glad that videos are useful!
--excelisfun
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.
Dear noobu,
I am glad that it was useful!
--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...
You are a kind of angel to those who wants to learn Excel effectively including me... thanks a million
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...
If i watched this video 2 hours ago i would have saved two hours of my life thanks!
Dear simonw1485,
I am glad that you like them!
You are right about how important cell references are for formulas!
--excelisfun
Thanks! You explained this better than my instructor, easy and through step by step instructions, will recommend.
EXCELlent explanation of absolute vs relative cell references. Thank you so much for providing this EXCELlent resource! :-)
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.
You are welcome soooo much!
You are welcome!
I am glad that the videos are helpful!
Dear JenicekUK,
EXCELlent!!!!!
--excelisfun
Dear bjwm1982,
I am glad that the video was helpful!
--excelisfun
You are welcome a million!
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
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
I glad that it helps! Good luck tomorrow!
Cool, I am glad it worked!
One of the best explanations for cell references.
Glad it helps, Rishi!!!
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.
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
You are welcome alot, M M!!!!
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
thanks you so much . you have no idea how helpful your videos are. you should e very proud.
Thanks Man! I was really worried about my exam but you saved me!
You are welcome ssssooo much!
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 TH-cam'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!
Dear vidpeon,
Thanks!
--excelisfun
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!
You are welcome! Good luck with the test!
You are welcome!
You are welcome!!
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
Dear vanniesha1,
I am glad that they help!
--excelisfun
Dear slaves2sin,
That is so cool - that moment that you learn something efficient!!
F4 key is efficient!
--excelisfun
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.
You are welcome! Good luck on your Test!
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)
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
Glad the videos help!
You are Welcome!
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...
I am glad that it helps!
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...
That character is the asterisk:
*
Use Shift + 8 to get the asterisk character.
Or use the * (asterisk) on the number pad.
...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...
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
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
I am glad that you liked it!
in 7 days i have learnt so much its incredible, fanx very moooooch
I am glad that you like it!
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!
Beautifully explained. Thank you so much! Am really enjoying delving deeper into Excel...
Glad the videos help!
ʜᴇʟʟᴏ
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
Great job on the videos. Clear and concise.
Dear superchess101 ,
You are welocme!
--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).
Dear Excelisfun,
That is what i need.. Thank you for your advice and vids..
I do not have any videos for certification. I am sorry about that.
Cool! I am glad that this helps!
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
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.
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
...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...
thanks for posting this useful explanatory videos! they are so great!
thanks for the help!
Tiy are welcome!
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
struggled with doing this for 20 min, watched the vid, easy as pie. fantastic video.
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...
Awesome explanation sir,thank you!
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.
Dear finbar826,
Search for and watch this video:
Excel Basics #13: Percentage Number Format
--excelisfun
Thanks for the video, now I understand more about the magic Key F4.
I just use the built-in settings for High Definition videos for TH-cam when i produce the videos in Camtasia. Not much thinking on my part, Camtasia does all the good work.
Great Job explaining absolute and relative cell references!
That really helped me a lot on my understanding of the different types of references! Thank you!
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)
thanks my exam is tomorrow and this is gonna help huuuuuge!!!!!!
What error message are you getting? What does the formula look like?
I don't understand your question. Can you re-state it?
Thank you so much, the easiest method out by far. Love it.
thank you so much! I always come to your channel for help in excel
very detailed explanation, a good tutorial indeed!
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.
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..
I do not understand your question. Can you restate your question?
Cool!!
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
You are weclome!
Hey, i am stuck in minor problem. i have a column of " PASS/FAIL Evaluation " . how can i count that column?
@amodhsinha , I am Glad that the video helps!
Just amazing!