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,
Step4. Verify that the "My table has headers" checkbox is selected.
Step5. To complete the table creation,
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,
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,
NOTE: Pre-existing fill colors that are applied manually will not be affected by styles.
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".
Step2. Find and replace the following embarkation codes with these port names:
|Code||Port Name||Number of Items Replaced|
Step3. Save the workbook, TitanicList.xlsx.