Manage Your Excel Spreadsheet with a Table

If you’re working with a list of data, you can turn it into a Table.  Excel gives you some features that make manipulating this data much easier. Your column headings will always be visible, and your rows are now formatted with alternating, colored ledger lines (like a checkbook register), which makes your data easier to see. Each column now has the AutoFilter dropdown menu. You can use AutoFilter to filter or sort columns of your data. It will sort everything all together at once, so you don’t run into the common problem where you sort one column, and then everything is no longer corresponding to the cells around it.


To convert your data into a Table, select your data. On the Home tab, under the Styles group, select “Format as Table” and choose the table formatting that looks best to you.


Click to enlarge


Now that you’ve converted your list into a Table, you’ll see that the Ribbon has now given you a new tab called Table Tools Design. You can check “Total Row” in the Table Styles Options group. When checked, a row will appear at the bottom of your data that calculates different totals you can toggle for sum, average, and count.

To take advantage of the AutoFilter feature, scroll up to the top with the column labels. Click the dropdown arrow of the column you want to sort. In my this case, I will sort the Date Opened column.

Click to enlarge

As you can see in the dropdown menu, I can sort Oldest to Newest or the opposite, but what’s cool is that I can quickly check and uncheck the months. If I only want to see what happened in November, I just uncheck October and December and now my table is only showing November.

Click to enlarge

If I want to see October and December data again, I just go back to the dropdown menu and recheck the boxes. Nothing’s been lost!


You can go back to a regular Excel range by clicking “Convert to Range” in the Tools group of the Design Table Tools ribbon.


For my example, I am using a spreadsheet of 140 pro bono clients that are in nine clinics. You can get a copy of this spread sheet to fiddle around with yourself here: