I am confused about how to use Excel's data consolidation feature. Can you explain to me how it works?
	I am using the AutoFilter to filter a large number of records on a worksheet. I notice that the filter dropdowns do not show all of my records. What is wrong?
	How do I use Excel's Advanced Filter option to show only records in my database that fall between two dates?
	What is AutoFilter and when would I use it?
	What are the AutoFilter options available?
	Can I use the Sorting Buttons on the toolbar to sort my list?
	Can I sort on part of my field only?
	Can I sort my list by more than three fields?
	How can I sort my list that contain articles?
	How do I define a range name that automatically refers to the available data?
	Is there a way to set up subtotals in a worksheet that are automatically updated, without using pivot tables?
	What options do I have when Excel sorts my list?
	How do I sort the items on my List?
	What is the Report Manager and how do I use it in?
	How can I find a record in my List?
	How can I edit, add, or delete records in my List?
	How do I print a report I have created in Excel?
	How do I enter data in my list?
	What is a list in Excel 5.0 or 7.0?
	How do I edit an existing report?
	How do I delete a report I no longer need?
	What is the Data Form dialog box?

I am confused about how to use Excel's data consolidation feature. Can you explain to me how it works?
Keywords: Data Consolidation
Posted December 20, 1996

Sure, here are step-by-step instructions:

1. If you are consolidating the data on a new worksheet, activate this sheet and select cell A1. If you are consolidating onto a sheet with prexisting data, select the cell in the upper left corner of the range where you want the data to go
2. Choose Data - Consolidate from the menu; the Consolidate dialog will appear
3. Place your cursor in the edit box labeled Reference
4. Switch to the worksheet containing the first range that you want to include in your consolidation and use your mouse to select all of this data. The address of your selection will appear in the Reference edit box
5. Click the Add button in the Consolidate dialog. The address you selected in Step 4 will be added to the All References list box
6. Repeat steps 4 and 5 until you have added all of your consolidation ranges to the All References list
7. Choose the function you want to use in your consolidation from the Functions dropdown in the upper left corner of the dialog and make your label choices in the checkboxes in the lower left corner of the dialog. *Do Not* choose Create Links to Source Data if you plan on sorting the result
8. Click OK and you should get a consolidation

I am using the AutoFilter to filter a large number of records on a worksheet. I notice that the filter dropdowns do not show all of my records. What is wrong?
Keywords: AutoFilter
Posted December 20, 1996

Unfortunately, there is nothing wrong. The maximum number of items that it's possible to show in an auto filter list is around 250. There's really nothing you can do about this short of programming your own filter.

I want to use Excel's Advanced Filter option to show only records in my database that fall between two dates. However, I can't seem to make it work.
Keywords: Filter Advanced Filter
Posted November 19, 1996

When using Excel's Advanced Filter method with multiple criteria, in this case two dates, you have to remember that an OR condition is signified by having the criteria in different rows, while an AND condition is signified by having the criteria in different columns. A criteria range that will filter records between two dates, must find records greater than Date 1 AND less than Date 2. Therefore, your two dates must go in the same row but in different columns. To see an example, click here.

What is AutoFilter and when would I use it?
Keywords: AutoFilter Filter List Database
Posted July 25, 1996

It is often difficult to find and extract data needed when working with a large list. To define the data you want to work with and have Excel display only those records on-screen is called filtering your data.

Excel's AutoFilter feature makes filtering out specific parts of your data as easy as selecting an option from a drop-down list. Simply choose from the menu bar Data - Filter - AutoFilter. Excel will add a drop-down arrow to the cells containing the list's column labels.

NOTE: If you want to use AutoFilter with only one field of data, highlight the entire column before choosing the AutoFilter command.

To utilize this feature, click on one of the arrows and a list of all the unique entries in the column will be displayed. If you select an item from one of these lists, Excel will:

• Display only those records that include the item in that field
• Change the color of the column's drop-down arrow to indicate which column is used to filter the list
• Display the row headings of the filtered records in a different color
• Display a message in the status bar indicating how many records were found that matched the selected item

To continue filtering the data, you may choose an item from one of the other lists.

What are the AutoFilter options available?
Keywords: AutoFilter Options List Database
Posted July 25, 1996

Finding and extracting data can be difficult when working with a large list. Excel offers the AutoFilter feature to define the data you want to work with and have Excel display only those records on-screen.

Using this feature makes filtering out specific parts of your data as easy as selecting an option from a drop-down list. Simply choose from the menu bar Data - Filter - AutoFilter. Excel will add a drop-down arrow to the cells containing the list's column labels. When you click on one of the arrows, an itemized list called the filter criteria will be displayed. Besides selecting specific criteria, you also have the following choices in each drop-down list:

• All - Removes the filter criterion for the column
• Top 10 - Displays the Top 10 AutoFilter dialog box (in Excel 7). The left drop-down list has a choice of Top or Botton; the center list enables you to choose a number; the right drop-down list has a choice of Items and Percent
• Custom - Allows you to enter more sophisticated criteria
• Blanks - Displays records that have no data in the field
• NonBlanks - Displays records that have data in the field

Can I use the Sorting Buttons on the toolbar to sort my list?
Keywords: Sort ToolBar Field List
Posted July 25, 1996

Excel's standard toolbar includes two buttons that enable you to sort a list in a specific field. Simply select a cell in the field and click on either the Sort Ascending button or the Sort Descending button. The entire list in that field will be sorted, but only that field, not your entire worksheet. However, if you highlight the worksheet by row headers and click on one of the sorting buttons, the entire worksheet will be sorted by the currently selected options in the Sort Options dialog box.

Can I sort on part of my field only?
Keywords: List Database Sort Field
Posted July 25, 1996

Normally Excel bases its sorting features on an entire field. However, there will be times when you need to sort only part of a field. For example, if a column includes both first and last names and you want to sort by last names, you will need to create a new column that extracts last names from the field. Then, you will be able to sort your list by the new column.

To do this, we will assume that the Name field has a first name followed by a space and then a last name. We will also assume the field value is located in C1. To extract everything after the space, use the following formula:

  	=RIGHT (C1, LEN(C1) - FIND(" ", C1, 1))
			

• FIND(" ", C1, 1) function returns the position of the first occurrence of a space in cell 1
• Subtracting this from the length which is LEN(C1) function returns the number of characters after the space
• The RIGHT() function returns the last name

After establishing this formula in the first row, use the fill handle to copy the formula to the following rows.

To hide this new column so as not to clutter your list:

• From the menu bar, choose Format - Column - Hide.

You do not need to unhide this field to sort by it as Excel will still include it in the Sort By list.

Can I sort my list by more than three fields?
Keywords: Sort List Database Field
Posted July 25, 1996

The ability to sort your list is one way to keep it organized and up-to-date. You can sort it by as many fields as you wish using Excel's Sort Dialog box. To sort an address list by the following fields, Name, Address, City, State, and Zip Code, you must perform two consecutive sorts.

• Choose the least important fields first and then:

1. From the menu bar select Data - Sort
2. In the Sort By field, enter the most important of these fields (City)
3. In the Then By field, enter the next most important (State)
4. In the Then By field, enter the last field (Zip Code)
5. Click OK

• Using the remaining fields:

1. In the Sort By field, enter the most important field (Name)
2. In the Then By field, enter the last field (Address)
3. Click OK

Your list will be sorted in the manner you indicated.

How can I sort my list that contain articles?
Keywords: Sort List Database Articles
Posted July 25, 1996

When sorting a list that includes some items that contain an article (A, An, or The) and some that do not, you will need to extract the articles and sort the list based on the new field.

To do this, we will assume that the text we are using is located in column A and that the left two characters are "A " or the left three characters are "An " or the left four characters are "The ". To extract the article, use the following formula:

=IF( OR(LEFT(A1,2) = "A ", LEFT(A1,3) = "An ", LEFT(A1,4) = "The"), RIGHT(A1, LEN(A1) - FIND(" ", A1, 1)), A1)
			

If the OR() function is TRUE, the command will extract everything after the first space. If, however, it is not true, it will return the entire title.

After establishing this formula in the first row, use the fill handle to copy the formula to the following rows.

If you would like to hide this new column so as not to clutter your list, from the menu bar choose Format - Column - Hide. You do not need to unhide this field to sort by it as Excel will still include it in the Sort By list.

How do I define a range name that automatically refers to the available data?
Keywords: Dynamic Name Range Automatic
Posted July 25, 1996

Name definitions can refer, not only to simple cell addresses, but also to formulas. To define a name called Database that always includes all rows and columns of your data, beginning from $B$2, define the name as:

=OFFSET($B$2,0,0,COUNTA($B:$B),COUNTA($2:$2))
			

Now, whenever you add new rows of data or a new column, the name Database will automatically extend to include your new data. To see an example where this type of name definition is used, click here to download our Automatic Subtotals example workbook.

Is there a way to set up subtotals in a worksheet that are automatically updated, without using pivot tables?
Keywords: Subtotals Update Pivot
Posted July 25, 1996

It is possible to set up your worksheet to compute totals or averages for changing data using data tables and defined names. The way to do it is illustrated and explained in our free sample workbook. You are welcome to download our Automatic Subtotals example workbook (27K).

What options do I have when Excel sorts my list?
Keywords: Sort Data List Options
Posted June 14, 1996

In sorting your list, there may be times when you need a custom sort order. You can do this by choosing the Options button in the Sort dialog box (from the menu bar, choose Data - Sort to display the Sort dialog box). You may then choose any of the following options:

• First Key Sort Order - Specifies a custom sort order for the column you specify in the Sort By list (in the Sort dialog box)
• Case Sensitive - Select this option for Excel to differentiate between upper and lower case letters for sorting (if used, lowercase letters would be sorted before uppercase letters)
• Orientation - To sort list rows, select the Sort Top To Bottom; to sort list columns, select the Sort Left To Right

How do I sort the items on my List?
Keywords: Sort Data List
Posted June 14, 1996

One advantage to a list in Excel is that you can rearrange the records so that they are sorted alphabetically or numerically. Using this feature you can view your data by customer name, account number, or any other field. You can even sort by multiple fields at once. The sorting procedure is determined by the options in the Sort dialog box. The choices it offers are:

• Sort By - This drop-down box lists the field names. This first box will make the determination as to how the entire spreadsheet will be sorted
• Then By - This drop-down list also contains the field names. This will refine your search
• Then By - This drop-down list with field names will finalize the sorting process

NOTE: Even though Excel enables you to sort on as many as three fields, for most sorts, choosing only the Sort By box will be sufficient.

To sort a list:

1. Select a cell inside the list
2. From the menu bar, choose Data - Sort - Excel will display the Sort dialog box
3. Enter the options you desire
4. Click on OK - Excel will sort the list

What is the Report Manager and how do I use it in?
Keywords: Create Report Manager
Posted June 14, 1996

The Report Manager is a special feature found in Excel that enables you to put together a sequence of views and print them in a report. Each view can have its own print settings that will be used when the reports are printed. The steps for creating a report are simple.

• Using Excel 5.0: From the menu bar, choose File - Print Report
• Using Excel 7.0: From the menu bar, choose View - Report Manager

1. The dialog box will appear
2. Click the Add button - the Add Report dialog box will appear
3. Enter a name for the report in the Report Name edit box
4. Using the Sheet drop-down list, select a worksheet to include in the report
5. If you wish to include a view in the report, select it from the View drop-down list box
6. If you wish to include a scenario in the report, select it from the Scenario drop-down list box
7. Click on the Add button - the selected worksheet, view, and scenario will appear
8. If you wish to include other sections in the report, repeat steps 4-7
9. Activate the Use Continuous Page Numbers check box to print the report with consecutive numbers
10. Click OK or press Enter - Excel will return you to the Print Report dialog box
11. Click Close - Excel will return you to the workbook

HINT: If your workbook is complex in nature, document your results, formulas, variables used, and range names by setting up a view for each component naming the report Documentation.

How can I find a record in my List?
Keywords: Find Search List Database
Posted June 12, 1996

A list is a collection of related information with an organized structure making it easy to find or extract data from its contents. The data form enables you to scroll through a list. However, to find items on a larger list, you can use the form's search capabilities.

NOTE: You can perform only simple searches with the data form. For more complex searches, use the Advanced Filter command.

You can prompt your search criteria using text, numbers, and comparison operators such as equals (=) and greater than (>). To complete your search:

1. From the menu bar, choose Data - Form to display the data form dialog box
2. Select the Criteria button - Excel will display a blank record and will replace the record-number indicator with Criteria
3. Select the field you want to use for the search
4. Enter the criterion
5. Repeat steps 3 and 4 for multiple criteria searches
6. Use the Find Next and Find Prev buttons to move up or down to the next record that matches the criteria

To refine your search, you can use multiple criteria. Excel will search for all items listed in the list. Another feature of the search function is the use of wildcard characters such as the asterisk or the question mark. The asterisk is used to substitute for any number of characters (*publishing* for Baarns Publishing or Smith Publishing) and the question mark is used to substitute for a single character (Re?d for Read, Reid, or Reed).

How can I edit, add, or delete records in my List?
Keywords: Edit Add Delete List Database
Posted June 12, 1996

A list is a collection of related information with an organized structure. It is also a management tool that easily allows you to edit, add to, or delete records. This can be done directly on the list or you can use the data form offered by Excel.

To Edit a record:

1. From the menu bar, choose Data - Form to display the data form dialog box
2. Select the record you want to edit
3. Edit the fields you want to change
4. Repeat steps 2 and 3 for other records to edit
5. Select Close to finish editing the list

NOTE: When you make changes to a record, Excel automatically saves the changes permanently when you move to a new record. To restore a record, select the data form's Restore button before moving to the next record.

To Add a record:

1. From the menu bar, choose Data - Form to display the data form dialog box
2. Select the New button or press Ctrl+Page Down. A blank record will be displayed with New Record as the record-number indicator
3. Fill in the fields for the new record
4. Repeat steps 2 and 3 for other records to add
5. Click on Close to finish adding new records

NOTE: When you add new records with the data form, Excel adds them to the bottom of the list without inserting a new row. If there is no room to extend the range, Excel displays a warning message. To add new records, you must either move or delete other records.

To Delete a record:

1. From the menu bar, choose Data - Form to display the data form dialog box
2. Select the record you want to delete
3. Select the Delete button - Excel prompts you for confirmation
4. Click on OK to confirm the deletion
5. Repeat steps 2 through 4 to delete other records
6. Click on Close to return to the worksheet

NOTE: When you delete a record from the data form, Excel clears the data and shifts the records up to fill the gap.

How do I print a report I have created in Excel?
Keywords: Print Report Manager
Posted June 7, 1996

The next step after creating and editing a report consisting of worksheets, views and/or scenarios, is printing the report. This can be done easily.

• Using Excel 5.0: From the menu bar, choose File - Print Report
• Using Excel 7.0: From the menu bar, choose View - Report Manager

1. The Print Report dialog box will appear
2. In the Reports list box, highlight the report you want to print
3. Click on the Print button - Excel will display the Print dialog box
4. Select the print options you desire
5. Click OK or press Enter - Excel will print the report

How do I enter data in my list?
Keywords: List Data Enter
Posted June 7, 1996

A list helps you keep track of anything from an inventory of bank accounts to a collection of CDs. Organizing the information is the first step in making a list and determining the field names is the next. Entering your list records can be done directly in the worksheet cells. If you have formatted any of the fields (numeric formats, alignment, etc.), be sure to copy them to the new records.

Entering and deleting records and fields is the same procedure as inserting or deleting rows and columns in a worksheet:

• To add a record - Select a row and from the menu bar choose Insert - Rows
• To add a field - Select a column and from the menu bar choose Insert - Columns
• To delete a record - Select the entire row and from the menu bar choose Edit - Delete
• To delete a field - Select the entire column and from the menu bar choose Edit - Delete

Entering list data can be tedious. Excel provides some keyboard shortcuts:

• Tab - Confirms the entry and moves to the field to the right
• Shift+Tab - Confirms the entry and moves to the field on the left
• Enter - Confirms the entry and moves to the next record
• Shift+Enter - Confirms the entry and moves to the preceding record
• Ctrl+" - Copies the number from the same field in the preceding record
• Ctrl+' - Copies the formula from the same field in the preceding record
• Ctrl+; - Enters the current date
• Ctrl + - Enters the current time

NOTE: If you press the Enter or Shift+Enter and another record is not selected, from the menu bar, choose Tools - Options and the Edit tab. Activate the Move Selection after Enter check box.

What is a list in Excel 5.0 or 7.0?
Keywords: List Database Data Field
Posted June 7, 1996

A list, formerly called a database in previous versions of Excel, is a collection of related information organized to allow accessing data from its contents easily. An example of a list is a phone book organized by name or a library card catalog organized by book title. You can use lists for just about anything you need to keep track of: inventory, accounts, CDs, or household items.

A list has the following properties in Excel:

• Field - A single type of information, such as a name, address or phone number. In Excel, each column on the worksheet is a field
• Field value - A single item in a field. In Excel, individual cells contain the values
• Field name - A unique name assigned to every list field. These are always found in the first row of the list
• Record - A collection of field values. In Excel, each row is a record
• List range - The worksheet range that includes all the records, fields, and field names of the list

For example, you could organize all of your personal bank accounts into a list. A simple system would include the bank name, account name, account number, balance, and date of last statement.

How do I edit an existing report?
Keywords: Edit Report Manager
Posted June 7, 1996

After you have created a report, it will be printed in the order in which you selected the sections (e.g. worksheet, view, scenario). It is easy to make changes to that order and edit other parts of your report.

• Using Excel 5.0: From the menu bar, choose File - Print Report
• Using Excel 7.0: From the menu bar, choose View - Report Manager

1. Excel will display the Print Report dialog box
2. In the Reports list box, highlight the report you wish to edit
3. Click on the Edit button - the Edit Report dialog box will appear
4. To add or change a section, choose the worksheet, view, and scenario form the Section to Add group and click on the Add button
5. To change the section order, highlight the section in the Sections in this Report list and use the Move Up and Move Down buttons to place the section where you want it
6. To delete a section, highlight the section in the Sections in this Report list and click on the Delete button
7. Click OK or press Enter - Excel will return you to the Print Report dialog box
8. Click on the Close button to return to the workbook

How do I delete a report I no longer need?
Keywords: Delete Report Manager
Posted June 7, 1996

There will be times when a report you have created will no longer be necessary. Deleting reports from your computer is quite easy.

• Using Excel 5.0: From the menu bar, choose File - Print Report
• Using Excel 7.0: From the menu bar, choose View - Report Manager

1. Excel will display the Print Report dialog box
2. In the Reports list box, highlight the report you want to delete
3. Click on the Delete button - Excel will ask you to confirm the deletion
4. Click OK to proceed - Excel will remove the report from the Reports list
5. To delete additional reports, repeat steps 2-4
6. Click Close - Excel will return you to the workbook

What is the Data Form dialog box?
Keywords: List Data Enter
Posted June 7, 1996

Excel lists are powerful and useful tools to organize data. Creating and maintaining them can be difficult. Therefore, Excel offers the data form dialog box. With this box you can add, edit, delete, and find records easily. There are several features of the data form dialog box:

• It shows only one record at a time making editing more simple
• You can view more fields than normally seen on-screen
• When adding or deleting information, Excel automatically adjusts the list range
• Excel prevents you from overwriting data when you add information and seeks confirmation for record deletions
• For the novice user, there are simple command buttons to add, delete, and find data

When you use data forms, Excel creates the form automatically based on the layout of your list. To view the form, select a cell on your worksheet and then from the menu bar, choose Data - Form.

Back to Top

Back to the FAQ Table of Contents

Those who live by the sword get shot by those who don't.