What’s So Great about Tables?
Tables have a ton of advantages but are still not used as much as they should be. I think it’s because a lot of people simply don’t know about them and how awesome they are. My two favorite advantages of a table (vs just a regular set of data) are 1) easy sorting and filtering, and 2) easy adding of new data.
Easy Sorting and Filtering
Let’s say you have a data set like the one below and you wanted to be able to quickly sort or filter it based on specific criteria. There are several ways you can do this, but probably the easiest way is to turn it into a table.
Download my data set to follow along with me.
To do this, select the entire data set first.
Then go to your ribbon up top and select the Insert tab. Then click on the Table button.
Click OK when you’re asked where your data is (the entire data set you’ve previously selected). Suddenly your data set will turn blue, with banded rows like this.
You’ll also notice that each header has an arrow next to it, which will allow you to quickly sort and filter. Let’s try sorting by AMOUNT, from smallest to biggest.
All you have to do is click the arrow next to AMOUNT. Then select “Sort Smallest to Largest”, and click OK.
Your data is all sorted! A quick way to tell that your data is sorted is by the little up arrow that is now in the arrow box next to AMOUNT.
Now let’s filter the categories, to see only the ones that are labeled as FOOD.
This time, click the arrow next to CATEGORY. Click the check box next to (Select All) to uncheck it, then click the check box next to FOOD. Click OK.
Your data is now filtered! You can tell a filter is applied by the little filter icon next to the CATEGORY header.
To unfilter, click the filter icon next to CATEGORY, and select “Clear Filter From ‘CATEGORY'”.
Now for my next favorite feature of tables…
Easy Adding of New Data
What do I mean by “adding of new data”? Let’s go back to the original data set before we turned it into a table. If you wanted to see how much you’ve spent in the month of February, you might add a formula off to the side.
You would simply enter the formula “=SUM(D3:D9)” into cell I3 and be done with it. But what happens if you want to keep building off your data set, adding new rows to the bottom?
In order for your formula to be correct, you would have to update it from “=SUM(D3:D9)” to “=SUM(D3:D10)”. And you would have to keep updating it every time you added new data. Who wants to do that?! With tables, you don’t have to worry about updating your formulas. Any formula that is reading off your table, will be automatically updated!
Let me show you what I mean.
Select the entire data set and turn it into a table (see steps above).
Notice that your total formula currently says “=SUM(D3:D10)”.
Now if I add another row of data, the formula updates automatically! It now says “=SUM(D3:D11)” to include row 11.
Every time you add a row to this table, your total formula will automatically update!
Tables are a simple yet powerful feature that was added to Excel starting with the 2007 version. They are one of my favorite tools, and probably one of the most underrated tools. Try incorporating them into your spreadsheets. You might just get addicted to them like I did!