Home and Learn: Microsoft Excel Course
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:
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:
If you only wanted to display the women's shoes, deselect all the other values. The spreadsheet would then look like this:
Let's see how to create the above table.
Create the simple spreadsheet below:
If you don't fancy typing all that out, download ours here: (Right-click the link and select Save As/Save Target As.)
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.)
When you click on Table, you'll see the following dialogue box appear:
Make sure that My table has headers is checked. Click OK and just like that, your data is transformed into a table!
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:
Click on a style and your table will be updated.
Now have a look at the Table Style Options panel:
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:
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:
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:
Select the Total Row again in Table Style Options. You should find that this new row's total is included in the calculation:
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.
Automatically updated charts -->
<--Back to the Excel Contents Page