Using tables in Excel

A Table is a range of cells that hold data. Each row corresponds to a single entity/record.

When you create a Table, you can manage and analyse the data in that Table independently of any data outside the Table. You can give the Table; a common style, filter the data, automatic Totals, and add and calculate columns easily.

Create and modify a Table

To create a Table:

  • Select a range
  • From the Home tab, in the Styles group
  • Choose the Format as Table command and select a style
  • Check the Table reference & if required, tick My Table has headers
  • Click OK

To change the Table style:

  • Click inside the Table
  • From the Design tab, in the Table Styles group, choose another style

Data Filters and Sort Options

Each data Table comes with filters and sorting options so that you can filter and sort the data in that Table independently of other Tables. Normally, Excel does not permit more than one set of filters per sheet.

To give your Table a Name:

  • Select the Table
  • From the Design tab, in the Properties group, type the name in the Table Name box

Automate the Totals

You can add "Totals" row to your Table with just a click.

To add a total:

  • From the Design tab, in the Table Style Option group
  • Choose Total Row and the last row becomes a totals row. If your formula does not appear click in the cell under the row you want to add the formula, and choose from the list.

The summary type does not have to be "Sum" there are other choices.

Add additional Columns or Rows

To insert columns (or rows):

  • Click the header/s (letter or number) to select the column/s (or row/s)
  • Right mouse click the selection and choose Insert, Excel includes the new column/s (or row/s) into the Table

To insert additional columns or rows at the end:

You can add additional columns (or rows) outside the existing Table. Excel connects the new column (or row) to the Table.

  • Simply entering data (or heading) in the adjoining (next) column
  • You can do the same thing to add Rows at the end. If you have a Totals column, just insert a row above the Totals and enter data.

Make Calculated Columns

With structured references, you can make calculations in columns and Excel automatically fills the formula in the rest of cells in that column.

To add a calculated column:

  • Add a column to the right of the Table (as above), it will be automatically formatted
  • In the first cell of the column (under the heading), enter the formula
  • Press Enter and the formula will fill down to the lasts cell (row)

Create Pivot Tables

Because your data is in a Table format its easy to create a Pivot Table.

To create a pivot table:

  • From the Design tab, in the Tools group, choose Summarise with PivotTable. Excel will take care of the formulas and change the references to cell references.

Remove Duplicates 

The Table format makes it simple to removed duplicates.

To remove duplicates:

  • From the Design tab, in the Tools group, choose Remove Duplicates. The Remove Duplicates dialog box appears
  • Choose My Data has headers (if it does)
  • Click the Unselect All button
  • Choose the field/s you want to chase the duplicates (e.g. Student No)
  • Click OK and a Message box appears informing you how many duplicates were found and removed
  • Click OK
  • If you have made a mistake simply use Undo

Convert Table back to a range

If you do not want to use a Table, you can revert back to the original sheet type called Named Ranges.

To convert named ranges:

  • From the Design tab, in the Tools group, choose Convert to Range. Excel will take care of the formulas and change the references to cell references. The Table style (colours) remain.

Print the Table

To print the table:

  • Click in the Table
  • Use Ctrl P (or from the File tab, choose Print ) The Print Preview and Print window opens
  • Under Settings in the first box (it probably says Print Active Sheets)
  • Choose Print Selected Table
  • Then click Print