The Excel Trick I Use EVERY DAY (and you should too!)
HTML-код
- Опубликовано: 10 сен 2024
- 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
❓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
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 love structured references too 😁
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.
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 🙂
Wow, thank you so much 🥰
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
Awesome to hear!
Named tables are great. They make formulae so much easier.
Sure do 😁
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
Love those tips. Thanks for sharing 😊I also use SHIFT+Space Bar to select rows and then CTRL+ or CTRL- to add/remove them.
that's a new one - I like it. Mind you I ctrl arrow all the time to get a round a table too
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!
So pleased we agree 😅🙏
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.
I love the way you explain ... precise, unagitated, without gimmickry (Ich liebe es, wie Sie erklären ... präzise, unaufgeregt, ohne Effekthascherei)
Thanks so much!
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.
Awesome to hear. Couldn't agree more 😁
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.
Nice tips, Martin. Thanks for sharing 😊
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.
Awesome to hear!
Amazing - I've used Excel for >30 years and I've never used tables! Thank you - this will be a great help.
Wow, this will be a game changer for you 😊
been using this since early excel like around 2013 and the reason why i like excel so much
Awesome to hear!
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
Awesome to hear!
you have all chances to learn Power Query and overpower your colleague's VBA in no time!
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.
🥁😁🙏
RUclips 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.
Awesome to hear! Welcome 😁
Same here. I just use Apple Numbers on iOS but glad to subscribe.
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]) ?
Thanks for sharing! That's my favourite feature too 😁
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.
Awesome to hear!
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.
Keep tooting the Tables horn, Dave. You'll wear them down eventually 😁
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 Mynda, they're pretty much all I use since I use Power Query everyday!
Awesome to hear, Chris!
I really learned something new. Thanks a ton. 😊
I'm so glad to hear that! 😊
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
So pleased you'll be making use of them going forward 😉
Thanks Mynda i have now purchased the course.
Awesome! Can't wait to teach you more 😁
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!
Great to hear!
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....
No workaround other than VBA to temporarily unprotect the table so you can add rows and then protect it again.
@@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 😢
@@MyOnlineTrainingHub Thanks anyway!!
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
Great to hear 👍
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.
Nice tip! Thanks for sharing 🙏
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 sharing!
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😁
I'm so glad to hear that! 🙏😅
I so love excel tables... makes administration work so much more enjoyable
Great to hear!
Short and Sweet explanation. Thank you so Much
So nice of you 🙏😊
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.
Yeah, I hear you. In that case you need the absolute references on the table structured references.
@@MyOnlineTrainingHub Thank you for the reply.
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!
So pleased to hear it was worth your while to watch 😅
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.
Great to hear 😁
The amazing technique was navigating the table, I never knew that I could select data in this way, Thanks Mam
Glad you discovered something new 😁
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.
Yes 💪😁
I love tables. Formulas are remembered as well even when you delete **all** the data and pasting in a new set of data.
Yeah, that's one of those unexpected 'features' of tables that's sometimes handy and sometimes annoying.
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.
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.
Glad you'll find that helpful!
Thank you very much ....
You are most welcome 😉
Wonderful video. I learned a lot!
Awesome to hear!
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 hear you, Ian. We MVPs have been nagging Microsoft for this for YEARS! We won't give up until we get it.
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.
Love that!
Simple and understandable, i liked the explanation of @Header
Glad you liked it 🙏😊
that is so good, thank you.
Glad you like it!
EXCELENTE SALUDOS DESDE CDMX TENGOO DISCAPACIDAD Y MANEJO EL INGLES
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.
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?
If you are using cell references, it won't expand. Instead, use table references.
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
Glad you discovered some new tips you can use 😁
Using Ctrl+T even for temporary tasks.😊
😁me too!
You're still the best
Thanks for your support, Robert!
Even before you really got started, I can't believe I wasn't using Ctrl-A to select my data (face turning red LOL)
So pleased it was worth your time to watch 😁
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.
Nice table naming tip, Rob! Thanks for sharing 😁
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.
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…)
Try File tab > Options > Advanced: 'Extend data range formats and formulas'.
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.
You can paste your new data in the total row and it will move down automatically 😉No need to switch it off and on.
@@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!
Ctr + L/T both works the same function.
Yes 👍
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.
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
@@MyOnlineTrainingHub Mynda, why not ask ChatGPT for some fake data? 😂
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 cant wait for them to allow spill values into tables. Idk how theyll do it. I get its complex but its so needed.
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.
How did you keep you charts from changing size when your data changed, e.g. when you added the Dec data?
By default, charts don't resize. They just squeeze more data into the chart space already available.
^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.
Awesome to hear!
I had never noticed before that when scrolling down the column headers change to being the table column headers. Doh!
Glad you discovered something new 😁
Power Query. Don't leave home without it. 😁
Yep!
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. : ))
Thanks for your kind words! I replied to your email about the courses. 😊
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.
@@MyOnlineTrainingHub I'd never even considered that as an option. It has merit.
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: ruclips.net/video/CNlw1-Vh4cE/видео.html
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?
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?
You can perform that filter in Power Query before loading to the worksheet.
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!
Well, actually....you can create pseudo-original thought with algorithms and a large enough dataset. Also, Co-Pilot is coming... 😁
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: ruclips.net/video/CNlw1-Vh4cE/видео.html
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
🤔
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.
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.
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).
Yikes! I'd use Power Query to get my data from another file: ruclips.net/video/Nbhd0B5ldJE/видео.html
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.
@@alexb9312 thank u.. sure will try n see result🫡🙂
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.
I would use Power Query for such purpose 😉
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. 🤔
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?
Yeah, it's a shame there isn't an option for totals at the top. You can paste new data at the bottom and the total row will just move down.
@@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. ❤️
😁🥰
Rowe Run
I wonder why tables are not implemented in other spreadsheet software.
Well....as of very recently Google Sheets now has them 😉
@@MyOnlineTrainingHub Now I am in a dilemma. Pay for Excel desktop or get Sheets for free, but have all my data scanned.
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.
I don't use tables because they don't allow for spill ranges. It's a deal breaker
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?
I love tables. I turn most data into tables.
Wonderful to hear!
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?
Sure! Process the data in Power Query and load the result as a table (or to the data model) and then visualize.
@weemanling Use this technique to automatically extract data to other sheets: ruclips.net/video/_ys8qNq1soM/видео.html
But I think there are some functions that works only with ranges and not with tables
All functions work with data in tables, but functions that spill cannot work inside a Table. That may be what you're thinking of.
💚💚💚
🙏
Dynamic arrays don't work inside tables.
Spilled arrays will never work inside tables. For the most part, this isn't an issue, but occasionally a SEQUENCE formula would be nice to have.
Knowing so much in Excel and working in a company that doesn't know or apply these knowledge gives so much headache
Shame. Hope you can slowly show them the light 😊
I noticed that xlookup does not work in tables😢
Yes it does, but it won't spill in a table. You can only use it to return one value.
@@MyOnlineTrainingHub thanks so much for responding. The spill is the best part🙂🙂
I use tables to track our real estate business.
Awesome to hear 😊
FYI, I just created a table of about 450,000 rows and it crashed. I had to pull all my data again. Office 365. I'm immensely upset because I spent half my day trying to salvage all the data I cleaned and transformed. It's all gone, even after converting the table back to a range in a last ditch attempt to save my work. Otherwise, I ❤ tables.
Oh no!🤦♀️
IT is recommended to always have a backup copy of your data, especially when you want to apply major changes. If your file on onedrive or SharePoint you can restore version.
@@AhmadAlMokdad-cx7sk I've definitely used that resource when I'm doing stuff for my job.
I'm losing "friends" over converting their data sets to tables. 😂 It amazes me the number of people i work with that dont use tables regularly.
It's just a different way of thinking. But once people have made the switch there's no going back.
@KingdomCtzen their loss! 😉
Out
🥰
🙏😁
I love tables, however...
I wish tables could handle spilled arrays 😞
My biggest gripe, there appears no easy way to lock cell references within a table, like we can normally do by cycling through the F4 key 😡
I love tables too. And I also love spilled arrays- but they don’t belong together. Tables are for raw data with the odd calculated column. Spilled arrays are essentially outputs- eg report outputs based on criteria in the array formula.
A compromise is that you can point power query to a spilled array and use that as an input to your data transformation and output as a table.
I agree, F4 for table absolute references would be nice.
@@MyOnlineTrainingHub That's the one thing that I have found annoying with table references. It's a bit inconvenient having to edit copied formulas to change the name of the columns.
Thank you for sharing!❤
What's the trick u've used in this video😂
I hate tables
😱