Only 1% of Excel Users Know Excel Tables! STAND OUT from the Crowd!
HTML-код
- Опубликовано: 22 май 2024
- Excel Tables can provide huge efficiency gains, but few Excel users know about them. This video will get you started with Excel Tables and showcase some of their cool features.
🎓 LEARN MORE If you'd like to learn more, including how to reference Excel Tables in formulas, please take a moment to check out my course: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e... 
Наука
This is the best tutorial I have seen on excel tables, thank you.
Glad it was helpful, Nicole!
Hi Mynda, i really appreciate your video's and no fuss training style.Thank you for sharing your content. Fred
Thanks for your kind words, Fred :-) glad you find the videos helpful.
Excellent RUclips. Just what I was looking for - easy to follow. Your narration is professional and concise. Thank you!
Glad it was helpful!
Well presented. Clear. Consistent vocabulary. Well-paced words and sentences. Clear voice.
Thanks for your kind words!
You are awesome! Simple and informative. Thank you and stay healthy 💪🏼.
Glad you liked it, Jin 😊
Excellent video! I have used Excel for many years and never really know Table can be so helpful and easy to use!
So glad you're now interested to use Tables now. They certainly are a huge help.
Elegance in simplicity via a "no-fuss", concise teaching methodology has already made me a fan. I use a range of tools in my self-learning quest aimed at econometrics but I don't have the financial freedom to access paid learning routes for the tools I use so channels like yours are extremely appreciated by people like me. I will be recommending this channel as a "must watch" as one of the best, free Excel learning resources about.
So pleased to hear I can help, Jimmy! Thanks for sharing our channel, too 😊
if any one has a problem with table i was one day looking for the solution
as i fever for here and say thanks to her i promised to share her value
ruclips.net/video/HdvlA8mdfWs/видео.html
EXCELLENT video; been using Excel most of my adult life, and had no idea how powerful tables can be! Using this for everything going forward, thank you, Mynda!
Awesome to hear!
Great tutorial!
Thank you very much for sharing your experience with us, appreciate it 🙏🏻
My pleasure 😊
I have looked at many, many excel youtubes. YOU are the BEST! thank you for doing all these.
Wow, thank you so much, Joyce!
Thank you. I was using Excel and VBA to a high standard upto 15 years ago. I dont think tables were around then (?). I am now retired but have a project I want to work on. I saw someone else using tables in another Vid (not yours) and knew that I needed to update myself properly. I was so fortunate to find your tutorial straight away. You have a great easy presentation style that flows continuously, simply, and comprehensively. Having subscribed after the first couple of minutes into your presentation I will now take a look and see what else you have to offer (hoping that you do one on the Stocks data object). Again thank you so much - I am looking forward to learning and enjoying your video's xx
Thanks for your kind words, Hougrel! I do have a tutorial on Data Types here: www.myonlinetraininghub.com/excel-data-types and a dashboard which uses them here: ruclips.net/video/D44EDant0rs/видео.html
That was really helpful. I've learned a lot binging your videos.🤩
Happy to hear that!
Really nice tutorial and very well explained - thank you so much Mynda
You are so welcome!
SIMPLE AND CLEAR!!! No one else mentioned sizing handle as marker of end of table! Like the way you highlight. Most trainers are NOT clear as to what they are highlighting!
Thanks so much, Mark! Glad you found this tutorial helpful :-)
Excellent video Ma'am. Learned a lot. Thank you.
My pleasure 😊
Great, love your seminar :D
Thank you :-)
Excellent video as always thanks for all the great work you do
So nice of you, Ian :-)
Enjoyed this .I am an excel junky, but never really gave thought to how powerful tables could be until now . In fact I normally would would remove them Anytime I see your video ,I just have to click cause I learn valuable things .Thank you for all you share
So pleased to hear you're a Table convert now :-)
Really clear and will be really useful; thanks.
Glad you liked it, Tony.
Excellent as always!
Glad you think so, Darcy!
Great tutorial. I have learn lot from it
Great to hear!
Hello, Thank you for your great videos, I have learned a lot form you.
Glad to hear that!
I learned a lot here, Thank you very much
Glad it was helpful!
Excellent video!
Thanks so much 🙏
Dear Mynda,
Your Excel tutorials are absolutely brilliant. To be honest, I started with a few different RUclips channels with Excel lessons, but my interest chucked away. I was then trying to filter out the channels by giving every channel a go. Yours was not there on the list either. Frustrated at the lack of good ones, I just took a poke around on Google. I struck gold with your channel.
Thanks a million for what you do.
Prasad
Sweden.
I am one of those excel self learners who learned most of what I know before I learned about Tables! Because of that, I am not a fan of Tables - however, I like them for an unchanging Pivot Table Report. What I mean about unchanging, is that you build it once and no additional analysis or formatting will be required except to append date to the the source Table. I use tables regularly for such scenarios.
Great to hear, Irene. Tables are perfect for this purpose.
I was going mad as I couldn't work out why some tables retain their row banding when drag copying and some don't and it was because I had areas of no data and needed to select the whole data area and not just create the table from a single cell. Thank you for pointing me in the right direction!
Glad I could help!
Nice, thanks!
Our pleasure!
Amazing knowledge. Super.
Thanks so much, Mario!
One of the best channel related to Excel. I always used to wait for new content. Please make some more videos on power query tool. The only concern is that videos are not uploading frequently.
Thanks, Zahir. If you want more frequent content then write a weekly blog on Excel here: www.myonlinetraininghub.com/blog
I thought: “Tables were very basic things and everyone knows about it” But I was wrong there were many things which I had seen first time! Thank you Mynda ma’am for your lessons😍 #MyExcelQueen #Excel #Tables
So pleased you discovered some new things about Excel Tables :-)
Thank you for this tutorial! It refreshed my mind on Tables! Ps. I added you on Linkedin.
Glad it was helpful!
Oh and one of the greatest excel shortcuts to use with tables are shift+ctrl+L to show/suppress filters. (of course there are many shortcuts but that one is newer to me and I wondered how I went so long without it!)
Nice! Thanks for sharing.
Thank you
Welcome!
Simple but great 😍
Thank you! 😊
I used to think Excel was very boring and now I’m in a job that requires lots of data work and spreadsheets. I’m falling in love with the power of excel. It’s amazing what you can actually do. I do almost everything in excel now.
Excel could never be boring, Pat :-D Glad you're enjoying it now.
Thanks very good
So nice of you :-)
Superb ❤️❤️❤️👍🎉 thanks madam a million 🙏😀
My pleasure 😊
very very useful
Glad to hear that 😊
very useful
Glad to hear that 🙏😊
Amazing ❤❤❤
Glad you liked it!
Like it. Thank you 😊
Great to hear :-)
Excellent
Thanks, Shankar. Have fun with Tables.
Great tutorial; I would like to see advanced filtering in tables
Thanks for the suggestion, Tommy.
Simple and essential lesson as alwsys! But I do stll prefer range to table. Range is easy to manipulate for me.^^
Thanks, Kang. Sometimes habits are hard to break :-)
Well Done
Hope you can make use of Tables, Shakira.
excellent
Thank you!
Hello , thanks for your video
is there some special feature in Excel Tables for conditional formating ?
thanks
No, Conditional Formatting works the same way in Tables as it does in regular cells. If you have the latest version of Excel 365 then you'll have updates to fix segmenting of conditional formatting that occurs sometimes in tables when rows/cells are inserted/copied in.
شكرا لك
My pleasure 😊
Tables are at the core of much of my work. I would have been surprised to learn that 1% of folks use tables.....until I recently started working at a client where clearly tables have not been in the tool kit!
Great to hear you're a Tables fan!
Great overview. I can well believe 99% are missing out on these benefits but it's a real shame. I tell anyone that will listen about using tables 👍
Thanks for spreading the word 😊
Hello Ms. I have just discovered your channel, and I would for sure watch more videos, but for now I have a question, why the fill option does not work in a table? I have a table similar to the one you have here, but when I add new data in a particular row, I want its code no. to update in that place, like in your example from E055 the ID number jumps to E061, without using the drag option, which is very much manual, how could you rename all ID cells in a linear series i.e E055 then E056, then E057 and so on until it gets to E061?
Thanks
Hi Yad, please post your question and sample Excel file on our forum where we can help you further as I'm not sure what you're referring to exactly: www.myonlinetraininghub.com/excel-forum
Nice to have mentioned pivot tables assuming we all know how to build them
Here's my introduction to PivotTables tutorial: ruclips.net/video/vQlFiLUaw4k/видео.html
Great video. Thank you for sharing. I noticed that in my table, when I change a formula in one particular cell, it automatically updates the forumula for all cells in that column, even though I want to change formula for that particular cell only. I am using Office 365. Is there a way I can control this automatic formula update? Thanks.
Hi Ram, Tables are designed to use best practice formulas where you have the same formula down a column. If you want to override this (which is not advised), you can enter the cell references in the formula rather than the structured references Excel enters for you when you reference cells in a table. You can also CTRL+Z to undo Excel copying the formula down the column.
Very informative.. Tks you
A question though
If the table is an output from power query and I need to add on columns that involve some calculation base on the data consist in the table. How can I do it?
How I do it now is I copy and paste link to the table on a new tab and add on the calculation manually
Thanks for your help in advance
Glad you found the video helpful. You should add any additional columns using Power Query to ensure they always stay synced with the rest of the data in the table.
@@MyOnlineTrainingHub Tks... It is helpful... Appreciated
Excellent Video. Is there a way to save a filtered result of a table as a second table that will automatically update results when new data is entered in the master table?
Hi Mark, you could use the FILTER function to do this: ruclips.net/video/ZCQAweoAdOw/видео.html
👍thanks
Welcome 👍
Slicers in tables .. just learned of it after 20+ years of excel usage
Better late than never, Neil 😊
I was wondering if I can transfer an excel table(that has forecasts /sale ) into other Excel method? in other word, I take a screenshot of that Excel sheet and paste it on my PPT.
Yes, you an insert screenshots or even the table itself into PowerPoint.
I noticed when you open your Name Manager there seems to be some list-definitions with a different icon than used for Named Range or Table. Would this be specific for Excel 2019 and higher and if not, how do you create those?. I'm still puzzled about the fact that it seems not possible to define a data validation list directly from an Excel table-column. I can ofcourse define a named range to be equal to the Excel table column using the Excel table structured reference (i.e. named_range=table[column_name]), and next use that named range for the data validation list, but that seems quite double work....so any comment/view from your side is much appreciated. And last but not least: thnx for the nice videos and blogs.
Glad you liked it, Henk! The icons might be new, I can't remember exactly. In my Name Manager at the 2 minute mark there are icons for Tables and Slicers. These icons aren't something you can create. They are automatic, so you'd have to upgrade to a later version of Excel to get them. Hope that clarifies things.
Yes, not being able to create a DV list from a column reference isn't possible in Excel. The workaround you describe is the next best thing.
Mynda
@@MyOnlineTrainingHub Thnx Mynda, the other icon must be for a slicer as you mention. I don't use them a lot so I probably never noticed. My take-away of the day :), thnx!
Hi, could you please help me with this. Do you know how to format cells at Number in the Header Row of the Table. It means when you input the number to cells in Header Row, they are not the number, you can check by Isnumber() command for that Header row's Cells and it gose False. I need the Header row's cells to show as number so I can use my command directly in this Header Row. Thank you for reading.
Tables do not accept numbers/dates in the header row. You might like to add a row above the table that stores the number header that's outside of the table.
Excel Tables are so amazing, you will not be able to imagine life without them!
What you get:
- Filtering for range outside of Table is not allowed
- Usual keyboard shortcut for filtering are not working with Table
- Feature Track Changes will not work, if Table exists in Workbook
- You can't share a workbook (feature allows two person edit workbook at the same time)
Sounds like you're using an old version of Excel, because:
- I'm able to filter outside the table.
- I use the right-click shortcut key > e to open the filter options
- I can share workbooks and co-author with Microsoft 365
- track changes is coming soon.
@@MyOnlineTrainingHub It is Office 2013. You can filter table even when it affects rows where table exists?
@@MyOnlineTrainingHub shortcuts: for example, I can't use Alt+4 shortcut to filter out by a current selection comparing to usual data range
Would have been helpful if you'd included a link to the spreadsheet so we can download it and follow along...
If you're interested in learning more about Tables you'll find the practice files are included with my Tables course: www.myonlinetraininghub.com/excel-tables-course
Is there a way to protect columns in an excel table while at the same time keep the functionality of the table? When I protect columns, new data does not extend the range
No, you're not able to. It's a huge frustration with Tables. If you're capturing data, then another option might be to use Excel Forms and allow the form to put the data in the table. That way the user never has to touch the table. More on Excel Forms here: www.myonlinetraininghub.com/gather-data-with-excel-forms
Can you add new lines to this table if you protect the worksheet?
No. Tables cannot be edited in protected worksheets.
I throw EVERYTHING in named tables now!
Great to hear 😊
Are there any disadvantages of using tables? Thanks.
I'd say there are trade offs rather than disadvantages. If your worksheet is protected then you can't update a table. If your data is massive (500k+ rows and 10's of columns with lots of formulas), then a table will make the file slow.
Can add dynamic serial number to the tables?
Can't see why not 😊
Hi there - I created the table but the header row content does not display ?? The info is there but not displayed
Go to the Table Design tab > in the 'Table Style Options' check the box for 'header row'.
@@MyOnlineTrainingHub Super appreciative of your reply. When I turn on the header box that's when the content disappears. The info is obviously still in the cells though. Its already a large file so maybe I'll do without the table. Thanks again.
Is there a way to automatically keep a formula from the above cell as you add in a new row into your table?
As long as all formulas above are the same, the formula will automatically be copied down to new rows.
@MyOnlineTrainingHub That doesn't seem to work. Maybe as I add in more rows, it will learn? I have two columns containing a formula. One of the columns pulls up an old formula I used in the cells before the table that I deleted out, and not the current one I'm using. Perhaps there's some kind of an underlay it picks up. 🤔 just very odd to me.
@@MyOnlineTrainingHub I figured it out, there was a square with FX on it, saying AutoCorrect options after clicking that it started to copy down.
in table creater., some times Vlookup formula not using ., how to solve ., ?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hello, where can I find a copy of this excel worksheet?
Hi Stephen, I didn't make this file available outside of my Tables course, sorry. However, this Tables tutorial comes with a supporting file which may be of use: www.myonlinetraininghub.com/excel-tables
Can you share a video on waterfall chart?
I'll make a note for the future. In the meantime I have a written tutorial on waterfall charts here: www.myonlinetraininghub.com/excel-waterfall-charts
Is it possible to merge table headers Mam?
Yes, when you merge columns the headers also get merged.
I wish you were talking about absolute and relative in the table
You can learn about absolute and relative references for tables here: www.myonlinetraininghub.com/excel-table-absolute-structured-references
@@MyOnlineTrainingHub Thank you dear teacher
@@MyOnlineTrainingHub It was great, thank you very much
Thanks,
Hope to clarify how to fix a cell when it's in an Excel Table, .
The header only appears in the formula and don't know how to fix it.
Col A , Col B, Col C , Col D
Profit%, PRODUCTION , Index JAN, Index FEB, MARC, APR
25%, 6500 , [profit%]*[PRODUCTION]/1, [profit%]*[PRODUCTION]/2, [profit%]*[PRODUCTION]/3 etc
How to FIX ColA cell horizontally?
Please see this tutorial on absolute references in Tables: www.myonlinetraininghub.com/excel-table-absolute-structured-references If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Indeed, tables are great tools in excel, but the only reason I'm not able to apply it in my workplace is that we work with yearly data and we need to hide those columns having the historical data, and we have bunch of tables under. Is there a proper solution to hide the columns of the sheet while there are tables? Currently I can't use tables because of this issue.
I'm not sure what the issue is because you can hide table columns. If you have multiple tables on the one sheet, then make sure they're beside one another, not under one another.
@@MyOnlineTrainingHub the thing is they have to be under each other due to the structure of the work, therefore I believe it is not possible unless they are put beside each other?
PLS. when I use a protect sheet the table doesn't work and also the formulas in it. Can you help me in this? Also when I us it google sheet to open it the formulas give me an error.
It is a known limitation that Tables cannot be edited when the sheet is protected, however formulas should calculate. I cannot speak for Google Sheets.
!!!!!!!!!!!!!!!!!!!Need so help plz!!!!!!!!!!!!!!!! I want to create a simple table with numbers from A1:A10. In cell B1 I want to type the number in, and have that number automatically enter in the table in cell A1. Than when i type the second number in cell B1, i want the number from A1 to drop to A2 and have the new number go on top "A1". I want to do this each time i type a new number in cell B1. How can i create this rule????
You'd need to write some VBA code to make this happen.
😮👍
🙏
Good video, but while it shows you can make pivot tables and data in input slightly better, it doesn't answer any of the reasons for using tables, such as when or why to use tables. For example, are calculations faster, does the file size increase, what does it provide that a range with filters, etc cannot, does it add to the data model, ie why would we use or not use a table?
Thanks for the feedback, Neil. This is just the first video in my Excel Tables course that goes into a lot more detail: www.myonlinetraininghub.com/excel-tables-course
5:20
If this were true, I would have a job within 3 weeks as opposed to...... Lol. Great vid
Thanks! That 1% stat came from Microsoft's telemetry. Maybe it's 2% now 😉 good luck with your job search.
@@MyOnlineTrainingHub thx, but I have one for 5 years now. I could have got one much sooner if this were true
how to remove the table creater
Not sure what you mean by remove the table creator?
Please share excel file..
Hi Usman, that file is part of my Excel Tables course: www.myonlinetraininghub.com/excel-tables-course
So different your.'s
Employee E038 is getting only 31k at 56 years-old, that's mean
😁 they’re semi retired, so don’t feel bad for them! 😉
I overuse them. : )
Not possible 😂
i dont use it becuase is shit when i add data to table doesnt referch and let it out from the table is oke usefull for analysis a data but now useful to track sales or trading journal u cant make it it as a template is totaly shit
Sounds like you need some help setting up your file properly. Feel free to seek help from our experts on our forum: www.myonlinetraininghub.com/excel-forum
This "old dog" is having trouble learning "new tricks". Just me I reckon. Have a table with 70 columns and 910 rows. No matter what I do, I cannot seem to enter a column on the far right of the table. Cannot cur/copy/paste either. Feeling defeated.
hmmm, that's odd. Have you tried editing the table range to include a new column?
@@MyOnlineTrainingHub Thanks for the idea - will try that.
You start off saying no one uses tables. Then you say "if your table has headers"
How do we know if our table has headers?
You haven't even told us what a table is...much less what a table header is.
SMH
As with all tutorials, there's an expectation that you have some prior knowledge of Excel, however as you are completely new to Excel you're missing some of that. To fill in some gaps in your knowledge; headers are column labels. In the video example they are Employee ID, Date Started, Age, Gender and Salary. A table is just a formal structure you give to your data that allows Excel to reference the data, among other things, as shown in the video. Hope that clarifies things.
@@MyOnlineTrainingHub I was just wondering what a table is, why I would want to use one, whats different from regule Excel.....I guess I just don't get it.
I'm googling it right now, but no one really seems to be able to tell me what they are for.
Maybe thats why 99% of excel users don't use them......
A table is a (smart) format you apply to your data. It allows Excel to recognise and reference your data using the table's name and structured references, as I showed in the video. It's far easier to reference the cells and columns of a table using names than cell references like A11, G2:G99 etc. It also has other built in features like automatically copying formulas down to new rows, Grand Totals and more. However, probably the most important reason for formatting your data in a table is that as you add more data to your table you don't need to update any references to the table that you made in formulas to include the new data as it will automatically be included in the table range. You don't have those features available if you don't format your data in a table.
@@MyOnlineTrainingHub I just wonder why I would need to, if 99% of people don't think they need to. I'm new to Excel and I need to know if it applies to my use case.
It's not that 99% of people don't think they need to, it's that 99% of people haven't heard about tables and don't know what they're missing. Most people are self taught. They don't know what they don't know. They're missing out on these amazing tools that make our Excel lives easy. I'm just trying to spread the word and help more people get their work done more efficiently.
Is it feasible for reference tables to automatically update prior to my dataset referencing them?
Not sure what you mean by a reference table vs a dataset exactly, but if you're referencing anything in a formula, then the formula will automatically update when the data it's referencing changes.
@@MyOnlineTrainingHub Not gonna lie, I can't recall what I meant either. The wonders of drink!
😂