Excel 2002 - Valencia College



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.

[pic]

• 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 Tags give 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.

[pic]

Exploring Excel 2002

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

[pic]

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 Excel 2002; you can also find them at by clicking the “Templates at ” option on the bottom of the New Workbook task pane. You can also create your own templates.

[pic]

Adding Data to a Workbook

Excel 2002 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 Excel 2002, 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

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

1. On the Standard toolbar, click the arrow on the right side of the AutoSum button [pic], click AVERAGE, and then click OK.

2. Make sure that the entire range of cells in that column is highlighted, and then press ENTER to accept the average formula.

3. 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 Office 2000 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

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

4. 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.

5. Click the new Smart Tag that appears at the lower right of the highlighted cells. Select a formatting option from the Smart Tag drop-down list. For example, choose Copy Cells. This cell now contains the average water temperature for the day.

[pic]

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, Excel 2002 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.

[pic]

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

1. Open Buoy.xls.

2. Select the entire range of times in the time column.

6. 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.

7. Click the Chart Wizard button [pic].

8. 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.

Note   You 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.

9. In step 2 of the Chart Wizard (Chart Source Data), click Next.

10. 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.

11. 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.

[pic]

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.

You decide to add password protection to the grading workbook you store on the school server. You want all the teachers in your school to be able to view the workbook, but only the teacher with whom you are co-teaching to be able to enter or change data in the workbook.

To add a password to a workbook

1. On the Tools menu, point to Protection, and then click either Protect Workbook or Protect Sheet.

12. If you chose Protect Workbook, select or clear the Structure and Windows check boxes.

13. If you chose Protect Sheet, type a password, and select or clear the options in the Allow all users of this worksheet to list.

[pic]

When you attempt to modify a protected worksheet, you will be prompted to remove the protection by clicking Protection on the Tools menu, where you will be asked for the password.

Note   If you lose or forget the password, it cannot be recovered. You can, however, paste the contents of a read-only version of the worksheet or workbook into a new blank workbook and save it as a different workbook.

Using AutoRecover

You can protect your work by using the AutoRecover feature to periodically save a temporary copy of the file you’re working on. To recover work after a power failure or similar problem, you must have turned on the AutoRecover feature before the problem occurred. You can set the AutoRecover save interval to occur more frequently than every 10 minutes (the default setting). For example, if you set it to save every 5 minutes, you’ll recover more current information than if you set it to save every 10 minutes.

With AutoRecover on, if an Office program stops responding while you have files open, you can use the Microsoft Office Application Recovery dialog box and recovered files will be displayed in the Document Recovery task pane. The data in the files reflects the last time AutoRecover saved the files. AutoRecover should not be used as a substitute for manually saving or backing up your files.

To turn AutoRecover on

1. On the Tools menu, click Options, and then click the Save tab.

14. Select the Save AutoRecover info every check box, and then select a time in the number box.

To recover a file

1. On the Start menu, point to Programs, point to Microsoft Office Tools, and then click Microsoft Office Application Recovery.

15. In the Application list, click the program or document that is not responding.

16. Do one of the following tasks:

• To attempt to recover the files you were working on, click Recover Application.

• If you want to close the program, and lose recent changes to the files, click End Application.

The error that caused the problem can be reported to Microsoft for use in improving future versions of the program. Click Report problem or Don’t report problem.

Taking It Further

As you continue to use Excel 2002 in the classroom, you will learn more about the features available for different classes. As you become more familiar with Excel 2002, you may want to try these activities:

• Use PivotTable and PivotChart reports to analyze complex data and make predictions about trends. Your students can analyze and predict demographic trends by creating PivotChart and PivotTable reports from census data. To learn more about PivotChart and PivotTable, type “pivotchart” or “pivottable” in the Type a question for help box. For a tutorial on analyzing data with Excel see:

• Create refreshable Web queries to track data from the Web. You can have your students track stock or currency prices to make strategic decisions in a simulated investing lesson. For additional information type “web query” in the Type a question for help box.

• Make a Web form to use in a poll. Your students can poll students from other schools and track and analyze the data. For additional information type “web form” in the Type a question for help box.

• Use charts and graphs to present visual representations of data. For example your students can create complex charts and graphs to plot the numbers of migrating birds in a region. For additional information type “create chart” in the Type a question for help box.

• Collect and track statistical data. Your students can gather statistics from your school’s sports teams and create visual representations of performance over the year.

• Use watch windows when working with large spreadsheets of data. If your students have a large spreadsheet and they want to see the effect of changing a particular value on the result they can use a watch window to watch a particular cell while they change the values elsewhere in the spreadsheet. For additional information type “watch window” in the Type a question for help box.

-----------------------

[pic]

You can open an Excel workbook directly from Microsoft Internet Explorer. This allows you to collaborate over the Web, without giving students access to a shared folder

What yo⁵楷汬搠㩯ഋ硅汰牯⁥桴⁥敦瑡牵獥漠⁦硅散〲㈰䌍敲瑡⁥⁡汢湡潷歲潢歯愠摮愠眠牯扫潯牦浯愠琠浥汰瑡൥潆浲瑡愠眠牯扫潯൫灁汰⁹潦浲汵獡琠⁡潷歲潢歯䌍敲u will do:

✓

✓ Explore the features of Excel 2002

✓ Create a blank workbook and a workbook from a template

✓ Format a workbook

✓ Apply formulas to a workbook

✓ Create a chart

✓ Import data to a workbook

✓ Share a workbook

✓ Create refreshable Web queries

[pic]

You can create your own Smart Tags to be used with Office XP. Smart tags can be used to integrate information and data from other Office applications, Web pages, and other sources. For more information about creating Smart Tags, refer to the Smart Tag SDK at

[pic]

New Workbook task pane

[pic]

The task pane is the pane on the right side of the Excel window. It opens automatically in some situations that require you to choose from a list of options, for example when you create a new workbook. The task pane is in a small window, which allows you to use these commands while still working on your file.

To open the task pane, on the View menu, point to Toolbars, and then click Task Pane. To change the task pane that is displayed, click the arrow in the upper right corner of the task pane, and select New Workbook, Clipboard, Search, or Insert Clip Art.

[pic]

The AutoSum button in Excel 2002 now gives you the choice of many common formulas in a drop-down menu. This new Excel 2002 feature makes it easy to create formulas in a workbook. For more information about the AutoSum feature, refer to Excel Help.

[pic]

You can now draw a border around a range of cells by using the Borders toolbar. To activate the toolbar, on the Tools menu, point to Toolbars, and then click Borders. To draw a border around a range of cells, select the type of line you want to use, and then highlight the cells, and a border appears around those cells.

[pic]

You can also cut and paste a table from a Web page directly into an Excel workbook. After you paste the table, a smart tag will appear that enables you to make the imported data into a refreshable Web query.

[pic]

Speech playback and speech recognition

You can use the Excel 2002 Speech Playback feature to read back data in a worksheet. You can also enter data in a worksheet by using speech recognition. These features may require additional hardware and memory for your computer. Check the system requirements at the beginning of this book.

Customize Toolbars

You can customize Excel 2002 by adding and modifying toolbars and command menus, and creating and modifying keyboard shortcuts. For example, you can use large toolbar icons. For more information, refer to Excel 2002 Help.

[pic]

Excel 2002 can help you write formulas with a feature called screen tips. When you begin to write a formula, for example “=SUM(“, a screen tip appears that helps you to complete the formula. This makes writing your own formulas much easier.

Sheet navigation buttons

New Workbook task pane

Help search text box

Current worksheet

Standard toolbar

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download