Simple Tables in Excel

A simple table in Excel is one that has dropdown lists where you can filter the data in your table. The one we'll be creating for this tutorial looks like this:

Data turned into a table in Excel

You can click on the column headings at the top to get a list of values you can filter on. For example, the Gender column has values for W, M and C, meaning Women, Men, and Children. Clicking on Gender reveals the following list:

An example of a table filter

If you only wanted to display the women's shoes, deselect all the other values. The spreadsheet would then look like this:

An Excel table with a filter applied

Let's see how to create the above table.

Create the simple spreadsheet below:

Excel spreadsheet before it has been turned into a table

If you don't fancy typing all that out, download ours here: (Right-click the link and select Save As/Save Target As.)

Shoe Data Spreadsheet

Next, select all the data from cell A1 to E9. With the data selected, click on the Insert ribbon at the top of Excel. From the Tables panel on the Insert ribbon, click on Table: (You can also hold down the keyboard keys CTRL + T to create a table.)

The Table item in the Excel ribbon

When you click on Table, you'll see the following dialogue box appear:

The Create Table dialogue box

Make sure that My table has headers is checked. Click OK and just like that, your data is transformed into a table!

The default table style

The default colour is blue, but you can change this. Click anywhere in your table and then select the Design tab at the top of Excel. Play around with the table styles:

The styles you can apply to an Excel table

Click on a style and your table will be updated.

Now have a look at the Table Style Options panel:

The Table Style Options

Check and uncheck these options to see what they do.

The Total Row option is quite useful. When you put a check in this box, you'll see a row added to the bottom of your table:

An Excel table with the Total Row item selected

In the image above, row 10 now has a total. In cell E10 you can see the total of 4001.09. this is a sum total of all the values in the E column. Click inside cell E10 and you'll see it's a dropdown list:

More formula options for the Total Row

You can select any of the functions on the list, if you don't want a SUM of the values.

If you don't want to see the total row, simply deselect it in the Table Style Options.

When you add a new row to your table, Excel retains the formatting you applied. Try it out. Add the following as row 10 (make sure to deselect Total Row in the Table Style Options):

Item: Shoe9
Gender: W
Quantity Sold: 23
Price: 31.45

When you add the row, your spreadsheet will look something like this:

A new row added to an Excel table

Select the Total Row again in Table Style Options. You should find that this new row's total is included in the calculation:

A new total after a row has been added

Making a table out of your data is often a good idea, especially if you need to filter on the columns.

 

In the next lesson, you'll see how charts are automatically updated when you use an Excel table.