Microsoft Excel – Format Table Feature

Microsoft Excel – Format Table Feature

Microsoft Excel Format Table

A useful feature of the Microsoft Excel spreadsheet is Excel Format Table. It is designed to assist with the analysis of a list of records.

1 List Tables

In Excel a list table, also known as a database (somewhat confusingly given that an Access file is also known as a database!) is a list of records about some particular subject; for example a list of products or suppliers. A typical layout for such a table is as follows:

The top row in a list table generally contains the column headings (if so it is known as the header row). The choice of columns is largely dictated by what data needs to be stored about the table’s subject. Good practice is for the first column to contain a value unique to each record. If so, it is known as the key column, and by default the records are sorted in ascending order of this column. It is also good general practice to split up the columns as much as possible so that each column contains the smallest meaningful data value; in the example above, the employee’s name has been split up into separate first and last names, rather than having a single column for the full name. Splitting up the columns in this way makes the table easier to analyse.

As a matter of terminology, columns in a list table are often referred to as fields. The latest versions of Excel can comfortably handle at least 500,000 records.

2 Applying Excel Format Table

To apply Excel Format Table, select any single cell in the table, and choose Home>Styles>Format Table. Select any style from the palette displayed:

On selecting a style the following dialog box is displayed:

Note that there is no need to pre-select the whole table; simply select a single cell anywhere in the table. Excel will scan the table, searching for its boundaries, and will select it automatically. (NB. This applies to other Excel features designed to be applied to a whole table, such as Insert Pivot Table).

The chosen style is applied to the whole table and, if a cell in the table is selected, an extra contextual tab “Design” is displayed in the ribbon:

Note also that Autofilter is switched on; an autofilter drop-down arrow is displayed next to each column heading.

3 Display features

When using Excel Format Tavle the applied style may easily be changed by selecting an alternative from the Table Styles palette. In addition, if you scroll downwards then the column headings (also known as field names) replace the worksheet column letters, thereby obviating the need to freeze rows:

4 Adding and removing rows and columns

Editing the data in such a table is easily done. If an extra column is required then it can either be added to the right-hand end of the table or inserted anywhere within the table. For example, suppose all delegates are to receive a bonus of 10% of their gross pay. The heading is entered to the right of “GROSS PAY” and the table boundary is automatically extended to include this:

The formula is inserted and, on entering, is automatically copied down the column:

It is only necessary to format the column. Note the use of the field name “@[GROSS PAY]” rather than a cell address.

Alternatively, a column may be inserted anywhere in the table by selecting a cell in any column, right-clicking and selecting Insert>Table Columnsto the Left:

To delete a column, right-click on any cell in it, and choose Delete>Table Columns. Note that this will only delete the cells from the list table, not the whole worksheet column. This also applies to deleting rows.

A row may either be added to the end of the table or inserted anywhere in the table by right-clicking and selecting Insert>Table Rows Above:

If a row is added to the base of the table then, as for columns, the table range is automatically extended:

Calculated columns, such as GROSS PAY, are automatically copied down:

5 Total Row

If the check box labelled “Total Row” is selected, then an extra row is displayed at the base of the table. Excel generally selects a suitable numeric column to sum:

However, it is an easy matter to edit the calculation and add further calculations to other columns. If the above GROSS PAY sum is selected and the drop-down arrow clicked, the following menu is displayed:

A different calculation, for example AVERAGE rather than SUM may easily be chosen:

Note: The COUNT calculation in the list is not the same as the =COUNT() function, which returns the number of cells in the specified range(s) that contain numbers. The Total Row COUNT returns the number of cells in the specified column that are not empty. Hence, if applied to any column where there are no gaps it would return the number of records.

Further Total Row calculations may be added by clicking in the appropriate cell in the Total Row and selecting the required calculation from the drop-down menu. In the example below SUM has been selected for GROSS PAY, AVERAGE for HRS and HOURLY RATE and COUNT for DEPT:

This tells us that there are 95 employees with a total wage bill of $49,078.98, an average hourly rate of $13.94 and working an average of approximately 35.9 hours.
The Total Row may easily be switched on or off via the check box. If switched off (e.g. to facilitate adding an extra record at the table base) then later switched back on, the same settings will be applied; Excel “remembers” the choice of calculations.

6 Filtering records

Filtering a table involves displaying just some of the records; those that satisfy the filter’s criterion. A criterion is a set of one or more conditions. A condition may be a simple test (e.g. DIVISION=”Great Britain”) or it may be more complex.

An easy way to filter records is via Autofilter. This may be switched on (or off) via the Filter toggle button on the Data tab:

It is automatically switched on when Format Table is selected. When an Autofilter arrow is selected, a suitable menu is displayed from which one or more items may be chosen:

If the table is filtered, then the values in the Total Row are recalculated based on the visible records, allowing easy subtotalling. For example, the list below has been filtered to display the employees who work in either the History or Science departments of the British division:

The Total Row displays the appropriate values.

For numeric or date columns other conditions may be chosen. For example, for GROSS PAY:

As well as filtering for one or more specific values, it may be filtered for (e.g.) values above or below the average for the column overall. The Top 10 Filter allows the user to specify the top or bottom 10 (or other specified number) of items (i.e. records) or percent of records:

For dates, one or more individual days, months or years may be selected, as well as records where the value is before or after a specific date or within a specified date range. The following shows the full drop-down list.

Autofilter can also be used to display records where a particular column is empty by selecting the “blank” option. This is only displayed in the autofilter list if for one or more records nothing has been entered in the column:

To “unfilter” and hence display all records, select “Clear” from the Sort and Filter group:

The autofilter arrows may also be used to sort by a different column:

You can also switch off the “Format Table” feature by selecting “Convert To Range” from the Design tab. It may easily be switched on again later to the benefits of Excel Format Table!