Excel Tables - How and Why
Excel Tables present data professionally, make formula easy to understand, and help maintain spreadsheets as they grow. This tutorial explores these benefits.
An accompanying Walks workbook is provided, which you can download below. With this you can follow the steps (in red) and play with the features described. On completion you will have converted the downloadable spreadsheet into something that looks like the above; and on the way you will have learned how to:
Create attractive tables
Use table references in formulas
Link and summarise tables
Easily manage rows and columns
You can compare notes with others, and also engage with me, by reading and adding to the discussion at the bottom of this post. I am always grateful for feedback.
Step 1: Download Walks Workbook
Walks
.xlsx
Download XLSX • 14KB
Step 2: Create Table
There are two ways to create a table: either from scratch; or by converting an existing range into a table. The Walks spreadsheet already has data in it, so we will just convert that into a table.
Select the Walks tab
Select a cell within data
Select the Insert Menu
Click the Table button
Note. If you want to create a table from scratch then at step 1 select an empty range where you want the table to appear.
Excel looks at the data around the selected cell(s), and draws a green dashed line around what is assumes it the table. You're asked to confirm the range and whether or not the top row already contains column headers. You may select a different range or tick/untick the, "My table has headers", checkbox. In most cases, though, Excel will have got it right, and all you'll have to do is click OK to get Excel to create a table.
Note the presence in the Ribbon of the, "Table Design", menu, which appears when a cell in a table is selected. There's much in this menu that's seems a little daunting, but you should not worry as you can achieve a great deal with Tables without going anywhere near most of these scarily named features - and we don't in this tutorial.
To the far left of the Table Design menu is the Table Name, which is probably set by default to something like, "Table1". One of the very biggest advantages of tables is we can assign meaningful names to tables, that will make our functions easier to read.
Change, "Table1", to, "tblWalk".
To the far right of the Table Design menu are the Table Style and Table Style Options groups. And here you can alter the appearance of the table. Try the following:
Tick and untick the checkboxes and observe the changes to the appearance.
Show the totals row and see what types of total you can add to the bottom of the table.
In the Table Styles Group select a different style or try creating a new style of your own.
Step 3: Lookup the Distance of each Route.
In the Walks spreadsheet a "Routes" worksheet has already been set up as a table (tblRoute) which contains a list of the routes walked and their distances. We can show in the Walks table these values from the Route table as follows.
First we create a new column in the Walks table to show the Distance.
Right Click in the Time column
Click 'Insert' from the menu
Click 'Table Column to the Left'
Type 'Distance' in the new column header.
Now we want to create a formula in the top row of the Distance column. This formula will use the INDEX function to extract a cell from the Distance column of tblRoute, and the MATCH function to determine the row within that column.
Select the cell in the new Distance column immediately below the header
Type "=INDEX("
Click on the Routes tab and select the range containing the distances (not the header)
Type ",MATCH("
Click on the Walks tab and select the top cell in the Route column (not the header)
Type ","
Click on the Routes tab and select all of the cells in the Route column (not the header)
Type ",0))"
Press Enter
This is a standard way to create formulas; but because each reference is within a table, instead of letters, numbers and dollar signs, we get a formula that's quite readable.
Note. Excel usefully copies this formula to all the other cells in the Distance column, which is the sort of thing that Excel tries to do everywhere, but it finds it easier to do in tables.
Step 4: Calculate Walking Speed
Now we have both the distance and the time in tblWalks we can calculate the Speed achieved on each individual walk. First we shall create a new column in tbkWalk
Select the Walks tab
Enter, "Speed" in the cell to the right of the "Time" column header. Excel will recognise this as a new column in the table
Now we will create a formula that calculates the speed from the distance and time. Because this is using two cells in the same table, rather than using the mouse and clicking between tables, we can use the cursor control keys.
Position the cursor in one of the cells in the new Speed column.
On your keyboard press the following keys: [=] [←] [←] [*] [←] [/] [2] [4] [Enter]
Again you will see that the formula Excel creates is familiar and easy to understand, but the cell value is not, as Excel has assigned the formatting from the adjacent cells. We can correct this as follows:
Select all the cells in the new column and apply the formatting string, 0.00 "kmh"
Step 5: Summarise Walks in the Route table.
Now we've got some more data in the Walks table, we're going to take the opportunity of summarising it in the Route table, so that we can see for example how many times we're taking each route, and what our average speed on the route is. To do this:
Using the above techniques, add two columns to tblRoute, as described below:
Notes
In typing these formula, you can set up get the named ranges by selecting them with mouse, which is not only quicker, but also less prone to typing error.
There are very many different Excel functions that can be of use here - far too many to me to list. But Microsoft have, of course, and their list of Statistical functions can be found here.
Step 6: Review Named Ranges
For each table that you declare, Excel provide the following Named Ranges, which can be used throughout the whole workbook.
You can also combine the above into, for example, a range that includes the data and the total, but not the header, or a range covering two or more adjacent columns.
Go to a blank cell near to a table
Type, "=", and then select a range from the table.
Observe the name of the range Excel inserts after the, "=", sign.
Step 7: Rearrange Rows and Columns
You have already seen how easy it is to insert columns into the middle of a table or to add them to the end. And Excel has made table manipulation very easy.
Take a closer look at the context sensitive menu that appears when you right-click on a table. Pay particular attention to the Insert, Delete and Select options
Select an Entire Table Column and see a green outline appear around the column.
Drag the green outline to the left or to the right (without going up or down) to reorder the columns
In a similar fashion select a Table Row and move it up or down.
Go to rightmost cell in the bottom row of data and press [Tab]. A new row will be created at the bottom of the table, moving the footer (if present) down to make space.
Step 8: Sorting and Filtering
When you create a table Excel will present filter buttons in each header. They can be switched off and on with the Filter button checkbox in the Table Design menu. And they can be switched off in a slightly more permanent way by clicking the Filter button in the Data menu. I often do this as the buttons in the header can distract, and they sometimes require the columns to take up more space. But if they are off now switch them on in the Data menu and/or in the Table Design menu.
Play around with the different options available by clicking on the Filter buttons in the column headers.
Filter out some of the rows, and see observe the difference between: (a) hiding the Filter buttons in the Table Design menu; and (b) turning filtering off in the Data menu,
Observe how sorting works on only one column.
Try the Advanced Sort option in the Data menu to sort to declare secondary (or tertiary) sort columns.
Summary
We have seen there are many good reasons to use Excel Tables.
Table styles allow professional presentation of tabulated data.
Named ranges make formulas far easier to understand.
Functions such as index, match and countif can join tables in meaningful ways,
It's easy to manipulate rows and columns, and to sort and filter them, too,
New rows are automatically created with the correct formatting and formulas.