A lot of people don't know about the table feature of Excel, and those how know about it usually don't realize all its power. In this short tutorial we will see how to use the table feature, and why you should start using it now. Hint: it's awesome.
My goal is that after reading this article you will start using tables all the time :-)
It's really easy to create a table.
Have the active cell somewhere on your data and go in the "insert" tab. There click on the "table" button and press enter.
That's how you transform your data into an Excel table. You can of course change the way it looks if you don't like the default styling.
Now let's see all the great things you can do with it.
What if you want to add a total row at the bottom of the table? Just click on the "add total" button in the "table" tab.
Now all you have to do is click on the cells at the bottom, and pick which kind of total you want (average, sum, count, etc.)
There's no need to type a formula or select a range, everything is done automatically.
It's very common to want to add an extra row of data in Excel. Well, with a table this is ridiculously easy.
Select the last cell of the table which is not a total (in the example below it's E6), press tab, and boom!
A new row appears with the correct formatting, and the totals are updated in real time.
When you have a lot of data, it can be time consuming to find and remove duplicate rows. Not with tables.
In the "table" tab, click on the "remove duplicate" button. There select the columns you want to use to find duplicates. In our example we want to find them if they have the same name and ID.
And our duplicate is gone.
And what if you want to add a column on your table?
Well, you just write your new column name, and you are done.
That can't be any simpler than that. And you can do the same thing to add a new line if you don't have a "total" row at the bottom.
One last thing I want to show you is how formulas are working within tables.
In our new column "value" we want to show the total value of an item, so we need to multiply the quantity by the price. Let's try to do that.
You can notice 2 important things here:
D3*E3in the formula, instead Excel use the column names directly
[@Quantity]*[@Price]. This is a lot easier to read.
Converting data into a table can be done in one click. And then everything becomes so much easier to do. Tables are awesome, and you should use them pretty much all the time.
So... did I manage to convince you?