Excel table: comprehensive tutorial with examples

This tutorial unveils the power of Excel tables, often overlooked yet incredibly efficient tools for data management. Discover how tables simplify tasks like dynamic named ranges, formula updates, and data formatting, saving you significant time and effort.

Converting data into a table eliminates the need for manual formula copying, formatting, filtering, and sorting. Excel handles these automatically.

- What are Excel Tables?

• Creating Excel Tables
• Top 10 Excel Table Features
• Managing Table Data:
  • Converting Tables to Ranges
  • Adding/Removing Rows and Columns
  • Quick Row/Column Selection
  • Copying Columns within Tables
  • Resizing Tables
  • Filtering with Slicers
  • Renaming Tables
  • Removing Duplicate Rows

What are Excel Tables?

An Excel table is a named object, managing its contents separately from the rest of the worksheet. Introduced in Excel 2007 (improving on Excel 2003 lists), they're available in Excel 2010 and 365. Key features include calculated columns, total rows, auto-filtering, sorting, and automatic expansion. Tables typically organize related data in rows and columns, though they can be a single row or column. The image below contrasts a standard range with a table:

Note: Excel tables differ from data tables used in What-If Analysis.

Creating Excel Tables

While worksheet data might be called a "table," it's not technically one until formatted as such. Here are three methods:

Three Ways to Create an Excel Table

Organize your data, select a cell within it, and choose one of these:

1. Insert Tab: On the Insert tab, in the Tables group, click Table. This creates a table with the default style.
2. Home Tab: On the Home tab, in the Styles group, click Format as Table, selecting a style.
3. Keyboard Shortcut: Press Ctrl T.

Excel automatically selects the data range. Verify the selection, check/uncheck "My table has headers," and click OK.

The result is a formatted table. While it may initially resemble a range with filter buttons, its functionality is far more extensive.

Notes: Multiple tables are possible per sheet. Tables cannot be inserted into shared workbooks.

Top 10 Excel Table Features

Excel tables offer significant advantages over standard ranges.

1. Integrated Sorting and Filtering

Tables automatically add filter arrows to headers, enabling various filters and sorting options (ascending/descending, by color, custom order).

Filter arrows can be hidden (Design tab > Table Style Options > uncheck Filter Button) or toggled with Shift Ctrl L. Slicers (Excel 2013 ) offer visual filtering.

2. Persistent Headers

Headers remain visible when scrolling through large tables. Ensure a table cell is selected before scrolling.

3. Easy Formatting (Table Styles)

Tables include pre-formatted styles. Change styles via the Table Styles gallery on the Design tab (50 options).

The Design tab controls header rows, total rows, banded rows/columns, first/last column formatting, and filter buttons.

Table Style Tips: The Design tab appears when a table cell is selected. Set a default style by right-clicking it and selecting "Set As Default." Remove formatting via the More button on the Design tab and selecting "Clear."

4. Automatic Expansion

Adding data next to a table automatically expands it, maintaining formatting and applying functions/formulas. Undo expansion with Ctrl Z.

5. Quick Totals (Total Row)

Add a total row (Design tab > Table Style Options > Total Row) and select functions from the dropdown.

Total Row Tips: Use any function (including More Functions). SUBTOTAL sums visible cells; use SUM, COUNT, etc., for all cells.

6. Calculated Columns

Enter a formula in a single cell to calculate the entire column.

Calculated Column Tips: Ensure "Fill formulas in tables..." is enabled (File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type). Overwriting existing data creates a calculated column. Undo with "Undo Calculated Column" or Ctrl Z.

7. Structured References

Use table and column names in formulas (e.g., =AVERAGE(Sales_table[@[Jan]:[Mar]])). These adjust automatically.

8. One-Click Selection

Select table rows/columns easily with mouse clicks or keyboard shortcuts (Ctrl Space for columns, Ctrl Shift right arrow for rows).

9. Dynamic Charts

Charts based on tables update automatically when table data changes.

10. Table-Only Printing

Select a table cell and press Ctrl P; "Print Selected Table" is automatically selected.

Managing Table Data

Converting Tables to Ranges

Convert a table to a range (Design tab > Tools > Convert to Range) to remove table functionality while retaining data and formatting. Structured references become standard cell references.

Adding/Removing Rows and Columns

Add rows/columns by typing below/to the right of the table (unless the Totals row is off, use Tab). Insert rows/columns within the table using the Insert options or right-click context menus.

Delete rows/columns via right-click context menus or the Home tab's Delete options.

Selecting Rows/Columns

Select table columns/rows with a single click on the header/border or using keyboard shortcuts (Ctrl Space, Ctrl Shift right arrow).

Copying Columns within Tables

Select only the data within the table column (avoid selecting the entire worksheet column), then copy and paste.

Resizing Tables

Drag the resize handle (bottom-right corner) to include or exclude rows/columns. Select the entire table by clicking the top-left corner twice or using Ctrl A.

Slicers for Visual Filtering

Add slicers (Design tab > Tools > Insert Slicer) to visually filter table data.

Renaming Tables

Rename tables via the Table Name box on the Design tab.

Removing Duplicates

Remove duplicate rows (Design tab > Tools > Remove Duplicates).

This overview highlights key Excel table features. Experiment to discover their full potential!

The above is the detailed content of Excel table: comprehensive tutorial with examples. For more information, please follow other related articles on the PHP Chinese website!