Excel Basics 15: Excel Table Feature & Dynamic Ranges for VLOOKUP, PivotTables, & more!
ฝัง
- เผยแพร่เมื่อ 8 พ.ย. 2017
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highline.edu/mgirvin/Y...
Download Full Written Notes: people.highline.edu/mgirvin/Y...
Class web page link: people.highline.edu/mgirvin/A...
Topics in this video:
1. (00:01) Introduction
2. (01:15) Example 1: Excel Table feature for VLOOKUP & Data Validation List
3. (07:10) Example 2: Excel Table feature for Chart
4. (09:45) Example 3: Excel Table feature for PivotTable
5. (13:10) Example 4: Excel Table feature for Other Formulas
6. (16:50) Example 5: Add New Column to an Excel Table AND Create Approximate Match Lookup Table from a written statement.
7. (17:47) Create Approximate Match Lookup Table from Written Instructions
8. (24:35) Change Settings to Allow Columns and Rows to be added to Excel Table as you work: Excel File, Options, Proofing, AutoCorrect, Auto Format As You Type, “Apply As You Work” - “Include New Rows and Columns in Table”
9. (26:10) Add columns to Excel Tables with Table Formula Nomenclature with Relative and Absolute References.
10. (29:30) Formula for Discounts when we need to calculate Net Revenue based on a Discount.
11. (34:14) Add new records to bottom of Excel Table and Formulas will automatically populate down
12. (35:05) Summary - Conclusion
![[LIVE] : ONE ลุมพินี 68 | คู่เอก "พระจันทร์ฉาย vs โจนาธาน"](http://i.ytimg.com/vi/F3IM3w-XchI/mqdefault.jpg)
This is the best channel ever for learning Excel 🙏
Glad it helps, yankisherpa!!!!
Seriously sir ur helping a lot .Like people like me housewife who couldn't afford to buy courses ur helping a lot sir .I am really thankful to u sir 😀😀😀🙏🙏🙏🙏
I am very happy to help you, housewife Excel fan!!!!
Learned a lot, specially the dynamic drop down list, and the advice about rounding is great.
Glad it helps!!!
I wish I could give you 1 MILLION Like for this channel. With you, Excel is really FUN!
Glad you like the channel and videos, Ahmad!! You can help support my efforts to bring free education to the world with comments and thumbs ups on each video that you learn from, and of course your Sub : )
I'm loving this series. Thank you so much.
You are welcome so much!!!
Another great video!
I have never noticed that diagonal arrow while selecting tables! - Cool!
Thanks, Teammate!! : )
This was most helpful. I wish there was an option to give 1000 likes!
I have been trying to understand VLOOKUP just by reading notes, but your execution has made it very easy for me to understand. Thank you.
Glad to help : )
Super amazing !!!!!!!!!!!!!!!!!!! "God of excel"
Glad you like the vids!!!!!
Sir the TAB key was legendary! at 6:22 You are an Excel Messiah!
Glad this helps!!!
Another terrific video from the Excel guru of youtube. Vlookup is so critical to business. Thank you!
You are welcome, Sharon!
Another great lesson! Thanks a lot! Auto Complete Function for the Excel Tables which auto completes even the formuals is awesome! Thumbs up! Keep it up like this.
I will keep it up! Thanks for the support, Elmar!!!!
Something I use as a test is to try to resolve the sheets before watching the video, even new formulas although i always fail at these. But when you do almost the same things i did, it feels amazing.
I also try to make things harder, for example create a formula where it directly calculates the net revenue after the discount, using round, etc.
Or even crazy things. The last one I made was to create a random list from 4 products with a simple ID and using the functions =random and =vlookup, and then copy-pasting the values of the result of the formula to a new column.
It's beautiful and really fun all the things I can do now. Of course, all thanks to you Mike. :)
Thank you for being so generous with your knowledge! Your love for your subject really comes through your videos and it makes it fun to learn with you. :-)
You are welcome, Orsolya!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Happy Teacher's Day... Love from India.... N it has been ABSOLUTE FUN...
Much appreciated your such passionate teaching on ExcelIsFun VIDS,,,,
GOD bless for all your good works Mr. Girvin,,,
You are welcome, TRUSTin WWJD!!!! Thanks for your support : )
Thanks for this lecture series...learnt a lot from you !!
Glad you are learning a lot, Chandan!!!!
Learning with you is very easy, thanks❤️❤️
what a great tutorials, I am grateful for your efforts.
Hey,
Thank you so much for these videos and the knowledge!! I'm currently trying to built some sort of database at my job and categorizing data in way so that It increases productivity without putting much effort.
Before starting watching your videos I didn't know a thing about excel!. All thanks to you, I really appreciate it man.
Glad I learnt English, Muchas gracias! Pura vida! Exitos!
Glad to help you, Devesh!!!!
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 sir. Thanks a lot to keep love and patience in your teaching field.
Glad that the videos and teaching helps! Thanks for the support with your comments, Thumbs Up and Sub : ) Please tell all your friends and colleagues to come and learn for free at TH-cam : )
U r Briliant 👍 🙏 Simply the best learning videos i ever have seen on YT. Big hug and greatings from Denmark 🇩🇰🙏🙏🙏🙏
You are welcome!
Colossal video. Thanks Mike. I too learned something new... diagonal arrow when selecting tables... very cool.
Colossal Thanks, WRH!!!!
"New Video = Something new to khnow" even a small one this is how to be perfectionist :-)
New Knowing is always fun!!!
Addicted to ExcellsFun. Thank you.
You are welcome, Chamchijjige!!!!
thank you so much! cant wait to practice and use it!
Yes, no doubt, the Excel Table feature is useful and amazing : ) Thnaks for the support, Idan Ben Ruby, with your comments, Thumbs Ups and Sub : )
you are awesome! learnt a lots from your videos, and you makes things much easier to be understood! coooooool!!!
Glad it was cool for you!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
It's really so much fun thank u so much Mr. Mike tables are really powerful
You are welcome!
This is really amazing stuff! I was just wondering if there was a way to automatically update my formulas to include new cells and here it is. Thank you so much for this fantastic series!
If you imagine it, Excel can probably do it : )
BIg thanks to the awsome teacher! Enjoying learning excel!
Glad you liked the video, Sun K : ) : )
Awesome Mike!!!! Keep them coming Please :)
Okay, more to come!!!
I'm going through your each vedio , I just loved it❤️
You are gaining the ability to do great things with Excel and have fun doing it!!!!!
@@excelisfun 😍❤️
Thanks, sir for the dynamic table and its application. It is surely informative
You are welcome, Devesh!!!
incredible!
Excel is really fun with you
Yes!!! I am so glad that it is fun for you, bumeshal!!!! Thank you for the support with your comments, Thumbs Ups and Sub : )
Great video. Thanks a lot for sharing your knowledge
You are welcome, Emmanuel!!!
thanks for another great video, you rock!!!
You are welcome!
Great video!
thank you very much Sir, God Bless you!
You are welcome, En A!!! Thanks for the support with your comments, Thumbs Up and Sub : )
Awesome lesson.
Glad you like it! Thank you for your support : )
This was a great video.Thank you so much
You are welcome so much!! Thanks for the support with your comment, Thumbs Up and Sub : )
Tanq Once again. Learned something new for the Day :)
as amazing as always. I am going to use this in my office
Glad it is amazing for you, Lukita!!!!!
Great Tutorial!
Glad that the videos and teaching helps! Thanks for the support with your comments, Thumbs Up and Sub : )
Learn learn n learn... Great mike
Glad you learn, learn, learn!!!
Excellent Mike!
Glad you like it!
Simply incredible.
Glad you like it, Daniel David!!!!!
@@excelisfun Very much so my friend. Thanks a million.
Hi Mike,Thanks a lot, please create a video about modifying existing excel table formats.
Another great video....Thank you sir
You are welcome, shahs!!!
ExcelIsFun as usual More than great
Glad it has "more great" for you, Hazem!
some of my calculations were off a bit at the end, but realised the reason why, by myself. great share.
You are welcome for the share!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Brilliant As always! :D
Yes, I am glad that you are watching and learning, Mostafa!!!!
Excellent!!!
EXCELlent!!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Great videos!
Glad that they can help you, Chris Webb : )
Thanks for the videos, learning heaps
Learning heaps is fun, just like Excel!!! Thanks for your support, Scott, with your comments, thumbs ups and of course your Sub : )
Great Videos!
Glad it is great for you!
great job sir, appreciate it
Yes!!! #15 and still going : )
Good Morning Teacher...I follow your instructions through every single class with diligence and application...Have searched through almost all of your videos but I am unable in finding a specialized class that would take me through Vlookup with Multiple Criteria & Three way Vlookup....Since there is a vivid and dedicated video on almost every single topic on Excel imaginable to me that you have uploaded and I am sure its out there somewhere, I request you to direct me to a class that will guide me through those 2 topics in your usual detail in structure ....
Gratitude Sir ..
Here is one video: Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells, th-cam.com/video/zjfpA2kJhm8/w-d-xo.html
Here is a playlist with 16 videos on this topic: th-cam.com/play/PLrRPvpgDmw0lP3hK6xearxD3F_GAUhLH7.html
@@excelisfun oh my god you have given me a dictionary Sir !!!...I knew you must have put them up some where YEARS back...With every single video my amazement to your knowledge and experience and respect grows exponentially...wish you were here somewhere close so that I could personally meet you to show my gratitude and pay homage to what you have done on TH-cam.... unfortunately due to recent catastrophic times I am not allowed to even shake a hand anymore Sir but atleast I can fold my hands to say my salute to you: "Namaste" 🙏 and put a garland of flowers on you since that's what we do as a show of The Ultimate respect 'round here for you ..
@@applesoranges559 Thank you for the garland of flowers and folding of your hands : )
Automatically adds to the table in mine too!
Yes!!!!
Great Post!!
Glad you like it! Thanks for the support with your comment, Thumbs Up and Sub : )
thanks a lot mike!
You bet!
Thank you!
: ) : ) : )
Wow! Excel is fun!
Yes it is, Jenny : )
Godbless you sir. You are amazing.
Glad it is all amazing for you, Boss Trucker!!! P.S. Love the user name: Boss Trucker !
Awesome!
Sorta like you, Awesome Highline Student ; )
thanks for the lesson sir
You are welcome, Febri!!!!!
I wished there was some HW... Awesome video as usual. 👌👌👌👌❤️
OK : ) I will give you some homework: redownload the workbook and then do all the work without watching the video and without looking at the red sheets : ) : ) Sounds fun!
@@excelisfun of course I do that. But I already watched the video... That's why I am kinda stuck. But that's fine. No issues.
@@mr.chinmayajoshi2688 That you already did that - that just means you are real smart : ) : ) : ) BAM: Mr Chin is becoming great with Excel!!!
Another fantastic tutorial! Thanks! 2 Questions.
First, how backwards compatible are these table features with older versions of Excel? I'm using the current version, but I have to share files with some that are using Excel 2010 and maybe even 2007.
Second, what screen capture software are you using? Thanks again!
They will go backwards fine. I use Camtasia.
Awsome Sir
Glad it is awesome for you, Huzaifah!!!
Excellent! :)
EXCELlent!!!
EXCELlent obviously ;)
: )
Very interesting video. I added this table to my data just as taught but when I try to update new information, the validated data does not see the change made in the rows addition.
Hey there,
Excellent free tutorials. Amazing pedagogy. Beautiful delivery and smoothness.
By any chance have you changed the location of the xls and PDF documents for this lesson? It throws a Permission/ Forbidden error when attempting to download.
Please help check on this whenever you are free and you can.
Working now, thank you very much.
Inspirational Mike as always. I was wondering if you offer an online certification, I would be one of the firsts to grab it.
I do not offer certification because there is no certification that actually tests the right skills to be an Excel Master. If there was a certification that actually tested the correct topics, I would offer it in a second : ) Thank you for your support!!
Thanks :)
You are welcome!
The mathematical way to explain (for the curious) where does that "1" (30:42) come from:
NetRevenue = TotalRevenue - AmountDiscounted
AmountDiscounted = TotalRevenue * DiscountDecimal
NetRevenue = TotalRevenue - (TotalRevenue * DiscountDecimal)
N = TR - (TR * DD)
Now we factor out the common factor TotalRevenue
N = (1*TR) - ((1*TR) * DD)
N = TR (1 - ((1)*DD))
N = TR (1- (DD))
NetRevenue = TotalRevenue (1 - DiscountDecimal)
Amazing❤
Glad it is heart-amazing for you, Lamees!!!!
@@excelisfun 😊
Thanks!
You are mosr welcome, S & P Schroeder!!!! Thank you for your kind donation : )
I look forward to each class, even the homework. Thank YOU so much!
@@TrailShake Yes!!!! I love to hear that. When a human looks forward to homework, they are found something that interests them and with hard work will become awesome = fact : ) So glad you are enjoying the class, S & P S!!!!!
I cant download the file anymore . Pls help and thank you for being a good tutor
Smashing.
BTW,
if you type CTRL+L (instead of CTRL+T) you'll get the same results :)
Yes, that is a great one! From the Excel 2003 version : )
Yes, as a matter of fact the 2003 LIST (CTRL+L) was the predecessor of 2007 TABLE (CTRL+T)
Great video! May I ask why your PC is not design to automatically incorporate the table when extra data are added in? Is there any specific reason that you set it up that way?
Thanks!
The default setting was not on when I filmed. You can turn this on or off with the check boxes in the AutoFormatAsYouType Tab in the AutoCorrect dialog box in the Proofing area of the Excel Options dialog box (File menu). There is usually not a good reason to have this turned off...
Hey Mike! I was trying to calculate my investment returns and had some issues. Can you guide me step by step or by any chance do you have a video on it? Thanks!
I have over 100 videos about fiance here: th-cam.com/play/PL90E1F26C7B85E78F.html
Thank you for your great videos! I always come to your site when stumped. But, i remain dumbfounded after multiple searches - is there a way to force the pivot table RESULT to act like a table?. Depending on the slicer selection, the number of rows within a pivot will change. The problem occurs when I have additional text/formulas below the pivot table, and depending on pivot table size - either have a large gap or prompts to overwrite existing data/text. Is there a way to implement the table concept - allowing for dynamic shifting of rows up/down depending on table size. I need similar functionality for pivot tables. Is there a workaround or solution? Thank you!
Not that I know of. Maybe re-build the whole thing with dynamic array formulas, but that requires that you have Microsoft 365 Excel.
@@excelisfun Thank you for confirming the idea that has been in the back of my mind - and I do have OFfice365. Currently the data resides inside a power pivot data model, so will have to figure out if I can manipulate w/cube functions?. To my knowledge - cube functions do not work with spilled arrays or inside tables? Another idea I ran across in one of your videos was linking the power query result to a table, and then try to use DAX within tables. But, that is beyond me - and another learning curve... Thank you again, for your great work!
Thank you Mike. It is obvious that you are a big fan of Tables, so am I. But there are also some issues with tables. for example how to (not) use them in protected sheets. Also if you combine formula's and numbers in a collumn (You should not do it but it is practice...). Also there is something like a cache memory of a formula in a column, which you can not delete. Bottom line: tables are great but please be careful. Maybe make a video about these issues? Greetings, Bart
Thanks for the tip! There are also other issues such as Mixed Cell references and Expandable ranges... But the video highlight some of these issues is for the next class after this Basic one...
Can't wait to see the next one
Thank you for the video! How did you organize bars in the last example?
I am not sure what you are asking. What minute mark? The chart I made was sorted by date.
Sorry, I mean in the HW(2an): Vertical bars are from lowest GPA to highest.
One way, is to sort the values in the cells, then the chart will also be sorted.
Thanks for the video.
I was wondering at around 29 mins that what if I put false in the lookup formula.
You mentioned that the default is false. But I tried to put the false in the formula and it showed me N/A.
I would appreciate your guidance.
it depends on where you put it in VLOOKUP. Where did you put it?
Firstly, I do have to thank you for all your excellent sharing. I have improved my productivity on excel because your course.
But I have an issue with validation data. I have done everything as in the video: I have a list of date which is the source for my validation, this list is an excel table, when I add a new data to this list the table automatically expand until this new cell, that's ok; nevertheless in the column (other excel table) where I am doing my data validation, the drop list does not update with the new data from the source list. I have made the selection of the source list manually (selecting) and by the upper arrow and none of them have worked out. I hope you can help me.
By the way, I am using Excel from Microsoft 365 version 2002.
I figured out that it happens when my source data table is in a different sheet of the table where I have the drop-down list. Does it have to be with any configuration on excel? I hope you can help me. Thanks in advance.
My PC showed the relative cell reference as "RevenueTable[[#This Row],[Units Sold]]"
5:18 how to add column index number in vlookup formula if I have very large table? Is there any shortcut? Thanks in advance.
Another great video Mike, but at 4:33 I'm only getting the table range in the formula, not the table name (PriceLookup). I've tried re-downloading the file and trying again.
I'm doing this: -
1) Select cell in table
2) Ctrl+T
3) Type: PriceLookup
4) In cell G24 Type: =VLOOKUP(F24,
5) Select cell range A24:D28
This is where I just get the range rather than the table name in the formula.
Any ideas where I might be going wrong as this is very frustrating for me?
Thanks,
Alan
Try changing settings as seen in one of these:
th-cam.com/video/w4E6KhDBFNk/w-d-xo.html
th-cam.com/video/sUAwRNAGsCQ/w-d-xo.html
Where can I find the Intermediate level excel videos? I tried to find it from your watch list and couldn't find it. Please Help.
Here they are: th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
@@excelisfun Thank you so much!!
@@akhtaruzzamanjoy8524 You are welcome!
Are there instances when you are better off deciding not to turn a table into an Excel table?
For me, there are three main advantages to Excel Tables: 1) they expand when you get new records, so anything pointing to tale (PivotTable, Chart, Power Query, Formulas, Data Model) can be refreshed to se the new records, It is easy to reference columns and tables using mouse cursors when you are creating worksheet formulas, Sorting and Filtering dropdowns are always there. If I am going to be doing filtering/sorting, creating formulas based on columns or tables, or want things to update when new data arrives, I use Excel Tables. Which is to say, most of the time they are just a huge advantage.
@@excelisfun Thanks :)
@@johngronkowski5132 Excel Tables Rule is sorta the bottom line..
To the person who gave this video a thumbs down:
Why?
Thank you!
Exactly.
16:47
10:00
11:30 25:16
I am not sure what you are trying to communicate with the times you are posting in the comments. Do you have a question or do you want to say something, PLAN XLSM?
@@excelisfun It's nothing . No question. Thank you !
+
SQRT(225) = Table and VLOOKUP fun!