Quick Reference Card: Use Excel tables to manage information

This quick reference card is for the course, Use Excel tables to manage information.

See also

Create a table

  1. On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.

  2. On the Insert tab, in the Tables group, click Table.

  3. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

Change table format

  1. Click anywhere in the table.

  2. On the Design tab, in the Table Styles group, click the arrow at the bottom of the box.

  3. Move the insertion point over the various styles. See a live preview of what each style would look like in your table.

  4. Click to select a style.

Add or delete columns and rows

  • To add a column immediately to the right of the table, type the column heading, and then press ENTER.

  • To add a row to the bottom of the table, type in the blank row immediately below the last row of the table, and then press ENTER.

  • To add a column inside the table, click a cell immediately to the right of where you want to insert the column, right-click, point to Insert, and then click Table Columns to the left.

  • To add a new row inside the table, click in a cell immediately below where you want to insert the row, right-click, point to Insert, and then click Table Rows Above.

  • To delete a column, right-click in the column, point to Delete, and then click Table Columns.

  • To delete a row, right-click in the row, point to Delete, and then click Table Rows.

Sort in a table

  1. When you create a table, drop-down arrows appear at the top of each column.

  2. Click an arrow to sort the column. Note that you can sort only one column at a time.

Filter in a table

  1. Click a drop-down arrow on the column you want to filter. Then:

  2. Clear the Select All check box, and then click on the boxes for the content you want to see. Or

  3. Depending on the type of information in the column, point to Number Filters, Text Filters, and so on to make your selection. Note that you can filter multiple columns.

Delete duplicates

Take care when using this feature. Excel does not hide duplicates; duplicates are deleted from the spreadsheet.

  1. Click inside the table.

  2. On the Table Tools Design tab, in the Tools group, click Remove Duplicates.

  3. Keep selected all the columns you want Excel to examine for duplicate information.

  4. Then click OK.

Autofill a formula down a column

  1. Click in the first cell under the column heading.

  2. Create your formula, and then press ENTER. The formula will automatically fill in every row in the column.

Use structured cell references in a formula

When you create a table, Excel automatically creates structured cell references (similar to named cells) for the table using the table name, and the name of each column. You can use structured cell references in your formulas. For more information about structured cell references see the link at the top of the page.

  • When typing a formula inside a table, type an opening square bracket ([) to see a list of the structured cell references for the table. Double click the name of the column you want in the formula, followed by a closing square bracket (]).

  • When typing a formula outside a table, type the table name followed by an opening square bracket ([) to see a list of the structured cell references for the table. Double click the column name in the list that you want in the formula. To include more than one structured cell reference in the formula, type the table name again, followed by an opening square bracket ([) to see the list of structured cell references again.

Delete a table, but keep the data and the formatting

  1. Click anywhere in the table.

  2. On the Design tab, in the Tools group, click Convert to Range.

Note    Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references.

Delete a table, and its data

  1. On a worksheet, select a table. Press CTRL+A twice to select the entire table, including the table headers.

  2. Press DELETE.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×