Insert a Table
Before you create a table, enter the data in the worksheet. When entering the data, don’t leave blank rows, columns, or cells in the block of data that will form the table. To create a table in Excel:
Table Features
Formatting a block of data as a table makes it easy to carry out a variety of tasks on the data without affecting other data in the worksheet. These tasks include sorting data, filtering data, quick calculations, adding column totals, and visually formatting the table. The most notable features that Excel adds to the block of data are:
The drop-down menus in the column headings that contain sort, filter, and search options. The alternate shaded rows that make it easier to read the data. The sizing handles located in the corners of the table. The Quick Analysis icon (in Excel 2013 and newer) that appears in the bottom right corner of the active cell when two or more cells in the table are selected. This makes it easy to analyze the data using charts, pivot tables, running totals, and conditional formatting.
Manage Table Data
Sort and Filter Options
The sort and filter drop-down menus added to the header row make it easy to sort tables in ascending or descending order, by font, or by cell background. You can also define a custom sort order. Additionally, the filter options in the menus allow you to:
Show only the data that meets the criteria specified. Filter by the font or by the cell background color. Search for specific records by matching individual fields of data.
Add and Remove Fields and Records
The sizing handle makes it easy to add or remove entire rows (records) or columns (fields) of data from the table. To resize the table, drag the sizing handle up, down, to the left, or to the right. Data that is removed from the table is not deleted from the worksheet, but it is no longer included in table operations such as sorting and filtering.
Calculated Columns
A calculated column allows you to enter a single formula in one cell in a column and have that formula automatically applied to all cells in the column. If you don’t want the calculation to include all cells, delete the formula from those cells.
Total Row Feature
The number of records in a table can be totaled by adding a total row to the bottom of the table. The total row uses the SUBTOTAL function to count the number of records. Also, other Excel calculations such as SUM, AVERAGE, MAX, and MIN can be added using a drop-down menu of options. These additional calculations also make use of the SUBTOTAL function. The Total Row appears as the last row in the table and displays the word Total in the leftmost cell and the total number of records in the rightmost cell. To add other calculations to the Total Row: Formulas that can be added to the Total Row are not limited to the calculations in the menu. Formulas can be added manually to any cell in the total row.
Delete a Table, But Save the Data
If you decide you don’t need the table for your data, you can delete it without damaging its contents using the following steps: Table features such as the drop-down menus and sizing handle are removed, but the data, row shading, and other formatting features are retained.
