Working with an Excel Table

We can easily turn a range of cells into an Excel table. Tables can be managed independently from other data in the worksheet.

When a range is identified as a table, Excel can perform certain actions with that table. Several characteristics of a table are:

• Activating a cell in the table gives you access to a new Table Tools contextual tab on the Ribbon.
• Each column header contains a drop-down list, which can be used to sort or filter data.
• As you scroll down the worksheet, the table headers replace the column letters in the worksheet header.
• A Total row can quickly be added to a table to calculate any column.
• A single formula is automatically propagated to all cells in the column.
• Rather than using cell references, you can use table names and column headers in formulas.
• Duplicate rows can be removed automatically.
• As rows or columns are added to or deleted from the table, the table range will adjust accordingly.

When you explicitly identify a range as a table, Excel knows what to do when you perform actions within a column. For example, if you apply currency formatting to a column and then add new rows, Excel applies the same formatting to the new values in that column. The same thing applies to other operations, such as conditional formatting, data validation, etc.

Creating a Table

When a range of cells is made into a table, data in that table can be analyzed and managed independently of data in other areas of the worksheet. We can create a table from an empty range or from an existing range of data. The data can be easily filtered, summarized with a total row, and formatted.

Let's make this data range a table.

Step1. Verify that a cell within the data range of the AllPass worksheet is the active cell.

Step2. To select the Insert tab, on the Ribbon,

Click the Insert tab

Step3. To create the table, in the Tables group,

Click

You see the Create Table dialog box. Excel guesses the intended range of cells for the table. If the range is incorrect, you can change the description. The dialog box also allows you to indicate the presence of a header row in the range.

Step4. Verify that the "My table has headers" checkbox is selected.

Step5. To complete the table creation,

Click

The range of cells now displays the default table style. The headings in row 1 display drop-down arrows for use in filtering the data.

Step6. To deselect the table,

Click any cell in the table

Changing the Table Style

When we created the table, a new Design tab appeared under the contextual tab, Table Tools. Let's use its tools to modify the style for this table.

Step1. Verify that the Design tab is selected on the Ribbon.

Step2. To view the various styles, in the Table Styles group,

Click

NOTE: The Live Preview feature in Excel 2016 for Windows permits you to preview a style before actually selecting it. It is possible to chose one style, then apply a different one.

Step3. To choose a different style,

Clickany style

NOTE: Pre-existing fill colors that are applied manually will not be affected by styles.

The entire table is currently selected.

Replacing Category Codes

We want to do some light Find and Replace in the data, to replace the single letter code for the city of embarkation with the actual city name.

Step1. To select the desired column of data,

Click on the column header K

NOTE: Do not try to select the Table column label "embarked".

Here is the data needed to perform and confirm the Find and Replace activity.

Step2. Find and replace the following embarkation codes with these port names:

Now the Titanic list of passengers is ready for further analysis.

Step3. Save the workbook, TitanicList.xlsx.

We have seen two large datasets, each with data entry done by multiple people. Excel provides features which can help restrict how data may be entered, so that it can be performed more consistently. Having consistent data makes analyzing it easier. Let's explore some of these features.