7 Tables
Much information in Excel is kept in tables with data, a kind of database in a worksheet. Although Excel is not a real database program such as MS Access, Excel can be used for simple databases. These simple databases consist of one table such as address lists, phone lists, etc. The name database is usually reserved for external files from specific database programs. A table with data in Excel was formerly called a list.
Working with well structured tables is a common activity, especially at data analysis. A structured Excel table is also a very good basis for creating pivot tables.
7.1 Characteristics of tables
A table is an organized set of data.
| customnr | name | date | price | discount |
|---|---|---|---|---|
| A104 | Anderson | 2010-10-02 | 400 | 0 % |
| K102 | King | 2010-11-03 | 395 | 5 % |
| S501 | Smith | 2010-12-04 | 375 | 8% |
A table or database in Excel is a table consisting of rows and columns. These rows are often called records and the columns are called fields. The type of the collected data is in the columns. In the first row of a table, you find column headers that indicate the kind of information in the column. The column headers are also called field names. The field names in Table 7.1 are: customnr, name, date, price and discount.
To analyze data, it is important that the data is well-structured in the worksheet. A structured table has the following characteristics:
The cells in each column contains the same type of data.
The cells in each row contain the individual data of the object.
The first row of the table contains the unique names of the fields. These names are usually formatted differently (larger font size, bold, italic, … ) than the rest of the table.
There should be no blank rows in the table. A row can contain empty cells, but not all cells in a row can be empty.
There should be no blank columns in the table. A column can contain empty cells, but not all cells in a column can be empty.
A table can start everywhere in the worksheet. It’s not necessary that the first row of the table must be the first row of the worksheet.
In Excel, a table is a rectangular range of cells where data is stored. Although a table looks much like a regular Excel range, it offers a number of features that differentiate it from a range.
A table can be created and maintained by just typing the data in the cells. In order to ensure a good structure, it is recommended to use a form for data entry. It’s not necessary that you first enter all your data before converting the range to a table. Once you have the table, you can always add new rows and columns. Large lists of data usually come from data from external files (text/csv files, web queries, databases).
In Excel, you cannot create a table from scratch and then fill that table with data. Instead, you must first create a range that includes at least some of the data you want in your table and then converting that range to a table. When a range of data is transferred to a table, the appearance changes, but also built-in features are available for filtering, sorting, and the ability to add a total row with several functions for totalization.
By default, an Excel table will expand automatically, and fill formulas down to the last row. Examples:
You can add new data in the row immediately below the last row of the table, or in the column to its immediate right. The table will expand automatically to include the new data.
When you enter a formula in de first cell of a new blank column, then after pressing Enter that formula fills down to all the remaining rows.
7.2 Form Button
An important task in working with tables is the correct entry of the data. Excel has a built-in form for this task. The command button for creating such a form is however not standard on the ribbon. In the following steps, you see instructions on how to add the button Form to the Quick Access Toolbar. This is a one-time action.
Task 7.1
- Click on the Customize Quick Access Toolbar arrow at the end of the Quick Access Toolbar.
Choose More Commands ….
Click on the arrow for Choose commands from and choose Commands Not in the Ribbon.
- Browse the alphabetic list with commands and select [Form…**
- Click on the button Add and then OK.
In the Quick Access Toolbar, you can see the button Form now.
7.3 Data Entry Form
To perform this task it is necessary that the button Form on the Quick Access Toolbar is available, see Figure 7.4. When this button is not visible you must enable it first through Task 7.1.
The easiest and best way to add records to a table is by using a data entry form. Excel automatically creates such form for you if you provide the field names of the columns and type in the first record. In the following figure, you see an example of a purchase list. The data of the first article have already been entered, including the necessary formulas in F2, G2, and H2.
Task 7.2 File: Purchases.xlsx
Open the file.
Enter the following formulas:
- F2: formula
=D2*E2 - G2: formula
=21%*F2 - H2: formula
=F2+G2
- F2: formula
Select any cell in the list.
Click on the button Form in Quick Access Toolbar.
The data form appears. On the left side are the 8 field names. Besides 5 field names are entry boxes. Here you can enter or change the data. The field names total excl., vat, and total incl. don’t have boxes but values. This is because those values are calculated through formulas.
- Click the button New and add two new records, see the example in Figure 7.7). End your last record entry with Close.
Use the Tab key or the mouse to navigate to the next field. When using the Enter key you go to the next record and when there is no record you get an empty form for adding a new record.
7.4 Searching data with a form
A data form is very useful to search for a range/table for records that meet specific conditions. You will learn this in the following task. At the end of this task, you will find examples of how criteria can be applied.
Task 7.3 File: Personnel.xlsx
Open the file.
Select any cell in the data area.
Click on the button Form. The data form appears.
Click Criteria. The form is cleared and can be used to fill in the search criteria.
Search for department
Accountingand divisionPrinters(see Figure 7.9).
Click Find Next. The data of the first person who meets the criteria is shown.
Browse using the buttons Find Next and Find Prev through the list. There are a total of 5 people matching your criteria.
7.4.0.1 Examples of search criteria
In the Table 7.2 are some examples of search criteria. Try this out and verify that the found records match the criteria. It is also possible to combine multiple criteria. Always first make the criteria list empty before you start with a new exercise.
| Field | Value | Explanation |
|---|---|---|
| Last name | Ja | Searches for people whose last name starts with “Ja”. |
| Last name | *os | Searches for people with “os” in the last name. |
| Salary | >70000 | Searches for people with a salary greater than 70000 |
| Start date | <1/1/1990 | Searches for people with a start date before 1/1/1990 |
7.5 Consolidating ranges
Similar data in different ranges can be summarized (consolidated) into a new range. In practice, the separate ranges are often in separate worksheets and the merged range is placed in a new worksheet.
When you consolidate the source data, you apply a summary function (SUM, AVERAGE, COUNT, …) to create the summary data. To consolidate ranges, the columns must have a heading, a label.
Task 7.4 File: Consolidation.xlsx
Open the file.
Select cell A1 in the worksheet Total.
The lists to summarize are in the worksheets PRA and PRB. The consolidated data will be put in the worksheet Total.
- Choose Data > Consolidate (group Data Tools).
Check that the function
SUMis selected. If not, select this function.Put the cursor in the box Reference.
Select worksheet PRA > range A1:B7 > Add and click Add.
Select worksheet PRB > range A1:B7 > Add and click Add.
Check Use labels in Top row and Left column.
- Click OK.
The aggregation is static. When the source data change, the consolidation results will not, then you should perform a new consolidation. Using pivot tables is often a better choice to summarize data.
7.6 Convert Range to Table
By converting a range or list to a real Excel table, you can then use the extra possibilities that tables have.
Task 7.5 File: Candyboxes.xlsx
Open the file and select a cell within the data range.
tab Insert > Table
The Create Table dialog box appears. If you previously selected a cell with data, the address of the range to be converted is already filled in. If not, or if it is incorrect, you still need to select the correct range of data, including the headers.
If your range has labels that you want to use as column headers, check the option My table has headers. When there no labels, then uncheck this option. Excel adds automatically headers to each column with generic names: Column1, Column2, …
It is strongly recommended to always provide your own clear headers.
- Click OK.
After a range has been converted to a table, the following changes are visible:
A table format (style) has been applied. You can change this to a different format if you wish.
Each column header now contains selection arrows for filtering and sorting.
A tab Table design has been added. It only appears when a cell in the table is selected.
7.6.1 Table Design
With Table design you can control various things for the table.
Each table is automatically provided with a Table name. The default names are Table1, Table2, table3, … It is recommended to change the default name to a meaningful name that reflects what data the table contains. This name is also used in references to the table and operations with the table.
You can change the name in the Table name box.
7.7 Table operations
7.7.1 Selecting rows/columns in a table
Right‐click a cell in the row or column you want to select. Then choose Select and then choose one of the possibilities:
- Table Column Data
- Entire Table Column
- Table Row
7.7.2 Inserting a table row/column
Right-click a cell, choose Insert and then one of the options:
- Table Columns to the Left
- Table Rows Above
When you select a cell in the last row or last column, you also have the options
- Table Column to the Right
- Table Row Below
At the last cell, at the bottom right, you have both possibilities.
Fast way to add a new row: Select the bottom-right cell and press the Tab key.
7.7.3 Deleting a table row/column
Right-click a cell in the row/column you want to delete, choose Delete and then one of the options:
- Table Columns
- Table Rows
7.7.4 Convert a table to a range
Select a cell inside the table. Choose tab Design (Table Tools) > Convert to Range (group Tools) and confirm the operation.
7.7.5 Apply a table style
Select a cell inside the table. Choose tab Design. Select a style from the Table Styles gallery.
7.8 Sort a table
Sorting is a common action associated with lists. If a cell in a column is selected, clicking the sort button will sort the whole table. It will be sorted in ascending or descending order according to the values in that column.
Restoring the table in the original order is only possible using the Undo button on the Quick Access Toolbar. An alternative is before starting to create a temporary new column with sequential numbering. By sorting this column afterward, you are back to the original state.
Task 7.6 File: Personnel.xlsx
Open the file.
Convert the data range to a table (see Section 7.6.
Click on the arrow beside the field name Department and select
Sort A to Z.
The table is sorted in ascending order of the column Department.
- Click on the arrow beside the field name Division and select
Sort Z to A .
The table is sorted in descending order of the column Division.
You can sort a table on multiple levels, based on the values in different columns. One of the columns will be the first level where you sort on. After that, you can add new levels of sorting. Another column provides the second level of sorting, etc.
- Select any cell in the table and click Home > Sort & Filter (group Editing) > Custom Sort ….
The dialog box Sort is displayed where you can specify the sort conditions.
Choose Sort by Division.
Click on Add Level and choose to sort on Department next.
- Click OK.
First, the values in the column Division are sorted in ascending order, then on the values in the column Department.
7.9 Filter a table
When filtering a table only those records that meet certain conditions are displayed. The other records are hidden.
Task 7.7 File: Personnel.xlsx
Open the file.
Convert the data range to a table if it isn’t already (see Section 7.6).
Click on the arrow beside the field name Division and only select
Copiers, then OK.
Only the records for the Copiers division are now displayed. The arrow at the column header Division is changed in the filter symbol 
- Now refine your selection by filtering on the Accounting department.
In the status bar at the bottom is displayed how many records are found.
- lear the filter with Data > Clear (group Sort & Filter).
7.9.1 Number filter
When the contents of a field are numbers, specific filter actions can be performed. Such filters fall under the category Number filters. The following example will be used to determine the top 10 salaries.
Task 7.8
- Click on the arrow beside the field name Salary and choose Number Filters > Top 10….
Instead of 10 you can also choose another number. And Top can be changed to Bottom.
Click OK. The 10 records with the highest salary are displayed now.
Clear the filter with Data > Clear (group Sort & Filter).
7.9.2 Custom filter
If you want to use filters on the number fields other than the default filters, you will need to use a custom filter. The following example will be used to show the records of all people with a salary from $ 20,000 to $ 25,000.
Task 7.9
- Click on the arrow beside the field name Salary and choose Number Filters > Custom Filter….
In te dialog box Custom AutoFilter you can specify the filter conditions.
- Create the two conditions for the salary as shown in Figure 7.18.
Click OK. 10 records are found.
Clear the filter with Data > Clear (group Sort & Filter).
7.9.3 Date filter
When the contents of a field are dates, specific filter actions for dates can be performed. Such filters fall under the category Date filters. The following example will be used to determine the records with a start date in September.
Task 7.10
- Click on the arrow beside the field name Start date and choose Date Filters > All Dates in the Period > September.
11 records are found.
2.Clear the filter with Data > Clear (group Sort & Filter).
7.9.4 Slicers
A slicer is an object in Excel with which you can also filter your data. It shows all possible values from a selected column and each value is displayed as a button in the slicer. Working with a slicer is faster than previous filter possibilities and you immediately see what is being filtered.
A slicer can be used with both tables and pivot tables (see Section 13.5.2). It looks like that the slicer floats above the spreadsheet, so it is always visible and ready for use.
Task 7.11
Select any cell in the table.
Choose tab Table Design > Insert Slicer (group Tools).
This opens the Insert Slicer dialog and displays the column headings (fields) for which you can insert a slicer. You can select one or more fields.
- Select the fields Department and Division and click OK.
Two slicers are now inserted. When the slicers overlap, move them so that they are next to each other, see Figure 7.19. Both slicers show all unique values for that field.
Click in the slicer Department on button Marketing. Only the data for Marketing is now displayed.
Cancel the filtering by clicking the Clear filter button (the one with the cross) in the top right corner of the slicer Department.
Click in the top right corner of the slicer Department on the button Multiple selection, Filter now on Administration and Marketing.
The buttons act like switches. Clicking a button successively toggles the filtering for that value on and off.
Select now Printers in the slicer Division.
Experiment with the slicers. Optionally, add a third slicer for column Function. Finish by clearing all filters.
7.10 Calculated Column
When you enter a formula into an empty table column, then that formula will automatically expand to the rest of the column. You don’t need a Fill or Copy command. Such a column is called a calculated column. And when you change a formula, then this change is automatically expanded to the rest of the calculated column.
The easiest way to create a new calculated column is to start typing in the column immediately to the right of the table. The table will automatically extend.
Task 7.12 File: Personnel.xlsx
Open the file.
Convert the data range to a table if it isn’t already (see Section 7.6).
Select cell I2 and enter the formula
=ROUND([@Salary],0). A new table column with the calculated values is created.
References like [@Salary] are called structured references, which is unique to Excel tables. The structured reference format is what allows the table to use the same formula for each row.
Change the column header in
Rounded Salary.Select a numeric cell in the new column, Right-click > Select > Table Column Data.
Change the cell format in
Accounting, 0 decimals.Select cell J2 and enter the formula
=YEAR(NOW())-YEAR([@[Start date]]).Change the column header in
Age.
7.11 Task: Total Row
You can summarize numeric data in a table with a subtotal that appears at the bottom of the table. Although the word subtotal implies that you are summing the numeric values in a column, Excel uses the term more broadly. That is, a subtotal can be not only a numeric sum, but also an average, a maximum or minimum, a standard deviation or a variance, or a count of the values in the field. The calculation is based on the visible cells in the table’s column.
Task 7.13 File: Personnel.xlsx
Open the file.
Convert the data range to a table if it isn’t already (see Section 7.6).
Choose tab Table Design > Check Total Row (group Table Style Options)
A row Total is inserted at the bottom of the table and a SUBTOTAL function is added below the last column. In this case, the function is meaningless because the last column is a date column.
Select in row Total the cell below column Start date, the selector
and choose None.Select in row Total the cell below column Salary, the selector
and choose SUM.
The total salary is $ 4,874,037.39.
7.12 Exercises
Exercise 7.1 Selecting (list001)
The following table lists the marks of some students.
Now apply a filter so that only the rows are displayed for students who have no rating or a mark 3 or lower. For this exercise, the table is kept small. For your solution, you should take much larger tables into account.
File: List001.xlsx
Exercise 7.2 Consolidating examination results (list002)
The following table shows the results of a group of students for an examination and a re-examination. Furthermore, you see the final result, obtained through consolidating the data. This final result is the highest rating.
Enter the data for the examination and re-examination in a worksheet. Determine the final result through consolidation.
File: List002.xlsx
Exercise 7.3 Consolidating sales data (list003)
The following table lists the sales numbers of bread types in the North and South branches of a store. Furthermore, you see the total sales numbers obtained by consolidation.
Enter the data for the two branches in a worksheet. Determine the totals through consolidation.
File: List003.xlsx
Exercise 7.4 Filtering overview cars (list004)
In a worksheet, you can find some data of cars. A customer is interested in cars that meet the following conditions:
- 6 cylinders
- top speed of at least 180 km/hour
- petrol (ordinary or super) as fuel
Make a list of all the cars with the corresponding data that meet these conditions.
File: Car.xlsx
