Excel > Excel 2007 Advanced > Data Management

Excel Tables

An Excel table is a collection of related data organised into rows and columns. It is an incredibly powerful feature that anyone using Excel should have a look at.

Excel will handle everything for you when you use Excel tables. When you insert new columns or rows to the table, they are picked up automatically. No need to update your cell references, formulas or formatting.

There are many benefits to using tables with your data. We will look at just a few features of Excel tables, but there are more just waiting to be tried out.

Create a table

  1. Select a cell within the table of data
  2. Click the Insert tab on the Ribbon
  3. Click the Table button from the Tables group
  4. The Create Table dialogue box appears displaying the range of cells to be used for the table.

    Create Table dialogue box

    If this is incorrect, select the range of cells you want to use

  5. Click Ok

Tables can also be created by clicking the Format as Table button on the Home tab of the Ribbon.

Formatting a table

Table formatting can be changed at the click of a button by selecting one of the built-in table styles

  1. Select a cell in the table
  2. Click the Design tab under Table Tools on the Ribbon
  3. Select a style from the Table Styles gallery. Click the More button to see the full gallery of options.
  4. Excel table styles gallery

The formatting applied to the table depends on the settings in the Table Style Options group of the Design tab. The options in here include banded rows (formats alternative rows in a different colour) and using a total row. When new rows are added to the table Excel will re-apply the formatting, saving you the work.

It is possible to create your own table styles and store them in the gallery for future use.

Using formulas with tables

Excel tables completely change the way you reference formulas. Column labels can be referenced in formulas meaning no need for confusing cell references and easier to understand formulas.

This also means that there is no need to copy formulas or update references within tables. When you write a formula in one cell, Excel copies it to the rest for you. And when you add new rows it copies the formula into the new row for you too.

  1. Select the cell where you want to enter the formula
  2. Enter the formula with references to column labels enclosed in square brackets. When you enter a square bracket, Excel provides a list of the columns headings to select from
  3. Selecting column labels in a formula

  4. Press Enter when the formula is complete and the formula result is replicated in every cell of the column in the table. No need anymore for copying that formula down
  5. Table formula

Adding totals to a table

Totals can be added to a table quickly and easily without the need to enter any formulas. The function can then be switched from Sum to Average, or Average to Count with just the click of a button.

  1. Select a cell in the table
  2. Click the Design tab under Table Tools on the Ribbon
  3. Check the Total Row box in the Table Style Options group
  4. A total row is added to the bottom of the table.

  5. Select the cell in the total row for the column you wish to total and click the list arrow that appears
  6. Click the list arrow and select the function you wish to use from the list
  7. Table totals

The function used can be changed simply by clicking the list arrow again and selecting a different function

Converting a table to a range

Unfortunately for those using previous versions of Excel, tables is a new feature to Excel 2007. Therefore before sending this file to someone using a previous version of Excel, the table will need to be converted back to a range.

  1. Select a cell in the table
  2. Click the Design tab under Table Tools on the Ribbon
  3. Click the Convert to Range button in the Tools group
  4. A message will appear questioning your sanity for wanting to do this
  5. Convert table to range message

  6. Click Yes and the table is converted back to a range
  7. As we saw earlier this range can easily be converted back to a table if needed in just a couple of clicks

Other table features

Other advantages to using tables include:

  • The table is assigned a name that can be used when selecting the table or when refering to it within a formula
  • The table name can be changed from the Design tab on the Ribbon

    Table name

  • The table can be summarised in a PivotTable
  • Duplicates can be removed from the table
  • The table can be printed without the need to select a print range. Simply select a cell within the table, open the print dialogue box, select Table from the Print what panel and click Ok
  • Printing a table

Follow us on

Facebook  Twitter  You Tube