Our first step is to open the Text Import Wizard by clicking Data => Get External Data => Import Text File....

In step one of the Text Import Wizard dialog box, specify the data type of the text file, delimited or fixed-width. In this case, we will make sure that Delimited is selected.

In step 2 of the Text Import Wizard, we will place a check next to Comma to tell Excel that the file to be imported is comma-delimited.

In step 3 of the Text Import Wizard, click within each column under Data preview and choose the appropriate data format beneath Column data format. Choose Text for columns that contain only text; choose General if the column contains both text and numbers.

In the Import Data dialog box, choose whether you would like the imported data to appear on the open worksheet or on a new, blank worksheet. If you select Existing worksheet, click the Collapse Dialog button to select the starting cell where the imported data will be placed once you click OK.

Here is the finished product:

If you have a lot of faith in Excel, you can actually sidestep the above procedure by simply dragging the text file icon into the Excel window. Excel will do its best to translate the text file on the fly into understandable format. However, you are usually much better off importing the text file by using the Text Import Wizard.

Import from other applications

To import a spreadsheet file from another application, such as Lotus 1-2-3, click File => Open. In the Open dialog box, open the Files of type: drop-down list box and select the appropriate file type. Then, browse to locate the desired file and click OK.

Import a table from an HTML file (insert, drag and drop-including HTML round tripping)

To import an HTML document that contains a table, use the procedure above to locate the HTML file. Then, make sure that you select Open in Microsoft Excel from the Open drop-down list. Otherwise, the file may open in your default Web browser instead of Excel.

Export to other applications

To save an Excel workbook in another file format, click File => Save As.... Next, select the appropriate file format from the Save as type: drop-down list in the Save As dialog box.

To save an Excel list as an Access database, ensure that you have installed the Access Links add-in, and that you have Access installed on your system. To make the conversion, click Data => Convert to MS Access.... In the Convert to Microsoft Access dialog box, select either New database or Existing database. If the latter, you can click Browse to simplify the task of locating the target Access database.

Excel will place a big comment box on your worksheet notifying you of the name and location of the new Access database that contains the list data. On the Access side, the Access Import Spreadsheet Wizard and Table Analyzer Wizard will take over helping you convert the spreadsheet data into Access table data. (Note: you need the two previously-mentioned Access add-in components already installed on your system for this conversion to successfully complete in Access.)

Using Templates

Apply templates

To create a new Excel document using a template, other than the default template, click File => New.... In the New dialog box, select the desired dialog tab and template and click OK. The number of alternate templates displayed in the New dialog box will correspond to how Excel 2000 was installed on your system.

Edit templates

To open a template for editing, click File => Open.... In the Files of type: drop-down list of the Open dialog box, select Templates (*.xlt). Next, browse to where the templates are located. If you installed Excel 2000 using the default path, they should be located in \Program Files\Microsoft Office\Templates\1033. Select the template to open and click Open. You can now edit the template file directly.

Create templates

When creating a template, you should open a copy of an existing template, make changes, and save the template under a new name. To save an Excel file as a template, click File => Save As... and before you type the file name make sure to select Template (*.xlt) next to Files of type: in the Save As dialog box. The file path should automatically change to \Application Data\Microsoft\Templates, which is where Excel stores custom workbook templates.

Using Multiple Workbooks

Using a workspace

A workspace allows you to save multiple workbooks and save their zoom level, window size, screen position, and so on. To save a workspace, click File => Save Workspace... in any open workbook. In the Save Workspace dialog box, give the workspace file a name and a storage location. Note that Excel gives workspace files the .xlw extension.

To open the workspace, simply double-click the appropriate .xlw file.

Link workbooks

To link workbooks means to create a formula in one workbook that references a cell or cell range in another workbook. To do this, follow these simple steps:

1. Create the formula in the target cell up to the point of the external reference.

2. Use the Window menu or open the workbook that contains the external reference.

3. Select the appropriate cell(s).

You should notice that your formula shows the external reference by displaying the workbook and worksheet name in addition to any referenced cells in the target worksheet.

Formatting Numbers

Apply number formats (accounting, currency, and number)

It may not be the most convenient, but the most comprehensive method for applying number formats to cells is to click Format => Cells... or right-click within a selected range and select the same command from the shortcut menu. In the Number tab or the Format Cells dialog box, choose a number category. Within each category, you can choose several configurable options, such as the number of decimal places to display, the number symbol and the format used for negative values. The Sample area provides a preview of the selected cell formatting.

Create custom number formats

Please see above, "Apply number formats," for information on how to create custom number formats. Essentially, the options in the right portion of this dialog box will change depending upon whether you are creating a number, unit of currency, date, percentage or time.

Use conditional formatting

Use conditional formatting to format cells a certain way if they meet a predefined set of conditions. For example, in the graphic below, all salaries that are greater than or equal to $50,000 display in red bold characters. To apply conditional formatting, first select the cells that will receive the conditional formatting. Next, click Format => Conditional Formatting.... In the Conditional Formatting dialog box, create a condition. In the graphic, I used the comparison operator greater than or equal to. Plug in the test value (50,000 in the example), and then click Format... to actually create the conditional formatting.

You can click Add>> to add a second and/or third condition set.

To remove conditional formatting, select the appropriate cell range, open the Conditional Formatting dialog box, and press Delete.... Select the condition(s) to delete in the Delete Conditional Format dialog box, and click OK.

Printing Workbooks

Print and preview multiple worksheets

To preview multiple open worksheets of the same workbook, click Window => New Window for each worksheet that you would like to display simultaneously. Next, click Window => Arrange.... In the Arrange Windows dialog box, select the appropriate window arrangement option, and make sure that you select Windows of active workbook.

To print all worksheets in a workbook, click File => Print... and select Entire workbook under Print what.

Use the Report Manager

The Report Manager is a convenient method to print one or more custom views or scenarios at one time. Before you can use Report Manager, you must install it. Click Tools => Add-Ins..., place a check next to Report Manager in the Add-Ins dialog box, and click OK. You will be asked for your Office 2000 CD-ROM, so hopefully you have it handy.

Before we discuss how to use Report Manager, it would be wise to define what "custom views" and "scenarios" are. Since scenarios will be explained later in this Cramsession, we will start with views. A custom view is sort of a "snapshot" of a worksheet that you can name, save, and reuse multiple times. For example, for a single worksheet you might have a view that hides certain rows and uses landscape page orientation, and another view that uses a different zoom magnification and includes a what-if scenario.

To create a custom view, arrange the worksheet in the desired manner. Next, click View => Custom Views.... In the Custom Views dialog box, click Add.... In the Add View dialog box, type a name for the new view and decide whether you would like to include print setup and document settings in the view.

To open a custom view, open the Custom Views dialog box, select the appropriate view and click Show.

Now then...on to Report Manager. To create a report, click View => Report Manager.... In the Report Manager dialog box, click Add.... In the Add Report dialog box, type a name for the report (no spaces allowed). In the Section to Add area, specify the sheet that contains the views or scenarios that you want to include. Select the specific view or scenario from the View: or Scenario: drop-down lists, and click Add. Under Sections in this Report:, use the Move Up and Move Down buttons to reorder the report sections. To add a single set of page numbers to the report, select Use Continuous Page Numbers.

To print a report, open the Report Manager dialog box, select the report under Reports:, and click Print.... In the Print dialog box, specify the number of copies to be printed, and click OK.

To delete a report, open the Report Manager dialog box, select the report under Reports:, and click Delete.

Working with Named Ranges

Add and delete a named range

Named ranges makes working with cell ranges much more user friendly. For example, you can reference the name "January_Sales_Data" in a formula rather than having to key in "B11-B256." To create a named range, select the cells that will receive a name, click Insert => Name => Define..., click in the Name box, type a name for the cell range, and press ENTER. Spaces are not allowed in the name.

To view the current named ranges in the open worksheet, click the down arrow next to the Name Box. To delete a named range, click Insert => Name => Define.... In the Define Name dialog box, select the named range under Names in workbook: and press Delete.

Use a named range in a formula

To use a named range in a formula, you can either type the name of the named range inside of your formula, or you can select the range using the mouse. When you type the name of the named range, you must type the name exactly, including capitalization. If you use the mouse, Excel will substitute the name of the range for the ordinary addresses of the selected cells.

Use Lookup Functions (HLOOKUP and VLOOKUP)

The best way to teach you VLOOKUP and HLOOKUP is just to dive into an example. Look very closely at the following graphic. In cell B8, we would like to compute Abrams' bonus amount based upon his gross pay, referenced in cell B7. You will note that the bonus table is located in the cell range F3:G9 (we don't need the header row for VLOOKUP or HLOOKUP). Also, notice that the lookup table has two columns; the first column gives the value range to be looked up, and the second column gives the return value, or the bonus. Now, the syntax for VLOOKUP is easy:

=VLOOKUP(lookup value,lookup table,column number)

In other words,

=VLOOKUP(B7,F3:G9,2) to find Abrams' bonus, which happens to be $1,000.

The HLOOKUP function works the very same way as does VLOOKUP, but we rotate the lookup table 90 degrees. The following graphic illustrates this point.

In this case, the lookup table range is B2:G3. Note the column number is still 2; column number 1 refers to the sales amount, and column number 2 refers to the bonus amount.

Working with Toolbars

Hide and display toolbars

The fastest way to display toolbars is to right-click on any available toolbar button. In the resulting shortcut menu, you can show or hide any of Excel 2000's built-in toolbars, as well as any custom toolbars that are attached to the current template. Of course, you can do the same thing by clicking View => Toolbars.

Customize a toolbar

To customize a toolbar, you must first open the Customize dialog box by clicking Tools => Customize.... On the Commands tab, select the appropriate category, and scroll through the associated commands under Commands:.

To add a command to an existing toolbar, simply drag the command from the Customize dialog box to its new location on a toolbar. You can also add a command to a menu by dragging the command to the menu. The menu will then open, allowing you to place the new command within it.

To remove a command from a menu or a toolbar, simply drag the command into the worksheet area and release the mouse button. Remember that you must have the Customize dialog box open!

Assign a macro to a command button

To assign a macro to a command button, select the category Macros in Categories: list in the Commands tab of the Customize dialog box. Next, drag the icon labeled Custom Button to the appropriate location on a toolbar. Right-click the custom icon and select Assign Macro... from the shortcut menu. In the Assign Macro dialog box, select the appropriate macro from the list and click OK.

You can do the same thing when assigning a macro to a menu item: just drag and place the icon Custom Menu Item from the Commands: list in the Customize dialog box.

Record macros

To record a macro in Excel 2000, click Tools => Macro => Record New Macro.... In the Record Macro dialog box, type a name for the macro beneath Macro name:. Spaces are not allowed. Assign a shortcut key if you would like, and decide whether you would like the macro stored in the current workbook, a new workbook, or your Personal Macro Workbook. Any macros you save in the Personal Macro Workbook will be available for use all templates.

Run macros

To run a macro, you can use a shortcut key combination if you created one, a custom menu item or toolbar button (as described above), or you can click Tools => Macro => Macros... to view the list of all macros that are available in the current workbook. Select the macro that you would like to run, and click Run.

Edit macros

To edit a macro, you must enter the Visual Basic Editor. Open the Macro dialog box, select the macro that you want to edit, and click Edit. The code for the selected macro will display on the right side of the screen. Don't worry- the MOUS exam will only have you make very basic, very intuitive changes to the macro code. The point here is ensuring that you know how to get into and out of the Visual Basic Editor.

To exit the Visual Basic Editor and return to Excel, click File => Close and Return to Microsoft Excel.

Auditing a Worksheet

Work with the Auditing toolbar

Excel's auditing feature is great for troubleshooting formulas, especially in large worksheets. With it, you can find out exactly to which cell(s) a particular formula is referring, as well as find out to which formula a particular cell provides data. To display the Auditing toolbar, click Tools => Auditing => Show Auditing Toolbar.

Trace errors (find and fix errors)

To trace an error in a formula, you first must select the cell that you suspect contains an error. Next, click the Trace Error button on the Auditing toolbar. In the example below, cell F2 contains an error because the precedent value $19,500 in cell E2 does not fall within a valid lookup range in the bonus schedule beginning in cell B11. To fix the problem, you would either have to boost Abrams' salary by at least $500 (I'm sure he or she would love that!), or you would have to add additional data to the bonus schedule to accommodate figures lower than $20,000.

To clear the tracer arrows, click the Remove All Arrows button on the Auditing toolbar.

Trace precedents (find cells referred to in a specific formula)

To find the cells that are referred to in a specific formula, first select a cell that contains a formula. Next, click the Trace Precedents button on the Auditing toolbar. In the example below, the formula in cell F5 is being evaluated. Notice that there are two precedents to this formula: the gross salary figure in cell E5, and the lookup table, which begins at cell B11 (actually the entire range B11:F12).

To remove only the precedent arrows from the worksheet, click the Remove Precedent Arrows button from the Auditing toolbar.

Trace dependents (find formulas that refer to a specific cell)

To find formulas that refer to a specific cell, first select a cell and click the Trace Dependents button on the Auditing toolbar. In the example below, cell E3 is being evaluated. The tracer arrowheads "stop" at cells F3 and G3. That tells us that there are two formulas that reference cell E3.

To clear only the dependent arrows from the worksheet, click the Remove Dependent Arrows button on the Auditing toolbar.

Displaying and Formatting Data

Apply conditional formats

Conditional formatting is covered above, in the section "Use Conditional Formatting."

Perform single- and multi-level sorts

Before you attempt single or multi-level sorts, you should make sure that your column headers occupy only a single row, and that they are formatted differently from the rest of the list. Excel needs some way to determine that you do in fact have a header row.

To perform a single-level sort, select a cell within the column by which you want to sort and click the Sort Ascending or Sort Descending buttons on the Standard toolbar.

To perform a multi-level sort, select a cell within the sort list and click Data => Sort.... In the Sort dialog box, specify whether or not your list has a header row by making the appropriate selection under My list has. Next, sort using up to three levels by specifying the column name and the sort order, ascending or descending. Note that you can sort one level in ascending order and another order descending order.

Use grouping and outlines

To have Excel automatically generate an outline, you must first make sure that you use a summary formula such as SUM to establish the logical outline levels. For example, in the graphic below you can see that the "total" rows (i.e., QTR 1 TOTAL, etc.) will be where Excel decides to automatically break up the data.

To create an Auto Outline, select a cell within the list and click Data => Group and Outline => Auto Outline.

You use the PLUS buttons (also called the Show Detail symbols) to expand outline levels, and you use the MINUS buttons (also called the Hide Detail symbols) to collapse outline levels. In the graphic above, you could also click the numeric buttons below the Name Box to quickly view only one outline level at a time.

You can use Excel's Grouping feature to customize an outline by manually grouping rows or columns. To do this, select the rows or columns to be grouped. Next, click Data => Group And Outline => Group.... To ungroup grouped rows or columns, select the appropriate rows or columns and click Data => Group and Outline => Ungroup....

To remove grouping or outlining from the worksheet, click Data => Group and Outline => Clear Outline.

Use data forms

A data form is a convenient way to browse records in an Excel list, one record at a time. A data form is also useful as a data entry tool. To invoke the data form, click Data => Form.... Use the New button to create a new record, or the up arrow and down arrow to scroll through the record set.

Note that you can also conveniently delete entire records by using the Delete button in the data form window.

Use subtotaling

To use automatic subtotals, make sure that your header row (the column labels) is in row 1 of the worksheet; it is also advisable to perform an ascending sort on the first column data before executing the subtotals. Once you have done all that, select a cell within the list and click Data => Subtotals.... In the Subtotal dialog box, select the appropriate column under At each change in:. In the example given below, Excel will stop at each change in account number. Next, decide which function you would like Excel to apply by making the appropriate choice under Use function:. Finally, select the column that contains the numeric data to be subtotaled under Add subtotal to:.

You will notice that you can expand or collapse the grouped data by using the Show Detail and Hide Detail buttons. Actually, subtotaled data behaves exactly as any grouped or outlined data.

To remove the subtotals from the worksheet, click Data => Group and Outline => Clear Outline.

Apply data filters

Data filters are very cool. The AutoFilter allows you to filter a list very quickly. To apply an AutoFilter, select a cell within your list and click Data => Filter => AutoFilter. Each column header will contain a drop-down list from which you can select your filter criteria. Note that with simple AutoFilters, you can only apply a filter using one criterion at a time.

To apply a filter using multiple criteria, choose (Custom...) from the column header AutoFilter list. In the Custom AutoFilter dialog box, use the first drop-down list to select the appropriate Boolean or comparison operator (for instance, equals or greater than), and use the second drop-down list to select the appropriate item. Notice that you can also use And or Or to develop complex filters. For example, in the graphic below, the Custom AutoFilter will return records where DIVISION equals Canada or Germany.

To remove a filter, open the drop-down list associated with the filtered column and select (All).

To turn off AutoFilter, click Data => Filter => AutoFilter.

Extract data

You can extract tabular data from a Web page. To do this, click Data => Get External Data => New Web Query.... In the New Web Query dialog box, type the URL or full path to the HTML file that contains the data to be extracted into Excel. Annoyingly, there is no Browse button for the file system. In step 2, determine whether you want to extract the entire page contents or only the table(s). In step 3, decide on how much formatting you would like to extract. Note that you can save the Web query for reuse by clicking Save Query.

In the Returning External Data to Microsoft Excel dialog box, specify where on the existing worksheet you would like the newly extracted tabular data to be placed.

After the HTML data "lands" into Excel, you can manipulate it as if it had been natively created in Excel.

Query databases

You can query Microsoft Access or Microsoft SQL Server databases from Excel 2000. Before you can do this, however, you must have installed the Microsoft Query add-in. Follow the instructions given above, under Use the Report Manager, for information on installing Excel add-ins.

In this example, we will extract data from the table called "Customers" in the Microsoft sample database called "Northwind."

From Excel, click Data => Get External Data => New Database Query.... In the Choose Data Source dialog box, you can select <New Data Source> to create and save a data source, or you can select MS Access Database to run a "one-shot" query. Let's just cut right to the chase and select MS Access Database, and the click OK.

Next, browse for the database to be queried. If you installed Microsoft Access in its default path, you will find Northwind.mdb in C:\Program Files\Microsoft Office\Office\Samples.

Once Microsoft Query has connected to and opened the database, your next step is to select the appropriate table and choose which columns from that table you would like to extract into Excel. In this example, I have selected the Customers table, and have chosen the Customer ID, ContactName and City fields. Notice the arrow buttons in the middle of the dialog box go in two directions. This is handy in case you accidentally bring over a column you do not want. When you have selected your columns, click Next to continue.

In the Query Wizard - Filter Data dialog, you have the opportunity to filter data on up to three criteria. In the graphic below, you will notice that I have specified that I want to see only those records from the Customers table where City equals London.

In the Query Wizard - Sort Order dialog box, you can sort your data up to three levels deep. In the graphic below you will notice that I have chosen to sort by Customer ID, in ascending fashion.

In the Query Wizard - Finish dialog box, you can click Save Query... if you think that you will reuse this query in the future. You can also specify where Excel should place the extracted Access data. Verify that Return Data to Microsoft Excel is selected, and click Finish.

The final step is specifying exactly where you want the extracted data to wind up. In the Returning External Data to Microsoft Excel dialog box, you can select Existing worksheet, New worksheet, or PivotTable report. In this example, I have selected New worksheet.

Use data validation

In Excel 2000, you can establish data validation rules to ensure that correct data is entered into your worksheets. For example, you can set up a data validation rule that will accept only dates within a certain range. To create a data validation rule, select the range that will require validation and click Data => Validation.... In the Data Validation dialog box, enter the validation criteria. In the example below, I have specified that only dates between January 1 and December 31 2000 are allowable.

On the Input Message tab of the Data Validation dialog box, you can create a pop-up message that appears every time you select a cell within the validation cell range.

The Error Alert tab of the Data Validation dialog box allows you to create a dialog box that appears when data that violates the validation rule is entered into a cell. You can select the dialog box icon to use from the Style: drop-down list box.

Assuming you have not established an error alert, it is still possible to enter invalid data into a cell range that uses a validation rule. To easily see the invalid cells, you can use the Circle Invalid Data button of the Auditing toolbar. Remember that you can invoke the Auditing toolbar by clicking Tools => Auditing => Show Auditing Toolbar.

To delete the circles, click the Clear Validation Circles button of the Auditing toolbar.

Using Analysis Tools

Use PivotTable AutoFormat

PivotTable AutoFormat works much the same way as Excel's standard AutoFormat feature. With it, you can instantly apply color and pizzazz to your otherwise drab and boring PivotTable report. To apply a PivotTable AutoFormat, select a cell within the PivotTable, display the PivotTable toolbar, and click Format Report... from the Pivot Table drop-down list.

Here is a PivotTable with an AutoFormat applied.

Use Goal Seek

Let's say you are trying to finance your dream car. For a 60-month loan, your monthly payment is figured at $628.49. However, you can only afford to pay $550. What input value can you adjust to meet your desired monthly payment? Goal Seek can help you solve that problem. To use Goal Seek, make sure that you are using a formula such as PMT in your payment cell. (For information on how to use the PAYMENT function, see the Excel 2000 Core Cramsession). Next, select a cell within the list and click Tools => Goal Seek.... In the Goal Seek dialog box, enter the cell address for your monthly payment next to Set cell:. Enter the desired payment next to To value:. Note that the value must be NEGATIVE. You will get an error if you use a positive value. (Recall that outgoing payments in financial functions are designated as negative values). Finally, enter the input value cell address (in this example, that would be Loan Period) next to By changing cell:. In plain English, what Excel is going to do is this: using your new monthly payment as a constant, it will quickly find a loan period value that will pay off your loan, given the specified interest rate.

Here is a graphic depicting the solution reached by Goal Seek in the above example. In plain English, the loan period would need to be increased to 71 months to accommodate a monthly payment of $550.

Create Pivot Chart Reports

To create a PivotChart report, select a cell in the list that is to be charted and click Data => PivotTable and PivotChart Report.... In step 1 of the PivotTable and PivotChart Wizard, specify the location of the data to be charted under Where is the data that you want to analyze?, and select PivotChart (with PivotTable) under What kind of report do you want to create? Click Next to continue.

In step 2 of the PivotTable and PivotChart Wizard, select the appropriate cell range or existing PivotTable from the list and click Next.

The final step is to decide whether to place the new PivotChart on a new worksheet or as an object in an existing worksheet.

Here is a graphic displaying the finished product. Notice that a PivotChart is fully interactive, as is a PivotTable report.

Work with Scenarios

Scenarios allow you to ask "what-if" questions of your Excel data, and save the results for reuse. In this example, we will ask the question "What if we increased our sales by 10 percent?" To get started, click Tools => Scenarios.... In the Scenario Manager dialog box, click Add.... In the Edit Scenario dialog box, enter a name for the new scenario, and select the cell range that will be modified under Changing cells: You can use the Collapse Dialog button to get the dialog box out of the way if you need to.

The next step is kind of a pain. Unfortunately, you will need to manually enter the changed values for each cell in the range. In this example, I had to compute 10% over each value for Quantity, and hand-type each modified value in the appropriate slot. Click OK when you are ready to proceed.

At this point, you have a fully saved scenario. Click Show to display the scenario results.

Because the scenario will overwrite the original data on your worksheet, Microsoft suggests that you create separate scenarios for both the "before" and "after" states.

Use Solver

Use Solver to perform what-if analysis using multiple input values. Let's take an example. Take a look at the following data table:

Suppose we wanted to see a net gain for Sales Team 1 of $50,000. We can have Solver determine the new budget value for all four sales teams, given the company's budget cap of $60,000. To start Solver, you must first have this Excel add-in loaded on your system. Then, you must click Tools => Solver.

In the Solver Parameters dialog box, input the cell that contains the formula next to Set Target Cell:, and next to Equal To: select the appropriate option. In the above example, I want to set cell B5 to a value of $50,000. We want Solver to change the budget values for all four sales teams, so I entered the appropriate cell range under By Changing Cells:. Finally, I needed to tell Solver that we have a budget cap of $60,000. To do this, click Add in the Subject to the Constraints area of the Solver Parameters dialog box.

In the Add Constraint dialog box, I added F3 under Cell Reference, select the equals comparison operator, and entered 60000 under Constraint:.

Here are the finished Solver parameters for the above example:

When you are ready to finish, click Solve. If Solver is able to find a solution, you will see the following dialog box:

Notice that you have the option to either keep Solver's solution or to restore your original values. It is suggested that you save a scenario of your original figures, because once you implement Solver's solution, you won't be able to use Undo to restore your original values. The following graphic displays Solver's solution to the above sample problem.

Use data analysis and PivotTables

Pivot tables allow you to take a long, complex list and view it in many different ways. To create a PivotTable, select a cell inside the list range and click Data => PivotTable and PivotChart Report.... In Step 1, you can just leave the defaults: creating a PivotTable from a Microsoft Excel list.

In Step 2, you verify the list range to be included in the PivotTable report. Click Next to continue.

In Step 3, you can click Layout... to specify the PivotTable fields, but we will wait until the PivotTable has already been placed in a worksheet to do that. In this case, leave the default setting, New worksheet, and click Finish.

Setting up a PivotTable definitely takes some getting used to. The bottom line here is that you can simply drag each list field from the PivotTable toolbar to a location on the empty PivotTable layout.

In this example, I have used Region as a page field, Year as a column field, Customer as a row field, and Sales as a data field. Notice that Excel automatically performs the summary calculations on your data field.

Notice also how interactive a PivotTable report is. Click the black down arrows for your page, column, and row fields to filter the results even further.

You can remove PivotTable fields by dragging them off the PivotTable and into a blank area of the worksheet. They will immediately disappear, and that field on the PivotTable will go blank.

Create interactive PivotTables for the Web

To save an Excel PivotTable report as a Web page, select any cell inside the PivotTable and click File => Save as Web Page.... In the Save As dialog box, select Add interactivity and ensure that Selection: PivotTable is enabled. Next, click Publish....

In the Publish as Web Page dialog box, make sure that Items on Pivot Table is listed next to Items to publish, and that the PivotTable is selected in the list. Under Viewing options, ensure that PivotTable functionality is selected. Next to File name:, type in the name of the HTML file along with its full path. You can click Browse... to find a location to store the file if you cannot remember the correct path. Select Open published web page in browser to instruct Excel to preview the published PivotTable list in your default Web browser.

Here is a graphic showing the finished product. Note that you must have Office Web Components (part of the Office 2000 installation) installed on your system in order to display interactive PivotTables in a browser.

Add fields to a PivotTable using the Web browser

Assuming that you have the Office Web Components installed on your system and are running Internet Explorer 4.01 or later, you can add or subtract fields to your browser-based PivotTable. To do this, click the Field List button on the PivotTable toolbar. In the PivotTable Field List pop-up box, you will see your table fields. Simply drag and drop the fields on or off the browser-based PivotTable to modify the table. Any modifications you make to the browser table do NOT update the original PivotTable located in Excel.

Collaborating with Workgroups

Create, edit and remove a comment

To create a comment, select the appropriate cell and click Insert => Comment. A small callout window will appear, in which you can enter your comment.

Excel generates the default reviewer name from information you might have provided in Excel options. To change the user name, click Tools => Options, navigate to the General tab, and modify the entry next to User name:.

To view a comment, simply hover your mouse pointer over the small red triangle perched in the upper-left corner of a cell that contains comments. To edit a comment, right-click inside a commented cell and choose Edit Comment from the shortcut menu. Notice that the comment window is just a text box: you can resize the box by dragging at the resize handles.

To format practically any aspect of either the comment text or the comment box itself, right-click the comment box border and select Format Comment... from the shortcut menu.

To delete a comment, right-click the commented cell and choose Delete Comment from the shortcut menu.

Apply and remove worksheet and workbook protection

To protect the active worksheet, click Tools => Protection => Protect Sheet.... In the Protect Sheet dialog box, you have the option to protect the active worksheet's contents and charts (select Contents), graphic objects (select Objects), or what-if scenarios (select Scenarios). You can also specify an optional password for additional security.

To unprotect a worksheet, click Tools => Protection => Unprotect sheet.... You will be prompted with the following dialog box.

To protect a workbook, click Tools => Protection => Protect Workbook.... In the Protect Workbook dialog box, you can protect a workbook against changes made to worksheet orientation (select Structure), or against window resizing or closure (select Windows). Like worksheet protection, you can and should supply a password to enforce the workbook protection.

To remove workbook protection, click Tools => Protection => Unprotect Workbook... and supply the correct password, if one was originally specified.

Change workbook properties

A workbook's properties sheet holds summary information about the file itself (size, creation date, etc.), as well as information about the file's creator (author name, manager, company, etc.). To view a workbook's properties sheet, click File => Properties.... The General tab provides file specs such as its save path; the Summary tab provides document title, subject, and author; the Statistics tab provides file creation and last modified dates; the Contents tab provides a snapshot image of the workbook; and the Custom tab has a plethora of detail fields related to document completion status. It is important to note that there are relatively few read-only fields in a workbook's property sheet: it is up to the user to provide the rest of the information related to the file.

Apply and remove file passwords

Sometimes you may want to restrict users' ability to view a workbook without a password. To password-protect a workbook, you must work from the Save As dialog box. This, in most cases, means resaving the file by carrying out the File => Save As... command. In the Save Options dialog box, you can require a password to open the file read-only, and another password to open the file with full access. To get to this dialog box, click Tools => General Options. Read-only recommended will require users, who want to save changes, to save to another file.

Okay- let's assume we have configured our workbook security like the above screen shot, and have established both a password to open and a password to modify the file, with the helpful option for Excel to recommend read only status.

Following is the initial dialog when someone attempts to open the protected file:

If the valiant user has supplied the correct password, then they will be confronted with the following dialog box. Note that the Read-only recommended check box we marked in an earlier step is NOT the reason there is a button labeled Read Only in this dialog box- you'd see that button anyway.

Again, assuming that the user supplies the correct password to modify, NOW we see the indefatigable Read-only recommended option kick in:

So there you have it- a quick primer on Excel document security. Unfortunately, there is no simple menu command or toolbar button to remove document passwords. You will find that if you open an Excel workbook with full access, save a copy of the document, and open the copy, the file will still be password-protected.

Track changes (highlight, accept, and reject)

To turn on revision tracking in an Excel 2000 workbook, click Tools => Track Changes => Highlight Changes.... Selecting Track changes while editing also shares your workbook, which means that multiple network users can open and edit the document simultaneously.

The option When: specifies the unit of time for which you want to track changes. The default is All.

The option Who: specifies whether you want to track your own changes along with everyone else's (the default setting), or only to track the changes made by other editors (select Everyone but Me).

The option Where: allows you to track changes for only a specified range of cells. Use the Collapse Dialog button to mark your range, if this is the option that you need for change tracking.

Select Highlight changes on screen to make your revisions visible to you and your fellow editors.

To view tracked changes, hover your mouse pointer over the small blue triangle perched in the upper-left corner of a cell that contains changes. A call-out box similar to the comment box will appear, containing detailed information regarding any changes made.

To accept or reject changes in a worksheet, click Tools => Track Changes => Accept or Reject Changes.... In the Select Changes to Accept or Reject dialog box, specify what time frame for tracked changes that you'd like to review, whose changes you'd like to review, and for what range in the worksheet you'd like to review changes.

The rest of the procedure is fairly easy. The Accept or Reject Changes dialog box will present the changed information, one change at a time. You will then have the opportunity to either accept or reject each change. If you are getting tired of the whole mess, you can click Accept All or Reject All and just be done with the whole reviewing process.

Create a shared workbook

To make your workbook available to other network users and to allow simultaneous access, click Tools => Share Workbook.... In the Editing tab of the Share Workbook dialog box, place a check in Allow changes by more than one user at the same time. This also allows workbook merging. Remember that the workbook must be saved to a shared network location for other users to be able to access the file.

The Advanced tab allows you to set advanced configuration options related to issues such as whose changes "win" if two simultaneous users make a change to the same cell.

After saving a workbook as a shared workbook, the title bar will display [Shared] after the file name.

To stop sharing a workbook, simply uncheck the Allow changes by more than one user at one time option in the Share Workbook dialog box.

Merge workbooks

You can use Excel 2000's Merge feature to merge changes from one copy of a shared workbook into another copy of the same shared workbook. Some requirements to do this are that the two workbooks are indeed copies of one another, are both shared by using the procedure outlined above, and that they each have different names.

To merge workbooks, first open the original workbook. Next, click Tools => Merge Workbooks.... (This menu option is unavailable if the workbook has not been shared.) In the Select Files to Merge Into Current Workbook dialog box, browse and open the workbook(s) to be merged into the currently open workbook.

Excel will display changed information in the same manner in which it normally displays tracked changes. You can then go ahead and accept or reject each change as usual.