25/01/2024
If it looks like a table, it doesn't necessarily mean it IS a table.
In most of my interactions with various people, when they share their files with me, I notice they often have data formatted in a way that looks like a table but in reality, Excel doesn't KNOW that this is a table per se.
So, if you have a data set, with column headers and a bunch of rows, select it all and press Crtl+T on your keyboard. This will bring a pop up asking you to confirm that range you selected is about to be formatted as a proper table and to tick/untick the option about whether it has headers or not (rule of thumb, it's always better to have headers).
Once you press Ok, you'll notice all of a sudden, your dataset colours now changed, automatic filters have been applied to all headers and things look a lot more.... organised and robust. You can easily adjust the colour scheme from the very top of the Ribbon (Table Design).
Here's a list of positive things that will happen now that you have a properly formatted table:
Any new rows you add at the bottom of that table, will be automatically added to the dataset, without you having to manually readjusting your formulas.
Speaking of formulas, any lookups you may do now to that table, instead of having this generic syntax of A2-B2 for example, will reference the actual column headers, i.e. [Total Revenue]-[VAT] so it's much easier to read for people you may share the file with to understand.
Auto-filtering is definitely a nice bonus. Plus, if your dataset has many rows and you start scrolling down past the top row, you'll actually still be able to see the headers of each column, even if you're in line 1000000 (and your table goes all the way down there I mean).
So, to recap, table formatting is a Godsend when it comes to data handling in Excel and once you start using it more often, you'll notice straight away the quality-of-life improvements in your daily workflows.