Creating And Working With Tables in Excel
You may think that an Excel Workbook already provides a table of data to you, and in a sense it does, an area consisting of Rows and Columns, with column and row headings and values.
It is, however, possible to create formal Tables from ranges of cells on an Excel worksheet.
Why would we want to do this?
Well, here are a few reasons:
- Automatic Filtering & Sorts
- Predefined formatting and colour schemes to choose from
- Colour banded rows
- Table Headings
- Single table name available to address all the data in that region of a worksheet (dynamic table size)
- Automatic Totals
- Slicers
There are many other benefits too.
Let’s take a look.
Create A Table From a Range of Cells
The screen shot below shows some raw sales data that has been pasted into an Excel Worklbook.
We can quite happily work on this data without converting it to a table. We can:
- Add columns
- Add rows
- Enter formulae into Cells
- Format cells
- Add Validation Rules to cells.
And so on ……
But, ……..
Tables give us just a little bit more.
We will create a table from the above data.
On the Insert ribbon there is a Table option – shown in the screen shot below:
Highlight the area of the worksheet that contains the data along with the column headings and then click on Table:
A dialog pops up where you can confirm or alter the area containing the table data. You can also indicate if the table includes column headings (it does in this example).
Clicking on OK results in the table being created with a default Table Style:
Immediate benefits are:
- Formatted with a Table Style
- Banded formatting for alternate row shading
- Headings that remain frozen at the top of the table as you scroll down records
- Automatic filter drop downs for each column in the table
- Sorting options via the drop downs for each column in the table
- Table Name that can be used to address the whole table
Table Styles & Column Banding
The default blue based table style can be changed via the Table Tools Design Ribbon.
The Table Styles part of the ribbon offers quite a few options for formatting the table:
We will select one of the green themes:
The default styles include Column Banding to make it easier to identify values from a row.
Column Banding can be turned off via the Table Tools Design ribbon:
You can see that the Table Style Options section of the ribbon offers tick boxes for Banded Rows (on by default) and Banded Columns.
In the following example we have deselected Banded Rows and selected Banded Columns.
Table Headings
In the screen shot below you can see the default table headings:
When you scroll down the data rows the headings stay displayed at the top of the worksheet area (where you would normally see the Row Letters):
Table Filters & Sort Operations
Every column in the table gets automatic filtering and sort options via the drop down seen to the right of each column name:
Table Name
The Table is automatically given a name. This can be changed as shown in the following screen shot, to make it a more easily remembered and relevant name. The following example shows a table renamed as SalesData.
This table name can then be used in any formula or tool that requires the range of cells in the table. This range will also be dynamic so that as the table grows with new rows and columns they will be included in the referencing formulae and tools.
For example, we can use the table name when using the table data as the source for a Pivot Table:
Note that we have used the table name, SalesData, in the Table/Range field instead of referencing the Worksheet and Cell Range.
Here is a sample Pivot Table from this source:
Adding Automatic Totals to an Excel Table
On the Table Tools Design Menu the Table Style Options group has a “Total Row” tick box.
If you tick this box a total row is added to the bottom of the table.
Once the total row has been added you can click in any of the cells in this row and an arrow will appear to the right of it enabling you to access a list of available aggregate functions to choose from. This is shown in the above sreen shot.
The following example shows an average has been added to the Product Unit Price and a total has been added to the Total Sales Amount column:
As new rows are added to the table the totals are automatically updated.
Another big benefit of Excel Table Total Rows is that if filtering is in place the aggrgate function results are automatically updated to reflect the filtered rows rather than just displaying the overall aggregate values.
In the following example there is a filter on the Sales Territory Region column, so the Totals row is showing the average product price for Canada and the total sales amount for Canada:
..
Using Slicers With Excel Tables to Filter Data
Slicers are a wonderful way of filtering records. Typically they are used wioth Pivot Tables and Pivot Charts to slice and dice to looik at different sections of business data, whether that be a specific product, a specific set of years or regions, etc.
Slicers can be used with Excel Tables too. With the cursor located in a cell that belongs to your Excel table select the Table Tools Design ribbon. In the Tools section of the ribbon you will see an “Insert Slicer” option.
A dialog will open enabling you to select any of the available table columns to be added as slicers:
In the following example you can see that we selected the Promotion column and a slicer has been added to the worksheet:
The four promotion values that appear in the table data are listed and selected by default. Filtering of the table can be implemented by clicking on promotion values in the slicer – the records in the table the slicer is linked to will automatically be updated to refelct the filter. More than one value can be selected using a <CTRL> click.
We can see that the slicer has “No Discount” selected and the table indicates a filter on the Promotion column (note the funnel icon to the right of the column name).
You can have many slicers associated with a table.
I hope you have found this article useful.
If you would like to learn more about working in Excel click here to view our courses and dates avaliable.
Did you know that all of our courses are available to attend virtually through platforms such as Teams and Zoom. To find out more about any of our courses or virtual training please email us at info@ptr.co.uk and we would be happy to assist you.