Microsoft Excel 2000 gives you and your students the opportunity to learn and What you will do: strengthen skills as you gather data, create worksheets, analyze and chart the results, and integrate your findings into reports and assignments. This chapter shows you ? Explore the features of how Excel 2000’s features help you to work better and teach better. The following Excel 2000. pages offer self-guided lessons to make it easy for you to use Excel 2000 as a classroom tool and management tool. Illustrations and steps written in plain
language help you to: ? Create and save a
worksheet. ? Gather and enter data.
? Format cells. ? Format data.
? Learn to apply ? Work with numbers, formulas, and functions.
worksheets. ? Use proofing tools.
? Emphasize your point with charts. ? Create a workbook.
? Put it all together. ? Create a chart from a
? Save a worksheet to
Excel 2000 offers a variety of new features designed to help you collaborate and
share information through the Web and to perform more extensive analysis of your
data. This version of Excel is also easier to manage and use than ever before.
? Web-enabled collaboration and information sharing. Excel 2000 allows you
to create and share rich Web documents with the same Office tools you use
to create printed documents. This means that your Excel 2000 content is
universally viewable by anyone with a browser.
? Drag and drop. Excel 2000 also supports the drag and drop of table data
from a Web browser directly into Excel.
? HTML as companion file format. Excel 2000 can save to and read from
HTML files with high fidelity. HTML is now on the same level as the Excel file
? New date formats. Custom date entry behavior and static date function
behavior give you more options for working with dates, and Excel 2000 is
Year 2000 compliant.
? Enhanced Web queries. A new dialog in Excel 2000 makes it easy for
anyone to bring data from the Web directly into Excel for tracking or analysis.
? List AutoFill. Excel 2000 automatically extends formatting and formulas in
lists, simplifying this common task and helping you work more efficiently.
? See-Through Selection. Instead of the inverse video selection that hides
formatting and sometimes text, the See-Through Selection in Excel 2000
lightly shades selected cells so that you can make changes and see the
results without un-selecting the cells.
? Euro currency support. Excel 2000 supports the new Euro currency, both
the symbol and the three-letter ISO code.
42 In and Out of the Classroom with Office 2000 Professional Excel 2000
When you start working in Excel 2000, you begin using a workbook that contains
screens called worksheets. They are identified as Sheet1, Sheet2, and so on.
The most recognizable difference between a word-processing document and a
spreadsheet is that the spreadsheet uses rows and columns because most data
entered onto a spreadsheet is numerical and is easier to read, understand, and
manipulate when presented in columns.
Use these buttons to The Chart The Drawing add Web links, calculate Wizard helps Tool helps you an AutoSum, add you create create WordArt formulas, and sort data. charts. and add shapes to worksheets. Merges selected Use these cells and then buttons to format centers text in the numerical data.cell.
The Excel 2000 Standard and Formatting toolbars includes buttons for formatting data and cells. The Name box indicates which cell is selected. The Formula box shows the data in the cell. Column and Row numbers make it easier to know where you are on the worksheet. Use these buttons to keep track of worksheets in a workbook. Click the Sheet buttons to move from one worksheet to another.
There are a number of ways to move around in a workbook. Moving from one cell to
another in Excel 2000 is quick and easy. The ways to move from cell to cell include
clicking a cell or using the Go To command, the scroll bars, the arrow keys, or the HOME, END, PAGE UP, and PAGE DOWN keys.
? To select any cell, click it. For example, click cell A1.
? To move one cell to the right, press TAB, or to move one cell to the left,
? To move one cell down, right, up, or left, use the arrow keys.
? To move to the uppermost-left cell, A1; press CTRL+HOME.
? To move to any cell, on the Edit Menu, click Go To and then type any cell
number (for example, J18).
? To move down in the worksheet, press PAGE DOWN.
? To move up in the worksheet, press PAGE UP.
? To move to the first column of the worksheet, press HOME.
? Click the worksheet tabs in the left-bottom area on the worksheet.
A worksheet is an effective tool for keeping track of all sorts of data. You can track
student attendance, books you have read and their authors, a check register, a list of
major purchases and the amounts, or student organization trips you have gone on
and their dates and costs. If students are tracking any type of data, then creating a
worksheet can help those students easily manage the information.
This activity is easier if you have a set of data available to add to the worksheet.
Any type of information works. The example worksheet is a student attendance
worksheet. The data used for this worksheet includes student names, student
numbers, and dates.
44 In and Out of the Classroom with Office 2000 Professional Excel 2000
1. Open Microsoft Excel 2000. A new workbook opens.
2. At the top of the worksheet, right-click the B (column header) and click
Format Cells. There is a difference between a workbook and a 3. On the Number tab in the Category box, click Text and then click OK. worksheet. A workbook can contain many worksheets. This keeps the student numbers in this column exactly as you type them. Each worksheet can contain up to 256 columns 4. Click cell A3. Type High Score. Click cell A4. and 65,536 rows.
5. Type a student name, last name first (for example, Jensen, Camille).
The words may not be entirely visible in the selected cell. The
information is still there, and later, you will format the column so you
can see all of the information.
6. Click cell B4 or press TAB.
7. Type in the student’s ID number. 8. Click cell A5.
9. Type the next student’s name, press the right arrow, and type the
student ID number.
10. Continue to type each of the names and student numbers for the
students in one class. For this exercise, enter in data for at least five
11. Double-click the Sheet 1 tab in the lower-left corner and type
1. Click cell C2 to select it.
2. Type the first date of class in mm/dd/yyyy order.
3. With the cell still selected, place the pointer over the lower-right corner
of the cell until the pointer turns into a + sign.
4. Click and drag the pointer across the next 4 cells in the row. Excel 2000
automatically fills in the next four dates.
5. Click cell H2 and repeat steps 2-4 to fill in the dates for the next week.
For this exercise, two weeks will be sufficient.
6. On the left side of the worksheet, right-click the row 2 header and click
7. On the Number tab in the Category box, click Date.
8. In the Type list, select the date format you want and click OK. All the
dates change to the selected format.
When “#######” appears in a cell, the cell is too narrow for the data to be
1. Select the columns in your worksheet by clicking the A column header
and dragging across to the last column in which you have dates entered.
2. Move the pointer over the border between any two-column headers until You may want or need to adjust the width of one or the pointer changes to a double arrow. more columns in your worksheet in order to allow for better spacing between 3. Double-click. The columns automatically adjust the width to the longest columns or to maximize the text in their respective columns. available space on the worksheet. 4. On the File menu, click Save As and type Attendance in the File name
Recording names and student numbers can be a full-time job. Just as soon as
you have everyone in the correct order, you have a new student whose last name
starts with the letter C. With Excel 2000, sorting the names by last name, or any
other order, is quick and easy. Using Excel 2000, you can track information for
each student, and then organize it according to importance or category.
You can also enter first 1. Open the Attendance worksheet if it is not already open. names in one column, last names in a second column, and middle initials in a 2. Select cells A4 through the end of the list in column B so that all names third column. This way, you and student numbers are selected. can sort by last name or first name. 3. On the Data menu, click Sort.
4. Under Sort by, select Column A and then click Ascending.
5. Click OK.
6. On the File menu, click Save.
46 In and Out of the Classroom with Office 2000 Professional Excel 2000
A worksheet can also calculate data when you add formulas. Formulas in worksheets
can save you time by doing many of the tedious calculations involved in record
keeping for grades and attendance. It can also help you to teach students the
relationships between numbers. For example, you can show students how much
money can be saved in interest if you pay a loan in 36 months instead of 42 months.
You can also keep track of loans, calculate averages for sports teams, determine gas
mileage, or perform any other mathematical equations.
A car payment calculation requires a fairly simple formula. This activity illustrates
the ease with which you can add formulas to your worksheets. To calculate the
payment, you need a loan amount, the term or length of the loan, and the
interest rate. The following activity teaches you to add a formula to calculate the
monthly payment on the car loan.
1. On the File menu, click New and then double-click Workbook.
2. In cell C2, type Car Payment Schedule and then press ENTER.
3. Type the following text in the cells shown in the worksheet below:
4. Format the column width.
5. Click cell D11, and on the Formula toolbar click the Edit Formula button
(the = sign).
6. Click the Name Box drop-down list and click PMT, if visible.
- Or -
Click More Functions, click Financial in the Function category, click
PMT in the Function name, and then click OK.
7. In the Rate window, type D5/12 (interest rate D5 divided by 12 months
8. Click the Nper window and then type D6 (the term of the loan).
9. Click the Pv window and then type D7 (the principle value).
10. Click OK.
11. To change the months of the loan to 24, type 24 in cell D6.
12. To change the interest rate to 12.5% to demonstrate how the payment
changes, type 12.5 in cell D5. Try several combinations on your own,
and see how it works.
13. Save your work, and close the file.
You can use Excel 2000 worksheets to collect and analyze information, including
student records, lesson notes, school activity budgets, and professional
organization information, and data. Students may use Excel 2000 for scientific
data, weather journals, financial reports, nutritional diaries, or legislative voting
records. Each worksheet can be easily customized and enhanced with graphics
and artistic additions. This lesson teaches you to build a workbook containing a
worksheet for student attendance and a worksheet for grades.
1. If necessary, open Microsoft Excel 2000.
2. On the File menu, click New.
3. On the General tab, double-click Workbook to open a new one.
4. Click cell B2 and then type Student List.
5. Save your work.
Because this workbook contains sensitive information, you can add a password
to the document. When a workbook is password-protected, no one can read or
change information in it without using the password.
1. On the File menu, click Save As.
2. In the Save As dialog box, on the Tools menu, click General Options.
3. In the Password to open box, type a password and then click OK.
4. In the Reenter password to proceed box, type your password again and
then click OK.
5. Click Save.
48 In and Out of the Classroom with Office 2000 Professional Excel 2000
6. If prompted, click Yes to replace the existing file.
In this section, you add the attendance worksheet that you already created. The
attendance worksheet is the first worksheet in the book you created previously.
1. Open the Attendance worksheet you already created.
2. On the Edit menu, click Move or Copy Sheet.
3. In the To book dialog box, click (new book).
4. Click OK.
5. Close the file. You do not need to save.
The days of the written grade book, while far from gone, are rapidly changing,
and educators are taking advantage of the ease of use and power of the
worksheet to make tracking student achievement less burdensome. Using Excel
2000 to create and maintain your grade book can save you time and energy and
eliminate clerical mistakes. Use Excel 2000 to create an electronic grade book to
keep track of student achievement and do more sophisticated reporting of
1. Open the Attendance workbook.
2. Click Sheet 2 at the bottom of the workbook to move to a new
worksheet. Double-click on the Sheet 2 tab and type Grade Book.
3. Click cell A1 to select it.
4. Type your name and press ENTER.
5. In cell A2, type the name of your class (for example, Science, English, or
6. Press DOWN ARROW to go to cell A3.
7. Type Period 1.
8. Press DOWN ARROW to go to cell A4.
9. Format the column width. Type 1st Quarter and press ENTER three
times to move to cell A7.
10. Click the Attendance tab to switch to that worksheet.
11. Select cell A3. Press SHIFT and drag down to the row containing the last
student name and number you have entered. Then, release the mouse
button. All selected cells are highlighted.
12. Right-click anywhere in the selected cell area and click Copy.
13. Switch back to the Grade Book worksheet and select the same number
of cells you selected in the Attendance worksheet, starting from cell A7.
14. Right-click the selected cells and click Paste. The names are copied. 15. In cell D6, type Test 1 16-Oct-1999.
16. Move to the next cell in the row and repeat the step with the following
column headings (these headings are used later for an example of
When you are finished, cells D6:R6 should be filled with the above titles.
17. Adjust the column widths on the worksheet.
18. Save your work.
19. To rotate text and cells, see “Formatting rows and columns” later in this
1. Open the Attendance worksheet if it is not already open.
2. In cell C1, type Monday.
3. Click and drag the fill handle to select the cells through cell G5. Do not
save your changes.
50 In and Out of the Classroom with Office 2000 Professional Excel 2000