Tables in Excel

July 24, 2019

It's all a table

When you first start using excel you sort of get this idea that each sheet is it's own table. This isn't really the case. Here I will go over what an excel table is, how to create them and benefits of using a table.

What is an Excel Table?

An Excel sheet is comprised of a range of cells. These cells are typically formatted to allow users to easily view data and focus the eyes on what's important (what to look at first). If all data is formatted the same way it makes it difficult to read the information. When we format the data we start to get closer to creating an actual table. But formatted data does not provide any other benefit to the information other then the visual benefit.

Take a look at the image above. Both tables are the same yet it's easier to read the one below. Why? You're eyes are drawn to the information because it has contrast. Most excel sheets typically stop here because it's not necessary to adapt more. However, the next step to being close to a actual table within the sheet, is to add some filters.

Filters allow you to sort remove and organize the cells according to categories. To setup a filter you can either use the Shortcut by selecting the range of cells to filter and then use Crtl + Shift + L to turn on or of filters. The other way would be to select the cell range and click on the filter button from the Home menu bar.

Within the above cell range you can quickly re-organize the cells according to price rather then item #. This is done by selecting the drop-down menu beside the column that you would like to adjust. You man also want to query/filter the data for a specific Item. Like only items below $100.

In this exampled, I resorted the data according to price and only show items below $100.

If you perform the same task you may notice that the total column gets filtered out as well. This is either because it was included when creating the filter, the totals do not update. This however works differently in tables, if setup correctly.

Lets now look at actually creating tables.

To create a table simply select your range of cells/data and then hit Ctrl + T or selecting the insert tab and clicking on Table from the tables group.

This will change the selected range into a table. Once your range is a table the first thing you should do is to name your table and update the style to suit your needs.

I typically use the Total row on most cells. But be aware that the formula that is used there is a bit different the just your typical =Sum(E47:E56) the formula it uses =Subtotal(109,[Cost]). There is a slight difference that I have noticed between these. This is only on filtering your table. When you filter and remove/hide items due to filtering the =Sum formula does not account for the removed/hidden cells. While, the =Subtotal(109,###) does.

Good luck and if you have any questions or comments on tables let me know.