I do understand the dilemma. However, I use and create tables every day. The flexibility to view data the way I want in a table is magical. I ❤ tables.
I use tables only for primary data sets. I belive the problem with the adoption rate for Tables and Structured References that they are considered intermediate concepts BECAUSE THAT IS WHEN THEYCARE TAUGHT. I believe they should be taught from day one in formal classes and in online courses. I actually find formulas more frustrating will cell reference formats than with structured references. It is also helpful to teach people to name things smartly. For example I always name Tables with a single word when possible and call it a Table in its name such as TblData. Also keep column names as short as possible.
Great point, Richard! And I should be clear that I will continue to teach and advocate Tables. This video allows me to help explain when to use Tables. I'm also hopeful that Microsoft will make changes to make Tables more approachable and user friendly.
I kinda don’t get this take. It’s a quick video, and the topic may be more complex, but I feel like a properly designed workbook doesn’t encounter these errors. If you are designing a workbook for thousands of users, one would think it is for them to simply use (input data, filter, view charts etc.) not to redesign to fit their needs. This example would ensure consistency in branding and data types throughout the organization. Complaining that structured references are different and confuses users is similar to complaining you can’t redesign Quickbooks because you don’t understand the backend code. Beginner users, even intermediate to an extent, just use the software. They know how it works (where to enter stuff and data relationships throughout the software) because they’ve done it for awhile and the GUI is clear, not because they see how the tables and input boxes are labeled in the code. Tables are an awesome tool that handle dynamic arrays and workbook relationships you cannot realistically match without them. They are the basis for the tools that make excel the powerhouse software that it is (as you mentioned pivot tables, queries, etc). Plus, it’s not like the references are random, you name the table and headers so =AVERAGE(CreditCards[Balance]) is pretty clear to me. More so than sheet names and cell numbers that I have to go find and understand. Again, if I am designing a workbook for other (typically multiple) users I design it as such. Everything is labeled and straightforward, input data is validated as needed, the sheets are protected and formulas are hidden. I like the content, I’m here weekly, but I think I found one I would argue a bit about. LOL
Great points, Ron! Planning, design, and restrictions can really help with the usability of Tables. I'm not sure I completely agree with the QB analogy. Excel is a different beast. But I get your point.
There are times when I build what I need for my boss using tables and then convert to range for her version. She doesn’t “like” tables, what that means is she doesn’t understand how powerful they are especially when data is dynamic and all those pivots/charts change size regularly. But that’s my job as her analyst and excel expert.
You've hit the nail on the head. For Excel 'professionals' (the people who subscribe to this channel) they are absolutely the way to go. But for the more casual user (including senior management) they're not liked, because they need time and effort invested to learn how to use them, and understand how powerful they are. If you handle data management with Power Query, they're the only show in town, for my money.
Good point. I get what you're saying. But with files that only I use, I always use structured Tables. They are fantastic to use with Power Query. And I'm training my team in using them. For me, they're the way to go!
I learned to used the structured table references, it's amazing. But collecting files from a large user base, I prefer to have the same headings, and various records, then use Power Query and / or Power Pivot to massage the data into a final format.
I fell in love with tables since the moment I learned about them, the structured referencing has made my life easier, because, compared explicit cell references it is easier to understand the formulas when you look at them, and I started changing the way I use Excel to all-tables, and I am teaching my co-workers the benefits of using them, and how to use them in their daily work around Excel. Thank you for the tips, they are really helpful.
Exactly this. Now my co-workers get admonished by me, when I catch them NOT using tables. Of course, they also make fun of me for being so in love with everything Excel. Guess what? Don't care. WORK SMARTER, NOT HARDER!! (but I completely understand about knowing your audience, too)
When I am teaching someone Excel, on of the first things I do is teach them tables. Once they understand them and how to work with them, it makes everything else in everything un Excel so much easier.
My view is your work with Excel you learn it, constantly. It's endless universe and it's fun, for me at least. Bring the lower Excel Knowledge level employees up, not other-way around. Besides, there is always an MVP with a solution to any of your problem. Accommodating ignorant is not productive.
I completely agree with bringing the knowledge of others up. That might be obvious as I've dedicated my life to teaching Excel. 😂 And also about your view on Excel as an endless universe of fun. I love that! But this isn't about accommodating the ignorant. As I mention in the video, Excel is not a primary skill for many, if not most, users. And I'd argue that I've made the mistake of being ignorant in thinking that my users and coworkers should learn and remember the many nuances of structured references, even if they only use Excel a few times a month. I will still continue to teach and advocate for Tables. As I mention at the end of the video, I'm also hoping Microsoft can make some changes to make Tables more user friendly and accessible.
As an Excel app developer I used to use cell refs but I'm now heavily into tables and it's structural nomenclature, especially within the confines of VBA. In developing an app things change often and changes can be cumbersome when you move a column for instance. My apps are locked and limit the user to customize anything since it was developed for user input and not user customization. Once finalized, it's locked. Tables and all it's features make VBA a little easier to update, edit, and write in the aspect of data manipulation and calculations. I'm now a Table man and will continue to expand my knowledge about them. Moving forward not backward. Thanks for the great video.
I'm very ambiguous. On the one hand, I completely agree with you and fully understand that tables can confuse some users, but on the other hand, you can't continue to let the lowest level decide how your work should be done. So I try to explain what tables are and what their benefits are. But when dealing with a large number of users, I tend to let the "lowest common denominator" determine how my Excel sheet looks.
I almost always implement structured tables in my Excel applications-- they have many more benefits than gotchas when working with more than two or three rows or columns. It would be helpful, though, if the F4 key worked in structured formulae the way it does outside tables-- adding '$' to lock cell references. If the dollar sign convention cannot be used in a structured table (why not?), the F4 key should at least add the extra brackets as shown in your video. Thanks for highlighting these issues.
I agree with your question on the F4 key. I actually created an add-in that allows you to use F4 to toggle the absolute reference notation for structured references. Here is a link to the video that explains more if you want to check it out. ua-cam.com/video/qt-yfnZGXFg/v-deo.html The video is over 10 years old, and this has been an issue for a long time. Still praying it will be fixed someday. 🙂
Thank you for bringing this topic into the light. I'm an Excel trainer, solution builder and 30 year long user. I ALWAYS include table in my classes even to beginners. But I have to admit that you've revealed to me one of the reasons why so few seasoned users use them. I think that most of the time, it is because they have not understood yet that it is the only object in Excel that has the power to dynamically change its size, making any reference to it so much more robust than regular references. But what you've pointed out here is important to be aware of. Thank you for presenting not only the beauty of Excel but also some grey area (for some people at least). Love your XL and PQ videos. Keep on you're great job !
Thanks so much, Lilou! I'm happy to hear you are teaching Tables. I will continue to do the same. They are an amazing feature even though they have some caveats. And also, thank you for the nice feedback. I appreciate your support. 🙏
I have been using excel for 25 years and in my line of work, did not need tables. I have used tables on occasion and did find structured referencing very confusing and it made me want to use tables even less! It is great to learn of that toggle so I may start using them more! Thanks for the info!!! 🙂
I love tables. Teach them to all my students and explain to them what structured references are but did not realize till now that it could be confusing to other users within their companies. Thanks for the clarification. Will make sure to use this info in future.
Great video! I've found that so many people that have trouble with tables are the seasoned veterans who never learned to use them. When I show tables and pivot tables to new users I find most never need the formulas as much and don't have to learn the old way anyway 😊.
I stop using Tables too after I found out calculation processing time with Tables is much slower than normal range when database is large. I work with >50K lines.
Microsoft has made some improvements to this over the years. Although there are still performance limitations, it might be worth giving them a shot again if you haven't in a while.
I quite like tables given that they auto expand and I don't need to put an anchor row or column in to avoid messing up range references when I want to add a row or column to a non-table data set. However, I have been tricked by copying table totals across a table when I've added new columns to the table. If you copy the table total formula over by using the fill handle, it gives you a different result (correct result) than if you copy and paste it over (which results in the total of the column you copied the total from). It's also problematic when you start with a table and then need to convert the table back into a regular data set (say if you need to have a column header that contains a formula - which you can't do in a table). Any references to the table that you did before convert to ranges, but if you find you need to convert back to a table, those structured references are gone. It doesn't happen to me often, but often enough, when I have trouble building something and find an issue using tables, convert it to range, find a different set of problems and have to convert back.
Great points! I didn't go into details on those issues in this video, but I'll add that to the list for future videos. These nuances and behaviors are really important for users to know and learn.
I love your postings!! BUT .... I use Excel for Mac and many times my Excel doesn't obey your suggestions. Is it possible for you to somehow accommodate us? Thanks. And again, I LOVE YOUR POSTINGS!!!
Hey Bruno, Thanks for the vote on Excel for Mac. We'll try to incorporate more training on the Mac version in the future. I see how we could improve my mentioning keyboard shortcuts on both Win and Mac, and also show/mention when screens or UI might look different. Thanks again for bringing this to our attention. I appreciate your feedback and support! 🙌
I use tables all the time because I use PQ to clean my data. I don't mind the structured reference language at all (locking ranges is a bit clunky). The issue I have with tables are related to how they don't work with features like spilled ranges or how I can't moving a group of sheets to another workbook when a sheet has a table. Mr. Excel gave an interview where he discussed his issues with tables on Ann K. Emery's YT channel that was useful (9.40 minutes in).
Thanks for sharing! The issue with moving/copying multiple sheets can be frustrating. I've come up with a few workarounds over the years with VBA, but nothing solid. The main challenge is that any formulas that link between sheets will break. It gets messy...
Hi John, I watch a lot of Excel video's made by MVP's, but they always focus on the techniques. This one focus on the user. Please keep doing so! Thanks. Other examples of business problems: Some cells require text input (CELL, INFO), terrible if you share this internationally..... So as a developer there are many issues you might not be aware of...
Harsh take: Tables have been around 17 years at this point. It's time people start learning new things. How will they ever pick up on the new array-centric formulas that provide so much power? Not that tables make that easy, but thinking slightly more abstractly is of tremendous benefit. My problem with table use is that they are not properly propagated-by Microsoft-to be used everywhere. In particular, Conditional Formatting. At least the "Refers to" references update automatically as the table grows (hit or mostly miss if shrinking the table). But God forbid trying to use structured references in your formulaic conditional formatting. Tables are great as a source of data, but as processing vehicles are problematic. And forget loading tables with array formula results directly. I fear tables have reached the end of their development cycle as far as Microsoft is concerned. Which is a shame, because they are such powerful structures, but they could be improved and should be. :::Le Sigh:::
I use table purely because of formatting or data organisation and for the fact that if I need to delete or include rows/columns, it won’t mess the whole worksheet, it gives me instant subtotals but i have to convert it back to the range because my formulas don’t normally work inside tables because the numbers change formatting to general. So the moment I convert back to range, I can see the display error for each cell, the little left green corner and I have to convert all to numbers so the formula work. I don’t understand why this happens, it must be formatting. I only convert to table, so I don’t miss blank rows and so I can get to power query easily. if I convert table back to data range, I don’t normally go back table once again. I don’t have the time to investigate it further, hence why I watch UA-cam channels in my spare time! 😂 I agree with you, It is so annoying because it is something that puts people off it too. I haven’t given up yet because I like the look of it, tidy! and it’s quick to navigate it , and the headings replace the excel column reference when you are right on the bottom of the table. my issue is indeed the formulas but I will change the settings like you have shown! That would improve a bit. ! I so worry that the formulas could NOT be telling me the truth, hence why I double double check it all the time because I will BE mortified if the figures I passed on are wrong, I’m stock analyst for a global market company and this could have a major impact on future costs, P&L or even over ordering, the lot!!!😮. Can’t even think about it!
Hi Jon, I know a trick if you write formulas and you DO NOT want the structured reference: just select more than 1 cells and write the formula, then press Enter or Ctrl enter (my favorite key combination, filling the selection with the formula, NOT changing the format). Selecting more than 1 cell while writing the formula always gives you - and all other users of your sheet - the A1 notation, without changing any settings. If you just need the formula in one cel, delete the formula in the other cell. When I give Excel trainings, the Ctrl Enter is my "power trick", unfortunately it doesn't work in tables because I then don't get the structured language although i personally prefere the structured language! I am so used to first select the cells, then write the formula for the active cell and then press Ctrl Enter... in tables I don't do it, I don't need it because filling one cell in an empty column duplicates the formula (in Structured Reference Style) to the whole column.
I use excel since version 1 and I hate tables. I must admit I use Excel primarily for engineering and not for financial analysis. In my use cases tables are useless. The average weight of a product varying from 1 to 10000 kilo's doesn't make much sense :-( I'd love a proper engineering version of Excel...At the end of the day I still prefer Google sheets because it's very east to program a custom function in plain old javascript...
Tables in Excel have advantages when it comes to accessibility . A non-sighted user trying to navigate a huge Excel workbook will have a much better chance of doing so if contains well-named tables, rather than have them trying to work out where data might start and finish, particularly if the workbook contains many worksheets.
i’m a very advanced Excel user and I disagree with the common YT expert advice on two main points. 1) Tables. 2) Whole column references. (Edit) Tables are too restrictive imo. The benefits are superficial and the tables slow down productivity. Related to that, whole column references are advised against by many professionals, but not for any good reason, I think. When you use whole column references, the need to have a table automatically update ranges in formulas vanishes. the only reasons not to use WCR’s is when using array formulas or when repeating tens of thousands of formulas in the data sheet. Otherwise, R1C1 referencing is very fast and intuitive, and seriously diminishes the need for tables when combined with WCR’s. If your data is so small that the entire data set can be shown on a single page or screen, tables might be fine. Otherwise, the data should be on one sheet, and report outputs on a different sheet. Which eliminates the need for totals rows in a table.
I understand the problem. I love tables and use them for my work and work with others who have a high level of skill in Excel. However, I've run into the same cases you have where tables can't be used due to varying skill levels with Excel users. It is a bit frustrating to deal with, but it's up to me to know my audience and design my Excel files accordingly.
People need to learn to use tables, rather than people who using them not. They are so much better for working with. If you can't use tables you can't use Excel. When working with others on things like budgets I use entry templates that get pulled into tables so I can get the spreadsheet to do the work in the background.
I see your point but tables is do much easier to understand where values are coming from. The downside of tables for me is dynamic arrays don't work too well
Excel was born with a fatal flaw: Each cell contains its own formula. There is no higher level structure. Tables looked like a nice feature, but I haven't studied whether they are really different in that sense. If so, please give them another chance.
I like tables because they allow me to read in new data through Power Query, have formulas run automatically against the new rows and have pivot tables get refreshed immediately without me ever having to worry if I’ve ever set the right range references. Maybe the compromise is to ensure that the user only gets to see Excel ranges as an output, but for internal processing, they can be incredibly useful.
I LOVE tables and use them all the time. But, I agree, don't use tables when sending out templates to a large audience. What I do in this situation is use power query at my end to "get data from folder" to append the data. Even when I build tools for a smaller audience that will use the tool on a regular basis and I can train them, I see eyes glaze over when they see structured references in formulas.
I use tables daily, mine are more informational type tables; with names, dates and adding hours. I love how simple it is to set up and use the filters; with just a click of the mouse can add columns & total lines. I am not by any means an expert as I don't do all the other technical features like charts, etc. I don't have a need for them. There are just some skills I know at my age it is too complicated to learn or that I want to learn since I won't be using them.
I only use tables when I need to create pivot tables or work with Power Query, because as it happened to you, it happened to me that I was all about tables until my boss encountered a problem, and then I had to convert the table to range so he could continue to use it.
Great video. Very informative Jon. I also LOVE tables and I use them as much as possible. I do however agree with your observations and reservations as well.
Definitely understand the issues you bring up. But, I love tables and use them daily. I find that, most of the time, the structured referencing actually makes it easier to explain where something is coming from, instead of trying to find and look at A1:C3. They also provide a way to make references sticky, without having to highlight random cells that may get moved over time. I try to teach my team all the time about how to read and use tables and its references. Over time, the team has come to use them too. There are downsides, though. The biggest downside I see is when you just want to write in cells and don't need autofill. Having autofill do its thing is just antithetical to what we're doing sometimes. The best use for tables, imho, is when you open a csv file and can immediately turn it into a table and be able to sort and filter so easily. Thanks for sharing your thoughts here.
Thanks James! I agree with your points on the descriptive nature of structured references. I didn't put this in the video, but I think it would be great if there was a way to see both A1 and structured refs at the same time. Maybe in a screentip on hover.
Hi Jon, good video on tables. I avoid tables and use dynamic ranges for formulas and pivot tables. Using a dynamic range allows me to add data and works like a table. What do you think?
Hey Steve, that's a great question! It's a tough one to answer. I think each feature/function has its place. The challenge with Excel is that there are so many ways to solve a problem, that it becomes difficult to say that you should always use one feature over the other. There are so many factors in each scenario. But I guess that is what keeps our jobs challenging and fun. I do have another video where I talk about the new PIVOTBY function versus regular pivot tables. And scenarios where you might want to use each. ua-cam.com/video/AFrzpEfdWlA/v-deo.htmlsi=A6Y7QKd7IJ_HtJyZ That's a bit different than dynamic arrays in general. This might be a good topic for a future video. Thanks for bringing it up! 🙂
I love tables and find the formulas much easier to use, especially if you have several sheets. No need to click to a different tab, then select a range, fix the range, then click again for the next part of the formula. It’s very intuitive to just type = tableName[columnName] and so on. And, no need to remember to drag formulas down when adding new data, which results in a lot of errors that are a pain to troubleshoot with obscure ranges that might be part of a huge number of columns on another sheet. I agree that it’s a pain that you can’t just drag formulas, but I don’t need to do that very often. As to sharing files, either I show my colleagues how to use tables, or I create files that don’t require users to use formulas. Nice discussion. Love your videos. 😊
I do understand the dilemma. However, I use and create tables every day. The flexibility to view data the way I want in a table is magical. I ❤ tables.
I use tables only for primary data sets. I belive the problem with the adoption rate for Tables and Structured References that they are considered intermediate concepts BECAUSE THAT IS WHEN THEYCARE TAUGHT. I believe they should be taught from day one in formal classes and in online courses. I actually find formulas more frustrating will cell reference formats than with structured references. It is also helpful to teach people to name things smartly. For example I always name Tables with a single word when possible and call it a Table in its name such as TblData. Also keep column names as short as possible.
I’m with you. Tables for president. ;-)
The name box for tables is on my QAT.
My tables are called fThis and dThat. All in CamelCase, of course.
Great point, Richard! And I should be clear that I will continue to teach and advocate Tables. This video allows me to help explain when to use Tables. I'm also hopeful that Microsoft will make changes to make Tables more approachable and user friendly.
I kinda don’t get this take. It’s a quick video, and the topic may be more complex, but I feel like a properly designed workbook doesn’t encounter these errors.
If you are designing a workbook for thousands of users, one would think it is for them to simply use (input data, filter, view charts etc.) not to redesign to fit their needs. This example would ensure consistency in branding and data types throughout the organization.
Complaining that structured references are different and confuses users is similar to complaining you can’t redesign Quickbooks because you don’t understand the backend code. Beginner users, even intermediate to an extent, just use the software. They know how it works (where to enter stuff and data relationships throughout the software) because they’ve done it for awhile and the GUI is clear, not because they see how the tables and input boxes are labeled in the code.
Tables are an awesome tool that handle dynamic arrays and workbook relationships you cannot realistically match without them. They are the basis for the tools that make excel the powerhouse software that it is (as you mentioned pivot tables, queries, etc).
Plus, it’s not like the references are random, you name the table and headers so =AVERAGE(CreditCards[Balance]) is pretty clear to me. More so than sheet names and cell numbers that I have to go find and understand.
Again, if I am designing a workbook for other (typically multiple) users I design it as such. Everything is labeled and straightforward, input data is validated as needed, the sheets are protected and formulas are hidden.
I like the content, I’m here weekly, but I think I found one I would argue a bit about. LOL
Great points, Ron! Planning, design, and restrictions can really help with the usability of Tables.
I'm not sure I completely agree with the QB analogy. Excel is a different beast. But I get your point.
There are times when I build what I need for my boss using tables and then convert to range for her version. She doesn’t “like” tables, what that means is she doesn’t understand how powerful they are especially when data is dynamic and all those pivots/charts change size regularly. But that’s my job as her analyst and excel expert.
Top tier comment here!
You've hit the nail on the head. For Excel 'professionals' (the people who subscribe to this channel) they are absolutely the way to go. But for the more casual user (including senior management) they're not liked, because they need time and effort invested to learn how to use them, and understand how powerful they are. If you handle data management with Power Query, they're the only show in town, for my money.
YES! I love this, Barbie! 🙌
@iankr very well said! This is a great distinction between 'pros' and casual users. Something I'll talk more about in the future.
Good point. I get what you're saying. But with files that only I use, I always use structured Tables. They are fantastic to use with Power Query. And I'm training my team in using them. For me, they're the way to go!
Awesome! I'm happy to hear that you are teaching them to your team, Ian! 🙌
I learned to used the structured table references, it's amazing. But collecting files from a large user base, I prefer to have the same headings, and various records, then use Power Query and / or Power Pivot to massage the data into a final format.
I love tables and quickly got used to structured references. I'm glad I don't have to share my files with hundreds. Great video as always.
I fell in love with tables since the moment I learned about them, the structured referencing has made my life easier, because, compared explicit cell references it is easier to understand the formulas when you look at them, and I started changing the way I use Excel to all-tables, and I am teaching my co-workers the benefits of using them, and how to use them in their daily work around Excel. Thank you for the tips, they are really helpful.
I'm happy to hear you are teaching Tables to your coworkers. 🙌 I will continue to teach and advocate for Excel Tables. As I said at 6:03, I 💚 Tables!
Exactly this. Now my co-workers get admonished by me, when I catch them NOT using tables. Of course, they also make fun of me for being so in love with everything Excel. Guess what? Don't care. WORK SMARTER, NOT HARDER!! (but I completely understand about knowing your audience, too)
When I am teaching someone Excel, on of the first things I do is teach them tables. Once they understand them and how to work with them, it makes everything else in everything un Excel so much easier.
My view is your work with Excel you learn it, constantly. It's endless universe and it's fun, for me at least. Bring the lower Excel Knowledge level employees up, not other-way around. Besides, there is always an MVP with a solution to any of your problem.
Accommodating ignorant is not productive.
I completely agree with bringing the knowledge of others up. That might be obvious as I've dedicated my life to teaching Excel. 😂
And also about your view on Excel as an endless universe of fun. I love that!
But this isn't about accommodating the ignorant. As I mention in the video, Excel is not a primary skill for many, if not most, users. And I'd argue that I've made the mistake of being ignorant in thinking that my users and coworkers should learn and remember the many nuances of structured references, even if they only use Excel a few times a month.
I will still continue to teach and advocate for Tables. As I mention at the end of the video, I'm also hoping Microsoft can make some changes to make Tables more user friendly and accessible.
As an Excel app developer I used to use cell refs but I'm now heavily into tables and it's structural nomenclature, especially within the confines of VBA. In developing an app things change often and changes can be cumbersome when you move a column for instance. My apps are locked and limit the user to customize anything since it was developed for user input and not user customization. Once finalized, it's locked. Tables and all it's features make VBA a little easier to update, edit, and write in the aspect of data manipulation and calculations. I'm now a Table man and will continue to expand my knowledge about them. Moving forward not backward. Thanks for the great video.
I'm very ambiguous. On the one hand, I completely agree with you and fully understand that tables can confuse some users, but on the other hand, you can't continue to let the lowest level decide how your work should be done.
So I try to explain what tables are and what their benefits are.
But when dealing with a large number of users, I tend to let the "lowest common denominator" determine how my Excel sheet looks.
I almost always implement structured tables in my Excel applications-- they have many more benefits than gotchas when working with more than two or three rows or columns. It would be helpful, though, if the F4 key worked in structured formulae the way it does outside tables-- adding '$' to lock cell references. If the dollar sign convention cannot be used in a structured table (why not?), the F4 key should at least add the extra brackets as shown in your video. Thanks for highlighting these issues.
I agree with your question on the F4 key. I actually created an add-in that allows you to use F4 to toggle the absolute reference notation for structured references. Here is a link to the video that explains more if you want to check it out. ua-cam.com/video/qt-yfnZGXFg/v-deo.html
The video is over 10 years old, and this has been an issue for a long time. Still praying it will be fixed someday. 🙂
Thank you for bringing this topic into the light. I'm an Excel trainer, solution builder and 30 year long user. I ALWAYS include table in my classes even to beginners. But I have to admit that you've revealed to me one of the reasons why so few seasoned users use them. I think that most of the time, it is because they have not understood yet that it is the only object in Excel that has the power to dynamically change its size, making any reference to it so much more robust than regular references. But what you've pointed out here is important to be aware of. Thank you for presenting not only the beauty of Excel but also some grey area (for some people at least). Love your XL and PQ videos. Keep on you're great job !
Thanks so much, Lilou! I'm happy to hear you are teaching Tables. I will continue to do the same. They are an amazing feature even though they have some caveats.
And also, thank you for the nice feedback. I appreciate your support. 🙏
I have been using excel for 25 years and in my line of work, did not need tables. I have used tables on occasion and did find structured referencing very confusing and it made me want to use tables even less! It is great to learn of that toggle so I may start using them more! Thanks for the info!!! 🙂
I love tables. Teach them to all my students and explain to them what structured references are but did not realize till now that it could be confusing to other users within their companies. Thanks for the clarification. Will make sure to use this info in future.
Great video! I've found that so many people that have trouble with tables are the seasoned veterans who never learned to use them. When I show tables and pivot tables to new users I find most never need the formulas as much and don't have to learn the old way anyway 😊.
Thanks for the tip about stopping "use table names in formulas" , because for me it was confusion
I stop using Tables too after I found out calculation processing time with Tables is much slower than normal range when database is large. I work with >50K lines.
Microsoft has made some improvements to this over the years. Although there are still performance limitations, it might be worth giving them a shot again if you haven't in a while.
I think tables are well worth learning. Going back might not be an option in the future
I quite like tables given that they auto expand and I don't need to put an anchor row or column in to avoid messing up range references when I want to add a row or column to a non-table data set. However, I have been tricked by copying table totals across a table when I've added new columns to the table. If you copy the table total formula over by using the fill handle, it gives you a different result (correct result) than if you copy and paste it over (which results in the total of the column you copied the total from). It's also problematic when you start with a table and then need to convert the table back into a regular data set (say if you need to have a column header that contains a formula - which you can't do in a table). Any references to the table that you did before convert to ranges, but if you find you need to convert back to a table, those structured references are gone. It doesn't happen to me often, but often enough, when I have trouble building something and find an issue using tables, convert it to range, find a different set of problems and have to convert back.
Great points! I didn't go into details on those issues in this video, but I'll add that to the list for future videos. These nuances and behaviors are really important for users to know and learn.
I love your postings!! BUT .... I use Excel for Mac and many times my Excel doesn't obey your suggestions. Is it possible for you to somehow accommodate us? Thanks. And again, I LOVE YOUR POSTINGS!!!
Hey Bruno, Thanks for the vote on Excel for Mac. We'll try to incorporate more training on the Mac version in the future. I see how we could improve my mentioning keyboard shortcuts on both Win and Mac, and also show/mention when screens or UI might look different.
Thanks again for bringing this to our attention. I appreciate your feedback and support! 🙌
I use tables all the time because I use PQ to clean my data. I don't mind the structured reference language at all (locking ranges is a bit clunky). The issue I have with tables are related to how they don't work with features like spilled ranges or how I can't moving a group of sheets to another workbook when a sheet has a table. Mr. Excel gave an interview where he discussed his issues with tables on Ann K. Emery's YT channel that was useful (9.40 minutes in).
Thanks for sharing! The issue with moving/copying multiple sheets can be frustrating. I've come up with a few workarounds over the years with VBA, but nothing solid. The main challenge is that any formulas that link between sheets will break. It gets messy...
Hi John, I watch a lot of Excel video's made by MVP's, but they always focus on the techniques. This one focus on the user. Please keep doing so! Thanks. Other examples of business problems: Some cells require text input (CELL, INFO), terrible if you share this internationally..... So as a developer there are many issues you might not be aware of...
Harsh take: Tables have been around 17 years at this point. It's time people start learning new things. How will they ever pick up on the new array-centric formulas that provide so much power? Not that tables make that easy, but thinking slightly more abstractly is of tremendous benefit.
My problem with table use is that they are not properly propagated-by Microsoft-to be used everywhere. In particular, Conditional Formatting. At least the "Refers to" references update automatically as the table grows (hit or mostly miss if shrinking the table). But God forbid trying to use structured references in your formulaic conditional formatting. Tables are great as a source of data, but as processing vehicles are problematic. And forget loading tables with array formula results directly.
I fear tables have reached the end of their development cycle as far as Microsoft is concerned. Which is a shame, because they are such powerful structures, but they could be improved and should be. :::Le Sigh:::
I use table purely because of formatting or data organisation and for the fact that if I need to delete or include rows/columns, it won’t mess the whole worksheet, it gives me instant subtotals but i have to convert it back to the range because my formulas don’t normally work inside tables because the numbers change formatting to general. So the moment I convert back to range, I can see the display error for each cell, the little left green corner and I have to convert all to numbers so the formula work. I don’t understand why this happens, it must be formatting. I only convert to table, so I don’t miss blank rows and so I can get to power query easily. if I convert table back to data range, I don’t normally go back table once again. I don’t have the time to investigate it further, hence why I watch UA-cam channels in my spare time! 😂 I agree with you, It is so annoying because it is something that puts people off it too. I haven’t given up yet because I like the look of it, tidy! and it’s quick to navigate it , and the headings replace the excel column reference when you are right on the bottom of the table. my issue is indeed the formulas but I will change the settings like you have shown! That would improve a bit. ! I so worry that the formulas could NOT be telling me the truth, hence why I double double check it all the time because I will BE mortified if the figures I passed on are wrong, I’m stock analyst for a global market company and this could have a major impact on future costs, P&L or even over ordering, the lot!!!😮. Can’t even think about it!
Hi Jon, I know a trick if you write formulas and you DO NOT want the structured reference: just select more than 1 cells and write the formula, then press Enter or Ctrl enter (my favorite key combination, filling the selection with the formula, NOT changing the format). Selecting more than 1 cell while writing the formula always gives you - and all other users of your sheet - the A1 notation, without changing any settings. If you just need the formula in one cel, delete the formula in the other cell.
When I give Excel trainings, the Ctrl Enter is my "power trick", unfortunately it doesn't work in tables because I then don't get the structured language although i personally prefere the structured language! I am so used to first select the cells, then write the formula for the active cell and then press Ctrl Enter... in tables I don't do it, I don't need it because filling one cell in an empty column duplicates the formula (in Structured Reference Style) to the whole column.
I use excel since version 1 and I hate tables. I must admit I use Excel primarily for engineering and not for financial analysis. In my use cases tables are useless. The average weight of a product varying from 1 to 10000 kilo's doesn't make much sense :-( I'd love a proper engineering version of Excel...At the end of the day I still prefer Google sheets because it's very east to program a custom function in plain old javascript...
Tables in Excel have advantages when it comes to accessibility . A non-sighted user trying to navigate a huge Excel workbook will have a much better chance of doing so if contains well-named tables, rather than have them trying to work out where data might start and finish, particularly if the workbook contains many worksheets.
Great explanation. Thank you Jon.
Thanks Ivan! 🙂
i’m a very advanced Excel user and I disagree with the common YT expert advice on two main points.
1) Tables.
2) Whole column references.
(Edit) Tables are too restrictive imo.
The benefits are superficial and the tables slow down productivity.
Related to that, whole column references are advised against by many professionals, but not for any good reason, I think.
When you use whole column references, the need to have a table automatically update ranges in formulas vanishes.
the only reasons not to use WCR’s is when using array formulas or when repeating tens of thousands of formulas in the data sheet.
Otherwise, R1C1 referencing is very fast and intuitive, and seriously diminishes the need for tables when combined with WCR’s.
If your data is so small that the entire data set can be shown on a single page or screen, tables might be fine.
Otherwise, the data should be on one sheet, and report outputs on a different sheet. Which eliminates the need for totals rows in a table.
I understand the problem. I love tables and use them for my work and work with others who have a high level of skill in Excel. However, I've run into the same cases you have where tables can't be used due to varying skill levels with Excel users. It is a bit frustrating to deal with, but it's up to me to know my audience and design my Excel files accordingly.
I like working with tables with the exception of dynamic formulas, which don't work with tables :-(.
Great recommendation
People need to learn to use tables, rather than people who using them not. They are so much better for working with. If you can't use tables you can't use Excel. When working with others on things like budgets I use entry templates that get pulled into tables so I can get the spreadsheet to do the work in the background.
I see your point but tables is do much easier to understand where values are coming from. The downside of tables for me is dynamic arrays don't work too well
Excel was born with a fatal flaw: Each cell contains its own formula. There is no higher level structure. Tables looked like a nice feature, but I haven't studied whether they are really different in that sense. If so, please give them another chance.
I like tables because they allow me to read in new data through Power Query, have formulas run automatically against the new rows and have pivot tables get refreshed immediately without me ever having to worry if I’ve ever set the right range references. Maybe the compromise is to ensure that the user only gets to see Excel ranges as an output, but for internal processing, they can be incredibly useful.
I LOVE tables and use them all the time. But, I agree, don't use tables when sending out templates to a large audience. What I do in this situation is use power query at my end to "get data from folder" to append the data.
Even when I build tools for a smaller audience that will use the tool on a regular basis and I can train them, I see eyes glaze over when they see structured references in formulas.
My biggest frustration with tables is getting the REF error in a formula referring to a closed table. Any workaround for this?
I use tables daily, mine are more informational type tables; with names, dates and adding hours. I love how simple it is to set up and use the filters; with just a click of the mouse can add columns & total lines.
I am not by any means an expert as I don't do all the other technical features like charts, etc. I don't have a need for them.
There are just some skills I know at my age it is too complicated to learn or that I want to learn since I won't be using them.
I always covert my data to tables where I can, but I see your point with some of the issues encountered. thanks Paul
Thanks Paul!
Spot On. Despite its advantages, it's irritating many a times. I am obsessed with single cell formulas which spills and life is hell if its a table.
What about creating a function in the workbook, to disable the the naming convention at run time, for a given worksheet?? 🤔
I only use tables when I need to create pivot tables or work with Power Query, because as it happened to you, it happened to me that I was all about tables until my boss encountered a problem, and then I had to convert the table to range so he could continue to use it.
I find it doesn't matter if I use tables or not. The managers are using the report just the same as if they were printed-out on paper. 😞
More folk should use MS Access Database instead of Excel!
Great video. Very informative Jon. I also LOVE tables and I use them as much as possible. I do however agree with your observations and reservations as well.
Thanks, Renier! 😀
Sir kindly make a video
Sendkeys for onother program data entry please
Definitely understand the issues you bring up. But, I love tables and use them daily. I find that, most of the time, the structured referencing actually makes it easier to explain where something is coming from, instead of trying to find and look at A1:C3. They also provide a way to make references sticky, without having to highlight random cells that may get moved over time. I try to teach my team all the time about how to read and use tables and its references. Over time, the team has come to use them too. There are downsides, though. The biggest downside I see is when you just want to write in cells and don't need autofill. Having autofill do its thing is just antithetical to what we're doing sometimes. The best use for tables, imho, is when you open a csv file and can immediately turn it into a table and be able to sort and filter so easily. Thanks for sharing your thoughts here.
Thanks James! I agree with your points on the descriptive nature of structured references. I didn't put this in the video, but I think it would be great if there was a way to see both A1 and structured refs at the same time. Maybe in a screentip on hover.
I will watch any video on Excel tables - even if I disagree with it! But seriously although I love tables you bring up some great points.
Thanks Don! I love Tables too. If I saw this video 12 years ago, I would have completely disagreed. 😂
Thanks for explaining something that I've seen but never understood. Now I know.
Glad it helped, Mickey! 😀
Hi Jon, good video on tables. I avoid tables and use dynamic ranges for formulas and pivot tables. Using a dynamic range allows me to add data and works like a table. What do you think?
Hey Steve, that's a great question! It's a tough one to answer. I think each feature/function has its place.
The challenge with Excel is that there are so many ways to solve a problem, that it becomes difficult to say that you should always use one feature over the other.
There are so many factors in each scenario. But I guess that is what keeps our jobs challenging and fun.
I do have another video where I talk about the new PIVOTBY function versus regular pivot tables. And scenarios where you might want to use each. ua-cam.com/video/AFrzpEfdWlA/v-deo.htmlsi=A6Y7QKd7IJ_HtJyZ
That's a bit different than dynamic arrays in general. This might be a good topic for a future video.
Thanks for bringing it up! 🙂
I love tables. They make things so much easier.
I am using tables. But filtering large tables is a nightmare!
I love tables and find the formulas much easier to use, especially if you have several sheets. No need to click to a different tab, then select a range, fix the range, then click again for the next part of the formula. It’s very intuitive to just type = tableName[columnName] and so on. And, no need to remember to drag formulas down when adding new data, which results in a lot of errors that are a pain to troubleshoot with obscure ranges that might be part of a huge number of columns on another sheet.
I agree that it’s a pain that you can’t just drag formulas, but I don’t need to do that very often. As to sharing files, either I show my colleagues how to use tables, or I create files that don’t require users to use formulas. Nice discussion. Love your videos. 😊
very good, congratulations
What is a good starting point to learn the basics about tables?
Hi Judy! 😊 A good starting point would be my free Beginners Guide video on Excel Tables.ua-cam.com/video/fJnJTff4Na8/v-deo.html&
One of your best ever videos and comments!
Thanks Kevin! 🙏