This Chapter Shows How You Can Use Excel 2002 to Perform the Following Tasks

Excel 2002

Microsoft® Excel 2002 gives you and your students the ability to gather data, create worksheets and reports, analyze results, and make your findings available to others on the Web. Excel can help you manage your grading and attendance records, and your students can use Excel for data analysis or data charting.

Assume, for example, that you are teaching an integrated science and history course with another teacher. You want your students to understand how the physical world influences the course of history by studying the shipwreck of the Edmund Fitzgerald and how the ship’s destiny was altered by weather. Your students will use Excel 2002 to create worksheets and reports that display and analyze data about shipwrecks, how water and weather conditions affect survival rates, and analyze data about weather patterns and wave size.

This chapter shows how you can use Excel 2002 to perform the following tasks:

Create and add data to a workbook.Your students can track and analyze static or changing data.
Use templates.You can use templates to create workbooks that have a similar structure, or that use the same formulas.
Import data.You can import data from a variety of sources. When you import data from the Web, you can create a refreshable Web query to automatically update the spreadsheet when the source data changes.
Work with numbers, formulas, and functions.You can integrate complex formulas into a workbook so that data can be calculated automatically.
Create charts and tables.You can create PivotChart® and PivotTable® dynamic views to display complex information in a way that is easy to understand.
Share files.You can make workbooks available so that they can be created and managed collaboratively.
Protect files.You can add password protection to files to prevent them from being modified or deleted.

New in Excel 2002

Excel 2002 offers a variety of new features designed to help you collaborate and share information through the Web and to perform more extensive data analysis.

Improved Web interactivity.Now you can save an Excel workbook in a special Web archive file format (also available for Microsoft Access, Microsoft PowerPoint®, Microsoft Publisher, and Microsoft Word), which allows you to save all of the elements of a Web site, including text and graphics, into a single file. You can save workbooks directly to Web sites on Microsoft MSN®, and from MSN share and collaborate with others over the Web.

You can open an Excel workbook that you have saved as a Web page in Excel from Microsoft Internet Explorer. This gives you more flexibility in presenting information, because students can view workbooks from computers that do not have Excel installed.

Refreshable Web queries.You can add a Web query from a frequently updated Web site to a worksheet, and have the Web query automatically refresh. A Web query is a query that retrieves data stored on the Internet or your intranet.
Smart Tags.You can use Smart Tags in Excel to help you format items that you have pasted from the clipboard, or automatically look up financial information about stock symbols. For more information about Smart Tags, refer to the Introduction to this book.

AutoCorrect options.AutoCorrect fixes your spelling and typing mistakes when you enter data into a worksheet, and automatically formats text as you type it. For example, you can automatically add a link to a URL when you type it. Smart Tagsgive you more control over the AutoCorrect options. You can access the AutoCorrect options by clicking AutoCorrect Options on the Tools menu.
Error checking.Excel 2002 checks formulas automatically when they are entered, and gives you options for correcting the formula, through the use of a Smart Tag.

Exploring Excel 2002

Excel 2002 is similar in appearance to other Office XP applications, with familiar toolbars and menus.

Creating an Excel Workbook

An Excel file is made up of worksheets, which collectively are called a workbook. Worksheets are like pages within a workbook and can contain data or be blank. Worksheets can work independently or together to form a workbook. You can create a blank workbook to which you add and format data yourself. Or you can use a template that contains formatting, formulas and layout and then populate it with data, such as grades or attendance data.

If you are new to Excel and need information about navigating an Excel workbook, refer to In and Out of the Classroom with Office 2000.

To create a new blank workbook, you can open Excel and then choose the type of file you want from the New Workbook task pane on the right side of the Excel window. Alternatively, you can create a workbook from a template.

Templates are useful for creating workbooks that require complicated formatting or formulas, because you can use the same template to create more than one workbook. For example, you can use a grade book template to create several grade books without re-entering the data and formulas. Templates are included with Excel2002; you can also find them at by clicking the “Templates at Microsoft.com” option on the bottom of the New Workbook task pane. You can also create your own templates.

Adding Data to a Workbook

Excel2002 gives you many options for the types of data you can add and how you format the data. You can:

Add data manually.This is useful when you enter data often or when you use a source of information that is not easily copied from another file.
Import data.You can import from the Web by using a Web query or from other sources by using the Data Connection Wizard. For more information on the Data Connection Wizard, refer to Excel Help.
Copy data from another file.You can use a file that contains information, such as a table with weather data over the course of a month, and copy it into a workbook for use as a data source to create charts or other analysis.

Your students are studying the wreck of the Edmund Fitzgerald and how weather conditions might have affected the wreck. As a first step in this project, your students create a simple workbook from a Web page that shows the water surface temperature from many different sources over a month. The workbook contains a large amount of data that can be arranged and analyzed in many different ways.

Analyzing Data in Excel

Excel 2002 provides a robust set of data analysis tools. With Excel2002, you can create formulas to perform simple calculations automatically, such as finding the sum of a list of numbers on your worksheet or the average of the values in an entire worksheet. You can use conditional formatting to make data that meets certain criteria stand out from the rest of the entries. You can also look for trends in your data or correlations between data sets by creating charts and graphs.

Using formulas

Formulas are equations that perform calculations in a worksheet. They can contain any or all of the following: functions, references, operators, and constants. You can use formulas to automatically calculate the sum, average, or other function for a range of cells. For example, you can add a formula to a cell that calculates the average wind speed at a shipwreck location in a month.

In this example, you will take weather data from the Great Lakes region, and calculate the average temperature over a period of time.

To add an Average function to a workbook

Open Buoy.xls, and then place the cursor in the cell at the bottom of the ATMP field.

On the Standard toolbar, click the arrow on the right side of the AutoSum button , click AVERAGE, and then click OK.
Make sure that the entire range of cells in that column is highlighted, and then press ENTER to accept the average formula.
The cell now contains the average temperature for that 24-hour period, and will automatically change every day that the Web query is refreshed.

For more information about formulas in Excel, refer to “Adding formulas to a worksheet” in the tutorial In and Out of the Classroom with Office2000 Professional.

Filling cells

Your students have now created a large workbook with weather data from many sources over the period of a week or month. They can now analyze the data in different ways. For example, your students can sort the data by the school where it was collected, and then add an average function in the temperature column to see what the average temperature was for the month for that school.

In Excel 2002, your students can automatically fill cells with data without manually entering data and formulas into cells. This is especially useful when you have many rows or columns of data and you need to add an existing formula to those cells.

To fill cells with or without formatting

Select the cell that contains the average air temperature, at the bottom of the ATMP column.

With the mouse, grab the square at the lower right corner of the cell border, drag the fill handle across the cell directly to the right, and then release the mouse button.
Click the new Smart Tag that appears at the lower right of the highlighted cells. Select a formatting option from the Smart Tag dropdown list. For example, choose Copy Cells. This cell now contains the average water temperature for the day.

Verifying your formulas

Excel provides several tools that assist you in verifying the accuracy of your data analysis. You can find and correct problems with formulas by using the following features:

Watch windows.You can watch a cell or range of cells that are out of view to monitor how they change based on other formulas you may be working on. A watch window is a window that stays open and shows the current value of a cell that you cannot see in the current Excel window.
Formula error checker.When you enter a formula in a cell, Excel2002 automatically checks it for errors. If there is an error in a formula, you can click the exclamation point Smart Tag to display options for correcting the error.
Formula auditing toolbar.This toolbar contains buttons for checking for errors in formulas, and for showing and hiding a watch window.
Evaluate formula dialog box.You can use this tool in Excel 2002 to evaluate formulas and their results before releasing a final version of a workbook.

To use the formula error checker

The formula checker is a useful tool that can save you time in entering formulas. When you manually enter an incorrect formula, or create a formula with the AutoSum button, the formula error checker notifies you immediately that there is a mistake in the formula.

To see how the formula error checker aids you in entering formulas, enter a flawed formula, such as =1/0 or =AVERGE(B1:B3), in a blank cell of a workbook.

The formula error checker appears as an exclamation point icon.

Click the Smart Tag, and a menu opens that gives you options for correcting the formula, such as Help on this error and Show calculation steps.

Charting Data

You can create charts in Excel for a visually appealing and clear representation of data. Charts can help your students see comparisons, patterns, and trends in data. For example, they can quickly analyze water temperature, air temperature and wind speed over a period of time without focusing on several columns of data.

Excel makes it easy to create charts from data in a workbook and there are many chart types to choose from in Excel, including bar, pie, area and bubble charts. You can create a chart, and then change the chart type later. For example, you can create a bar chart from a range of data, and later decide to use a pie chart instead. To make that change, you simply change the formatting of the chart by right-clicking the chart and then clicking Chart on the drop-down menu, or clicking Chart Type in the Chart menu. You can also expand the data range or add new data, and refresh the chart instead of regenerating it.

To create a chart

Open Buoy.xls.
Select the entire range of times in the time column.

Hold down CTRL, select the entire range of data in the ATMP and WTMP columns, and then release CTRL. By holding down CTRL, you can select rows, columns, and cells that are not adjacent.

Click the Chart Wizard button .
Select the chart type you want and the chart sub-type, and then click Next.

There are many chart types to choose from, including column, line, bar, and scatter (xy). For this exercise, choose a line graph.

NoteYou can click Finish after choosing the chart type, and the Chart Wizard creates a chart with the default values and adds it to the present worksheet as an object.

In step 2 of the Chart Wizard (Chart Source Data), click Next.
In step 3 (Chart Options), type a name for the graph, and then in the Category (X) axis text box, type Time and in the Value (Y) axis text box, type Temperature. Click Next.
In step 4 (Chart Location), page, make sure that the As object In option is selected, and that Sheet1 is selected from the drop-down list. Click Finish, and a chart appears in your worksheet.

You can move and resize the chart, and you can change any options you set in the Chart Wizard by right-clicking on the chart and selecting one of the formatting options. For example, if you right-click in the white space of a chart, and then select Source Data, you can name the lines in a line graph so that they appear in the legend.

Sharing Files

Excel 2002 allows you to easily share files with others. You can collaborate by sending workbooks for review, creating forms, and adding password protection to a file. Students who are collaborating together can easily track who added edits, comments, and additional data to a worksheet, use forms to perform the data entry, and secure files that contain private information.

Sending a file for review

You can use the Send to Mail Recipient (for Review) command to send a file for others to review. You can have your students collaborate on a project by sending workbooks to each other and accepting and commenting on the changes. You can also have your students send drafts of workbooks to you for review during the process of creating their analysis.

When you send a file for review, a review request form is created automatically with the following information:

A link, an attachment, or both a link and an attachment to the file.
A follow-up flag.
Message text for the reviewer. This text varies depending on the location of the file.

You can include a file in a review request in one of three ways, depending on the location of the file:

If the file is on your computer only, it is attached to the review request automatically.
If the file resides in a shared location, such as a network server, the review request contains a link to the shared location. You also have the option to include the file as an attachment. If some reviewers do not have access to the shared location, you may want to attach the file.
If the file resides on a Web discussion server, such as a server running Microsoft SharePoint™ Team Services, the review request includes a link to the document discussion and includes instructions on how to use a discussion server. You have the option to include the file as an attachment. If some reviewers do not have access to the shared location, you may want to attach the file.

When a reviewer receives the file, the reviewing tools are enabled and displayed. Files returned from reviewers with edits and comments automatically prompt you to merge changes. You can then use the reviewing tools to accept or reject the changes. After you’ve received all feedback from the reviewers, or after you decide to stop accepting feedback, you can choose to end the review cycle. For more information about sending a file to review, refer to “Reviewing Your Document” in the Word chapter in this book.

Protecting Your Files

Excel 2002 gives you the ability to protect your files to prevent data loss or manipulation. Adding protection to a workbook, or a worksheet within a workbook, gives you many options for how you share information with students or co-workers. For example, you can add a password to a workbook so that only those with the password can read or change data in the workbook.