Importing and Exporting Data

To import data from a comma-separated value (.CSV) text file, click Data => Import External Data => Import Data.... In the Select Data Source dialog box, browse to the desired .CSV file and click Open.

The following screen capture shows the .CSV file that is used in this example.

In the Text Import Wizard-Step 1 of 3 dialog box, ensure that Delimited is selected and press Next.

In the Text Import Wizard-Step 2 of 3 dialog box, select Comma in the Delimiters option group and preview your data in the Data preview list box. Press Next to continue.

In the Text Import Wizard-Step 3 of 3 dialog box, select each column in the Data preview list box and choose an appropriate data type from the Column data format option group. Note that the General setting automatically determines whether the data in each column is text, a number, or a date. Press Finish to continue.

In the Import Data dialog box, decide where you want to place the newly imported data by making the desired choice from the Where do you want to put the data? option group. Click OK to complete the procedure.

To import data from a Microsoft Access database table, click Data => Import External Data => Import Data.... Browse to the Access .MDB file and click Open. In the Select Table dialog box, select the Access database table that you want to import into Excel and click OK.

In the Import Data dialog box, specify where you want Excel to place the imported data and click OK.

To import an HTML Web-based table into Excel, click Data => Import External Data => Import Data.... Browse to the HTML file that contains the table and click Open. In the New Web Query dialog box, use the arrow icons to select the table or tables that you want to import into Excel. Press Import to continue.

You should be well familiar with the Import Data dialog box by now.

Note in the following screen capture that imported HTML tables lose all of their formatting in Excel.

One simple way to export Excel data is to save an Excel workbook file in a different file format. Click File => Save As..., and in the Save As dialog box open the Save as type: drop-down list box. Note that you will be able to save the Excel file in a variety of different file formats, including MS-DOS and Macintosh file formats. Be aware, however, that most formatting will be stripped from your data in order to make the data conversion to alternate file formats, especially earlier file formats than Excel 2002.

To import Excel data into a Microsoft Access database table, open an Access database and click File => Get External Data => Import....

In the Import dialog box, open the Files of type: drop-down list box and select Microsoft Excel (*.xls). Then browse to the desired Excel file and click Import. I should say at this time that your Excel data should be in list format. That is to say, the very first row in the table should be somehow set off from the rest of the data (using bold or italic formatting, for instance), and there should be absolutely no spaces anywhere within the list. This is an important point. And by the way, the Excel file should be closed before you undertake the import procedure.

In the first page of the Import Spreadsheet Wizard dialog box, select First Row Contains Column Headings to ensure that Access doesn't consider your column headings as simply another row of data. Click Next to continue.

In the second page of the Import Spreadsheet Wizard dialog box, decide whether you want the imported Excel data to reside in a brand new Access table, or if you want to append the Excel data to an existing Access table. Click Next to continue.

In the third page of the Import Spreadsheet Wizard dialog box, select each field in the lower portion of the dialog box and optionally adjust each field's name and index status. Note that Access usually makes a pretty good guess as to what data type each field uses. For example, in the following screen capture, Access will assign the Date/Time data type to the Start Date field. Don't worry about not being able to adjust the data type in this dialog box: you will be able to make this change after the Access table has been created. Click Next to continue.

In the fourth page of the Import Spreadsheet Wizard dialog box, you must determine which field will serve as the primary key field. Select Let Access add primary key if you don't want to be bothered with this determination. In such a case, Access will create an ID field that will serve as the primary key field for the data table. Click Next to continue.

In the fifth page of the Import Spreadsheet Wizard dialog box, type a name for the new Access table and click Finish.

The following screen capture displays the Access 2002 Database window, with the newly created Excel table included.

To publish an Excel 2002 worksheet or workbook to the Web, click File => Save as Web Page....

In the Save As dialog box, use the Places bar or the Save in: drop-down list box to browse to the destination Web folder for the Web page. Alternatively, you can type a Uniform Resource Locator (URL) in the File name: box to publish the page directly to a Web server (assuming that you have appropriate permissions to the folder on the Web server). Select either Entire Workbook or Selection:Sheet to publish either the entire Excel 2002 file or only the active sheet.

Assuming that the Web site visitors who will be viewing your Excel Web page have the Office Web Components installed in their browsers, you can enable interactivity to allow them to manipulate the Excel worksheet or workbook information. Select the Add interactivity check box to enable this option. Click Publish... to specify Web publishing options.

In the Publish as Web Page dialog box, use the Choose: drop-down list box to select which sheet (or which parts of which sheet) you want to publish to the Web. Under Viewing options, open the Add interactivity with: drop-down list box to specify the desired level of Web interactivity. The two options are Spreadsheet functionality and PivotTable functionality. Under Publish as, click Change... to change the title of the Web page as it will appear in a Web browser window. Enable the Open published web page in browser check box to have Excel automatically load the Excel Web page into your default Web browser as soon as you press Publish.

The following screen capture shows sample Excel 2002 data as viewed in the Internet Explorer 6 Web browser, with the Office Web Components installed. Note that you can use the Web Components toolbar to perform many of the same operations on the data as you can from within Excel 2002.

Managing Workbooks

A template is simply a predefined set of formatting, macros, options, and, in some cases, boilerplate text that can be used over and over again. To create an Excel 2002 template, set up a workbook the way you want the template to appear each time that it is used and click File => Save As.... In the Save As dialog box, open the Save as type: drop-down list box and select Template (*.xlt) from the list. Note that ordinary Excel workbook files have the extension .XLS; Excel template files have the extension .XLT.

You should note that the view in the Save As dialog box automatically transports you to the default save location for Excel template files, which is

C:\Documents and Settings\user_name\Application Data\Microsoft\Templates

Name the template, click Save, and your new template is now ready for use. To use a template, click File => New.... The New Document task pane will appear. (As long as the Show at startup check box is enabled at the bottom of the New Document task pane, the task pane will appear each time Excel is started by using its Start menu icon.)

In the New from template list, select General templates... to view the templates that are available on your computer. Simply select the desired template and click OK. A new, unsaved Excel workbook file will be created based upon the selected template.

In the New from template list, select Templates from my Web sites... to view the templates that are available from a network or Internet location. In the New from template list, select Templates on Microsoft.com... to view the templates that are available from the Microsoft Web site.

To edit an existing Excel 2002 template, click File => Open.... In the Open dialog box, ensure that All Microsoft Excel Files or Microsoft Excel Files is listed in the Files of type: drop-down list box. Next, use the Places bar and the Look in: drop-down list box to browse for the desired template. Finally, use the Open button drop-down list to determine how you want to open the template file. You can open the file with read/write access (the Open option), open the file read-only, open a copy of the template with read/write access, or open and repair a damaged Excel template file.

Create workspaces

Imagine you are working on two or more Excel workbooks simultaneously, and have them all arranged the way that you want them. When you close down for the day, do you want to have to manually open all two or more documents and then manually arrange them again each time that you start a work session? Of course not. That is where workspaces come in. A workspace is a single file with an .xlw extension that stores all your view and position settings in one convenient place. Therefore, by launching your workspace file, Excel will automatically open all associated workbooks and restore the viewing configuration to how it was when you saved your workspace file.

To create a workspace file, arrange your workbooks and viewing configuration the way you want it to look. Next, click File => Save Workspace.... In the Save Workspace dialog box, name and store the file as you would any other Excel file. To use the workspace file, simply double-click it and watch the magic!

By the way, you should be noticing the slightly different appearance between Excel document files, template files and workspace files. Check out the following screen capture:

Consolidate data

Consolidating data in Excel simply means performing a calculation on data that resides on different worksheets of a single workbook. There are a few different ways to do this; however, because I got hosed on this part of the Excel 2002 Expert exam, I'm going to teach you the long way. However, the long way is the absolutely accurate way, so listen up! :)

Suppose we had a workbook with three sheets. On cell B8 of sheet3, we want to sum the range A1:A5 on sheet1 and the range C5-C10 on sheet2. This is the formula that you would need to perform this action:

=sum(sheet1!A1:A5,sheet2!C5-C10)

Note that references to other worksheets begin with sheet_name!, and are immediately followed by the desired cell address ranges. Remember to add the comma between different cell ranges. (If you are fuzzy on how to put together formulas in Excel, I refer you to my Excel 2000 Core and Excel 2000 Expert Cramsession study guides.) A formula that references data on multiple sheets in a workbook is known as a 3-D formula.

Audit a Worksheet

In the past, the exam objectives on the Microsoft Web site included both a "Check and review data" heading and the "Auditing a Worksheet" heading. This must have been a typo, because these topics cover the same information! In any case, I'm now going to teach you how to perform worksheet auditing in Excel 2002.

Formula auditing is simply the process of tracing the precedents and dependents of a particular formula cell, and of resolving formula errors. To perform formula auditing, it is helpful to have the Formula Auditing toolbar on screen. Click Tools => Formula Auditing => Show Formula Auditing Toolbar.

Here is an annotated screen capture of the Formula Auditing toolbar.

Find cells referred to in a specific formula

Cells that are referred to in a specific formula are known as precedents. To trace precedents for a formula, select the cell that contains the formula and click the Trace Precedents button on the Formula Auditing toolbar.

For example, in the following screen shot you can see the precedent cells that are referenced in the formula cell; the precedent cells are marked with a blue tracer arrow that points to the target cell.

To clear the tracer arrows, click the Remove Precedent Arrows or Remove All Arrows buttons on the Formula Auditing toolbar.

A dependent is a formula that refers to a specific cell. To view all formulas that reference a given cell, select that cell and click the Trace Dependents button on the Formula Auditing toolbar. For example, in the following screen capture cells C13 and E7 contain formulas that refer to the selected cell, which is cell C7.

To clear the tracer arrows, click the Remove Dependent Arrows or Remove All Arrows buttons on the Formula Auditing toolbar.

To scan an Excel 2002 worksheet for formula errors, click the Error Checking button on the Formula Auditing toolbar. Alternatively, click Tools => Error Checking....

The Error Checking dialog box will display each cell whose formula displays an error. You then have the option of getting online help regarding the error, stepping into the formula one step at a time, ignoring the error or manually correcting the error by using the Excel 2002 formula bar.

If you already know which cell contains an error, you can use the Trace Error button on the Formula Auditing toolbar to have Excel draw tracer arrows that reveal the cell's associated precedent and/or dependent cells.

The Watch Window dialog box is pretty cool when you want to observe both the underlying formula as well as the formula result for a cell when you are troubleshooting formula errors. To invoke the Watch Window, select the appropriate cell and click the Watch Window button on the Formula Auditing toolbar.

From within the Watch Window dialog box, click Add Watch.... In the Add Watch dialog box, ensure that the appropriate cell is selected and click OK.

Formatting Numbers

You can format your numbers in Excel any way that you want to. Simply select the appropriate cells and click Format => Cells.... Alternatively, right-click within the selected cell range and select Format Cells... from the shortcut menu.

The Number tab of the Format Cells dialog box is where you can specify your custom number formats. You can start with a number type by making a selection from the Category: list box. Next, you can customize how your numbers are displayed by manipulating the other formatting controls. For example, in the screen shot above, my numbers will appear in Currency style, with two decimal places, a dollar symbol, and with negative numbers displayed with a minus sign. Use the Custom category to create your own number formats. (Beware, the Custom category is not for the faint of heart: you will need to study Excel's number format codes closely to get the hang of how they structure number formats. Run a search in Excel 2002 online help for "number format codes" for more information.)

Use conditional formats

To apply conditional formatting to cells, select the appropriate cell range and click Format => Conditional Formatting....

In the Conditional Formatting dialog box, you are basically constructing an IF...THEN formula. If a particular cell value meets a predefined criterion or multiple criteria, then you will instruct Excel to format the cell data a certain way. To begin, open the first drop-down list box. The first condition will be based on either a cell value or a formula. Assume we are using a cell value for the sake of this example.

Next, open the second drop-down list box. These are what are known as Boolean operators. Select the appropriate Boolean operator and proceed to the next boxes. For between there will obviously be two text boxes; for other operators, such as greater than or less than, there will be only one text box. Type your condition into this area and click Format....

The Format Cells dialog box that appears only allows you to modify certain formatting properties, such as font style, underline, and color. After you have made your changes, click OK to close the Format Cells dialog box and click OK again to close the Conditional Formatting dialog box. The following screen capture shows a sample conditional format such that numbers less than 75 will display in bold, red, and underline, while numbers greater than 90 will display in bold and blue.

Oh yeah, I almost forgot to mention this: use the Add >> button to add multiple conditions. You can have up to 3 conditions in an Excel 2002 conditional format.

Using Macros

A macro is simply a sequence of actions that are recorded for easy playback. Macros save you time because you can condense multi-step procedures into a single easy click of the mouse. To record a macro in Excel 2002, click Tools => Macro => Record New Macro....

In the Record Macro dialog box, name the macro, assign an optional shortcut key and add an optional short description of what functionality the macro provides. An important question is where you are going to store the macro. Open the Store macro in: drop-down list box. Select This workbook if you want to attach the macro to the current workbook. Select New workbook if you want to attach the macro to another, blank workbook. Select Personal Macro Workbook if you want to make the macro available to all new workbooks that are based on the global template. Click OK to continue.

Carry out the steps of your macro by using the keyboard as much as possible (the macro recorder may not pick up mouse clicks). Use the Relative Reference button on the Stop Recording toolbar to instruct Excel to run the macro relative to the position of the active cell. Click Stop Recording when you are finished.

To run a macro, use the keyboard shortcut (if you remember it!), or click Tools => Macros => Macros.... In the Macros dialog box, select the appropriate macro from the list and click Run.

To delete any macros that you create, open the Macros dialog box, select the macro and click Delete.

Don't worry, don't worry: you are not required to know any Visual Basic for Applications (VBA) code in order to pass the Excel 2002 Expert MOUS exam. But you are required to know how to get into and out of the Visual Basic Editor, as well as how to make minor changes to the code. To edit a macro, open the Macros dialog box, select the macro and click Edit.

Although it looks daunting if you have never worked with programming code before, for the purposes of the exam, it really isn't that bad. All you will be asked to do is to make some very minor change, such as changing the font from Times New Roman to Tahoma or changing the font size from 10 to 12. In fact, the following screen capture shows where you would go in macro code to change the font size. The font name is two lines above what I've highlighted.

When you have finished making your changes, click the Save button in the Visual Basic Editor, and then click File => Close and Return to Microsoft Excel. There. That wasn't so bad, was it? :)

Working with Ranges

Recall from the Excel 2002 Core material that you can name a cell range by selecting the range, clicking within the Name box on the Formula bar, typing a name (without spaces) and pressing ENTER. Now let us consider how to use named ranges in formulas.

Basically, you can either type the range names in your formulas, or you can select the cell ranges with your mouse. When you select the cell ranges, Excel will automatically substitute the range names for the cell addresses. Consider the following example:

In the above example, named ranges were created for the ranges B6:B11 (AVGReading), C6:C11 (AVGWriting), and D6:D11 (AVGRithmetic). The AVERAGE formula in cell E12 uses these named ranges in its calculation, as can be viewed in the Formula bar. It is important to note than named cell ranges are separated by a comma, not a colon (:).

Let's look at the VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) functions, shall we? To get an idea on how lookup functions work, consider the following table:

To calculate Allen's commission bonus in cell F3, we will need to lookup up his total commissions for QTR 1 (cell E3) in the lookup table located in the cell range h4:I9. It looks as though his total will generate a bonus of 4%. See how the function works? Specifically, the syntax of the VLOOKUP function is

=VLOOKUP(lookup_value,table_array,col_index_num), where

lookup_value is the cell address of the value being looked up (E3)

table_array is the cell range of the lookup table (h4:I9)

col_index_num is the column number containing the return value (2)

Therefore, in this example we need the function =VLOOKUP(E3,h4:I9,2) to derive Allen's commission bonus. Of course, we can then use the formula =(E3*F3)+E3 in order to compute Allen's adjusted total commission income in cell G3.

The HLOOKUP function works identically to the VLOOKUP function; the only difference is that we are dealing with a horizontal data table instead of a vertical data table. Consider the following example (it should look pretty familiar to you!)

In this case let's compute Davis' bonus. Note that his total pre-bonus commission is located in cell E5, and the lookup range is now located in the range B17:h28. Therefore, the formula that we need is

=HLOOKUP(E5,B17:h28,2)

Therefore, Davis' bonus is 12%. Lookup functions are pretty cool, aren't they?

Now let's look at the standard-issue LOOKUP function. LOOKUP differs from VLOOKUP and HLOOKUP in that with LOOKUP you are providing the lookup value from within the formula. The syntax of the LOOKUP function is

=LOOKUP(lookup_value,lookup_vector,result_vector), where

lookup_value is the value being looked up in a lookup table.

lookup_vector is the cell range that is being referenced against the lookup value.

result_vector is the cell range that contains the value associated with the lookup value. The result_vector range must be the same size as the lookup_vector range.

A quick example: what would be the return value if we used the function =LOOKUP(24,000,A2:A8,B2:B8) with the data in the following screen capture?

You got it: 8%. Good job.

In terms of reference functions, they are a strange lot, to say the least. Reference functions reference row numbers, column numbers, or area numbers. I'm just going to give you the syntax of the major reference functions in a sort of scattergun style:

The ROW function returns the row number of a given reference. For example, the function =ROW(A25:C55) returns the value 25.

The ROWS function returns the number of rows involved in a reference or cell array. For example, the function =ROWS(A1:B4) returns the value 4.

The COLUMN function returns the column number of a given reference. For example, the function =COLUMN(D145:E220) returns the value 4.

The COLUMNS function returns the number of columns involved in a reference or cell array. For example, the function =COLUMNS(h21:O20) returns the value 8.

The AREAS function returns the number of contiguous cell ranges referenced. For example, the function =AREAS(A1:C3,D1:D3,E1:E3) returns the value 3.

Just because I have never used these reference functions, or ever foresee using them (do they seem unnecessary to you?), I provided you with them to ensure that I have fully covered the objectives of the Excel 2002 Expert MOUS exam.

Customizing Excel

To move or undock a toolbar, simply click and drag it by its move handle. The move handle is the ridge that runs along a toolbar's leftmost side.

When a toolbar is undocked, it can be closed by clicking the Close button (except for the Menu toolbar). You can always show or hide toolbars by clicking View => Toolbars, and making the appropriate selection(s) from the submenu. Toolbars can be docked on any side of the Excel interface: top, bottom, left or right. It's all good.

Now how about adding or removing buttons? Well, for that you need only click the Toolbar Options arrow and then click Add or Remove Buttons. Simply select or deselect any button from the submenu; click Reset Toolbar to reset the toolbar to the factory default configuration. Click Customize to open the Customize dialog box...we will get to that a bit later.

The Toolbar Options arrow and the Add or Remove Buttons menu is such a wonderful addition to the Office XP products. In prior versions, you had to work strictly from within the confines of the Customize dialog box, which was a tedious and non-intuitive affair. Speaking of which, why don't we open the Customize dialog box right now and see what treasures it has to offer? Another way of doing this is by clicking Tools => Customize....

On the Commands tab of the Customize dialog box, you can select a category and then click and drag a command to a toolbar or to a menu. To add a command to a menu, just hover your mouse over the menu- the menu will then expand, allowing you access to its contents. By the way, you can also drag any existing toolbar button or menu command into the document area to get rid of it. Click Description to invoke a short description of a command. The above screen capture shows the description text for the Save Workspace command.

Now let's create a new toolbar. Navigate to the Toolbars tab, and click New....

In the New Toolbar dialog box, type the name of your new toolbar and click OK.

Now comes the fun part. Navigate to the Commands tab and simply begin dragging toolbar buttons on to your new toolbar. You can dock, undock your toolbar as you wish. By default, the new toolbar will be available in the Normal template, and will thus be available for all of your new documents.

The Toolbars tab of the Customize dialog box is also where you manage toolbars, in case you haven't already guessed. Check or uncheck any toolbar to show or hide each toolbar, respectively. Select a built-in toolbar and click Reset to reset the toolbar to its factory defaults. Select any of your own homemade toolbars and click Delete to, well, delete the toolbar.

To create a new menu, navigate to the Commands tab of the Customize dialog box, select New Menu from the Categories: list box, and select the New Menu command to the appropriate location on the Excel 2002 Menu bar.

To add commands to the menu, select the commands from the Commands tab and drag them up into the new menu. To rename the menu, right-click the menu name. In the Name: text box, enter the new name. Place an ampersand (&) character before the letter that you want to serve as the hotkey for the menu.

Summarizing Data

Subtotals are a great way to summarize data. For example, consider the following list:

Wouldn't it be nice to get subtotals for each category, considering that there are several separate entries for each category? Here's how to do subtotals. First, ensure that you have an Excel list (contiguous "block" of data, with first row set off in formatting from the rest of the data). Second, select a cell within the list and click the Sort Ascending button . Finally, click Data => Subtotals....

In the Subtotal dialog box, open the At each change in: drop-down list box and select the column that you want to break up by using subtotals. In this examples, it is Category. Next, Open the use function: drop-down list box and decide what kind of function you want to apply. You can choose SUM, AVERAGE, MIN, MAX and several other functions. Finally, in the Add subtotal to: option list, select which column contains the data that you are going to perform arithmetic on. In this example, the data is located in the Amount column. The following screen shot shows the previously displayed list summarized with subtotals.

Pretty neat, eh? You can use the buttons to show or hide levels of detail in your subtotaled list. By the way, your list is now in what is called outline format. With subtotals, you have only outline levels 1 and 2 (which can be shown or hidden by using the buttons).

You can apply subtotals to ranges as well as to entire lists. Just remember to perform an ascending sort first, and select the range before applying subtotals.

To remove subtotals from a list, select a cell within the list, invoke the Subtotal dialog box and click Remove All.

To apply a quick filter (also called an AutoFilter) to a list, select a cell within the list and click Data => Filter => AutoFilter.

Use the filter arrows for any column that you want to filter. To remove a filter, select (All) from the filter list. Alternatively, click Data => Filter =>AutoFilter.

One limitation of the AutoFilter is that you can filter by only a single column. To perform an advanced filter, click Data => Filter => Advanced Filter.... Here is the confusing part, for those who are not "in the know": you actually have to use some free space on the worksheet to construct your own query table! Strange, but effective. Check out the following screen capture.

In the Advanced Filter dialog box, use the Collapse Dialog button in the List range: text box and select the list range. In this example, that range is A1:D22. Next, select the Criteria range: text box and select the range that comprises your homemade query table. Note that you must select the column headings as well as the data, and the column headings must be spelled identically to the column headings in the list. In this example, the criteria range is F4:G5. Pressing OK executes the filter. (By the way, in the example I filtered for all records from the category Printing that also had an Amount value of 25.00.) You can create some pretty advanced filters this way, as the following example shows.

To remove an advanced filter, click Data => Filter =>Show All.

To outline a list, verify that you have sorted the list such that similar information occurs together. Also ensure that you have a summary row that Excel can use to outline your data. For example, consider the following list:

In the above screen capture, I used the SUM function to create summary rows that also serve as subtotals. Note that there exists a subtotal row beneath each break in the Salesperson column. To have Excel create an outline for you automatically, select the list range and click Data =>Group and Outline => Auto Outline.

Note that you can use the outline level buttons or the buttons to expand or collapse your outline. To remove the outline, select a cell within the outlined list and click Data => Group and Outline => Clear Outline.

You can manually group rows or columns, also. To do this, select the rows or columns to be grouped (see my Excel 2002 Core study guide if you require this procedure), and click Data => Group and Outline => Group.... You will see that the grouped columns or rows behave as if there were outlined. To remove grouping from columns or rows, select those columns or rows and click Data => Group and Outline => Ungroup....

Data validation is cool. With it, you can force your users to enter consistent information when they are doing data entry on your worksheets. To perform data validation, select the cell that will be protected by data validation and click Data => Validation....

On the Settings tab of the Data Validation dialog box, open the Allow: drop-down list box and select what type of data you want to allow in the cell. Choices include Whole number, Decimal, List, Date, Time and Text Length. Select Ignore blank if you don't want Excel to display an error message if the user leaves the cell contents blank. Use the Boolean operators that are contained in the Data: drop-down list box to construct your constraint. For example, the following screen capture shows a data validation mechanism whereby only whole numbers between 10 and 20 are allowed. Any value outside of this range (including blank value cells) will produce an error message.

And here is the aforementioned error message:

On the Input Message tab of the Data Validation dialog box, select Show input message when cell is selected if you want Excel to display ScreenTip text as soon as a user enters the cell. Note that this text will pop up before the user types anything in the cell! Use these messages sparingly, please, because they can tend to really annoy after awhile. The following two screen captures show the data validation setting and the resulting ScreenTip box for a sample input message.

Click the Clear All button on the Input Message tab of the Data Validation dialog box to clear out the message in "one fell swoop." Navigate to the Error Alert tab if you want Excel to produce a custom error message only if the user enters data that does not meet the predefined data validation requirements. You can select the system icon that is displayed by making a selection from the Style: drop-down list box. The Title: text box allows you to customize the dialog box's title bar. The Error message: box allows you to customize the dialog box message text.

Retrieve external data and create queries

What we are going to do here is use Microsoft Query to access data stored in a Microsoft Access 2002 database. To begin, click Data => Import External Data => New Database Query. If you don’t have the Microsoft Query add-in installed on your machine, you will be prompted to do so (you must have your Office XP installation CD-ROM handy to do this successfully).

In the Choose Data Source dialog box, select MS Access Database* and click OK.

In the Select Database dialog box, use the directory list controls to locate the desired Microsoft Access database and click OK.

In the Query Wizard-Choose Columns dialog box, select each field from the target table that you want to import into Excel and use the arrow buttons to move them to the Columns in your query: list box. Use the double-arrow buttons to move all fields at once. Click Next> to continue.

The Query Wizard-Filter Data is really where the actual querying takes place. Select your fields to be queried in the Column to filter: list box. Next, under Only include rows where:, set up a query condition. In the following example, only database records where the UnitsInStock field value is greater than 1000 will be imported into Excel. Click Next> to continue.

In the Query Wizard-Sort Order dialog box, you can specify up to three sorting criteria. In the following example, the Access table data will be sorted first by the ProductID field, and then by the ProductName field, both using an ascending (A to Z) sort order. Click Next> to continue.

In the Query Wizard-Finish dialog box, ensure that Return Data to Microsoft Excel is selected in the What would you like to do next? option list and click Finish.

Before you actually see your Access data in Excel, you must tell Excel where to place the imported information. You should be very, very familiar with the Import Data dialog box by now. Perhaps to the point of nausea.

Create Extensible Markup Language (XML) Web queries

We have essentially performed this procedure in the "Importing Data to Excel" portion of this study guide. However, since as an instructor I feel that redundancy is a good way to drive a concept home, let's do it again! Basically an XML Web query is simply importing an HTML table into Excel. That's all there is to it. Forget about what you know about traditional database queries, such as those that you might perform in Access.

To snag a Web table, we can go one of two routes: through the Microsoft Internet Explorer browser, or through the Excel interface. Let's go the browser road first. First, browse to the Web page that contains the table you want to import into Excel. Next, click the Edit button drop-down arrow on the Internet Explorer toolbar and select Edit with Microsoft Excel.

Excel will launch if it isn't already open, and the New Web Query dialog will appear with the Web page loaded.

The other way to do this is the method previously discussed: open Excel, click Data => Import External Data => New Web Query.... Type the URL of the Web page in the Address: combo box and click Go to load the page. Use the button to select the HTML table that you want to import into Excel and click Import.

In the Import Data dialog box, make the appropriate selections and click OK to import the HTML table data into Excel 2002.

Analyzing Data

Microsoft is very proud of PivotTables. With fairly good reason, because they are an excellent way to summarize and dynamically view list data. Let's create a PivotTable, shall we?

To illustrate this process, we are going to look at another example. Check out this list:

That's quite a bit of data to digest all at once, isn't it? Let's summarize it. To create a PivotTable, select a cell within your list range and click Data => PivotTable and PivotChart Report....

In the PivotTable and PivotChart Wizard-Step 1 of 3 dialog box, ensure that Microsoft Excel list or database is selected for Where is the data that you want to analyze?, and that PivotTable is selected for What kind of report do you want to create?. Click Next> to continue.

The second step in the PivotTable wizard asks you to verify the list range. Click the Collapse Dialog button to tuck the window out of the way and allow you to verify the range. If Excel guesses incorrectly, use you mouse to select the proper range. Click Next> to continue.

In step 3 of the PivotTable wizard you simply decide where you want Excel to place the new PivotTable, either on a new worksheet or embedded on the current worksheet. It is generally easier and cleaner to put the PivotTable on its own worksheet. Click Finish to continue.

As you can see from the following screen capture, actually populating a PivotTable with data is a drag-and-drop affair. Simply select the appropriate fields from the PivotTable Field List and drop them in the appropriate field in the PivotTable. In this example, let's suppose that we want to summarize the data by customer number, showing the amount billed to each customer by each salesperson involved with the customer. To do this we need to drop the Cust. No. field to the Drop Page Fields Here area, drop the Salesperson field to the Drop Column Fields Here area, and drop the Amt. Billed field to the Drop Data Items Here area of the PivotTable report.

If you don't like all this dragging and dropping, there is another method. Select a field from the PivotTable Field List, select a PivotTable area from the drop-down list in the same sector and click Add To.

Check out the above screen capture closely: notice how compact the data is. You can use the drop-down arrows for most PivotTable fields to filter data even further. In the above illustration, for example, we can open the Cust No. drop-down and select another customer number.

You can remove fields from a PivotTable by selecting the grey field labels and dragging them into an unused space on the worksheet. You can then select another field from the PivotTable Field List and build another PivotTable view.

To quickly format a PivotTable report, click the Format Report button on the PivotTable toolbar. In the AutoFormat dialog box, select a style and click OK. Nothing more to it than that (at least for our purposes).

Now how about a PivotChart report? Follow the same procedure for creating a PivotTable report, with the exception that you should select PivotChart report (with PivotTable report) in step 1of the PivotTable wizard.

To add PivotTable fields to the PivotChart, follow the same procedure that was outlined previously.

The following PivotChart (ugly though it is, in its raw, unformatted state) shows the very same view and data that the PivotTable in the previous portion of this document showed.

To format a PivotChart, use the Chart toolbar controls. Select a chart object from the Chart Objects drop-down list and then click Format to invoke the corresponding Format dialog box. Following is an annotated image of the Chart toolbar.

Oh yeah: one final note regarding PivotTable and PivotChart reports. When the underlying data changes in the source list, you may need to manually update the PivotTable or PivotChart data. Simply click Refresh Data on the PivotTable toolbar.

We will discuss three what-if analysis tools: Goal Seek, Solver and Scenarios. A what-if analysis changes variables to see how it impacts a final result. For example, we can change the interest rate or the terms on a car loan to calculate the overall price or payoff date of an automobile.

Before we use any of these analysis tools, they must be loaded into Excel. Click Tools => Add-Ins..., select the appropriate add-in components in the Add-Ins dialog box and click OK. For our purposes, you will need to load the Analysis ToolPak and the Solver Add-in.

Now, getting back to Goal Seek, consider the following example:

The PMT function in cell B5 shows a monthly payment of $372.69 for a loan amount of $18,599.00 with an interest rate of 8% over a loan term of 60 months. (By the way, you can learn about the PMT function by reading my Excel 2002 core study guide here at Cramsession.). What if we could only afford to pay $300 per month? How would this affect our loan term? How about if we adjusted the interest rate by switching to another bank? Well, let's run a Goal Seek analysis on the data.

Select a cell within your data table and click Tools => Goal Seek.... In the Goal Seek dialog box, click within the Set cell: box and then select the appropriate cell in the Excel worksheet. In this example it will be cell B5, which contains the monthly payment formula. Next, click within the To value: box and enter the changed value. In this example we'll use -350. You MUST use the minus sign to denote a negative value because payments that are computed by using the PMT function display as negative numbers. If you don't make the To value: value negative, then Goal Seek will fail. Finally, click in the By changing cell: box and select the cell that will be made variable during the operation. In this example it is cell B3, because this cell contains the interest rate. Click OK to begin the Goal Seek process.

Excel will display the Goal Seek Status dialog box when it finds a solution. Press OK to accept the change to your data; press Cancel to undo the change.

You can 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 want to see a net gain for the salesperson named Addams of $30,000. We can have Solver determine the new budget value for all four salespeople by changing their individual budget values, given the company's budget cap of $221,553. To start Solver, click Tools => Solver.... In the Solver Parameters dialog box, use the Collapse Dialog button in the Set Target Cell: dialog box to shrink the window and allow you to select the cell that will be reset to a new value. In this example, that is cell B5, which contains Addams' Net Gain value. Because we want this cell to equal $30,000, we must select Value of: in the Equal To: option group and type 30000 into the box. We next want to define the range of cells that will change in order for Solver to find us a solution; type or select the appropriate range in the By Changing Cells: text box.

Finally, we must define the maximum value that Excel can use as it changes the values in the cell range B3:E3 in finding a solution to this scenario. To do this we must add a constraint. Click Add in the Subject to the Constraints: portion of the Solver Parameters dialog box. In the Add Constraint dialog box, mark the cell reference, select = as your operator and type 221553 into the Constraint box. Then click OK.

Click Solve in the Solver Parameters dialog box in order to let Solver find a solution. When it does, you will see the following dialog box:

Select the appropriate choice and click OK to finish the procedure.

Scenarios allow you to ask "what-if" questions of your Excel data, and save the results for reuse. Before we begin, consider the following sample data:

In this example, we will ask the question "What if we increased our quantity sold 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. In the following screen capture, you will notice that the range C3:C8 has been defined in the Edit Scenario dialog box.

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 Qty, 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 in the Scenario Manager dialog box to display the scenario results. It seems I always tell you this after the fact, but Microsoft advises that you create a scenario with your original data before you create any additional scenarios. The reason for this is because the scenario permanently changes your data (you can always undo, of course, but that isn't going to help you if you close out of Excel and open the file again at a later time.) So ALWAYS create a scenario called "Original Data" or some such thing with your original data.

Workgroup Collaboration

To password-protect an Excel 2002 workbook, click Tools => Options, navigate to the Security tab, and examine the File encryption settings for this workbook options.

In particular, the Password to open: setting completely locks a workbook. The following screen capture displays the dialog that appears when you try to open a workbook for which a Password to open password has been applied.

On the other hand, the Password to modify: will allow a workbook to be opened read-only at the least, and with full access if you have the proper password. The following screen capture displays the dialog box that appears for a workbook for which a Password to modify password has been applied.

The following screen capture shows the dialog box that appears if you select the Read-only recommended option in the Options dialog box. This dialog box will only appear if the person opening the workbook has supplied the correct password to unlock the document.

You can also apply protections to an Excel worksheet or workbook. Protections differ from completely locking a document; protections limit what a user can do on a particular worksheet or workbook.

To apply protection to a worksheet, click Tools => Protection => Protect Sheet.... In the Protect Sheet dialog box, supply an optional password and decide what actions you want to allow on the sheet by making the appropriate selections from the Allow all users of this worksheet to: list box.

If a user attempts an action that has been prevented through worksheet protection, they will see the following dialog box.

To protect an entire workbook, click Tools => Protection => Protect Workbook.... In the Protect Workbook dialog box, decide whether you want to protect only workbook structure (prevents worksheets from being added, moved, renamed, or deleted), and/or workbook window positions. Again, you can supply an optional password.

To unprotect a protected worksheet or workbook, click Tools => Protection => Unprotect Sheet..., or Unprotect Workbook..., respectively, and supply the correct password.

A cool new feature of Excel 2002 is the ability to password-protect specific ranges of a worksheet. To do this, select the first range and click Tools => Protection => Allow Users to Edit Ranges....

In the Allow Users to Edit Ranges dialog box, click New... to specify a range.

In the New Range dialog box, name the protected range and enter the appropriate cell range in the Refers to cells: dialog box. You can use the Collapse Dialog button to scoot the New Range dialog box out of the way and assist you in locating the appropriate cell range. Finally, supply an optional password. Clicking Permissions... will allow you to specify discrete user accounts who will be allowed to edit the range without being prompted for a password. These accounts are either network accounts or accounts that reside on the local computer (if the local computer is running Windows NT, Windows 2000 or Windows XP). We won't get into this subject any deeper, for it goes well beyond the depth necessary for the Excel 2002 Expert MOUS exam.

Create a shared workbook

A shared workbook is an Excel 2002 workbook that is available for multi-user access. Sharing a workbook from within Excel does not make the file available to users across a network- this must be accomplished by using the operating system. However, file sharing in Excel does allow more than one user to open a workbook simultaneously to make changes to it.

To share a workbook, click Tools => Sharing.... In the Share Workbook dialog box, enable Allow changes by more than one user at the same time. This also allows workbook merging.

The Who has this workbook open now: list box will display every user who currently has the file open. To disconnect a user (and have the user lose all of his or her unsaved work), click Remove User. Navigate to the Advanced tab of the Share Workbook dialog box in order to customize how the file is shared among multiple users.

Revision tracking is always a fun subject. To enable revision tracking for an Excel 2002 workbook, click Tools => Track Changes => Highlight Changes....

In the Highlight Changes dialog box, enable Track changes while editing. This also shares your workbook. Beneath Highlight which changes, determine when you'd like your changes tracked (options include All, Since I last saved, Not yet reviewed and Since date), who you want to track (options include Everyone and Everyone but Me), and (optionally) which portion of the workbook you want to track changes on -- the default is the entire thing, of course. Finally, the Highlight changes on screen clearly shows any edits that are made to the document.

As long as revision tracking is turned on and changes are configured to appear on screen, any cell that contains an edit will contain a small, dark-colored triangle. You can hover your mouse pointer over this triangle to display a ScreenTip that contains a description of the change.

To review changes in a document, click Tools => Track Changes => Accept or Reject Changes....

In the Select Changes to Accept or Reject dialog box, decide the "who, what, and where" and click OK to begin the edit review process.

P ALIGN="LEFT">The Accept or Reject Changes dialog box will give a full description of the change that was made as well as the user who made the change. Use the buttons at the bottom of this dialog box to determine the fate of each edit. (if you are in a particularly nasty mood, you can just click Reject All and be done with it.)

Merge workbooks

To merge changes between two or more Excel workbooks, a few requirements must be met. First of all, the original document must be shared, per my instructions a bit earlier in this document. Second, both files must reside in the same location. To perform a document merge, open the original document and click Tools => Compare and Merge Workbooks....

Browse to the modified file and click OK. That's all there is to it! Unless revision tracking was turned on, you won't see any revision marks in the original document after it has been merged with the copy.