An important but often underutilized feature in Excel is tables. This tip describes when to use a table and also lists the advantages and disadvantages.
Understanding what a Table is:
A table is a rectangular range of structured data. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a bank transaction, an employee, or a product. Each column contains a specific piece of information. For example, if each row contains information about an employee, the columns can contain data, such as name, employee number, hire date, salary, or department. Tables have a header row at the top that describes the information contained in each column.
You’ve probably created ranges that meet this description. The magic happens when you tell Excel to convert a range of data into an “official” table. You do so by selecting any cell within the range and then choosing Insert ➜ Tables ➜ Table.
When you explicitly identify a range as a table, Excel can respond more intelligently to the actions you perform with that range. For example, if you create a chart from a table, the chart expands automatically as you add new rows to the table. If you create a pivot table from a table, refreshing the pivot table will include any new data that you added to the table.
Below figures shows a range before it was converted to a table and the range after it was converted to a table.
Difference between a standard Range and a Range that has been converted to a Table:
- Activating any cell in the table gives you access to a new Table Tools context tab on the Ribbon.
- You can quickly apply background color and text color formatting by choosing from a gallery. This type of formatting is optional.
- Each column header contains a filter button that, when clicked, lets you easily sort the rows or filter the data by hiding rows that don’t meet your criteria.
- A table can have “slicers”, which makes it easy for novices to quickly apply filters to a table.
- If you scroll down the sheet so that the header row disappears, the table headers replace the column letters in the worksheet header. In other words, you don’t need to freeze the top row to keep the column labels visible.
- If you create a chart from data in a table, the chart automatically expands if you add new rows to the end of the table.
- If you create a name for a column in a table, the “refers to” range for the name adjusts as you add new rows to the table.
- Tables support calculated columns. A single formula entered in a cell is automatically propagated to all cells in the column.
- Tables support structured references in formulas outside of the table. Rather than use cell references, formulas can use table names and column headers.
- When you move your mouse pointer to the lower-right corner of the lower-right cell, you can click and drag to extend the table’s size, either horizontally (add more columns) or vertically (add more rows).
- Selecting rows and columns within the table is simplified.
Limitations of using a Table:
- If a workbook contains at least one table, a few Excel features are not available:
- For some reason, when a workbook contains at least one table, Excel doesn’t allow you to use the Custom Views feature (choose View ➜ Workbook Views ➜ Custom Views).
- You cannot share a workbook (using Review ➜ Changes ➜ Share) if the workbook contains a table.
- You can’t insert automatic subtotals within a table (by choosing Data➜Outline➜Subtotal).
- You cannot use array formulas within a Table.