The Excel Trick I Use EVERY DAY (and you should too!)

One overlooked Excel feature that will change how you work with data forever.
👩‍🏫 Master Excel with 20% off all my courses until August 15, 2024: bit.ly/trick24...
⬇️ Download the example file here and follow along: bit.ly/trick24...
I'm about to show you how one overlooked tool can transform the way you work with data. It's not a complex formula or a hidden setting - it's something so fundamental, you'll be shocked you haven't been using it all along.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetr...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetr...
#Excel #ExcelTrick #ExcelTutorial

[@MyOnlineTrainingHub]

❓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

[@bjbrooks]

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 🙂

[@MyOnlineTrainingHub]

Wow, thank you so much 🥰

[@kgadeberg]

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)

[@MyOnlineTrainingHub]

I love structured references too 😁

[@Pronellyllon]

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.

[@lewesmariner]

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

[@MyOnlineTrainingHub]

Love those tips. Thanks for sharing 😊I also use SHIFT+Space Bar to select rows and then CTRL+ or CTRL- to add/remove them.

[@WildRover1964]

that's a new one - I like it. Mind you I ctrl arrow all the time to get a round a table too

[@gjgylse]

@@WildRover1964 And ctrl+shift+arrow to highlite: Being in the top left corner of a list, ctrl+shift+down then ctrl+shift+right to highlite the list (or ctrl+*)

[@BenWolverton-m1t]

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.

[@MyOnlineTrainingHub]

Thanks for sharing, Ben! We can also use Paste Special > Linked Picture as an alternative to the Camera tool which is no longer on the ribbon.

[@Metheglyn]

@@MyOnlineTrainingHub The Camera Image can also be inserted in other MS Office documents (Excel, Word or PowerPoint), and still automatically update.

[@martinstannard2859]

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.

[@MyOnlineTrainingHub]

Nice tips, Martin. Thanks for sharing 😊

[@msmith3090]

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.

[@MyOnlineTrainingHub]

Awesome to hear!

[@DrDamoStrikesBack]

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.

[@MyOnlineTrainingHub]

Awesome to hear. Couldn't agree more 😁

[@babydadoo]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@sanchowitfurrows1561]

you have all chances to learn Power Query and overpower your colleague's VBA in no time!

[@bjornkumpf274]

I love the way you explain ... precise, unagitated, without gimmickry (Ich liebe es, wie Sie erklären ... präzise, unaufgeregt, ohne Effekthascherei)

[@MyOnlineTrainingHub]

Thanks so much!

[@jokearney3540]

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!

[@MyOnlineTrainingHub]

So pleased we agree 😅🙏

[@mrzorrombo]

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

[@MyOnlineTrainingHub]

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

[@arpwable]

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

[@bryanfrew]

Amazing - I've used Excel for >30 years and I've never used tables! Thank you - this will be a great help.

[@MyOnlineTrainingHub]

Wow, this will be a game changer for you 😊

[@traciesmobile683]

Named tables are great. They make formulae so much easier.

[@MyOnlineTrainingHub]

Sure do 😁

[@robsbisa1411]

Establishing and naming tables is almost always my first step. It's especially useful with 365 features, like LET() or INDEX(MATCH), where I'm able to create complex formulas quickly, knowing the names of my tables.

[@MyOnlineTrainingHub]

Yes, naming tables is a great habit to form. Thanks for sharing.

[@ifoster3011]

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.

[@MyOnlineTrainingHub]

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.

[@ifoster3011]

@@MyOnlineTrainingHubTrue - my calculations also reference appropriate non-table cells etc, so editing those formulae after copy and paste would be tedious.

[@MyOnlineTrainingHub]

Yeah, I hear you. In that case you need the absolute references on the table structured references.

[@ifoster3011]

@@MyOnlineTrainingHub Thank you for the reply.

[@waterbourne9282]

Wow. I'd been doing all that table functionality manually. This will save me so much time. Much appreciated.

[@MyOnlineTrainingHub]

Awesome to hear!

[@lewerim]

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.

[@MyOnlineTrainingHub]

Awesome to hear!

[@ivanbork4175]

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.

[@MyOnlineTrainingHub]

Thanks for sharing!

[@leerv.]

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]) ?

[@MyOnlineTrainingHub]

Thanks for sharing! That's my favourite feature too 😁

[@stephenyounger8280]

Really terrific video, I had no ideas tables were so easy to use and so powerful. Kicking myself for hours spent updating old spreadsheets. Thank you

[@MyOnlineTrainingHub]

So pleased you'll be making use of them going forward 😉

[@Pronellyllon]

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!

[@MyOnlineTrainingHub]

Great to hear!

[@chrism9037]

Thanks Mynda, they're pretty much all I use since I use Power Query everyday!

[@MyOnlineTrainingHub]

Awesome to hear, Chris!

[@yvlove77]

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.

[@MyOnlineTrainingHub]

Nice tip! Thanks for sharing 🙏

[@JudeBarak]

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]

No workaround other than VBA to temporarily unprotect the table so you can add rows and then protect it again.

[@JudeBarak]

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

[@MyOnlineTrainingHub]

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 😢

[@JudeBarak]

@@MyOnlineTrainingHub Thanks anyway!!

[@gjgylse]

Tables have been one of my favorite Excel features for a long time. They are timesavers!

[@MyOnlineTrainingHub]

Awesome to hear!

[@geoffreyrhine8210]

I like that you have some rows above your table, which is a great location for subtotal formulas to react to applied filters.

[@MyOnlineTrainingHub]

Thanks, glad you like it!

[@telstraroenssel3701]

been using this since early excel like around 2013 and the reason why i like excel so much

[@MyOnlineTrainingHub]

Awesome to hear!

[@thearchibaldtuttle]

Absolutely! Tables help a lot in organizing your data and make working with data much easier.

[@MyOnlineTrainingHub]

Glad to hear you're a table fan!

[@thearchibaldtuttle]

@@MyOnlineTrainingHub Absolutely a table fan 🙂

[@iankr]

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?

[@MyOnlineTrainingHub]

I hear you, Ian. We MVPs have been nagging Microsoft for this for YEARS! We won't give up until we get it.

[@ChickenSandwich--OIIIIIII0--]

One table tool I like is the Slicer. Under Table Design/Insert Slicer. It's a quick and easy way to filter what is displayed in a table.

[@MyOnlineTrainingHub]

Yes! I love that too. Thanks for sharing.

[@idjles]

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!

[@MyOnlineTrainingHub]

So pleased to hear it was worth your while to watch 😅

[@brianmills1844]

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.

[@MyOnlineTrainingHub]

🥁😁🙏

[@99bogatyrs99]

Again, great information. I always like your presentations, which are in everyday language, simple to follow, and, most importantly, contain practical information I can put to use. Well done!

[@MyOnlineTrainingHub]

Wow, thank you!

[@SteveAldred]

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.

[@MyOnlineTrainingHub]

Yes 💪😁

[@adena65]

I learned about tables from one of your courses years ago and haven't looked back!

[@MyOnlineTrainingHub]

Love that! 😍

[@cranialzoo]

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.

[@MyOnlineTrainingHub]

Great to hear 😁

[@AtoZTrivia]

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😁

[@MyOnlineTrainingHub]

I'm so glad to hear that! 🙏😅

[@khersheonteoh5697]

I love tables. Formulas are remembered as well even when you delete **all** the data and pasting in a new set of data.

[@MyOnlineTrainingHub]

Yeah, that's one of those unexpected 'features' of tables that's sometimes handy and sometimes annoying.

[@chahineatallah2636]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@dagordon1]

Wow, saves a ton of steps in cases of independent rows. Without turning into a table, Aggregate is a similar function to Subtotal.

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@Azhar_Khan383]

The amazing technique was navigating the table, I never knew that I could select data in this way, Thanks Mam

[@MyOnlineTrainingHub]

Glad you discovered something new 😁

[@anv.4614]

Thank your tutor, I have joined your lessons on the other learning platform. Appreciated.

[@MyOnlineTrainingHub]

Great to hear!

[@jimduncan7764]

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.

[@MyOnlineTrainingHub]

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.

[@mengkeangveng3899]

There is an option under the Formula Ribbon. It is at the far right. You have the option to turn off Auto Calculation.

[@WildRover1964]

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

[@MyOnlineTrainingHub]

Great to hear 👍

[@m.ssharma535]

Excellent. thanks. Minute 5.0 and later for using structured references is useful.

[@MyOnlineTrainingHub]

Glad you found it useful 😁

[@Tooxcade]

Short and Sweet explanation. Thank you so Much

[@MyOnlineTrainingHub]

So nice of you 🙏😊

[@akinsinasegun2379]

A key take away for me is using slicer to filter a Table. I used to think it's only with Pivot tabkes i can connect Slicers to. Thank you for this.

[@MyOnlineTrainingHub]

Glad you'll find that helpful!

[@Bluemont]

that is so good, thank you.

[@MyOnlineTrainingHub]

Glad you like it!

[@Tina-f6m7x]

Tables are a MUST know! I use tables for every list I have. Let MS know we need more colors!

[@MyOnlineTrainingHub]

They sure are! You can create your own custom colour themes 😉

[@yatinpurohit]

I knew all about tables. Or so I thought. Using slicers with tables is revelation.

[@MyOnlineTrainingHub]

So pleased you've got a new way to use them 😊

[@NoiseBlindness]

My top table tip is to pin the table naming area to quick access so you can always see table names when you are in one.

[@MyOnlineTrainingHub]

Love that!

[@rahulpanse4692]

Thanks Mynda i have now purchased the course.

[@MyOnlineTrainingHub]

Awesome! Can't wait to teach you more 😁

[@dalesmf4310]

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

[@MyOnlineTrainingHub]

Thanks for your kind words! I replied to your email about the courses. 😊

[@hughsutherland500]

Awesome as usual!

[@MyOnlineTrainingHub]

Thanks so much!

[@thebesttechnical3608]

super clear and easy to understand!

[@MyOnlineTrainingHub]

Great to hear!

[@John.Mann.1941]

EDITED for typos. I’m still using 2010, as a limitation. I use tables often, but not always. I love the filters/sort drop-downs in the column headers, and almost always have the total row at the bottom turned on. I have found 2 limitations for my uses. Creating a running total column (eg account balance) doesn’t work with structured references, since they are always along the current row, while a running balance needs to include the total from the row above in the formula. The other issue I ran into was when trying to use sheet protection (mostly to guard against my clumsy elderly fingers) I found the tables and protection didn’t like each other. I did read elsewhere that tables and sheet protection don’t always play nicely, and some simple test done more recently didn’t encounter problems. I mostly use Excel for personal matters, so my data doesn’t get very large. I also maintain the accounts of a very small non-profit group using Excel.

[@MyOnlineTrainingHub]

This post explains how to write running totals in tables using structured references: www.myonlinetraininghub.com/excel-table-running-total-formula The limitation of worksheet protection is that you can't add new rows to tables that are in protected sheets.

[@rhettmedford629]

😍 If i paste a whole data set over the top of an existing data set will the table formatting remain?

[@MyOnlineTrainingHub]

If you paste special > as values, or without formatting

[@MrHorzel]

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.

[@MyOnlineTrainingHub]

Unfortunately, the sheet protection limitation has been around since Excel 2007, so I don't expect it will be fixed anytime soon.

[@lornavantilburg4622]

I so love excel tables... makes administration work so much more enjoyable

[@MyOnlineTrainingHub]

Great to hear!

[@hadiuzzaman]

I really learned something new. Thanks a ton. 😊

[@MyOnlineTrainingHub]

I'm so glad to hear that! 😊

[@theturtledave]

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.

[@GeertDelmulle]

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.

[@theturtledave]

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

[@MyOnlineTrainingHub]

Keep tooting the Tables horn, Dave. You'll wear them down eventually 😁

[@brianspiller9075]

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.

[@lefrinj]

It's sad that we have to keep working in a crap way to please the people who won't learn their tools. I feel the same about using styles and headings in Word, instead of manually making things big and bold and formatting with spaces.

[@lgvdbroek]

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

[@MyOnlineTrainingHub]

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

[@Metheglyn]

In my Danish language version, ctrl+T creates Tables.

[@petr6617]

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

[@MadMonk67]

I learned some new things, thank you!

[@MyOnlineTrainingHub]

I'm so glad!

[@wordexceltips-vn]

great! thanks

[@MyOnlineTrainingHub]

You're welcome!

[@rob4canada]

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.

[@MyOnlineTrainingHub]

Nice table naming tip, Rob! Thanks for sharing 😁

[@MitchellAllen-h6u]

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.

[@sahralsahri7180]

Thank you very much ....

[@MyOnlineTrainingHub]

You are most welcome 😉

[@kerrimiaso9359]

Wonderful video. I learned a lot!

[@MyOnlineTrainingHub]

Awesome to hear!

[@joppy125]

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.

[@MyOnlineTrainingHub]

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.

[@tahirwaseem8684]

very helpful

[@MyOnlineTrainingHub]

Glad to hear that

[@robertcooper3759]

You're still the best

[@MyOnlineTrainingHub]

Thanks for your support, Robert!

[@ignatiussilveira4257]

Very beautiful & clear explanation 👌 👍 😊

[@MyOnlineTrainingHub]

So nice of you 🙏

[@gudzmist6448]

Slicers in tables….. wow would have saved me so much time!!

[@MyOnlineTrainingHub]

So pleased you can use them 😊

[@leelawrence3379]

I love your content and always learn something new. I just wish sometimes you would use inventory management as an example instead of sales data.

[@MyOnlineTrainingHub]

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

[@MitchellAllen-h6u]

@@MyOnlineTrainingHub Mynda, why not ask ChatGPT for some fake data? 😂

[@MyOnlineTrainingHub]

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.

[@leelawrence3379]

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

[@MitchellAllen-h6u]

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

[@Seftehandle]

Simple and understandable, i liked the explanation of @Header

[@MyOnlineTrainingHub]

Glad you liked it 🙏😊

[@nawazsayed7110]

what are the benefits of using table in excel over sheet data?

[@MyOnlineTrainingHub]

All the things I listed in this video.

[@nunesgon]

How did you keep you charts from changing size when your data changed, e.g. when you added the Dec data?

[@MyOnlineTrainingHub]

By default, charts don't resize. They just squeeze more data into the chart space already available.

[@jjbolton2009]

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?

[@MitchellAllen-h6u]

If you are using cell references, it won't expand. Instead, use table references.

[@mikedbman]

I have a table I have used for many years. Some of the default formulas for certain columns is now different but the table seems to default to some default formulas when I had a new row of data. How can I change what the default formula for columns is in a table? I have thought about completely rebuilding the table but was hoping there might be another way. Thanks in advance.

[@MyOnlineTrainingHub]

You need to replace all the formulas in the column with the new default that you want.

[@ASMKaikobad]

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

[@MyOnlineTrainingHub]

You can't protect cells in an Excel Table and still edit it.

[@ME-VJCHAUHAN]

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

[@MyOnlineTrainingHub]

Yikes! I'd use Power Query to get my data from another file: kzbin.info/www/bejne/hJPLlWN4atGXgKc

[@alexb9312]

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.

[@ME-VJCHAUHAN]

@@alexb9312 thank u.. sure will try n see result🫡🙂

[@GeertDelmulle]

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

[@MyOnlineTrainingHub]

Try File tab > Options > Advanced: 'Extend data range formats and formulas'.

[@GeertDelmulle]

@@MyOnlineTrainingHubThanks, but I’m afraid my kutools add-in is messing with this behaviour. Annoying but not the end of the world.

[@thomas_didymus]

Even before you really got started, I can't believe I wasn't using Ctrl-A to select my data (face turning red LOL)

[@MyOnlineTrainingHub]

So pleased it was worth your time to watch 😁

[@BrendaDolloff]

Is there a reason why you should choose to Insert a table from your data instead of just selecting "format as table" in the home tab?

[@MyOnlineTrainingHub]

No, they both do the same thing. I normally prefer the keyboard shortcut CTRL+T.

[@timothyjohnston4083]

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 ?

[@MyOnlineTrainingHub]

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: kzbin.info/www/bejne/eX_PqGRji81nmac

[@ennykraft]

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.

[@MyOnlineTrainingHub]

You can paste your new data in the total row and it will move down automatically 😉No need to switch it off and on.

[@MitchellAllen-h6u]

@@MyOnlineTrainingHub 🤯 Thank you, Thank You!!!! I didn't know that. I used to pull the table extender in the corner before adding new data. Wow!

[@LauraAtkinson-zx6ij]

I have a sheet that I want to convert to table. Will the formulae I have that reference cells automatically update to the column name reference?

[@MyOnlineTrainingHub]

No, you'll need to re-write the formulas.

[@LauraAtkinson-zx6ij]

@@MyOnlineTrainingHub Thanks

[@LauraAtkinson-zx6ij]

@@MyOnlineTrainingHub Thanks!

[@2bquest]

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?

[@Meraxeshandler]

I cant wait for them to allow spill values into tables. Idk how theyll do it. I get its complex but its so needed.

[@MyOnlineTrainingHub]

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.

[@moodberry]

How did you add rows above the column headers at about 4:36?

[@MyOnlineTrainingHub]

You can insert a table anywhere in the worksheet. It doesn't have to start in the first row. If you want to insert rows after inserting the table, simply select the row containing the column labels > CTRL+ Plus key to insert rows.

[@ianbriers5019]

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

[@MyOnlineTrainingHub]

🤔

[@williamarthur4801]

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 .

[@MyOnlineTrainingHub]

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.

[@tonyb4653]

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

[@MyOnlineTrainingHub]

😱nooo! Tables should never have more than one row of column headers. See this video that explains why and what you should do instead: kzbin.info/www/bejne/eX_PqGRji81nmac

[@hollywoodinkannada2826]

Ctr + L/T both works the same function.

[@MyOnlineTrainingHub]

Yes 👍

[@martinezmartinez9517]

EXCELENTE SALUDOS DESDE CDMX TENGOO DISCAPACIDAD Y MANEJO EL INGLES

[@TheIvalen]

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?

[@MyOnlineTrainingHub]

You can perform that filter in Power Query before loading to the worksheet.

[@Rice0987]

Using Ctrl+T even for temporary tasks.😊

[@MyOnlineTrainingHub]

😁me too!

[@rudiklein]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@BrandonOsborn404]

Yees, I use Tables every single day! :)

[@MyOnlineTrainingHub]

The best! 😁

[@hirolau]

Love tables and structured references. I find it annoying that chrats still are having trouble with them. Also spilled ranges and table does not work well together. Its like 3 ears of excel that are not quite compatible.

[@MyOnlineTrainingHub]

I don't have any trouble with charts referencing tables, but I agree, it would be nice on some occasions for spilled arrays to work in them, but I don't expect this will ever change because it's in contradiction to how table formulas are designed to work.