Date Formula Microsoft Excel 97 2000 2002 free tutorial format function workbook worksheet sum report printing pivot table forms templates template Date Data CountIf Count VBA Macro excel formula subtotal filtering chart graph

add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba

Microsoft Excel Pivot Table

Of all the techniques that Excel offers for data analysis, PivotTables are the most exciting; the variety of options for data analysis is huge, and the results are immediate.

A PivotTable can sort, filter, create dynamic subtotals by dragging the mouse, add calculated formulas, create a chart that is automatically linked to various dynamic data, and more.

The data for creating a PivotTable can come from a variety of sources, including data organized in an open or closed workbook sheet, a number of tables in sheets in different workbooks, and data drawn from external systems.

With a PivotTable, you can create multiple queries and subtotals that are grouped according to daily totals or totals by days of the week, months, quarters or years; add calculated formulas; and more.

Basic Concepts: Terminology Used in PivotTables

Creating a PivotTable

Rules for organizing data to create a PivotTable

Defining a name for the data table

1. Select the data table from the sheet.

2. Select a cell in the data area, and press Ctrl+*.

3. Press Ctrl+F3 (the Define Name dialog box).

4. Type the text in the Names in Workbook box. For example, the name Data is defined.

5. Click OK.

Steps 1 and 2

1. Select Cell A1 from another sheet in the workbook.

2. From the Data menu, select PivotTable and PivotChart Report.

3. In Step 1 of 3, select Microsoft Excel list or database.

4. Click Next.

5. In Step 2 of 3, select the Range box.

6. Press F3 (Paste Name dialog box).

7. Paste the name Data.

8. Click OK.

9. Click Next.

Caution

Did you have a problem continuing to Step 3? Cancel the PivotTable, return to the data sheet, and check that the text in each cell in the header row is different than the text in the other cells. Do not leave an empty cell without a header.

Data table in another workbook, open or closed

In the example, you created the PivotTable in the workbook in which the data table is located. If you want to create a PivotTable from a data table located in another workbook, open or closed, define a Name for the data table in the source workbook before beginning to construct the PivotTable.

In the explanation above, the work steps from Step 5 change (Paste the Name of the data table).

Data table in an open workbook

1. Select the Range box.

2. From the Window menu, select the open workbook.

3. Select one of the sheets.

4. Type an exclamation point, type the Name that you defined for the data table, and then click OK.

Data table in a closed workbook

1. Select the Range box.

2. Click Browse, and select the workbook after locating it in the directory on the hard drive.

3. Type an exclamation point, type the name that you defined for the data table, and then click OK.

Step 3

Click Layout (Excel 97 does not include this button).

Construct the PivotTable by dragging fields to Data and Page.

There are three types of PivotTable fields:

1. Data fields.

2. Query/Data filter fields.

3. Fields not relevant to the PivotTable.

For example:

1. Data fields – Quantity, Income.

2. Query/Data filter fields – Date, Invoice Number, Market, Customer.

Transfer the data fields to Data.Click and drag the Quantity field to the white Data area. Click and drag the Income field to the white Data area. Transfer query/data filter fields to Page. Click and drag the Invoice Number field to the white Page rectangle. Repeat this action to drag the fields Date, Market and Customer.

Formatting data fields in a PivotTable

Format each data field separately. You can format or change the formatting later on by clicking the Field Settings (PivotTable Field in Excel 97) icon on the PivotTable toolbar.

Format the Total Data column with the SUM function

1. Double-click the field Sum of Quantity. Excel uses the header text at the top of the column as the Source Field Name. In the Name box, the text Sum of Quantity points to the SUM function, according to which the field items are summed. Change the text in the box to something else. For example – Quantities. You cannot use the Source Field name Quantity.

2. Click Number.

3. Click OK twice.

4. Repeat steps 1-3 to format the Income field.

5. Click OK.

6. In the Step 3 dialog box, click Finish (in Excel 97, go to Step 4 and click Finish).

The PivotTable toolbar is not displayed

Select any toolbar in the toolbar area, right-click, and from the toolbar shortcut menu, select PivotTable.

Notice the figure of the PivotTable. The query fields are in the upper left, and the itemized data fields are in the lower section of the PivotTable.

The Quantities and Income data fields are displayed as rows. Change the direction of the data displayed in the PivotTable from rows to columns. Simply click and drag the gray data field button (in the figure, the field is called Data) slightly up and to the right, and release the mouse.

The result:

Regular Work with the PivotTable

Filtering and inserting a query into the PivotTable

Place the cursor in the upper left of the sheet and select an item in one of the Page fields (open the list of items by clicking the arrow in the box of one of the fields). This action filters the data in the PivotTable. The results of
the filter are displayed in the lower section of the PivotTable.

Inserting a complex query

Example:

Click and drag the Market field from Page to Row. Notice the figure – the Market field is located to the left of the Customer Name field, and there is a subtotal below all the items in the Market field. Insert an additional query by selecting an item in the Page field (in the upper left of the PivotTable).

Caution

Dragging a field’s button outside of the PivotTable area in the sheet deletes it. If you drag the button outside the PivotTable area, an X sign appears. If you release the mouse at this point, the field is deleted. To cancel this action and return the deleted field to the PivotTable, press Ctrl+Z, or click the Undo icon on the toolbar.

Inserting subtotals

With a PivotTable, you can insert automatic subtotals, delete subtotals, or insert subtotals and additional functions.

Drag at least two fields to the row area and position them next to one another in a row. In the figure, the two fields that are placed in the row are the Market field and the Customer Name field.

The subtotals you insert are calculated for the items in the first field – Market. Double-click the Market field name (gray button). In the PivotTable Field dialog box, there are three options for subtotals.

Automatic subtotals

Excel uses the SUM formula as the default for inserting subtotals for an item in the Market field.

Custom subtotals

Select the Custom subtotals option, and select additional functions (see figure). Click OK.

None

Data is displayed without subtotals.

Hiding items

You can hide items to calculate sums for only the displayed items.

In Excel 97, double-click the name of the Market field, and in Hide Items, select the item called Africa. Click OK.

In Excel 2000 and 2002, click the arrow to the right of the Market field, and in the drop-down list, cancel the selection of the items you wish to hide.

In the figure, notice the item Africa is not included in the list of items and the data for the displayed items is totaled.

Problem

There is a significant difference between the Hide techniques in Excel 97 and Excel 2000 and 2002.

In Excel 97, if you wish to display just a few items from a long list of customers, you must uncheck every hidden customer name separately. You can use Shift to select a contiguous series, but this is not a simple solution.

In Excel 2000 and 2002, this problem does not exist. You simply cancel the selection of Show All and select the items you want to display. The rest are already hidden.

Solution

In Excel 97, use Grouping (see the explanation below) to group all the customer names you want to hide. Leave the rest of the names ungrouped, hide the grouped customers, and leave the ungrouped list of customers displayed.

Canceling "hide items"

Caution

Cancel Hide Items immediately after finishing the data analysis. In the PivotTable, there is no icon or any other way to automatically cancel Hide Items, and later in your work with the PivotTable, you will not remember which items are hidden.

Canceling "Hide Items"

In Excel 97, double-click the name of the field, for example, the Market field. Select the item Export. The selection changes color from blue to white. Click OK.

In Excel 2000 and 2002, select the boxes you had previously hidden.

Sorting items

You can sort PivotTable items according to a selected field, according to Excel’s sorting rules. Select an item in the Row field. Click the Sort Ascending or Sort Descending icon, or from the Data menu, select Sort.

Displaying Top 10 Records with AutoShow

You can set the PivotTable to display only the top or bottom ten invoices using the AutoShow feature.

You can set the PivotTable to display the top 10% of records.

New

In 2002

In the example shown, the PivotTable is showing data for a number of invoices. A common requirement would be to provide a list of the top ten invoices.

Result: only the top ten invoices in terms of quantity are displayed.

Refreshing data

The PivotTable is not automatically linked to the data table. During the construction of the PivotTable, data is drawn from the data table to the computer’s memory. Update the data in the memory by refreshing it – click the Refresh Data icon (red exclamation point) on the PivotTable toolbar.

If the data table changed its size, or rows or columns were added to it, you must refresh the Name reference that you defined. The Name of the data table must include all the data in the table.

Inserting a sub-detail as an item

With a PivotTable, you can insert a sub-detail as an item without changing the structure of the PivotTable fields.

Example: in the customer called Cisco, insert a detail according to the Market field.

1. Select an item in the Customer Name field.

2. Double-click.

3. In the Display Detail dialog box, select the name of the sub-detail’s field.

4. Click OK.

Sending drill-down detail to a new sheet

1. Select a cell in the data area in the customer row for which you want details. For example, select Cell C9 for a customer called Cisco.

2. Double-click.

3. A new sheet is automatically inserted with an itemized data table showing all rows included in the selected cell.

Result: see the figure below.

PivotTable Fields

You can insert fields into a PivotTable; this includes inserting the same Field of data several times into the data area. You can format each Field differently by using different functions; inserting a calculated Field (a Field with a formula); and using a variety of additional options that will be explained later in the chapter.

Additional options are as follows:

Inserting a field

Did you forget to insert a field while constructing the PivotTable, or did you mistakenly delete a field (see Deleting a Field below)? Select a cell in the area of the PivotTable, click the PivotTable Wizard icon on the PivotTable toolbar, and select Layout (in Excel 97, there is no Layout button). Drag the field to Page or Data, click OK, and click Finish.

Deleting a field

Click the PivotTable Wizard icon on the PivotTable toolbar, and select Layout. Drag the field outside the data area of the PivotTable, click OK, and click Finish.

Grouping items and inserting a new field

Group text items. In the figure, notice the field Customer Name.

If you group two items into one, you can create subtotals for groups of items with the same properties. For example, group customers according to their properties.

1. Select the items (Cells A7-A10) in Customer Name. Press Alt+Shift+Right Arrow or right-click the cell; from the shortcut menu, select Group and Outline, and then select Group.

2. Select Cell A7 (with the text Group 1), and type Customer Group 1.

3. Drag the Customer Name field to Page (upper left section).

Result

You inserted a new Customer Name field.

Change the name of the Customer Name 2 field by double-clicking the gray Customer Name 2 button. Type the new name of the field, and click OK.

Alternatively, type directly onto the gray button.

Grouping a field with items that are numbers or dates

Grouping data in a field, or in the language of the PivotTable – items, requires that all the items in the field have the same properties. In other words, a date column will have dates in all the cells. A cell without a date is a text cell, and the PivotTable cannot group incomplete dates or numeric data.

If, while grouping dates, you receive the message Cannot group that selection, you must return to the data sheet and check that the date column contains only dates.

After checking, repairing and completing the data, return to the PivotTable. Refresh the PivotTable by clicking the Refresh Data icon on the PivotTable toolbar, and try again to group the field as described below.

Example:

Group the Invoice Number field.

1. Return the PivotTable fields to Page. The data area contains one Total row.

2. Click and drag the Invoice Number field to a row.

3. Select one of the cells with an invoice number. For example, select Cell A9.

4. Right-click, select Group and Outline from the shortcut menu, and then select Group.

5. In the Group dialog box, enter the grouping method in the third box. The example has groups of ten invoices.

6. Click OK.

Grouping a date field

Grouping dates and inserting total fields by day, month, quarter, year and/or grouping and summing by day

By grouping the Date field, you can create filtering queries and sum data according to day, month, quarter, year, and even by the number of days.

Adding three new fields: month, quarter, year

1. Restore the PivotTable to its original structure, in which the fields are located in the upper left of the PivotTable (Page) and are not filtered. Notice the appearance of the word All in the Field Name box. The data area includes one total row for the data.

2. Click and drag the Date field from Page to Row.

3. Select one of the dates under the Date field name.

4. Right-click, select Group and Outline from the shortcut menu, and then select Group.

5. In the Grouping dialog box, select Days, Months, Quarters, Years.

6. Click OK.

Three fields have been added to the PivotTable – Years, Quarters, Months. (The least aggregate grouping selected – in this case, days – is shown in the Date field.

7. Click and drag each of the three new fields from Row to Page.

Grouping dates by days

1. Drag the Date field from Page to Row. Select one of the dates under the Date field name.

2. Right-click, select Group and Outline from the shortcut menu, select Group, and select Days.

3. In Number of Days, enter a number, or select the number of days. For example, select 7.

4. Click OK.

The result of grouping dates in groups of seven days:

Note

You cannot group according to Number of Days, and also according to months, quarters or years in the same PivotTable.

If you group by Number of Days, the date groups that you created earlier become ungrouped (the three new groups that you created are removed). You cannot simultaneously group according to Number of Days and insert the three new columns of months, quarters and years.

If you want to save the PivotTable with grouping by days and grouping by months, quarters and/or years, you must construct an additional PivotTable (see explanation below of inserting additional PivotTables into the workbook.

Grouping dates by weeks

You will certainly want to create groups of seven days that begin with the first day of the week. To do so, you must locate the first Sunday or Monday of the data table and define it as a date to begin grouping the dates.

Often, the location of the first Sunday will be before the first date of the data table. For example, the first date of the data table is 5/10/1996. To determine if this is a Sunday:

1. In any cell outside the PivotTable, enter the date 5/10/96.

2. Select the cell and press Ctrl+1 (Format Cells).

3. Select the Number tab.

4. Select Custom.

5. In the Type box, type dddd.

6. Click OK.

7. The result – Friday. The conclusion – the first Sunday before 5/10/96 is 5/5/96.

8. Drag the date field and position it in Row.

9. Select one of the dates in the Date field.

10. Right-click, select Group and Outline from the shortcut menu, and select Grouping.

11. In the Starting at box, type 5/5/96, and leave the check box empty.

12. Select Days.

13. For the number of days, type 7.

14. Click OK.

Inserting a calculated field

Calculated fields are fields with formulas. The dynamic formulas you insert into the PivotTable will allow you to perform calculations between fields or in a single field.

Example: calculate the average price of an item sold to a customer or conversion according to the dollar exchange rate of the Income field. In the example, insert a field that calculates the average price per unit.

1. Select one of the cells in the data area of the PivotTable.

2. On the PivotTable toolbar, select PivotTable, Formulas, Calculated Field.

3. In the Name box, type the name of the formula. This name will be the name of the calculated field, and the formula will be saved along with the field name.

4. In the Fields box, select the Income field and click Insert. In the Formula box, insert the division sign (/).

5. In the Fields box, select the Quantity field, and click Insert.

6. Click OK.

Formatting a calculated field

1. Select one of the cells in the column of the Total of Average Unit Price Sold calculated field.

2. On the PivotTable toolbar, click Field Settings.

3. In the Name box, change the field name to Average Unit Price Sold.

4. In Summarize by, check the function for summing the data.

5. Click Number, and format as desired.

6. Click OK.

Updating a calculated field / deleting a formula

1. Select one of the cells in the PivotTable.

2. On the PivotTable toolbar, select PivotTable, Formulas, Calculated Field.

3. Open the Name box, and select the name of the formula. Update the formula in the Formula box, or click Delete to remove the calculated field.

Adding a data field and changing the calculation method

With a PivotTable, you can insert additional data fields that you have already used and change the calculation function by which you create new calculated columns.

Insert an additional data field, the Quantity field.

1. Click and drag the Customer Name field to Row.

2. Select an item in the Customer Name field (one of the customer names).

3. Right-click, and from the shortcut menu, select Wizard, Layout (in Excel 97, there is no need to select Layout).

4. Drag the Quantity field to the data area.

5. Click OK and Finish (in Excel 97, click only Finish).

Changing the calculation function and formatting the field

1. Select a cell in the data area of the new field you inserted, Sum of Quantity.

2. From the PivotTable toolbar, select Field Settings (in Excel 97, select PivotTable Field).

3. In the Name box, type Invoices Number.

4. In Summarize By, select Count. Click Number, and select the number format.

5. Click OK.

Use the same method to insert the Quantity and Income fields. Change the calculation functions for each field to a different function, including Average, Maximum, Minimum or statistical functions.

In the figure, notice that the Quantity field is used to create additional data columns, each with a different calculation function.

Inserting fields to calculate % and more

Insert various additional calculated fields by using the Options button in the PivotTable Fields dialog box.

Select a cell in the data area of the new column field you created, Invoice Numbers. On the PivotTable toolbar, click the Field Settings button, and press the Options button to view the Show data as options:

The figure below illustrates examples and uses of the options found in Show data as:

Growth (Reduction) by Previous Customer

% Difference From, Between Customers

Calculated Field %

Adding a running balance column

Adding a running balance column involves adding a calculated field and changing the calculation function of the new field.

Stage 1 – inserting a calculated field, credit-debit

1. On the PivotTable toolbar, select PivotTable, Formulas, and Calculated Field.

2. In the Name box, type Calc Running Balance as the field Name.

3. Select the Credit field from the list of fields, and click Insert.

4. Select the minus sign (–) in the Formula box.

5. In Fields, select the Debit field, and click Insert.

6. Click OK.

Stage 2 – display data as a running balance

1. Select a cell in the data area in the Calc Running Balance column field.

2. On the PivotTable toolbar, click Field Settings.

3. In the Name box, type Running Balance.

4. Click Options.

5. Open Show data as, and select Running Total In.

6. In the Base Field box, select the Details field, and click OK.

Changing the presentation of multiple data fields in a PivotTable

This PivotTable is unique in that there are two fields in the Data section – Quantity and Income. Excel struggles with how to best present the PivotTable with two data columns. By default, this awkward presentation is used.

You may prefer this presentation where all of the Quantity fields are kept together. Simply drag the Years field to the right side of the Data field.

The other option (not shown) is to drag the Data field to the left of the Market field.

Formatting a PivotTable

1. Select a cell in the PivotTable.

2. In Excel 97, click the Format Tables icon in the PivotTable toolbar. From the Format menu, select AutoFormat.

3. Select one of the format options.

4. Click OK.

Printing a PivotTable

1. Select one of the cells in the PivotTable.

2. Press Ctrl+* (select the current region).

3. Select File, Set Print Area.

For further information, see Chapter 11, Printing.

Note

For users of Excel 97 and 2000, pressing Ctrl+* selects the entire PivotTable, including the fields in Page Field. Select the itemization rows in the detailed lower section of the PivotTable, without using the shortcut mentioned above, and set the print area.

The options dialog box

Select a cell in the PivotTable. On the PivotTable toolbar, select PivotTable, Table Options, or right-click and select Table Options from the shortcut menu.

Important topics in the options dialog box

Select or clear the selection of the relevant checkbox in Format Options.

Selecting this option saves PivotTable data when the workbook is saved and closed. This option is unnecessary if you selected the Refresh on open option.

Changing the page layout to Down, Then Over changes the positioning of the fields in the upper left of the PivotTable (see figure).

Define a number different than the default, and the fields are displayed as groups. This action is crucial for working with PivotTables that have a large number of fields.

Inserting Additional PivotTables from a Single Data Table

The PivotTable enables dynamic analysis of data. The data in the table varies with filtering and with changing the structure of the table by moving fields from Page to Row or Column.

To produce regular reports, the fields in the PivotTable must be set. That is, PivotTables must have a defined structure for row and column fields. For example, construct a single PivotTable to analyze sales per customer, a sales report by customer in a different sheet that is linked to this PivotTable, an additional PivotTable to analyze sales by market, and a sales report by market in a different sheet that is linked to this PivotTable.

The solution – construct several PivotTables from a single data table.

Note

The PivotTable is created from data that is drawn from the computer’s cache. Inserting duplicate or triplicate data is unnecessary and slows the computer down. To avoid inserting duplicate data into the memory, use the data that is already in the memory to insert a PivotTable from a single data source.

Select a new sheet in the workbook in which you constructed the PivotTable.

1. Select Cell A1.

2. Select Data, PivotTable and PivotChart Report.

3. In Step 1 of 3, select Another PivotTable or PivotChart (checkbox no. 4).

4. Click Next.

5. Select the name of the PivotTable you created in the workbook.

6. Click Next.

7. Continue constructing the PivotTable.

Quickly creating several PivotTables from a single data table

An easier way to create additional PivotTables from a single PivotTable is to copy and paste the existing PivotTable.

1. In the first PivotTable you created, select one of the cells.

2. Right-click, choose Select from the shortcut menu, and select Entire Table.

3. Press Ctrl+C (copy).

4. Select a new sheet or workbook.

5. Select Cell A1, and press Enter.

Another method is to copy the entire sheet.

1. Select the tab of the sheet with the PivotTable.

2. Press Ctrl. Click and drag the sheet to a new location. Release the mouse and the Ctrl button.

Continue inserting additional sheets with the PivotTable, and change the structure of the PivotTables as necessary.

Note

© You cannot use this method if you have selected the Number of Days grouping. In this case, you cannot create a second pivot table from the data in memory. You would have to create a new pivot table with the usual method.

Retrieving Data from a PivotTable

The use of a PivotTable saves you enormous use of formulas. After creating a PivotTable, you can create complex reports in other workbook sheets by using the GETPIVOTDATA function to extract cells from the PivotTable.

The easiest way to extract data from the PivotTable and insert it into the cells of other sheets to prepare reports is to insert simple formulas linking the cells in the regular sheet to the cells in the PivotTable. The PivotTable is, by nature, a dynamic data table. Changing the data in the data table and refreshing the PivotTable also refreshes the PivotTable’s resulting data and the reports it creates.

There is a problem with changing the structure of a PivotTable and refreshing the data – the data table includes the invoice data for the company. The total income (see the PivotTable in the figure) is $1,215,980. The result is located in Cell D16.

Upon refreshing the PivotTable after recording additional invoices, it seems as if a new customer has been added. The total in the PivotTable is in Cell D19.

Inserting a row of data into a PivotTable is natural and expresses the dynamic character of the PivotTable; however, this damages the reports that you have prepared by inserting data from the PivotTable.

The solution is to use formulas to locate and extract PivotTable data

1. Use the INDEX formula in combination with the MATCH formula.

2. Use the SUMIF formula in combination with the OFFSET formula.

3. Use the GETPIVOTDATA formula.

For an explanation, see Chapter 23, Using Functions and Objects to Extract Data.

The INDEX and MATCH formulas

The INDEX and MATCH formulas belong to the LOOKUP family (the formulas are part of the Lookup & Reference category).

The second formula has two names that have been defined in the PivotTable sheet: the name of the sheet is PivotTable, and the name of Column A is ColA. In order for the calculation to succeed, make sure that the text Grand Total is identical in both sheets, in the PivotTable sheet and in the sheet with the formula.

The SUMIF and OFFSET formulas

The solution is similar to the earlier one, as the SUMIF and OFFSET formulas are combined.

The GETPIVOTDATA formula

This is a special formula for extracting data from a PivotTable.

Example:

The formula extracts the total income from sales for 1998.

The arguments for the formula are as follows:

Argument 1 – the name of the data field.

Argument 2 – PivotTable reference.

Arguments 3+4 – Field+Item. The rest of the arguments are the Field+Item pair.

In the figure, note the formula arguments.

Data_Field – the name of the data field is surrounded by quotation marks – “Income”.

PivotTable – the PivotTable reference – the reference is a single cell – A9.

Field1 – the field for the row – “Customer Name”.

Item1 – an item in the Customer Name field – “Intel”.

Inserting a Chart from PivotTable Data

Insert a chart while constructing the PivotTable. The chart is automatically linked to the data in the PivotTable, and every change in the PivotTable data results in a change in the chart.

Excel 97 does not include this option. The PivotTable is treated like a regular table. Changing the structure of the data in a PivotTable results in an undesired change in the chart. The solution is to insert a PivotTable with a set structure for the row and column fields, in addition to the PivotTable you use for filtering and creating queries.

In Step 1 of 3, for What kind of report do you want to create, select the second option, PivotChart Report (with PivotTable report). Construct the PivotTable according to the explanations provided in this chapter.

In addition to the PivotTable you constructed in the sheet, a sheet called Chart1 is added. In this sheet, notice the chart with buttons for the PivotTable fields.

You can change the field structure of the PivotTable or filter data. The chart changes automatically. A change in the structure of the PivotTable in the PivotTable sheet results in a change in the chart in the Chart1 sheet.

Creating a PivotTable by Consolidating Several Data Ranges

See Chapter 21, Consolidating Data.

Tips	Excel Keyboard Shortcuts \| Excel General \| Excel Editing \| Excel Text \| Excel Importing Text Files \| Excel Formatting \| Excel Formula \| Excel Summing \| Excel Counting \| Excel Range Name \| Excel Dates \| Excel Time \| Excel Styles \| Excel Printing \| Excel Charting \| Excel Security - Protection \| Excel Worksheet, Workbook \| Excel Customizing \| Excel Tools \| Excel Files \| Excel Information \| Excel Data \| Excel Sorting \| Excel Filtering \| Excel Subtotals \| Excel Loan Formulas \| Excel Grouping and Outlining \| Excel Consolidating \| Excel Pivot Tables \| Excel Macros - VBA \|
Tips by Version	Microsoft Excel 97 \| Microsoft Excel 2000 \| Microsoft Excel 2002 \| All Microsoft Excel Versions \| New in Excel 2002 \| New in Excel 2003 - Office 11
Website	Home \| Tip of Hour \| Recommended Tips \| Most Viewed Tips \| Tips by Version \| Submit a Tip \| My Tips Mr Excel on Excel Book \| Excel Forum \| Excel Links \| About Us
Excel Book	Excel 97 Book \| Excel 2000 Book \| Excel 2002 Book \| Excel XP Book