Tables in Excel

What are Tables

Tables are Excel’s way of allowing you to manage and analyse a group of related data in a structured way. You can turn a range of cells into an Excel table at the click of a couple of buttons & this then provides a wide range of analysis possibilities. They also make you focus on keeping your input data away from your calculations, leading to much neater spreadsheets.

What are the benefits of using tables?

There are a number of key benefits to setting up your input data in tables…

  1. The table is given a name (starts with “Table1” & increments as you add more) which you can change to something more memorable (I usually prefix with “tbl” so, for example a table of customers would be named tblCustomers). As more data is added, the range automatically expands to include it (whether adding more rows of data or additional columns)
  2. As the range automatically expands, if you refer to it in a VLOOKUP() Function or Pivot Table, etc, then your function or analysis will always be looking at the latest set of data, you don’t need to go back & change the range
  3. As you scroll down your data, the column letters (A, B, C, etc) are replaced by the headings from your table for ease of data input
  4. Autofilter arrows are automatically added to column headings
  5. When adding a calculation in a column of your table, it’s automatically copied into all of the relevant cells of that column & if you add a new row of data, the formulas are already in place
  6. You can add totals ate the foot of your table by adding in a Total Row which will automatically take account of new data

Are there any downsides of using tables?

The main one is that, at first glance, formulas look a little “odd”… rather than seeing, for example =F2*G2 your formula will be something more like =[@[Qty Sold]]*[@[Sold Price]] where the “@” symbol relates to “this row” & the items in the inner “square brackets” Qty Sold and Sold Price relating to the name of the field (i.e. the column name); these are known as Structured References. The reason why there is an “inner” set of [] is because there’s a space between each of the words & it’s Excel’s way of showing that this relates to the same thing. If you want to avoid that, then removing the space between each of the headings would mean that the formula above would be =[@QtySold]*[@SoldPrice] which is slightly neater.

Basic rules to follow when using tables

  1. Every column of your table needs a name that must be unique (but that’s just common sense!). If you insert a new row, to preserve this, Excel will initially call it Column1 which you can then change.
  2. The first row of your table should be the headings of each column (only use a single row for this, don’t try to merge cells, if you want the appearance of more than one row tell Excel to Wrap the text in the cell or use ALT+Enter to insert a line break within the cell
  3. Remove any complete blank rows or columns (sometimes users have a blank row to distinguish between different months, but this isn’t necessary & can cause problems in using your data effectively)

Creating a table

  1. Having selected a single cell in your data
  2. From the Home Tab
  3. Select Format as Table

Select the style that you want to use (this can be amended later) & Excel will bring up a prompt…

If you have no complete blank rows or columns in your data, this will have automatically selected all of your data. If there are some blank rows or columns, it’s a good idea to delete them first so that this process is automatic

Your table is now set up

  1. Filter arrows are added to column headings
  2. A Table Tools Tab has been opened
  3. The Design Tab has all of the initial options that you can use within your table
  4. It has been given a name (which you can change)
  5. You can choose whether to have a Header or Total Row, Banded Rows or Columns, etc

Changing the name of your table

To change the name of your table, you simply type the new name in the Table Name box (No. 4 above) & hit the ENTER key

I always prefix the name with tbl to differentiate it from other named ranges that I may have

Adding more data

To add more data to your table, you could insert rows & input the information (in the same way as with a normal list), however, the most effective way is to go to the last row & right most column & then use the TAB key

This will then take you to the first column of your table, but on the next row. In addition, the Totals Row (if you’ve used one) will have moved down a row automatically to accommodate your new row of data

Next time, we’ll look at some of the ways that we can use the features within Excel to begin to analyse our Table