Managing information in lists

Applies to
Microsoft Office Excel 2003

Book cover

This article was excerpted from Microsoft Office Excel 2003 Inside Out by Craig Stinson and Mark Dodge. Visit Microsoft Learning to buy this book.

In this article

Sample files    You can download sample files that relate to excerpts from Microsoft Office Excel 2003 Inside Out from Microsoft Office Online. This article uses the files Staff.xls and Staff2.xls.

Spreadsheets are great for making lists—phone lists, client lists, task lists, transaction lists, lists of assets and liabilities, you name it. In fact, years ago, Microsoft began asking people how they used Excel (as distinguished, perhaps, from what the product’s designers had expected them to do). The researchers discovered that not only was list management the number one spreadsheet activity, but also that a lot of users did nothing but create and maintain lists of various sorts. Accordingly, Excel's designers over the years have added more and more features to simplify the building, maintenance, and use of lists. We'll explore Excel's many list-related features in this chapter.

If you've been working with lists in earlier versions of Excel, you'll notice that Excel 2003 includes a new set of list-related commands on its Data menu, along with a new List toolbar. These additional list-management features are designed to help ensure the integrity of your lists—for example, to prevent you from making accidental row or column deletions from a list range and to enforce consistency in list columns that contain calculations based on values in other columns. Most important, though, the new features enable you to publish your lists to a SharePoint site with or without a link to that site. If you publish and link, you can synchronize your worksheet copy of the list with the published copy so that any changes made in either venue are updated to the other.

If you don't use SharePoint or don't want to publish your lists, you can safely ignore the Data, List submenu and the List toolbar. You might, however, find that some of the new features are useful for purely local list-building as well as for sharing. We'll look in detail at the new features later in this chapter.

Part 1: Building and maintaining a list

To function effectively, a list should have the following characteristics:

  • The top row should consist of labels, with each label describing the contents of the column beneath it. Each label should be unique.
  • Each column should contain the same kind of information.
  • Each type of information that you want to be able to sort by, search on, or otherwise manipulate individually should be in a separate column.
  • The list should not contain blank rows or columns.
  • Ideally, the list should occupy a worksheet by itself. If that's not possible, separate the list from any other information on the worksheet by at least one blank row and column.

Figure 1 shows an example of a seven-column list. Each column in this list records a particular kind of information—a field, in database terminology. The top row holds unique field labels. We've separated first and last names in case we want to extract last names only or use the first and last names separately in a mail-merge process. The list has no blank rows or columns and occupies a worksheet by itself.

Each column in a list should contain a particular kind of information and the first row should consist of labels describing the columns' contents.

Figure 1: Each column in a list should contain a particular kind of information and the first row should consist of labelos describing the columns' contents.

 Note   The workbook used in this example, Staff.xls, is included in the sample files download.

We've used Excel's Freeze Panes command to lock the top row so it remains visible as we scroll downward through the list. Excel marks the pane boundary with a rule.

You can make the top row bold to set it off visually from the rest of the list. This is helpful to whoever uses your list (including yourself), but Excel doesn't require it. Because the first and second rows in the list in Figure 1 contain different kinds of information—columns C and D , for example, have text in the top row and dates below—Excel recognizes the top row as a header row. If you sort your list, Excel is smart enough to keep the header row in place while it rearranges the rest of the list. If you create a PivotTable Report from your list, Excel will use the labels in the top row as field names.

Using label-based formulas in calculated columns

Column G in Figure 1 is a calculated column—one whose values are derived from calculations performed on other values in the list. In this case, we're calculating the age of each staff member by dividing the difference between today's date and the staff member's birthdate by 365.25 (the number of days in a year) and then using the INT function to round down to the nearest integer. The formula for each cell in this column (other than the header) is:

=INT(TODAY()-Date of Birth)/365.25)

The formula is remarkable in several ways.

First and most important, it uses the heading from column D—Date of Birth—to extract values from that column. Using column D's heading instead of explicit references to D2, D3, and so on, makes the formula easy to read and understand. We didn't have to define "Date of Birth" as a name for the range D:D (or any portion of column D). Because of the label in D1, Excel simply recognized that that text stood for as many rows of column D as were occupied by the list.

Second, none of the formulas in column G had to specify the name of the person whose date of birth we needed. Excel is smart enough to get just the birthdate value from the current row, and it does this faithfully for every row of the table using the identical formula in each cell of the Age column.

(Had our formula applied a statistical function to Date of Birth, Excel would have assumed that we were interested in the entire column of birthdates, not simply the date on the current row. Thus, for example, we could determine the birthdate of the youngest staff member by writing =MAX(Date of Birth). We could write this formula anywhere on the worksheet, including outside the current list.)

Third, even though the space character is Excel's intersection operator (the formula =7:7 B:B, for example, returns "Lionel," the value of the cell at the intersection of row 7 and column B), Excel accepts the column label Date of Birth without complaint and doesn't make the mistake of trying to find the intersection of a range named Date and another range named of. The label-based formula mechanism is friendly and accommodating that way. (The older range-naming technology accessible via the Insert, Name, Define command is less so. Names defined in this way cannot use space characters.)

Turning on the label-based formula feature

Inexplicably, the ability to use row and column labels as the basis for plain-English formulas is turned off by default. To make this functionality work, you have to do the following:

  1. Choose Tools, Options.
  2. In the Options dialog box, click the Calculation tab.
  3. On the Calculation tab (see Figure 2), select Accept Labels In Formulas.

Excel’s splendid label-based formulas will not work unless you select the Accept Labels In Formulas  option.

Figure 2: Excel's splendid label-based formulas will not work unless you select the Accept Labels In Formulas option.

If you want to use label-based formulas in a calculated column, be sure to turn this option on before you begin creating the formulas. You can't convert explicit cell references to label references retroactively by simply turning on the option after the formulas are in place. If you change your mind about using labels, though, you can go back to the Options dialog box and clear the check box. Excel will then display the following message:

Microsoft Office Excel dialog box

Simply click OK and your label-based formulas will be converted to formulas that use ordinary cell references.

Making label-based formulas available in all workbooks

The option that enables or disables label-based formulas is workbook-specific. That is, because Excel turns this feature off by default, you have to enable it explicitly in each workbook where you want to use it. You can change the default, however, as follows:

  1. Start Excel, and enable the option in a blank workbook. (Choose Tools, Options, click the Calculation tab, and select Accept Labels In Formulas.)
  2. Choose File, Save. On the Save As Type line in the Save As dialog box, choose Template.
  3. Save the file under the name BOOK in your XLSTART folder. This folder is a subfolder of the folder that contains Excel.exe, the Excel executable file. On a typical system, that location is C:\Program Files\Microsoft Office\Office 11, but on yours it might be different. If the folder in which Excel.exe is stored on your system does not include a subfolder named XLSTART, create it. Then, save the BOOK template in that new folder.

If the XLSTART folder includes a template named BOOK.XLT, that template becomes the model for all new workbooks.

ShowInside Out    Don't use row labels in formulas if you plan to sort

Because Excel can use row headers as well as column headers in formulas, it might be tempting to qualify the Age formulas in column G of Figure 1 (Staff.xls) with the row headers in column A. For example, at G2, we could have written

=INT((TODAY()-Berry Date of Birth)/365.25)

This change would make the formula even more readable, and Excel would happily replicate the new version of the formula down the column, so that G3 would say

=INT((TODAY()- Kleinerman Date of Birth)/365.25)

and so on. Resist any temptation to do this! With the age formulas "overqualified" in this manner, the moment you sorted your list (putting the last names in alphabetical order, for example), your carefully constructed age formulas would turn to hash, because Excel would not make the appropriate formula changes after rearranging the rows. Leave readable enough alone. If all the calculated-column formulas are identical, as they are in Figure 1, they'll remain identical after a sort, and everything will continue to work.

Using (or disabling) other list-building aids

Excel offers the following additional features to simplify the task of list creation:

  • Automatic format and formula extension
  • AutoComplete for cell values
  • The Pick From List command
  • Move on Enter
  • Custom lists

Most users find these features to be a great convenience, but if you happen to be one who does not, you can disable or ignore them, as described later in this section.

Automatic format and formula extension

Excel looks for patterns as you build or extend a list. If you enter a text value in a column, for example, and the text in the same column in the three preceding rows happens to be italic, Excel will make the current text italic to match. (In list objects, Excel recognizes and extends a formatting pattern more quickly than it does in ordinary lists.)

Similarly, if your list includes a column whose values are based on calculations involving other columns, Excel can—in some cases—find the formula pattern and replicate it as you add data. Whether or not this formula extension works depends on the types of calculations involved.

ShowInside Out    Format extension doesn't work in some cases

The format-extension feature breaks down under certain circumstances—usually when it collides with some other convenience feature. If you apply an AutoFormat to the existing rows of a list, for example, Excel doesn't extend the AutoFormat appropriately. Thus, if your current row has a nice thick rule at the bottom, courtesy of an Auto-Format, that thick rule will not get up and move as you append rows. (You need to reapply the AutoFormat after adding rows or add your new rows within the list, rather then appending them to the bottom.) If you enter dates by typing a recognized date formulation (such as 11/16/88), those dates won't assume the format of dates in previous rows. If you copy data from the Clipboard, Excel doesn't deploy the extension feature at all.

If for some reason you don't fancy having formats and formulas extended automatically, choose Tools, Options. On the Edit tab, clear the Extend List Formats And Formulas check box.

AutoComplete for cell values

Certain columns of lists typically repeat the same information. For example, if your Region column has four possible entries—North, South, East, and West—and encompasses hundreds of rows, you could be typing those same four words an average of 100 times apiece. AutoComplete to the rescue! If Excel notices that you're starting to enter something you've entered before (in the current column), it offers to complete the job for you. With your four regions, you have to type only N, S, E, or W and then press Enter, Tab, or a cursor key to accept Excel's completion.

If AutoComplete gets in your way for any reason, you can turn it off. Choose Tools, Options, click the Edit tab, and clear Enable AutoComplete For Cell Values.

Move on Enter

By default, Excel moves the selection down one row when you terminate a cell entry by pressing Enter. If you create a new row in a list by pressing the Right Arrow or Tab key after each cell entry, and then press Enter when you get to the end of the row, your Enter key becomes like the carriage-return key on a typewriter; it takes you to the leftmost cell in the next row. Most users like this. If you don't, choose Tools, Options, click the Edit tab, and clear Move Selection After Enter.

 Note   Move on Enter does not work in list objects.

 Tip   To use the Enter key without moving, hold down Ctrl while you press Enter. Excel terminates the current entry but does not move the selection.

Custom lists

Use custom lists to repeat particular sequences of entries by dragging the fill handle. If you need to create lists repeatedly that include the entries Pitcher, Catcher, First Base, and so on, in consecutive rows, you can simplify the task by creating a custom list.

Part 2: Working with list objects

If you select any cell within a list and press Ctrl+L (or choose Data, List, Create List), Excel presents a dialog box similar to the following:

Create List dialog box

The dialog box (and the menu command that summons it) is somewhat misleading. You already have a list (even if all you have created so far is a header row), so Excel is not about to create one for you. What it is proposing to do is turn your list into a list object, thereby conferring upon it certain properties that it didn't have before. Those properties are as follows:

  • The list object can be published to a SharePoint site.
  • A simple command (Data, List, Total Row) toggles on or off the display of a summation row at the bottom of the list. With the help of drop-down lists that Excel displays for you, you can use this row to display sums, averages, counts, maxima, minima, and so on.
  • You can print the list object by clicking the Print List Range button on the List toolbar.
  • The list object expands as you add new rows.
  • Columns added directly to the right are automatically incorporated into the list object.
  • Charts based on columns in list objects expand as new data is added.
  • Range names assigned to columns in list objects expand as new data is added.
  • The list is protected against accidental row and column deletions.
  • The Move On Enter feature is disabled.

 Note   If you don't want Excel to expand your list object when you add rows or columns, choose Tools, AutoCorrect options. On the AutoFormat As You Type tab of the AutoCorrect dialog box, clear Include New Rows And Columns In List.

Before you click OK to create your new list object, make sure that the range reference proposed in the Create List dialog box is correct (if you have selected more than one cell in the list but not the entire list, you'll need to adjust this reference or start again with a single-cell selection), and that the state of the check box accurately indicates the presence or absence of a header line atop your list. If you don't have headers, Excel will create them for you when you click OK, using the labels Column 1, Column 2, and so on.

When you click OK, Excel does the following:

  • Selects the entire list and draws a blue border around it. The border becomes thick while your select rests within the list, indicating that the list is active.
  • Inserts a new row at the bottom of the list and displays an asterisk in the first column of that new row. You'll use this new row to add new data to the list.
  • Creates a default header row if you don't already have headers, formats the header labels in boldface, and turns AutoFilter on.
  • Displays the List toolbar (unless you have previously dismissed this toolbar while selecting a list object).

Figure 3 shows the effect of these changes on the Staff list shown in Figure 1 (Staff.xls).

When you create a list object, Excel turns AutoFilter on and inserts a row below the list to accommodate your next entry.

Figure 3: When you create a list object, Excel turns AutoFilter on and inserts a row below the list to accommodate your next entry.

Publishing a list object

Provided you have authoring rights on a SharePoint site, you can publish a list object to that site. Select any cell within it (or the entire list), and then choose Data, List, Publish List. The Step 1 dialog box appears.

Type the URL for your SharePoint site on the Address line. (If you have already published one or more lists to this site, you can simply select the URL from the drop-down list.) Select the Link To The New SharePoint List check box if you want to be able to synchronize your Excel-resident list object with the version you publish. (Synchronizing allows your worksheet to be updated if anyone changes the list in SharePoint, as well as letting you transmit changes you make in Excel to the SharePoint site.) Supply a name and (optionally) a description for your new SharePoint list, and then click Next. The Step 2 dialog box, which might look something like this, appears:

Publish List to SharePoint Site dialog box

This dialog box asks you to acknowledge two bits of bad news. First, SharePoint handles only particular data types and constrains whatever you publish to those formats. Second, in the process of publishing your list, SharePoint converts any calculated values to constants; that is, it removes formulas and replaces them with current results. As we'll see, workarounds for both limitations are available.

You can't change the data types proposed in the Step 2 dialog box. All you can do is acknowledge and acquiesce (by clicking Finish) or bail out (by clicking Cancel). When you click Finish, a message confirming the transfer will appear, complete with a link that takes you straight to the published list.

If you have chosen to link your list object to the SharePoint site, your worksheet will now show the following changes:

  • A read-only ID column appears at the left side of the list, and the remaining columns move to the right to accommodate it.
  • Any formulas in your list object are replaced with their calculated values.

Dealing with data type and formatting issues

Figures 4 and 5 show some problems that might arise when you publish an Excel list object to a SharePoint site:

  • The Zip codes, which in Excel are numbers formatted with the special Zip Code format, appear in SharePoint as ordinary numbers formatted with commas. Because SharePoint's number format regards leading zeroes as insignificant, East Coast Zips (such as Jo Berry's 02134) no longer look like Zips.
  • The telephone numbers, which were entered as numbers in Excel and formatted with the special Phone Number format, are converted to ordinary comma-formatted numbers in SharePoint and now look like estimates of the national debt.
  • SharePoint reports that all staff members were born at midnight.

We’ve used the Zip Code and Phone Number formats in Excel to simplify data entry.

Figure 4: We've used the Zip Code and Phone Number formats in Excel to simplify data entry.

In addition to mangling the Zip codes and phone numbers, SharePoint reports that everyone was born in the middle of the night.

Figure 5: In addition to mangling the Zip codes and phone numbers, SharePoint reports that everyone was born in the middle of the night.

Fixing dates that appear in SharePoint with unwanted times is simple. You can tell SharePoint to display the dates and omit the times, as follows:

  1. After displaying the list in SharePoint, click Modify Settings And Columns.
  2. In the Columns section of the Customize List page, click the field that you want to modify (Date of Birth, in our example).
  3. In the Optional Settings For Column section of the Change Column page that appears, select Date Only.
  4. Click OK, and then redisplay the list.

Publishing a column of five-digit Zip codes that have few or no leading zeroes also presents little problem. If you have no zeroes, you can let the codes remain numeric in SharePoint. If you have only a few, you can convert a numeric column in SharePoint to text, and then edit those entries that SharePoint has truncated. To convert the column, follow steps 1 and 2 in the procedure just described. Then, select Single Line Of Text in the Name And Type section of the Change Column page.

For other types of numeric data in Excel that SharePoint might disfigure—such as telephone numbers, Social Security numbers, or nine-digit Zip codes—your best bet is to convert numbers to text in Excel before you publish (or enter them as text in the first place). For example, suppose you have used Excel's Phone Number format to create a column of telephone numbers in your contacts list. That format (which you apply by choosing Format, Cells, clicking the Number tab, selecting Special in the Category list, and then selecting Phone Number in the Type list) is handy for data entry, because it relieves you of the need to type parentheses. But it doesn't transfer to SharePoint. You can use Excel's TEXT function to convert a column of such numbers to text. Follow these steps:

  1. Insert a new column beside your phone-number column.
  2. In the new column, next to the first phone number, enter the formula =TEXT(D2,"(000) 000-0000") (replacing D2 with a reference to your first phone number).
  3. Copy this formula downward so that each numeric phone number acquires a text equivalent in the new column.
  4. Copy the entire column of textual phone numbers to the Clipboard.
  5. Select the column of numeric phone numbers, choose Edit, Paste Special, and select the Values option.
  6. Delete the column you inserted earlier. (If your list is a list object, select the column header of the column you inserted earlier. Then choose, Edit, Delete Column.)

Synchronizing a linked list

A list object linked to a SharePoint site becomes a query against an external data source. It can be refreshed (updated with current data) on a schedule or on demand. In the case of a linked SharePoint list, the refresh action is bidirectional. That is, changes you make in Excel are passed to the SharePoint list, and changes made there are returned to Excel. This kind of refresh is called a synchronization.

To synchronize your list object, choose Data, List, Synchronize List With SharePoint, or click the Synchronize Equivalent tool on the List toolbar. To synchronize on a schedule, right-click any cell in the list object and choose Data Range Properties from the shortcut menu. (You can also find the Data Range Properties command by clicking List Range on the List toolbar.) In the External Data Range Properties dialog box that appears, select the Refresh Every check box and enter a refresh interval in minutes.

If changes have been made to the same cell in both the SharePoint copy and your local copy of the list, you will see a dialog box similar to Figure 6 when you synchronize. To accept the SharePoint changes and abandon your own, click Discard My Changes. To force your own changes to be accepted, you'll need to display the list in SharePoint and make the changes there (or delete the other user's changes in SharePoint and then click Retry My Changes in Excel). If you have more than one conflicting cell, Excel will ask you to resolve the first one. Then it will present the second, and so on. You can discard or retry all of your changes at once using the buttons at the bottom of the Resolve Conflicts And Errors dialog box.

If your list has been changed both in SharePoint and on the worksheet since your last synchronization, you’ll be asked to resolve each conflict in this dialog box.

Figure 6: If your list has been changed both in SharePoint and on the worksheet since your last synchronization, you'll be asked to resolve each conflict in this dialog box.

If you make changes to your list in Excel but subsequently change your mind about those changes, you can discard your changes and restore a fresh copy of the list as it stands in SharePoint. Choose Data, List, Discard Changes And Refresh, or click the tool for that command on the List toolbar.

If you make changes to your copy of the list and then save your workbook without synchronizing, Excel will display a warning dialog box. The next time you open the workbook, however, you will see the following:

Microsoft Office Excel dialog box

If you want to preserve the changes you made earlier and eventually synchronize those changes with the SharePoint copy, click Yes. The changes then will remain pending, and you can perform a synchronization immediately or later, as you choose.

If you do not want to synchronize your changes with SharePoint, click No. Excel will then display your workbook as it was when you last saved it—that is, with the unsynchronized changes in place. But if you then try to change the list in any way, you will see the following:

Microsoft Office Excel dialog box

This is Excel's way of saying that your list is currently in an anomalous state: You've made changes to it, but no synchronizable changes are pending. To restore the list to normal functioning, you can either unlink it (choose Data, List, Unlink List) or revert to the data that exists on the SharePoint server (choose Data, List, Discard Changes And Refresh). If you got to this state by mistake—that is, if you really did want to update the SharePoint copy with your previously unsynchronized changes—you should do neither. Instead, close your workbook, reopen it, and choose Load Changes instead of Discard Changes.

Dealing with calculated columns in lists linked to SharePoint

If you need to develop a shared list that involves calculations, it's best to create it in SharePoint and then export it to Excel, rather than working the other way around. That's because any calculated columns you've set up in Excel will be converted to constants when you publish.

But what if you already have an Excel list and you're not eager to recreate it from scratch in SharePoint? In that case, the best approach is to publish the list you have and then massage it in SharePoint. The following example will illustrate this.

Suppose that in Excel you have created the list of quarterly scores shown in Figure 7. You would like to publish this to your SharePoint site so that the various groups can update the list with their fourth-quarter scores. You would also like to be able to synchronize your worksheet and the SharePoint list from time to time as new data arrives. Unfortunately, the SUM formulas in column F will be replaced by the current values in F2:F6 as soon as you publish.

Excel and SharePoint will convert the calculated column (F) to constants when you publish, defeating the purpose of maintaining a link between Excel and SharePoint.

Figure 7: Excel and SharePoint will convert the calculated column (F) to constants when you publish, defeating the purpose of maintaining a link between Excel and SharePoint.

To work around the difficulty, first publish the list object. Then, display the list in SharePoint and click Modify Settings And Columns (in the task pane to the left of the list). SharePoint will respond by displaying the modification page, where you will discern not one, but two problems with your list! (See Figure 8.) Your Total column has the data type Number (that is, its values are constant, not calculated), and your Quarter 4 column, because it is currently blank, is set to SharePoint's default data type, which is Single Line Of Text.

A glance at the column parameters for the list published from Figure 28-6 reveals two problems: Quarter 4 is assumed to be text, and Total is a fixed value instead of a formula.

Figure 8: A glance at the column parameters for the list published from Figure 6 reveals two problems—Quarter 4 is assumed to be text, and Total is a fixed value instead of a forumla.

You can fix the Quarter 4 problem as follows:

  1. In the Columns section of the SharePoint Customize page, click Quarter 4.
  2. In the Name And Type section of the next page that appears, select Number (1, 1.0, 100).
  3. Click OK.

To take care of the problem with the Total column, you need to delete that column, then create a new one in SharePoint. You can set the new one up as a calculated column. Here are the steps:

  1. In the Columns section of the Customize page (shown in Figure 8), click Total.
  2. At the bottom of the Change Column page, click Delete, and then answer the confirmation prompt that appears.
  3. Back in the Customize page, click Add A New Column.
  4. In the Column Name box of the Add Column page, type Total (or another suitable name).
  5. In the Name And Type section of the Add Column page, select Calculated (Calculation Based On Other Columns).
  6. In the Optional Settings For Column section of the Add Column page, select Number.
  7. In the Formula box (directly above the list of data types), type =
  8. In the Insert Column list, double-click Quarter 1.
  9. In the Formula box, after =[Quarter 1], type +.
  10. In the Insert Column list, double-click Quarter 2.
  11. Continue in this manner until you have created the complete formula (see Figure 9), then click OK.
  12. Back in Excel, click the Synchronize List With SharePoint button on the List toolbar. Your Excel list object should now be updated with the calculated field you created in SharePoint (see Figure 10), and the worksheet will now correctly calculate totals as group members add their data in SharePoint.

You can create a calculated column in SharePoint using simple arithmetic operators.

Figure 9: You can create a calculated column in SharePoint using simple arithmetic operators.

When you synchronize, the calculated column created in SharePoint produces a comparable formula in Excel, allowing you to update your worksheet correctly as data points are added in SharePo

Figure 10: When you synchronize, the claculated column created in SharePoint produces a comparable formula in Excel, allowing you to update your worksheet correctly as data points are added in SharePoint.

Working with existing SharePoint lists

If your company, department, workgroup, or other organizational unit uses SharePoint, you might already have lists stored on the SharePoint site that you would like to be able to work with in Excel. You can do that in two different ways. While your Web browser is displaying the list in SharePoint, you can choose Export To Spreadsheet from SharePoint's task pane. Alternatively, in Excel, you can choose Data, Import External Data, Import Data. If a link to the list you want does not appear in the Select Data Source dialog box, you can click New Source, select Other Data Service Provider, select Microsoft SharePoint List Data Service Provider, supply the URL for your SharePoint site, then select the list you want. Following the latter method allows you to create a query against the list, importing only those rows that meet particular criteria.

Toggling the total row

If you choose Data, List, Total Row, or if you click the Toggle Total Row button on the List toolbar, Excel inserts a new row into the list object and displays a grand total under the rightmost numeric column. Initially, this column is the only one totaled, but if you select the bottom cell of any other column, a drop-down list appears, and you can open that list and select an aggregation function for that column. In Figure 10, for example, we could select Sum from the drop-down list to total the Quarter 3 column, we could select Average to get that column's average, and so on. We could also change the grand total G8 to a grand average, ground count, or whatever, by clicking G8 and opening its drop-down list.

Because the Data, List, Total Row command is a toggle, choosing it again makes the total row disappear.

Calculations created by the Data, List, Total Row command use the SUBTOTAL function, with function_num arguments in the range 101 to 111. These arguments perform calculations that ignore all hidden rows—both rows that are hidden by AutoFiltering and rows that you might hide manually. This means that any time you use the Data, List, Total Row command, the aggregate results you see reflect only those rows that are currently visible.

ShowInside Out    List object totals create a backward-compatibility problem

Because the calculations performed by the Data, List, Total Row command use the SUBTOTAL function with function_num arguments in the range 101-111, however, they don't work correctly in earlier versions of Excel. If you open a subtotaled list object in Excel 2002, for example, the subtotal formulas will initially display their most recent calculated values. If you recalculate any part of the worksheet, however, the subtotal functions will return #VALUE! errors. Unfortunately, you can't edit the SUBTOTAL functions in a list object. If you need to reopen your list in an earlier version of Excel, convert the list object to an ordinary list first (choose Data, List, Convert To Range). Then, compute your totals using ordinary statistical functions.

Resizing a list object

You can change the dimensions of a list in either of two ways: by dragging the resize handle that appears in the lower-left corner of the list border or by choosing Data, List, Resize List. (If you use the handle, you can drag horizontally or vertically, but not diagonally. To change the width and depth of a list object using the handle, you have to drag twice.) Note that with a linked list, you can add or remove rows but not columns. You can change the columnar dimensions of a linked list in SharePoint, but not in Excel.

Inserting and deleting rows and columns within a list object

To protect you against accidental damage while your selection rests on any part of a list object, Excel disables the normal worksheet commands for deleting rows, columns, and cells. For example, if you select an entire row that runs through a list object and then press Ctrl+- (the normal keyboard shortcut for the Edit, Delete command), Excel beeps and refuses to delete. To delete a row, select any cell in that row and choose Edit, Delete Row.

If the list object is not linked to SharePoint, you can delete a column by selecting the column header and choosing Edit, Delete Column. If the list is linked, however, you cannot delete columns, because such a change would necessitate a restructuring of the list in SharePoint. (For the same reason, you can't rename a column in a linked list object.) To remove a column, open the list in SharePoint, click Modify Settings And Columns, select the column name, and click Delete.

To insert a row, select a cell in the list object and choose Insert, Rows. To insert a column in an unlinked list, select a cell and choose Insert, Columns. To insert a column in a linked list, open the list in SharePoint and click Modify Settings And Columns. Then, in the Columns section of the Customize page, click Add A New Column.

Applies to:
Excel 2003