Working with Tables

It may take you a while to get use to working with tables, but you'll soon discover that a table offers many advantages over a standard worksheet database.

A major advantage of using a table is the ease with which you can format the table as well as change the formatting. See "Changing the Look of a Table," later in this chapter.

If you normally use a lot of named ranges in your formulas, you may find the table syntax to be a welcome alternative to creating names for each column and the table as a whole-not to mention the advantage having named ranges that adjust automatically as the table changes.

A similar advantage is apparent when working with charts. If you create a chart from data in a table, the chart series expands automatically when you add new data. If the chart data isn't in a table, you need to edit the chart series definitions manually (or resort to a few tricks) when new data is added.

If your company happens to use Microsoft's SharePoint service, you'll see yet another advantage. You can easily publish a table to your SharePoint server. To do so, choose Table Tools Design External Table Data Export Export Table to SharePoint List. This command displays a dialog box in which you enter the address of your server and provide additional information necessary to publish your designated table.

Tables, however, do have a few limitations compared to worksheet database. (See the sidebar, "Table Limitations.")

The sections that follow cover common operations that you perform with a table in Excel 2007.

Creating a Table

Although Excel allows you to create a table from an empty range, most of the time, you'll create a table from an existing range of data (a worksheet database). The following instructions assume that you already have a range of data that's suitable for a table.

Table Limitations

An Excel 2007 table offers several advantages over a normal worksheet database. For some reason, the Excel designers imposed some restrictions and limitations on tables. Among them are that

• If a worksheet contains a table, you cannot create or use custom views (View Workbook Views Custom Views).

• A table cannot contain multicell array formulas.

• You cannot insert automatic subtotals (Data Outline Subtotal).

• You cannot share a workbook that contains a table (Review Changes Protect and Share Workbook).

• You cannot track changes in a workbook that contains a table (Review Changes Track Changes).

• You cannot use the Home Alignment Merge & Center command cells in a table (which makes sense because doing so would break up the rows or columns).

If you encounter any of these limitations, just convert the table back to a worksheet database by using Table Tools Design Convert To Range.

1. Make sure that the range doesn't contain any completely blank rows or columns.

2. Activate any cell within the range.

3. Choose Insert Tables Table (or press Ctrl+T). Excel responds with its Create Table dialog box. Excel tries to guess the range and also whether the table has a header row. Most of the time, it guesses correctly. If not, make your corrections before you click OK.

After you click OK, the table is automatically formatted, and Filter mode for the table is enabled. In addition, Excel displays its Table Tools contextual tab (as shown in Figure 9-3). The controls on this tab are relevant to working with a table.

Figure 9-3: When you select a cell in a table, you can use the commands on the Table Tools contextual menu.

Tip 

Another method for converting a range into a table is Home Styles Format as Table. By selecting a format, you force Excel to first designate the range as a table.

In the Create Table dialog box, Excel may guess the table's dimensions incorrectly if the table isn't separated from other information by at least one empty row or column. If Excel guesses incorrectly, just specify the exact range for the table in the dialog box. Or, click Cancel and rearrange your worksheet such that the table is separated from your other data by at least one blank row or column.

Changing the Look of a Table

When you create a table, Excel applies the default table style. The actual appearance depends on which document theme is used in the workbook. If you prefer a different look, you can easily change the entire look of the table.

Select any cell in the table and choose Table Tools Design Table Styles. The Ribbon shows one row of styles, but if you click the bottom of the vertical scrollbar, the table styles group expands, as shown in Figure 9-4. The styles are grouped into three categories: Light, Medium, and Dark. Notice that you get a live preview as you move your mouse among the styles. When you see one you like, just click to make it permanent.

Figure 9-4: Excel offers many different table styles.

For a different set of color choices, use Page Layout Themes Themes to select a different document theme.

Tip 

If applying table styles isn't working, the range was probably already formatted before you converted it to a table. (Table formatting doesn't override normal formatting.) To clear the existing background fill colors, select the entire table and choose Home Font Fill Color No Fill. To clear the existing font colors, choose Home Font Font Color Automatic. After you issue these commands, the table styles should work as expected.

Navigating and Selecting in a Table

Moving among cells in a table works just like moving among cells in a normal range. One difference is when you use the Tab key. Pressing Tab moves to the cell to the right; when you reach the last column, pressing Tab again moves to the first cell in the next row.

When you move your mouse around in a table, you may notice that the pointer changes shapes. These shapes help you select various parts of the table.

• To select an entire column: Move the mouse to the top of a cell in the header row, and the mouse pointer changes to a down-pointing arrow. Click to select the data in the column. Click a second time to select the entire table column (including the header). You can also press Ctrl+spacebar (once or twice) to select a column.

• To select an entire row: Move the mouse to the left of a cell in the first column, and the mouse pointer changes to a right-pointing arrow. Click to select the entire table row. You can also press Shift+spacebar to select a table row.

• To select the entire table: Move the mouse to the upper-left part of the upper-left cell. When the mouse pointer turns into a diagonal arrow, click to select the data area of the table. Click a second time to select the entire table (including the Header Row and the Totals Row). You can also press Ctrl+A (once or twice) to select the entire table.

Tip 

Right-clicking a cell in a table displays several selection options in the shortcut menu.

Adding New Rows or Columns

To add a new column to the end of a table, just active a cell in the column to the right of the table and start entering the data. Excel automatically extends the table horizontally.

Similarly, if you enter data in the row below a table, Excel extends the table vertically to include the new row. An exception to automatically extending tables is when the table is displaying a Total Row. If you enter data below the Total Row, the table will not be extended.

To add rows or columns within the table, right-click and choose Insert from the shortcut menu. The Insert shortcut menu command displays additional menu items that describe where to add the rows or columns.

Tip 

When the cell pointer is in the bottom-right cell of a table, pressing Tab inserts a new row at the bottom.

When you move your mouse to the resize handle at bottom-right cell of a table, the mouse pointer turns into a diagonal line with two arrow heads. Click and drag down to add more rows to the table. Click and drag to the right to add more columns.

When you insert a new column, the Header Row displays a generic description, such as Column 1, Column 2, and so on. Normally, you'll want to change these names to more descriptive labels.

Deleting Rows or Columns

To delete a row (or column) in a table, select any cell in the row (or column) to be deleted. If you want to delete multiple rows or columns, select them all. Then right-click and choose Delete Table Rows (or Delete Table Columns).

Moving a Table

To move a table to a new location in the same worksheet, move the mouse pointer to any of its borders. When the mouse pointer turns into a cross with four arrows, click and drag the table to its new location.

To move a table to a different worksheet (in the same workbook, or in a different workbook), do the following:

1. Press Alt+A twice to select the entire table.

2. Press Ctrl+X to cut the selected cells.

3. Activate the new worksheet and select the upper-left cell for the table.

4. Press Ctrl+V to paste the table.

Excel Remembers

When you do something with a complete column in a table, Excel remembers that and extends that "something" to all new entries added to that column. For example, if you apply currency formatting to a column and then add a new row, Excel applies currency formatting to the new value in that column.

The same thing applies to other operations, such as conditional formatting, cell protection, data validation, and so on. And if you create a chart using the data in a table, the chart will be extended automatically if you add new data to the table. Those who have used a previous version of Excel will appreciate this feature the most.

Using a Data Form

Excel can display a dialog box to help you work with a worksheet database or table. This dialog box enables you to enter new data, delete rows, and search for rows that match certain criteria. This data form works with either a database or with a range that has been designated as a table (choosing the Insert Tables Table command). Unfortunately, the command to access the data form is not in the Ribbon. To use the data form, you must add it to your Quick Access toolbar (QAT):

1. Right-click the QAT and choose Customize Quick Access Toolbar. Excel displays the Customization Customize panel of the Excel Options dialog box appears.

2. From the Choose Commands From drop-down list, choose Commands Not in the Ribbon.

3. In the list box on the left, select Form.

4. Click the Add button to add the selected command to your QAT.

5. Click OK to close the Excel Options dialog box.

After performing these steps, a new icon will appears on your QAT.

Excel's Data Form is handy but is by no means ideal. If you like the idea of using a dialog box to work with data in a table, check out my Enhanced Data Form add-in. It offers many advantages over Excel's Data Form. Download a free copy from my Web site: http://www.j-walk.com/ss.

Setting Table Options

The Table Tools Design Table Style Options group contains several check boxes that determine whether various elements of the table are displayed and also whether some formatting options are in effect:

• Header Row: Toggles the display of the Header Row

• Totals Row: Toggles the display of the Totals Row

• First Column: Toggles special formatting for the first column

• Last Column: Toggles special formatting for the last column

• Banded Rows: Toggles the display of banded (alternating color) rows

• Banded Columns: Toggles the display of banded (alternating color) columns

Removing Duplicate Rows from a Table

If you have a table that contains duplicate items, you may want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task, but Excel 2007 makes it very easy.

Start by selecting any cell in your table. Then choose Table Tools Design Remove Duplicates. Excel responds with the dialog box shown in Figure 9-5. The dialog box lists all the columns in your table. Place a check mark next to the columns that you want to be included in the duplicate search. Most of the time, you'll want to select all the columns, which is the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells you how many duplicates it removed.

Figure 9-5: Removing duplicate rows from a table is easy.

Unfortunately, Excel does not provide a way for you to review the duplicate records before deleting them. You can, however, use Undo (or press Ctrl+Z) if the result isn't what you expected.

Tip 

If you want to remove duplicates from worksheet database that's not a table, choose Data Data Tools Remove Duplicates.

Caution 

Duplicate values are determined by the value displayed in the cell-not necessarily the value stored in the cell. For example, assume that two cells contain the same date. One of the dates is formatted to display as 5/15/2007, and the other is formatted to display as May 15, 2007. When removing duplicates, Excel considers these dates to be different.

Sorting and Filtering a Table

The Header Row of a table contains a drop-down arrow that when clicked, displays sorting and filtering options (see Figure 9-6).

Figure 9-6: Each column in a table contains sorting and filtering options.

Tip 

If you're working with a worksheet database (rather than a table), use Data Sort & Filter Filter to add the drop-down arrows to the top row of your database. This command is a toggle, so you can hide the drop-down arrows by selecting that command again. You can also use Data Sort & Filter Filter to hide the drop-down arrows in a table.

SORTING A TABLE

Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made.

To sort a table by a particular column, click the drop-down in the column header and choose one of the sort commands. The exact command varies, depending on the type of data in the column. Sort A to Z and Sort Z to A are the captions that show when the columns contain text. The captions for columns that contain numeric data or True/False are Sort Smallest to Largest and Sort Largest to Smallest. Columns that contain dates change the captions into Sort Oldest to Newest and Sort Newest to Oldest.

You can also select Sort By Color to sort the rows based on the background or text color of the data. This option is relevant only if you've overridden the table style colors with custom colors, or if you've used conditional formatting to apply colors based on the cell contents.

Tip 

When a column is sorted, the drop-down in the header row displays a different graphic to remind you that the table is sorted by that column.

You can sort on any number of columns. The trick is to sort the least significant column first and then proceed until the most significant column is sorted lasted.

For example, in the real estate listing table, you may want the list to be sorted by agent. And within each agent's group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Price column, then sort by the Area column, and then sort by the Agent column. Figure 9-7 shows the table sorted in this manner.

Figure 9-7: A table, after performing a three-column sort.

On the CD 

This workbook, named  real estate table.xlsx, is available on the companion CD-ROM.

Another way of performing a multiple-column sort is to use the Sort dialog box. To display this dialog box, choose Home Editing Sort & Filter Custom Sort. Or, right-click any cell in the table and choose Sort Custom Sort from the shortcut menu.

In the Sort dialog box, use the drop-down lists to specify the first search specifications. Note that the searching is opposite of what I described in the previous paragraph. In this example, you start with Agent. Then, click the Add Level button to insert another set of search controls. In this new set of controls, specify the sort specifications for the Area column. Then, add another level and enter the specifications for the List Price column. Figure 9-8 shows the dialog box after entering the specifications for the three-column sort. This technique produces exactly the same sort as described in the previous paragraph.

Figure 9-8: Using the Sort dialog box to specify a three-column sort.

FILTERING A TABLE

Filtering a table refers to displaying only the rows that meet certain conditions. (The other rows are hidden.)

Note 

Excel provides two ways to filter a table. This section discusses standard filtering (formerly known as AutoFiltering), which is adequate for most filtering requirements. For more complex filter criteria, you may need to use advanced filtering (discussed later in this chapter).

Using the real estate table, assume that you're only interested in the data for the N. County area. Click the drop-down in the Area Row header and remove the check mark from Select All, which unselects everything. Then, place a check mark next to N. County and click OK. The table, shown in Figure 9-9, is now filtered to display only the listings in the N. County area. Notice that some row numbers are missing; these rows contain the filtered (hidden) data.

Figure 9-9: This table is filtered to show only the information for N. County.

Also notice that the drop-down arrow in the Area column now shows a different graphic-an icon that indicates the column is filtered.

You can filter by multiple values-for example, filter the table to show only N. Country and Central.

You can filter a table using any number of columns. For example, you may want to see only the N. County listings in which the Type is Single Family. Just repeat the operation using the Type column. All tables then display only the rows in which the Area is N. County and the Type is Single Family.

For additional filtering options, select Text Filters (or Number Filters, if the column contains values). The options are fairly self explanatory, and you have a great deal of flexibility in displaying only the rows that you're interested in.

In addition, you can right-click a cell and use the Filter command on the shortcut menu. This menu item leads to several additional filtering options.

Note 

As you may expect, the Total row is updated to show the total for the visible rows only.

Some of the standard spreadsheet operations work differently with a filtered table. For example, you might use the Home Cells Format Hide & Unhide Hide Rows command to hide rows. If you then copy a range that includes those hidden rows, all the data gets copied (even the hidden rows). When you copy data in a filtered table, though, only the visible rows are copied. This filtering makes it very easy to copy a subset of a larger table and paste it to another area of your worksheet. Keep in mind that the pasted data is not a table-it's just a normal range.

Similarly, you can select and delete the visible rows in the table, and the rows hidden by filtering will not be affected.

To remove filtering for a column, click the drop-down in the Row Header and choose Clear Filter. If you've filtered using multiple columns, it may be faster to remove all filters by choosing Home Editing Sort & Filter Clear.

Working with the Total Row

The Total Row is an optional table element that contains formulas that summarize the information in the columns. Normally, the Total Row isn't displayed. To display the Total Row, choose Table Tools Design Table Style Options Total Row. This command is a toggle that turns the Total Row on and off.

By default, the Total Row displays the sum of the values in a column of numbers. In many cases, you'll want a different type of summary formula. When you select a cell in the Total Row, a drop-down arrow appears, and you can select from a number of other summary formulas (see Figure 9-10):

• None: No formula.

• Average: Displays the average of the numbers in the column.

• Count: Displays the number of entries in the column. (Blank cells are not counted.)

• Count Numbers: Displays the number of numeric values in the column. (Blank cells, text cells, and error cells are not counted.)

• Max: Displays the maximum value in the column.

• Min: Displays the minimum value in the column.

• Sum: Displays the sum of the values in the column.

• StdDev: Displays the standard deviation of the values in the column. Standard deviation is a statistical measure of how "spread out" the values are.

• Var: Displays the variance of the values in the column. Variance is another statistical measure of how "spread out" the values are.

• More Functions: Displays the Insert Function dialog box so that you can select a function that isn't in the list.

Figure 9-10: Several types of summary functions are available for the Totals Row.

The drop-down choices insert the SUBTOTAL function and refer to the table's column using a special structured syntax (described later). The first argument of the SUBTOTAL function determines the type of summary displayed. For example, if the first argument is 109, the function displays the sum. You can override the formula inserted by Excel and enter any formula you like in the Total row cell. For more information, see the sidebar, "About the SUBTOTAL Function."

Caution 

The SUBTOTAL function is the only function that ignores data hidden by filtering. If you have other formulas that refer to data in a filtered table, these formulas don't adjust to use only the visible cells. For example, if you use the SUM function to add the values in column C and some rows are hidden because of filtering, the formula continues to show the sum for all the values in column C-not just those in the visible rows.

About the SUBTOTAL Function

The SUBTOTAL function is very versatile, but it's also one of the most confusing functions in Excel's arsenal. First of all, it has a misleading name because it does a lot more than addition. The first argument for this function requires an arbitrary (and impossible to remember) number that determines the type of result that's returned. Fortunately, the Excel 2007 formula AutoComplete feature helps you insert these numbers. In addition, the SUBTOTAL function was enhanced in Excel 2003 with an increase in the number of choices for its first argument, which opens the door to compatibility problems if you share your workbook with someone who uses an earlier version of Excel. The first argument for the SUBTOTAL function determines the actual function used. For example, when the first argument is 1, the SUBTOTAL function works like the AVERAGE function. The following table shows the possible values for the first argument for the SUBTOTAL function:

Open table as spreadsheet

Value	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP
101[*]	AVERAGE
102[*]	COUNT
103[*]	COUNTA
104[*]	MAX
105[*]	MIN
106[*]	PRODUCT
107[*]	STDEV
108[*]	STDEVP
109[*]	SUM
110[*]	VAR
111[*]	VARP
[*]Excel 2003 and Excel 2007 only

When the first argument is greater than 100, the SUBTOTAL function behaves a bit differently. Specifically, it does not include data in rows that were hidden manually. When the first argument is less than 100, the SUBTOTAL function includes data in rows that were hidden manually but excludes data in rows that were hidden as a result of filtering or using an outline.

To add to the confusion, a manually hidden row is not always treated the same. If a row is manually hidden in a range that already contains rows hidden via a filter, Excel treats the manually hidden rows as filtered rows. After a filter is applied, Excel can't seem to tell the difference between filtered rows and manually hidden rows. The SUBTOTAL function with a first argument over 100 behaves the same as those with a first argument under 100, and removing the filter shows all rows-even the manually hidden ones.

The ability to use a first argument that's greater than 100 was new to Excel 2003. You can use this updated version of the SUBTOTAL function anywhere in your workbook; that is, it's not limited to tables. Be aware, however, that this function is not backward compatible. If you share your workbook with someone who is using a version prior to Excel 2003, the SUBTOTAL function will display an error if you use a first argument greater than 100.

Another interesting characteristic of the SUBTOTAL function is its ability to produce an accurate grand total. It does this by ignoring any cells that already contain a formula with SUBTOTAL in it. For a demonstration of this ability, see "Inserting Subtotals" later in this chapter.

Caution 

If you have a formula that refers to a value in the Total Row of a table, the formula returns an error if you hide the Total Row. However, if you make the Total Row visible again, the formula works as it should.

Using Formulas within a Table

Adding a Total Row to a table is an easy way to summarize the values in a table column. In many cases, you'll want to use formulas within a table. For example, in the table shown in Figure 9-11, you might want to add a column that shows the difference between the Actual and Projected amounts. As you'll see, Excel 2007 makes this very easy.

Figure 9-11: Adding a calculated column to this table is easy.

On the CD 

This workbook, named  table formulas.xlsx, is available on the companion CD-ROM.

1. Activate cell E2 and type Difference for the column header. Excel automatically expands the table to include a new column.

2. Move to cell E3 and type an equal sign to signify the beginning of a formula.

3. Press the left-arrow key, and Excel displays =[Actual], which is the column heading, in the formula bar.

4. Type a minus sign and then press left arrow twice. Excel displays =[[Actual]-Projected] in your formula.

5. Press Enter to end the formula.

   Excel copies the formula to all rows in the table.

Figure 9-12 shows the table with the new column.

Figure 9-12: The Difference column contains a formula.

If you examine the table, you'll find this formula for all cells in the Difference column:

 =[Actual]-[Projected] 

Keep in mind that I didn't define any names in this worksheet. The formula uses table references that are based on the column names. If you change the text in a column header, any formulas that refer to that data will update automatically.

Although I entered the formula into the first data row of the table, that's not necessary. Any time a formula is entered into any cell in an empty table column, it will automatically fill all the cells in that column. And if you need to edit the formula, edit the copy in any row, and Excel will automatically copy the edited formula to the other cells in the column.

The preceding steps used the pointing technique to create the formula. Alternatively, you could have entered the formula manually using standard cell references. For example, you could enter the following formula in cell E3:

 =D3-C3 

If you type the formulas using cell references, Excel still copies the formula to the other cells automatically: It just doesn't use the column headings.

Tip 

When Excel inserts a calculated column formula, it also displays a Smart Tag, with some options, one of which is Stop Automatically Creating Calculated Columns. Select this option if you prefer to do your own copying within a column.

Referencing Data in a Table

The preceding section described how to create a column of formulas within a table. What about formulas outside of a table that refer to data inside of a table? You can take advantage of the new structured table referencing that uses the table name, column headers, and other table elements. You no longer need to create names for these items.

The table itself has a name (for example, Table1), and you can refer to data within the table by using column headers.

You can, of course, use standard cell references to refer to data in a table, but the new method has a distinct advantage: The names adjust automatically if the table size changes by adding or deleting rows.

Refer to Figure 9-13, which shows a simple table that contains regional sales information. Excel named this table Table2 when it was created; it was the second table in the workbook. To calculate the sum of all the values in the table, use this formula:

 =SUM(Table2) 

Figure 9-13: This table shows sales by month and by region.

This formula will always return the sum of all the data, even if rows or columns are added or deleted. And if you change the name of the table, Excel adjusts all formulas that refer to that table automatically. For example, if you renamed Table1 to be Q1Data, the preceding formula would be changed to

 =SUM(Q1Data) 

Tip 

To change the name of a table, select any cell in the table, use the Table Name box in the Table Tools Design Properties group. Or, you can use the Name Manger to change the name of a table (Formulas Defined Names Name Manger).

Most of the time, your formulas will refer to a specific column in the table, rather than the entire table. The following formula returns the sum of the data in the Sales column:

 =SUM(Table2[Sales]) 

Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you change the text in the column heading.

Caution 

Keep in mind that the preceding formula does not adjust if table rows are hidden as a result of filtering. SUBTOTAL is the only function that changes its result to ignore hidden rows. Use the following formula instead:

 =SUBTOTAL(109,Table2[Sales]) 

Even better, Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 9-14 shows the formula AutoComplete feature helping create a formula by showing a list of the elements in the table.

Figure 9-14: The formula AutoComplete feature is useful when creating a formula that refers to data in a table.

Here's another example that returns the sum of the January sales:

 =SUMIF(Table2[Month],"Jan",Table2[Sales]) 

Cross Ref 

For an explanation of the SUMIF worksheet function, refer to Chapter 7.

Using this structured table syntax is optional-you can use actual range references if you like. For example, the following formula returns the same result as the preceding one:

 =SUMIF(B3:B8,"Jan",D3:D8) 

To refer to a cell in the Total row of a table, use a formula like this:

 =Table2[[#Totals],[Sales]] 

If the Total row in Table2 is not displayed, the preceding formula returns a #REF error.

This formula returns the value in the Total row of the Sales column in Table2.

To count the total number of rows in Table2, use the following formula:

 =ROWS(Table2[#All]) 

The preceding formula counts all rows, including the Header row and Total row. To count only the data rows, use a formula like this:

 =ROWS(Table2[#Data]) 

A formula that's in the same row as a table can use a #This Row reference to refer to table data that's in the same row. For example, assume the following formula is in row 3, in a column outside Table2. The formula counts the number of entries in the row 3 of Table2:

 =COUNTA(Table2[#This Row]) 

You can also combine row and column references by nesting brackets and including multiple references separated by commas. The following example returns Sales from the current row divided by the total sales:

 =Table2[[#This Row],[Sales]]/Table2[[#Totals],[Sales]] 

A formula like the preceding one is much easier to create if you use the pointing method.

Table 9-1 summarizes the row identifiers for table references and also describes which ranges they represent.

Table 9-1: TABLE ROW REFERENCES
Open table as spreadsheet

Row Identifier	Description
#All	Returns the range that includes the header row, all data rows, and the total row.
#Data	Returns the range that includes the data rows but not the header or total rows.
#Headers	Returns the range that includes the header row only. Returns the #REF! error if there is no header row.
#Totals	Returns the range that includes the total row only. Returns the #REF! error if there is no total row.
#This Row	Returns the range that is the intersection of the active row and the table's data rows. If the active row does not intersect with the table or it's the same row as the header or total row, the #VALUE! error is returned.

Filling in the Gaps

When you import data, you can end up with a worksheet that looks something like the one in the accompanying figure. In this example, an entry in column A applies to several rows of data. If you sort such a range, you can end up with a mess, and you won't be able to tell who sold what.

When you have a small range, you can enter the missing cell values manually. If your worksheet database has hundreds of rows, though, you need a better way of filling in those cell values. Here's how:

1. Select the range (A3:A14 in this example).

2. Choose Home Editing Find & Select Go To Special to display the Go To Special dialog box.

3. In the Go To Special dialog box, select the Blanks option.

4. Click OK to close the Go To Special dialog box.

5. In the Formula bar, type =, followed by the address of the first cell with an entry in the column (=A3 in this example), and then press Ctrl+Enter to copy that formula to all selected cells

6. Press Escape to cancel the selection.

7. Reselect the range and then choose Home Clipboard Paste Values.

Each blank cell in the column is filled with data from above.

Tip 

You can use the SUBTOTAL function to generate consecutive numbers for nonhidden rows in a filtered table. The numbering will adjust as you apply filtering to hide or display rows. If your table has the field names in row 1, enter this formula in cell A2 and then copy it down for each row in your table:

     =SUBTOTAL(3,B$2:B2) 

Converting a Table to a Worksheet Database

If you need to convert a table back to a normal worksheet database, just select a cell in the table and choose Table Tools Design Tools Convert To Range. The table style formatting remains intact, but the range no longer functions as a table.

Formulas inside and outside the table that use structured table references are converted, so they use range addresses rather than table items.