Managing, analysing and referencing lists of data becomes easier when you know how to use Excel Tables.
There are so many advantages to using Tables which I will cover within this post. Once you see how helpful they are I’m pretty sure you will be using Tables a lot.
When you insert a Table over a list of data Excel automatically takes over and applies many features for you. You will also have many tools for working in lists made available to you on a contextual tab, making working with lists so much easier.
Here are just a few features that are applied or made available when you use Tables.
- Filter is automatically applied to your data
- Colour formatting in banded rows is applied to columns and rows
- Excel will automatically apply Freeze Panes when you scroll down.
- With one click you can quickly apply the SUBTOTAL function to filtered data
- You can name the Table range so that you can quickly identify and use the data within the table for VLOOKUP functions and Pivot Table reports
- Excel will instantly add additional rows and columns of data to the Table range
- Excel will instantly copy formulas to all cells within a column. Write it once and Excel automatically copies it to the bottom of the column.
Inserting, formatting and filtering a table
Firstly, make sure your data is set out in a format that is suitable for an Excel table, i.e. no empty rows or columns and a column header row at the top of the table data.
1. Select any cell in the data list.
2. On the Insert tab click Table. Excel will display the Format As Table dialog box. Ensure the data range is correct and click OK.
3. Excel will immediately apply a table format to the selected data and Filter will be turned on. The Table Tools contextual tab will be displayed. To change the table format select another format from the Table Styles gallery on the Design tab.
4. Use the filter arrows to sort and filter the data.
Tip: you can keep the Table formats but remove the Table functionality if you like. A lot of people do this so that they can quickly apply a Table Style to a list of data. However the other functionality isn’t required so they then remove it. To do this, on the Design tab click Convert to Range. The Table elements will be removed but the formats will remain.
Inserting a Total Row
Add a total row to your table by clicking the Total Row option in the Table Styles group. This will instantly add a row at the bottom of the table.
To insert a calculation to a cell on the ‘Total, row, click the drop-down arrow on the cell and then select the function you require.
A SUBTOTAL function will be created for the column meaning when the data is filtered only the total for the filtered information will be displayed.
Note: for more info on the SUBTOTAL function see my post Sum only visible rows when Filtering data in Excel.
Note: if your total returns an error you may need to fix something within the data. Check our my post 5 Reasons why your Excel filter may not be working.
Automatic Freeze panes
Note that when you scroll down the worksheet the column headings will move up into the column header bar. This is an automatic feature of working in Tables saving you the hassle of having to turn on Freeze Panes.
Creating a calculated column
To instantly copy a formula to every row within a table column simply write the formula in the first row of the column and press ENTER.
The formula will be copied to the last row in the column. Each time a new row is inserted into the column area the formula will instantly be copied into the row.
Expanding the Table range
To add data to the table range simply insert data in the row immediately below the table or in the column to the right of the table. The range will automatically be extended. If the table has a Total Row either place the cell selector in the last column of the last row and press TAB or copy and paste the data above the Total Row.
To manually extend or reduce the table range click and drag the handle on the bottom right corner of the table range. Inserting and deleting rows or columns within the range will automatically adjust the Table range.
Note: when you insert or delete rows and columns they are only inserted inside the Table area. Data in surrounding columns and rows outside of the table will remain static. Table ranges are added to the Name Manager dialog box on the Formulas tab.
To quickly remove duplicates from Tables, click the Remove Duplicates button on the Design tab. Select the column(s) that include duplicates and then click OK.
Using the Table name in a formula – BEST FEATURE!
In my opinion, one of the best features of using Tables is that they are dynamic ranges. This means that as you add more data (rows and columns) to the range they are automatically included. Therefore when you can use a the name of a Table in a formula as the Table grows you don’t need to reset your formulas to include new rows and columns. They are automatically included. Gold!
Each table is automatically given a name. In most instances this is ‘Table1’.
To rename a table on the Table Tools, Design tab type the name for your table in the Table Name box (very left of the Ribbon) and press ENTER. BTW, Table names cannot contain spaces and must begin with a letter or underscore.
Use the Table name in formulas to quickly identify a range. e.g. =VLOOKUP(L5,SalesData,2,0). And the excellent thing about this formula is that should the data in the Table grow, I don’t need to go back and reset my VLOOKUP Table Array range. So great!
Removing a Table from a data range
To convert a table back to a normal data range, select the Table and then on the Table Tools, Design tab click Convert to Range.
A prompt will ask you if you are sure you want to convert the table to a normal range. Click Yes. Formatting will remain. However, the Table features will be removed.
If you enjoyed this you may also enjoy...
Learn how to change a Pivot Table back to the classic display of version 2003 and earlier. If you were familiar with Pivot tables in Excel versions prior to Excel 2007 you will be very used to the classic display of working with the blue lines of the Pivot Table and the ability to drag and
If you want to save yourself a huge amount of time when reconciling data, learn how to use VLOOKUP to compare two lists or columns of data. Years ago the Training and Development manager of a large organisation invited me to spend some time in their Accounts department in order to observe how the Accounts team
There are times when you want to return a match for a VLOOKUP based on more than one value. For example, we may have customers who have multiple branches. Each branch is recorded under the same customer number. So how do we look up info for a specific branch if all of the branches are
To group dates by month in a Pivot Table is easy when you know how. Unfortunately, displaying date values in a Pivot Table can produce unexpected results. For example, in the Pivot Table below I have added a Date field to the Column area of my Pivot Table. Every date in the underlying data table is
Finding and removing duplicate data in Excel can be a quick process. However, for many people who haven’t learnt how to do this the quick way in Excel, it can be an absolute chore. One of the many things I love about my job is that I get to work with so many different people from
PROBLEM: having to retype text when your data is in UPPERCASE, lowercase or a Mix oF bOTH. SOLUTION: quickly change the text back to the proper case without having to retype it.