Excel Basics 18: Defined Names in Excel Formulas & Functions & For Jump Go To!

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highlin...
Download Full Written Notes: people.highlin...
Class web page link: people.highlin...
In this video learn about how to Create Defined Names, and use them in formulas and to Jump to areas in the Excel Workbook.

[@P1X1E56]

I am totally blown away by your delivery. I'm retired now, but still dabble in Excel for a friend who runs a small business. I thought I was fairly proficient at Excel, and while working thought I didn't have the time to spend on out of hours study; I wish I had. In just 18 lessons I have learned many things which save time. I am hooked until the end of this series. Cannot get enough. Thank you so much. keep it coming.

[@excelisfun]

You are welcome so much! I am glad that the videos help : ) Now you can help: be sure to click that thumbs up and leave a comment on each video because it helps the excelisfun channel : )

[@rrrprogram8667]

Believe me... One learns more than excel in this channel.... Which is amaaaazingg

[@excelisfun]

Thanks for watching and for your support!!!

[@rrrprogram8667]

Mike one sincere request, can you please make a video on developing effective CV or Resume in word......Please you have got amazing sense of selection and hugeeee experience

[@excelisfun]

I have one video but it only shows how to create a resume with Word features: kzbin.info/www/bejne/nKu7m2WogsacrpI

[@pashayakub9]

Another great video. Thank you so much Mike ✋

[@excelisfun]

You are welcome!!!

[@sardorabdurahimov4569]

Thank you so much for all your effort and good work! I am using everything you have thaught at my work at the Bank! God Bless You! Sardor from Uzbekistan!

[@excelisfun]

You are welcome, Sardor!!! I am very happy to hear that you can use all these efficiencies at work! Thanks for your support on every video that you watch : )

[@nghiepcrypto7034]

I not only learn excel from you but also learn other skills from you. You're amazing!

[@excelisfun]

Glad to help, Nguyen!!! What other skills did you learn in this one?

[@nghiepcrypto7034]

@@excelisfun your presentation, pay attention to details... skills. I also admire at your enthusiasm and your consistency ;)) really, you're my idol

[@excelisfun]

@@nghiepcrypto7034 Cool! Keep watching and having fun : ) Because I am having fun too!!!

[@lewisyaworski8962]

7 more videos to complete. Almost ready to do Data Analytics Videos.

[@excelisfun]

Great work!!!! I hope the free education is good : ) Thank you for the support, Lewis : )

[@MonirulIslam-zj3ve]

Sir, Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. You are the best. Thanks a lot to keep love and patience in your teaching field.

[@excelisfun]

Glad that the videos and teaching helps! Thanks for the support !!!!!

[@joramirez159357]

There is always something new to learn on Excel

[@excelisfun]

Yes there is! That is what makes it so much fun : ) Thanks for the support with your comments, Thumbs Up and Sub : )

[@dorissweanapo9860]

Another great video. Thank you Mike :)

[@excelisfun]

You are welcome, Doris! Thanks for your support : )

[@masterjack9505]

Thank You Teacher.

[@excelisfun]

You are welcome!!!!!

[@paddy8937]

I think these are the best videos on Excel, thank you very much.

[@excelisfun]

You are welcome very much!!!

[@Nyuklehead]

Thank you for the video! This seems like a great way to keep track of complex workbooks and data sets.

[@excelisfun]

: )

[@shubhampawar8506]

Great vedio as usual 👌👍

[@excelisfun]

: ) : ) : )

[@mohamedchakroun4973]

Always fun to watch your videos

[@excelisfun]

I am glad that they are fun! They are fun to make!!!

[@mohamedchakroun4973]

If they aren't you might not make them perfectly and doing a great job ....but they requires a lot of time..thanks for sharing your khnowledge with us

[@excelisfun]

You are welcome! Thank you, Mohamed for always taking the time to support the excelisfun channel : )

[@MsNoeynono]

Thank you for these videos. I very much enjoy watching this channel!

[@excelisfun]

You are welcome! Thanks for the support with your comment,m Thumbs Up and Sub : )

[@Kizo_55]

Thank you!

[@excelisfun]

You are define names welcome!!!!

[@deveshdangi7209]

This video was okay. I learned a lot but I believed this was not something which blows my mind like your other videos. Btw all your previous videos were awesome!!!

[@excelisfun]

Yes, Defined Names were the best way to do this sort of thing from 1990s till 2010s. But now the Excel Table feature and Table Formula Nomenclature is used more often. I still have to teach Defined Names, because a lot of legacy Excel Solutions use them and there are still a few cases where they are good.

[@excelisfun]

Of course, the BIG downside to Table Formula Nomenclature is when you have to make the references, absolute.

[@emile8147]

while writing the formula you can hit F3 to paste a name too

[@excelisfun]

Niice!!

[@SyedMuzammilMahasanShahi]

Another amazing video. Thanks Mike.

[@excelisfun]

You are welcome! Thanks for your continual support : )

[@pmsocho]

Great as always!

[@excelisfun]

Thank you, Teammate : )

[@ezgikocak7457]

Amazing! So helpful! Thanks!

[@hasannaomir.3037]

Thank you soooooooooo much!!!! You so rock, Professor!!!

[@excelisfun]

You are soooooooo welcome! Thank you for the support with your comment, Thumbs Up and Sub : )

[@stephanieclancy254]

Great video!

[@excelisfun]

Thanks, stephanie!!!

[@pumpkinpie1557]

Great timesaver. Thanks Mike

[@excelisfun]

You are welcome, Pumpkin Pie!!!!

[@febrianto984]

thank you for today's lesson

[@excelisfun]

You are welcome, Febri : )

[@bavajikj3267]

great job sir. thank you

[@excelisfun]

You are welcome, bavaji : ) : )

[@abdulwahabnajeeb8337]

Awesome channel. Thanks for the detail demos...

[@excelisfun]

You are welcome for the detailed videos! Glad that the videos and teaching helps! Thanks for the support with your comments, Thumbs Up and Sub : )

[@anderson40th60]

Another rad video! Thanks!

[@excelisfun]

Defined Names are THE original Rad in Excel - going way back : )

[@KWChamonix]

Thank you, and I thought I knew everything there was to know about ranges.

[@excelisfun]

You are welcome! Thank you for your support!

[@josephmegalaa602]

wow it is absolutely outstanding

[@excelisfun]

Glad it is outstanding for you, Joseph!!!! Thanks for your support : )

[@josephmegalaa602]

ExcelIsFun I have a question I want to get certificate at excel it will help me with job career how may I obtain it ?

[@a3amatikys]

Hi Mike! Thank you for another gr8 video about Excel. Btw. Excel Basic Video №18 is going earlier on KZbin, than video №17. Just FYI.

[@excelisfun]

No, Excel Basics 17 is there. If you serahc for it it is there. In the classroom playlist it comes after video #18. Are you using the KZbin Learning Playlist that I have set up for this class: kzbin.info/aero/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k

[@ismailismaili0071]

Thank u so much Mr. Mike this us really useful for me

[@excelisfun]

You are welcome! I am glad that it is fun!!!

[@chandu7spark]

Good Info Tanq once again.

[@excelisfun]

Glad you like it, Bharat!!!

[@kisslaci36]

THumbs up. Continuos learning.

[@excelisfun]

Thanks for the Thumbs Up! Continuous learning is a LOT of fun : ) Thanks for the support with your comments, Thumbs Up and Sub : )

[@johnborg5419]

Very Interesting and thanks. :)

[@excelisfun]

You are welcome, John Borg!!!!!

[@idanbenruby8741]

very effective mike! makes saves alot of clicks (:

[@Whyvardhann]

super useful

[@excelisfun]

Glad the video helps!!! Thank you for your support : )

[@socheatnhoeb1239]

very good

[@lucatoml]

Hi there great explanation, but I have a doubt it it is possible to achieve. I have set a name to go to a certain column. But I need when I call this column, that the cursor goes to the selected cell column, but keep in the line it was, in other words, when I call this "column" I want to go to the line where the cursor was at (selected cell) but positioned on the called column? Any idea how to manage it or if is there some formula? Thanks.

[@ahmed007Jaber]

how to cancel the cell names and use the reference instead?? go to the manager and delete those names? or is there a way to deactivate? insofar I don't have use for them as the data I get to work with isn't perfectly designed and arranged

[@masterof]

If I have an Excel sheet, many of the cells has been named with similar style (e.g. the same pre-fix or the same suffix), is it possible to add these cells together? For example, in a sheet that has cells named as "StockLocationA1", "StockLocationA2", "StockLocationA3"...etc, and these cells are located randomly in thet sheet but all clearly defined in the Name Manager. How to add these cells named "StockLocation"&"*" together? I tried =SUM(INDIRECT("StockLocation"&"*")) but that has a reference error. Many thanks for your help...

[@hiteshjoshi3148]

My monthly pmt came as ####### in red color why need help sales also came as ##### though I applied according to the instructions as you stated

[@excelisfun]

The column is not wide enough ; )

[@FlossinBBQ]

You mentioned that named ranges are not dynamic. They can be by using the offset and counta formulas in the Name Manager Refers to: window.

[@excelisfun]

Yes, indeed : ) I wrote a book with a whole chapter on those cool OFFSET & INDEX Dynamic Range formulas : ) But they are less relevant now because of the Excel Table feature.

[@Nikol0zi]

Hey Mike, the download excel in this link is a little different, it does not have homework and Defined Names (an). thanks for everything again.

[@excelisfun]

Yes, becasue I did not want to confuse people with Defined names already there.

[@Parseval16]

Thanks!!

[@excelisfun]

You are welcome!

[@ashishsinha8671]

Sir this video is no 17 or 18...which one watch first 18?

[@kareemkoka1747]

can I make drop list from many of define_name range i.e fill the range by another range from drop list

[@excelisfun]

Yes. Here is one of a few videos I have made on this topic: kzbin.info/www/bejne/Y2ibk3tmh79qg6M

[@kareemkoka1747]

@ExcelIsFun Thank you a lot ,I appreciate it but the sended video not the requested . if I make more thane one define-name each on refers to some of horizontal range that contains fixed number of cells had numerical values . I want to replacing all the horizontal range by another one to fill all the horizontal range cells with values by another define-name range until Ican use the new values in each range can I email you ? but your video make me go to brilliant ideas

[@excelisfun]

@@kareemkoka1747 I am not able to currently do consulting. Too much work in my real job at Highline : (

[@hazemali382]

Wow

[@meniporat3527]

Excellent, as usual. However, the file cannot be downloaded. Please correct the link. Best regards, teammate :-)

[@excelisfun]

Okay : ) I fixed it : )

[@sorenzollamas]

Brilliant as usual. Question: If you create a table and are going to use the data from its columns in other formulas, should you name the columns or is that redundant in some way?

[@excelisfun]

It depends... on multiple facts... The topic is for a different class, not this basic class. But... If you are just copying the formula across the rows and you want the reference locked, then you use Table Formula Nomenclature. If you want the default Mixed Cell References behavior of Table Formula Nomenclature when you copy the formula across the columns, then use Table Formula Nomenclature. If you want to avoid the the default Mixed Cell References behavior of Table Formula Nomenclature when you copy the formula across the columns, then you use Defined Names.

[@playlistyee8099]

as DJ Khaled: Another one

[@excelisfun]

Yes!!!!! Another one : )

[@mostafaahmedzein450]

Great Lesson as expected !! :D But why I would name a cell for moving between tabs instead of using the tab at the bottom of screen? 😅

[@excelisfun]

Glad it was good for you!!! You asked: "why I would name a cell for moving between tabs instead of using the tab at the bottom of screen?" The reason is because some workbooks have 100s of sheets and it can make it easy to jump to just the sheet you want, and just the section of the sheet that you want.

[@mostafaahmedzein450]

@@excelisfun Aha! Thanks again.

[@clarklynn2313]

Hi, looking for some help!! The question is as follows: If one cell is greater than another by a specific amount then a third cell returns a value. How do I do that? Example: Mission is 6 ( 6 goes in A5) Attained 7 ( 7 goes in B5) Because B5 is greater than A5 by 1, C5 needs to say 25 ( as if I get one over prescribed mission I receive 25 points) Any hep would be great, Thank you, Clark

[@AdiCristea]

In cell C5 type the formula =IF(B5-A5=1,25,0)

[@clarklynn2313]

Adi Cristea I must be doing something wrong that did not work!

[@AdiCristea]

Unless you have some number formatting in place, the formula definitely works. Check to see if values are actually integers (i.e. not 5.1 showing rounded to 5).

[@clarklynn2313]

Adi Cristea I really appreciate your help! Is it possible to contact you by email? I am still having a bit of trouble with this problem.

[@clarklynn2313]

Adi Cristea all it is doing is returning 0

[@chuckhamdan1283]

The link to Download Full Written Note is wrong. It is not pointing to the right address and get an error: "The page you requested is not available"

[@excelisfun]

Fixed! Thank you for helping me to edit. It is so hard to create all the content, film videos, edit videos, make the web site... - I just don't have enough time - so it really helps when you help me to edit : )

[@alba5580]

No hw in downloaded excel. :(

[@excelisfun]

Sorry : (

[@lazalazarevic6192]

XLNT

[@excelisfun]

Thanks : )

[@PieterSteens]