❓What Excel feature do you use daily that others might be missing out on? Master Excel with 20% off all my courses until August 15, 2024: bit.ly/trick24courses
Excellent romp through the capabilities of tables. But what I must comment on is the sheer quality of the video presentation. The content, the text, the pace, the examples, the fluent delivery, the highlighting, all perfect. Awesome, using the word in its proper sense. Thank you 🙂
In starting a new job or whenever I open Excel for the very first time, the first thing I do is customize the Quick Access Toolbar to include: freezing pains, trace dependents and remove arrows, and, the best tool ever!, the camera tool (and other tools as well). The camera tool literally takes a picture of whatever object or data I want to appear somewhere else in the workbook. Say for example you're building a dashboard and you want to put a small chart or little table in an area that doesn't conform to the columns and rows where it needs to go. Putting a dynamic chart or table there as a picture, that's automatically updated whenever the data or calculation changes, is very handy.
I have been using tables for years and can now hardly enter a single value in Excel without turning it into a table 🙂 I think my favourite benefit is the ability to reference cells using the structured formulas but also find the filtering very useful (right-click any cell and use 'Filter by Selected Cell's value' is one I use all the time)
I can relate with you regarding turning everything into a table. I turned so many shared sheets at my work into tables... it just makes the data so much better.
Great video as always Mynda :) Couple of things that I think could be also worth mentioning with tables: Every time I create the first table in new workbook, I always duplicate the Table Style and use that instead. The two changes I always make are to remove the internal horizontal borders (not required because of the banded rows) and add internal vertical borders (so the columns have borders). Looks much better I think! And it makes the data easier to read. The other great thing about table ranges that no one seems to mention is that if you use Conditional Formatting with formulas, it is of course dynamic. If you don't use a table, as I am sure you already know, every time you add a new row/s or copy and paste rows, it duplicates every CF formula rule in that range. This can make the CF list really messy after a while, and at worst, after a long time it can slow down the workbook.
For Tables, I use keyboard shortcuts CTRL and full stop/period to cycle through the four corners of the Table (good for getting to the bottom of a large Table). Also CTRL and spacebar to select the Table column you are in (excluding heading - repeat to include heading) - great shortcut for functions such as SUM and SUMIFS
Great look at tables and their value! I didn't learn about tables until I started studying Power Query a few years ago. Once I realized how useful they are, I started incorporating them into my workflows.
Tables are a complete game-changer in being able to safely and accurately update a table of data, and the pivots and graphs that feed off it. So great!
Some problems with Excel tables that I face daily: 1. When you enter data manually in several rows in a column, if it is not formula based, it will take ages compared to a regular column to update; that is, if it doesn't crash. For most such transformations, I find myself converting a table to a normal range 2. I think tables take more space & makes the files slower to open or operate 3. If I refer to cells in a table from outside the table, the references do not change dynamically if I drag the formula. For example, pulling monthly summary of costs or running subtotals for columns above an Excel table. I have to manually change the reference for each column from period 1 to period 12, if they are in separate columns in a table. To summarise, Excel tables are not a panacea for most regular ad hoc work. It is only useful if you regularly work on structured big data sets that's are not going to change for some time
@mrzorrombo some solutions to your daily Table challenges: 1. I haven't experienced performance issues with Tables since Excel 2013. In Excel 2016 these limitations were fixed. 2. As above. 3. Left-clicking and dragging a formula containing structured references will treat them as relative, copying and pasting the formulas treats the references as absolute.
@@MyOnlineTrainingHub@mrzorrombo and in case you need an absolute reference when dragging across (e.g. if you have a mix of absolute and relative), you can reference as [[Column1]:[Column1]] to set an absolute reference to all of Column1, or [@[Column1]:[Column1]] for an absolute reference to the current row in Column1.
I've been using tables for years and can't live with out them. I literally use them many times a day. The default behaviour of copying formulae down when adding rows is amazing. Structured referencing is also a boon for any non-trivial work. Lookups and aggregation that refer to a table (or its component columns) as named objects saves significant time and prevents the possibility ranging errors when expanding the table. Also, tables are fantastic as a data source for pivot tables-hugely useful.
Love this content! A few months back I was blown away when looking at a coworkers code which used tables to easily manipulate data in VBA, and will be looking at more options to use them in my work go forward
Great to find out there's new stuff for me to learn. I do use tables a lot and I love that they are dynamic and doesn't need to be refreshed, but I wasn't aware of the slicers. I will definitely apply slicers in a couple of my sheets from this day forward. Thank you!
Thank you Mynda as always, you always suprise me in your videos, which ever one I am watching I always find a nugget of information that I didn't know, many thanks😁
Wonderful, Mynda. As you say, Tables are a must-have when working with large data that is regularly updated via Power Query. My only quibble with Microsoft is that there isn't a Table Name field in the + T dialog! Something for MS to consider?
I use Tables for almost EVERYTHING! My favorite feature is that I reference column header values in formulas and VBA. And when (not if, right?) those names change the formulas update on their own.
Pick just one of my favorite Table features? That's really REALLY hard, Mynda! I would have to say I enjoy table references the most! Who wants to try to explain =IF(AA5*AQ5>AK5,etc....) to a peer (or yourself, when you look back at an old sheet) when it could read =IF([price]*[unit]>[forecast]) ?
I love tables and work with them on a daily basis. I love using slicers and as dynamic ranges for pivot table. My biggest caviat is not being able to protect cells in a dynamic table - unless you have a workaround....
@@MyOnlineTrainingHub Thank you! Unfortunatly, VBA sets other issues and will not work on the web version. Maybe you'll be ablevyo work your magic with Microsoft...
Ah, yes it's no use on the web. We MVPs have been asking Microsoft for this for years...it's not looking like they'll do anything about it anytime soon 😢
Yes agreed , tables are great , I use them mainly due to auto update in pivot tables without changing data source to include new data and for fixed headings without need for freeze
Hi Mynda One more good subject, and you present it so well. Nothing much to add, only that I like to use a small summary above the table, using Aggregate or sometimes Subtotal.
Thanks for the great content that you produce Mynda; I’ve learned a lot from you. My table tips are: I use table column references in calculations in other tables, and I was originally frustrated by not being able to ‘drag out’ those calculations, as Excel changed the column references accordingly. The way to freeze those references is to write in the form table[[col1]:[col1]] I use tables to hold lookup values for data validation settings. Create a table, then create a named range of the same cells in the table; use that range for the data validation setting. As users need a new valid input, they can append to the table and the range grows to match.
Thanks for sharing your tips! Another way to have table references locked is to copy and paste the formulas instead of left clicking and dragging them to copy.
@@MyOnlineTrainingHubTrue - my calculations also reference appropriate non-table cells etc, so editing those formulae after copy and paste would be tedious.
Frankly, it's hard to imagine working without tables now. They save time, reduce errors, simplify formula writing (after investing a little time in understanding structured formulas), and more. Thanks for beating the drum a bit more on this unbelievably powerful tool.
First, YOU ARE the best teacher to follow. second, I would love to purchase your courses. But I cannot afford all the Excels you have. Which (few) would you recommend for going into Data Analysis? Ive been watching your basic/beginner/ tutorials so I can feel comfortable navigating excel, but now I wanna learn deeper for the Data Analysis (but like i mentioned...i cannot afford All of them you have listed) so which one(s), would be suitable for liftoff. lol thank you for what you do. : ))
RU-vid showed me your channel as I often watch other excel tutorials. I learned a LOT. Thank you! I am now a subscriber and look forward to more of your content.
I use tables almost exclusively; while not always an option, I especially prefer them to non-tables when working with dynamic arrays. One of my favorite tricks is to put the column numbers in the row above the table with the sequence function to use with CHOOSECOLS formulas (=SEQUENCE(,COUNTA(TableName[#Headers]))); that way the count will automatically be updated as more columns are added. And the little black arrow (down or right) is very helpful to select only something within the table; it makes deleting rows much easier. Yeah, I love tables and I encourage people to use them whenever I can.
oh well, I thought I might finally be able to say that I knew everything in a video of yours. But of course I did learning something new, and that was at 7:35 "Namebox dropdown" can navigate to tables and select them!
I work with a lot of CSV files and the first thing I do when I pull them into Excel to take a quick look at them, I press Ctrl-T. It's now muscle memory.
The primary reason that I stay away from tables is that they seem to trip up anyone I send them to. Most people take what I send them and think they can hack around trying to get different reports and data. With ranges, they can usually do that without too many negative consequences. Enter tables into the equation, and suddenly they've burned everything to the ground. Same with pivot tables.
Very strange statement, indeed. Don’t have that experience myself: if anything: tables are easier to understand than assembler -er- I mean: ranges. I do know of one person who thinks like you and sticks to “just cells in the grid”. This way of thinking is a major road block to getting better at Excel. Where I work, I advise people to use Excel to the best of their abilities (both theirs and Excel’s) - if they open their minds to it. Those who do shine and become little Excel gurus themselves.
@@GeertDelmulle I guess we have different experiences. You also seem to have made quite a few assumptions. I use tables when I can and when it makes sense. I am known as the company guru in a national company with hundreds of employees - having written dozens of VBA scripts for other people and departments alone - and hosting several “office hours” to help other people understand Excel and find best practices, including the use of tables. You can lead a horse to water, you can’t make them drink.
And that's why we lock down the workbook to limit the changes others make as well as keeping a copy away from everybody in case they think to hack the wirkbook.
Thanks. Loved the video. When I populate a table manually it performs everything perfect. However, when I populate a table via formulas, it doesn’t expand. How can I overcome this?
What I like is that when I add a new row formulas are copied down as well. What I don’t like is that this doesn’t work anymore when protecting the formulas. I hope they are going to fix this issue in next release.
I download data umpteen times a day. I almost always CTR-T as it's a safe way to sort and filter and very often insert a pivot to get at some quick summaries
Small detail, Ctrl+T only works in the UK version. In the other versions, Ctrl+T stands for TOGGLE. Excel then switches between normal view and formula view. To quickly create a table, that is Ctrl + L
Thanks for sharing. CTRL+T works in all English language versions of Excel. Interesting fact: CTRL+L is the old shortcut for Lists, which were replaced by Tables in Excel 2007, hence why it still works. 😁
EVERYTIME THE ANSWER IS TABLES An Angel gets their wings. Also, I will be using filters on my table tomorrow. Thank you so so much for the tip. I’m always clicking filters.
Nothing wrong with wearing that green Microsoft T-shirt, but with all due respect to you and your family, may I say you look beautiful and classy on this video? And content is very useful and to-the-point as usual. Cheers! 🤓
I have a balance sheet table that I have created using powerquery and power pivot. My problem is how I can create a total liabilities and equity row which is a sum of liabilities and equity subtotals. This is because there is no general ledger number for such a total. I hear it’s about transition context, but I don’t have that skill. Can you please do an example?
Hi great content. I like tables too. Is there a way to have multiple headers? Sometimes I work with time series data. The header is the channel name, the next row is the measuring unit followed by a row with a short description. In the 4th row starts the data. Is there a way to work with tables in this structure? Thanks
😱nooo! Tables should never have more than one row of column headers. See this video that explains why and what you should do instead: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CNlw1-Vh4cE.html
Hi, have a off topic question than this vedio. I would like to lock a table formate excel sheet. But it has formula columns thus when filled with data, it will not drag formula rows from previous. Any idea or way to lock this kind of table format sheet ? Thanks in advance
I often have data sheets with multiple header rows -- a Main Header row and a Sub-Header row. How can you change/select which row becomes the table's header with filter options ?
Tables are for your source data. Your source data should never have headers over multiple rows. Sounds like one of those headers should be in a column of its own. See this tutorial on tabular data which will help you avoid issues you'll have as a result of nested header rows: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CNlw1-Vh4cE.html
Agree with everything you highlighted. The only issue I find with large tables is - if you have formulae in them and you are posting data to them, they can be very slow in recalculating and it doesn't seem possible to turn off the auto calculation for tables. Any comments on this welcome.
Agree. Tables used to be quite slow if they were large or had a lot of columns containing formulas, but since 2016 they have fixed these issues. That said, if you have a lot of IF formulas or lookup formulas, Excel can get slow, but that's typically true whether the formulas are in a table or not.
No: Ctrl + A. Yes: Ctrl + *. No: SUM aboveTable (filter). Yes: SUBTOTAL. No: manual entry of the Table column. Yes: Down black arrow over header of Table column and mouse click. No: left click on Table Style. Yes: Right click on the Table Style and Apply and Clear Formatting (style will be fully applied to the header).
Yes, all this is very well known. And should be! Of course, once you venture into data models, tables become essential. Here’s a trick if you don’t want calculated columns auto fill: uncheck the appropriate box in the… autocorrect options. BTW, Mynda, my tables don’t auto grow anymore, what setting should I correct? (for the life of me, I can’t find what it is…)
U always explains so nicely❤. I have a query and it would be nice if u can help. While updating table using macro(data pasted from another file to table), it slows down and sometimes stops responding and takes around 5 minutes to complete the job( copy paste the data into table).
A couple of things you can try: 1) Start the code with Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual and end it with True and xlCalculationAutomatic 2) Use resize rather than letting the table autoexpand as you copy. You can precalculate the number of rows being copied in and resize it before the copy or do the initial copy one row below the table (leave a blank row) and then delete the blank row and resize the table.
To this day, one of my favorite things to use tables for are for the dynamic dropdowns. Where you use =INDIRECT(TableName[ColumnName]) in the list and you have a dropdown you can update by just adding a row in your dropdown table.
Yes, love tables for drop down lists. Just be careful using INDIRECT if you plan to use the drop down multiple times e.g. in a column as this function is volatile and can slow down your file. Better to define a name for the table column and then use that defined name as your drop down list source.
I would like to add that when adding new data, the total row needs to be switched off. By definition it's the end of a table so if it's there, the new data won't become part of the table. Just switch it back on once you're done. It will remember which cells that which function added to them. IMO there are two disadvantages when using tables: you can't lock columns with F4. That's a problem for users of older versions of Excel when FI using SUMIFS. Typing [[Column Name]:[Column Name]] is a bit annoying. The second one is that when doing a lookup in a separate file with a table, you get a #REF! error when the file is closed. People tend to forget that they need to select a whole column e.g. A:A if they want to avoid that.
What's the best way to do a 'grep' in Excel? Here's my scenario, I load raw data through Power Query (hit refresh to get the latest raw data), but there's quite a few 'rows' of content I immediately want to filter out. Is this a step I should do before Excel, or does Power Query have a way to exclude rows containing content that matches a list of keywords/phrases so it excludes them from being loaded in to Excel?
I've used some of these features but I found you look at data And try go back to the original sheet it's forgotten it cannot go back to the full sheet anymore so I copy and paste to another sheet if it goes wrong I still have the original
I love tables once I learned about them. I have two little gripes with tables. One is the fact you cannot use F4 to lock table cell references, come on Microsoft I am sure you can add it. The other is when you try to search an instruction, feature, or formula for an Excel table, you get information about an array of range of cells because people consider data in a tabular format as a table. Furthermore, I often use multiple tables in my spreadsheets, so I often name my variables by [type]_[name], so for example "Table_Sales_Data". This way when I am writing a formula, I can start type "Table" and the intelligent selector will show me a list of all my tables and I can select the right one without having to remember the exact name.
I like to distinguish the named ranges within a table, as well. So, if the Table_Sales_Data has a column called Regions and I want to use that for whatever reason, I'l select the data in the column and name the range List_Regions.
Thanks for your support! The challenge with example data is getting your hands on it. The easiest data to come by is sales data. If you have some inventory management data I can use, please email it to me: website @MyOnlineTrainingHub.com
Because I don't know what I'm looking for exactly. Inventory management isn't my area of expertise. I've asked ChatGPT for fake data for areas I'm familiar with and it's often less than useful, not to mention very small datasets.
@@MyOnlineTrainingHub I work with a lot of inventory and recipe data but it all belongs to a large bevearge company. I am trying to find the time to make it generic so it could be used as samples. It will take some time.
@@MyOnlineTrainingHub Fair enough. I hope the OP can send you some useful data. Personally, all the sales stuff goes over my head, so I get where you're coming from. (My expertise is in text processing.)
I do like table and structured referencing even if I means resorting to index, , but when will they handle spilled arrays or for that matter outline subtotal .
They'll never handle spilled arrays. The Outline/Subtotal tool was pre PivotTables. The new solution is to create these outlined/subtotaled view of your data with PivotTables.
Agree can’t get by without tables but I don’t use the TOTALS ROW for two reasons: 1. it’s at the bottom of the table and my tables get very long. So I always write subtotal formulas across the top. I’m not aware of anyway to get the totals row to be positioned at the top. 2. I don’t think you can past new data at the bottom of the table if there’s a Totals Row in place. Is that correct or am I missing something here?
@@MyOnlineTrainingHub Thanks Mynda. I didn’t know that. BTW you’re now second generation in my family. My son has become an accountant and now, like his mum, he uses your videos and website as a valued resource. ❤️
It'll never happen, I'm afraid. Spilling formulas is in direct opposition to the inbuilt table formula functionality where it copies the formula down a column.
I work with wheels and it seems every brand has a "Warrior" model. It occurred to me that I can stack tables of wheel data, and then use xlookup to search either the entire column -or- use table references if I'm looking for data from a specific brand, using entire columns or table header references. Sure, it's a lot of rows, but Excel seems to have enough.
Sure, stacking tables is one option, or you could use Power Query to append the tables and then create one consolidated view of the data for extracting/looking up.
^T is my loyal companion. Sorting is so much easier, and more important: safer. Changing a formula without the need to update the complete column with the same formula is great.
Is it possible to have a table populate itself with information that is on individual sheets within the workbook? I have a template sheet that gets populated with new project information, and always has the same type of information in the same position in the sheets, they just have different names. Is there a way to pull that information into a table so I can then use that table as my source data for a dashboard?
The only thing that I find annoying about Tables is that you cannot « hard-code » a default formula into a column. As soon as the default formula is overwritten by values in some rows in the column, the default formula that you’d want added any time a new row is added to the table often no longer appears and the cell is empty. I wish that there were a more robust way to define a default formula in the columns (besides VBA)
Yes, I know what you mean. I've had that experience too. I get why it works that way, but sometimes I wish it didn't 😆 there's no workaround other than to follow the best practice of not having a column contain mixed data. If you want a formula, it should be the same in every cell and you need to put your manually entered results in a different column.
Here's one way: Type in Search bar: Autocorrect. Navigate to "Autoformat As You Type". UNCHECK the option that reads "Fill formulas in tables to create calculate columns." It gives you the best of both worlds, because you can hard-code a default formula to your heart's content. In other columns, where you might want the formula to copy down, you will be given that option with a fly-out button (the grid with the fx) that says AutoCorrect Options. While this violates best practices, I find it useful for setting up Power Query parameter tables. These often have unrelated data that I want to be able to pull into a Power Qury without having to have a bunch of lookup tables.
What I find unfortunate is that when you use the tables tool in Excel, it doesn't allow colum headers to contain formulas, which prevents the use of dynamic headings.
Sounds like you might be using Tables for unpivoted data e.g. columns for each month etc. when ideally the data should be in a tabular layout and then use a PivotTable to create the pivoted view of the data with the months across the columns. 🤔
Aha, I didn't know I could [a] click ctrl-A to get all the content selected or [b] use slicers with ordinary table instead of pivots, which is way handier than the filter arrows [c] use that summary row feature, that's awesome. -- Thanks Mynda
Did you ever wonder if anyone knows everything about the capabilities of Excel … including Microsoft employees? But, there is one thing Excel can’t do, it’s incapable of an original thought. Someone somewhere has to tell it what to do or to tell something somewhere tell it what to do. It’s like a library containing all the world’s knowledge but, it takes someone to walk in and open a book, to make the place worth anything. Excel is a very humbling piece of software. No matter what you do with it, someone likely has a better way of doing it. If not, you own the temporary title of “Guru” until someone does it even better, eventually!
Using excel for years... And to be honest... I thought as soon as I enter rows and columns and fill them with numbers and text value this would qualify them as a table. I insert top row filters etc.... Pivot etc So what is it, if not a table? Guess I am lacking the most basic understanding of Excel
"Table" in Excel is a setting you can apply to a matrix of data. It's a wrapper for your data that comes with additional functionality as shown in the video. If you don't apply this setting, you won't be leveraging the shortcuts available in Excel Tables like structured references etc.
Interesting. I wonder if you're using tables for non-tabular layouts as this is the main reason to use a spilled array in a table. I'm interested to know which functions you'd want to use in a Table aside from SEQUENCE?
