Table Benefits

This terrific Excel feature does many things automatically including formatting, formulas and much more. It is very easy to convert worksheet data to a table and convert back to a worksheet range with one mouse click, if needed. (Note, prior to the 2007 version, Tables were called Lists). 

Here’s some treasures you get by Inserting a table:

• Creates a sophisticated worksheet in a flash. Inserting a table auto formats your data with the built-in style and applies filtering to each column for easy data analysis.
• Auto selects all the data for the table range. (Make sure you have column headings and don’t have blank rows or columns in your data. Insert a blank row if you don’t want all data in the table).
• Instant access to a Total Row function where columns can be summed, averaged, counted and more.
• Insert Table Rows and not affect data cell referencing in columns outside of the table.
• Easy to select entire data or column or row no matter how large the table.
• You can name a table and use it in a formula.
• Change the table style with one mouse click. Live Preview displays how your table would look as you point at each style.

Create a Table in a Flash

You have checked the worksheet and removed any unwanted blank columns and rows:

• Click anywhere in your data
• Click the Insert tab on the Ribbon
• Click the Table icon in the Tables group (Excel will display the Create Table dialog box and auto insert the cell range but be sure there is a checkmark in My Table has headers)

Presto! Excel has applied its default Table format and inserted filters for each column.

When you really want to impress or in a hurry, just click anywhere in your data and press CTRL T to auto insert your table!

Select the Table

Tables make it a snap to select all the data with or without the column headers:

• Point your mouse to the very upper left of the very first table cell (column header).
• When mouse pointer changes to a small, black down-angled arrow, single click for data only, or double-click for entire table. (If you double-clicked too slowly and only highlighted data, just single click and now column headers are included).

Customize the Table

Add a column to the table or click below the last row and enter data, and the table is automatically expanded along with the formatting to include the new cells.

Add the Total Row feature. Click in any cell in the table and then, on the Ribbon, under the Table Tools / Design tab in the Table Style Options group, click in the Total Row box to automatically add a Total Row and sum to bottom of the table. Click in any cell in that new row to display a drop down arrow and choose a function, i.e., average, count, etc.

Table Restrictions

A couple of things to be aware of in the table:

• You cannot create Groups and Outlining within a table. The SUBTOTAL function that is used at the bottom of each column allows selection of function formulas such as SUM, AVERAGE, COUNT, etc., that apply to the entire column.
• You cannot share a spreadsheet that contains a table.

Convert a Table Back to a Range

In a hurry to share or need to group your data? The good news is you can use the one click method to convert to a data range:

• The Design contextual tab for the Table is displayed on the Ribbon.
• Click Convert to Range in the Tools group. (Excel asks if you really mean it).
• Click Yes, and you are back to the data range but the table formatting style and the column filtering is still applied. This can be handy because it still has those table benefits but you can now group and outline and/or share the worksheet.

Remove Table Formatting

If you want to be back to Square 1 so the table formatting and filters are completely removed, you have  some choices depending on whether you apply before or after Convert to Range, and if you want to retain your font and numerical formatting.

After converting table back to a range: The table style and filters are still applied.

• Ensure all relevant data is selected.
• Click the Home tab, and then click the Cell Styles arrow in the Styles group, and choose None.
• Another option is to click the Clear arrow in the Editing group and choose Clear Formats.

Be aware that both these methods remove the table formatting style but you may have to reformat font and numerical formatting.

Want to have all the formatting back to its state before you inserted the table? Apply these steps to remove all formatting before you Convert to Range.

Before converting table back to range:

• In the Design tab on Table Tools, click the More drop down arrow button in Table Styles group.
• Choose the first icon under Light which is None.
• Click in the Filter button icon in the Table Styles Options group to remove the filtering icons from the columns.
• Click Convert to Range in the Tools group and confirm.

You now have your spreadsheet back just as you left it. Experiment with tables. I think they will save you time and frustration, especially in those humongous worksheets!