CREATE EXCEL DATASHEET

Much of the data that you use Excel to analyze comes in a list form. You might need to sort the data, filter it, sum it, and perhaps even chart it. Excel tables provide superior tools for working with data in list form.

If you want to sum columns of data automatically so that the totals show only the sum of visible cells (for example), Excel's Tables features can do it. And if you want to format any Excel data in just a couple of quick steps, Excel's Tables features can handle that task, too. And as for using a form instead of punching numbers into ordinary spreadsheet cells, Tables once again can do the job.

Here are my top 10 secrets for managing lists of data using Excel Tables.

1. Create a Table in Any of Several Ways

You can create a table from the Insert tab or (as here) from the Home tab, where you can choose a style at the same time.

The first step in learning how to work with Excel's Tables features is to use the program to create a table. You'll need a list with column headings and (if you wish) row headings. Select the data, including the heading rows and columns, and click Insert > Table. Visually confirm that the range you've selected is correct, click the My table has headers checkbox, and click OK. Excel will then create a formatted table for you. If you would prefer to choose a particular table format, select the same data area and click Home (instead of Insert); then choose a table style from the Table Styles gallery.

2. Remove the Filter Arrows

Click the Filter option to toggle the display of the filter arrows on or off.

When you want to use some features of an Excel table, but you don't plan to filter or sort your data, you can hide the filter arrows. To do this, click somewhere inside the table and then click Data > Sort & Filter > Filter. Now you can toggle between hiding the arrows with one click and revealing them with the next. The shortcut keystroke combination Shift-Ctrl-Laccomplishes the same thing.

3. Take the Format but Ditch the Table

Formatting data as an Excel table is the quickest way to achieve a neatly formatted range of cells in Excel. The only potential problem is that it may seem that you can't get the formatting without getting all the unwanted table features as well. But while this limitation is technically true, you don't have to keep the table features if you don't want them. To borrow a table style for any worksheet, first create the data as a table, making sure to choose your preferred table style for formatting it.

Next, click inside the table and then click Table Tools > Design > Convert to Range. Click Yes when Excel prompts you with 'Do you want to convert the table to a normal range?' and the table will revert to being a regular range--but with its attractive formatting intact.

4. Fix Ugly Column Headings

Use the Increase Indent option with right-aligned column headings to push them to the left of the filter arrows.

The filter arrows in an Excel table's column headings look downright ugly when those headings are right-justified. The arrows cover the rightmost characters in the headings, and there is no obvious way to fix the problem. The workaround is to indent the content from the right side of the cell. To do this, select the cells containing the headings that are partly hidden and click Home > Increase Indent. If the cell contents respond by jumping to the left edge of the cell, click Home > Align Right to return them to right justification. Click Increase Indent more than once as necessary to position the heading text well clear of the filter arrows.

5. Add New Rows to a Table

Rows in a table behave a little differently from rows in a regular worksheet. If you need to add a new row to a table, and if the Totals row is not visible, click in the bottom right cell in the table and press the Tab key. This simple procedure adds a new row to the table, just as it would if you were working with a Word table.

To add a new row inside a table, use the special insert options that appear only when you've selected a cell inside a table.

To add rows to the end of a table, drag the small indicator in the bottom right corner of the table to add more rows and more adjacent columns, if desired. To add a row inside a table, click in a cell either above or below where the row should be inserted and click eitherHome > Insert > Insert Table Row Above or Home > Insert > Insert Table Row Below, depending on where you want the new row to appear. The table's formatting will automatically adjust so that the new row is correctly formatted.