We can easily turn a range into an Excel 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.
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 is a drop-down list that contains sorting and filtering options.
- 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.
- Each table becomes a named range as well as each column in the table.
- Duplicate rows can be found and removed automatically.
- As rows or columns are added to or deleted from the table, the table's named 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 a certain 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 and data validation. In short, tables make many Excel tasks easier to accomplish.
Creating a Table
As we examine the data, we can see that the fields are consistent and the set is large. This dataset would be much easier to handle if we were to convert it to a table.
Let's make this data range a table.
Step1. Verify that any cell in the range of data is active.
Step2. To select the Insert tab, on the Ribbon,
Click the Insert command 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 cells,
Clickany cell in the table