I've Stopped Recommending This Excel Feature

In this video we look at an Excel feature that should be more popular. Unfortunately, the advanced nature and unusual behavior of structured references make Excel Tables difficult for many users.
I explain the reason that Table formulas can cause so much confusion and inconsistency with formulas. And I share best practices on when to use Tables versus when to avoid them. DOWNLOAD THE WORKBOOK ► www.excelcampus.com/tables/st...
🔗LINKS
🔎 Get free weekly Excel tips: www.excelcampus.com/newsletter/
💡 Free 60-minute Excel training session: www.excelcampus.com/blueprint
📖 Join Our Comprehensive Excel Training Program: www.excelcampus.com/elevate
00:00 Introduction
01:15 Structured Referencing
02:45 Corporate Budgeting Example
06:03 When to use Tables
06:47 Possible Solutions

[@user-dh2fd9qu1j]

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.

[@ExcelCampus]

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!

[@andreajava8402]

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)

[@karencummings6083]

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.

[@richardhay645]

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.

[@GeertDelmulle]

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.

[@ExcelCampus]

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.

[@michaelt312]

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.

[@iankr]

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!

[@ExcelCampus]

Awesome! I'm happy to hear that you are teaching them to your team, Ian! 🙌

[@Barbie.7884]

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.

[@ronestes667]

Top tier comment here!

[@iankr]

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.

[@ExcelCampus]

YES! I love this, Barbie! 🙌

[@ExcelCampus]

@iankr very well said! This is a great distinction between 'pros' and casual users. Something I'll talk more about in the future.

[@LilouBERARD]

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 !

[@ExcelCampus]

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. 🙏

[@alphamaniac9411]

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.

[@sherri2538]

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!!! 🙂

[@Tommie_Rogers]

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.

[@HenricAndersen]

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.

[@radiali482]

Thanks for the tip about stopping "use table names in formulas" , because for me it was confusion

[@sloanzoner]

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.

[@ExcelCampus]

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. kzbin.info/www/bejne/p6WQqpmkj6yLfMk The video is over 10 years old, and this has been an issue for a long time. Still praying it will be fixed someday. 🙂

[@PaulEBrownbill]

I always covert my data to tables where I can, but I see your point with some of the issues encountered. thanks Paul

[@ExcelCampus]

Thanks Paul!

[@barttitulaerexcelbart9400]

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...

[@h.esther9400]

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. 😊

[@robertwoodman738]

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.

[@sridharramadurai1206]

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.

[@mickeytee6533]

Thanks for explaining something that I've seen but never understood. Now I know.

[@ExcelCampus]

Glad it helped, Mickey! 😀

[@ziggle314]

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).

[@ExcelCampus]

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...

[@ronestes667]

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

[@ExcelCampus]

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.

[@IvanCortinas_ES]

Great explanation. Thank you Jon.

[@ExcelCampus]

Thanks Ivan! 🙂

[@woodpigeon01]

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.

[@marlenemccullough1567]

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.

[@sabinezinkpielage1742]

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.

[@geekatari4391]

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.

[@RenierWessels]

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.

[@ExcelCampus]

Thanks, Renier! 😀

[@BLTT06525]

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!!!

[@ExcelCampus]

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! 🙌

[@jd123jd122]

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.

[@GSC-zz8zt]

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.

[@ExcelCampus]

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.

[@ianrhile]

I love tables. They make things so much easier.

[@mochehrzad9077]

Great recommendation

[@kevinweaver6999]

One of your best ever videos and comments!

[@ExcelCampus]

Thanks Kevin! 🙏

[@dondgc2298]

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.

[@ExcelCampus]

Thanks Don! I love Tables too. If I saw this video 12 years ago, I would have completely disagreed. 😂

[@WillieWebCam]

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?

[@ExcelCampus]

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. kzbin.info/www/bejne/d3fVq6N7m8mKoqMsi=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! 🙂

[@jmklei0]

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.

[@ExcelCampus]

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.

[@josealvesferreira1683]

very good, congratulations

[@cruzinsweetsntreats]

What about creating a function in the workbook, to disable the the naming convention at run time, for a given worksheet?? 🤔

[@shaharyarahmed6124]

Sir kindly make a video Sendkeys for onother program data entry please

[@user-uk8us7xd8h]

I think tables are well worth learning. Going back might not be an option in the future

[@sircorn4248]

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.

[@ExcelCampus]

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.

[@rnorthern6]

My biggest frustration with tables is getting the REF error in a formula referring to a closed table. Any workaround for this?

[@georgebli9120]

I am using tables. But filtering large tables is a nightmare!

[@rexbk]

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.

[@ExcelCampus]

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.

[@mmaimmortals]

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.

[@jimbo9247]

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. 😞

[@jackcurl2005]

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:::

[@LinkeLifestyleAsWeSeeIt]

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.

[@judywilder7883]

What is a good starting point to learn the basics about tables?

[@ExcelCampus]

Hi Judy! 😊 A good starting point would be my free Beginners Guide video on Excel Tables.kzbin.info/www/bejne/nHvRe4ecm5mBl5o&

[@karolinab9749]

I like working with tables with the exception of dynamic formulas, which don't work with tables :-(.

[@montebont]

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...

[@Digital-Dan]

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.

[@PerthSoftware]

More folk should use MS Access Database instead of Excel!