Sign up for newsletter

May 1, 2002

Excel Spreadsheets: An Excel Shortcourse for Teachers

By Wesley A. Fryer

Technology integration is a process. In order to be "sold" on technology, teachers need to experience (not just be told or shown) the ways computer technologies can help them save time in the classroom, be more productive, and provide new opportunities for communication and information access. After becoming comfortable using gradebook software, e-mail, word processing software, and surfing/searching the web, a logical "next step" for educators is to begin using spreadsheet software to improve administrative tasks and communication.

This article describes ways of using spreadsheet software, specifically Microsoft Excel, to manage lists within an educational context. Steps for Excel in this article apply to the following versions: Excel 2000 for Windows, and Excel 2001 for Macintosh.

Spreadsheet Basics

Every teacher is required to manage student data in some way. While word processing software can certainly streamline the creation and management of text documents, a spreadsheet offers advantages over word processors that should not be overlooked. Spreadsheet software was first developed in 1961 by Professor Richard Mattessich, but did not become widely used until 1978 when VisiCalc was created for the Apple II. Despite this relatively long history (in the relative timeline of computing), the ability to create computer documents that perform simple (or complex) mathematical formulas remains unrealized for many educators.

According to dictionary.com, a spreadsheet is "a piece of paper with rows and columns for recording financial data for use in comparative analysis." People often think of spreadsheets only in this financial context, for creating budgets or other reports requiring data calculations. Yet spreadsheet software, including the widely used Microsoft Excel program, offers much more than financial calculation potential.

A computer spreadsheet is composed of rows and columns, which divide the document into unique cells. In Excel, columns are assigned alphabetical letters starting with A through Z. After column 26, a preceding 'A' is added to the column titles: AA, AB, AC, etc. Rows are numbered in ascending order from 1 on up. When explaining spreadsheets to students, it is helpful to ask how many are familiar with the game Battleship. In the game, players identify where their ships will shoot in search of enemy targets by calling out grid coordinates like those in a spreadsheet: "Fire on D5." "Miss!" "Fire on B2." "Hit!" An understanding of the coordinate grid used in playing Battleship is transferable to a computer spreadsheet.

In addition to the use of fields and a coordinate grid, the FORMULA BAR distinguishes spreadsheets from word processors. Because spreadsheet cells have a defined width, novice users often wonder how more information can be entered or edited into such a small space. The answer is by using the formula bar, which (in Excel) can be made visible or hidden by selecting its name from the VIEW menu.

Each field in a spreadsheet can be formatted in a variety of ways. After selecting a cell or group of cells, from the FORMAT menu in Excel choose CELL. Alternatively, right click (Windows) or control click (Macintosh) and choose FORMAT CELL to bring up a tabbed menu. Under the GENERAL tab, cells can be formatted to contain text, numbers, dates, times, percentages, currency amounts, or fractions. The default format type for a cell is GENERAL, which means no specific number format is identified. If the number format is selected, the number of shown decimal places can be specified, and whether a 1000 separator (comma) should be displayed.

To select more than one cell in Excel at the same time, (before applying a format change for example):

1. Click on a cell in the corner of a group of cells you want to select.
2. Hold down the shift key.
3. Click on a cell in the opposite corner of the group of cells you want to select.
4. Apply the desired formatting or other commands, and all selected cells will be affected.

This procedure works when copying and pasting groups of cells as well, which can be handy.

Managing Lists

Making a budget from scratch using a spreadsheet can seem complicated and intimidating to a novice user, and is probably best left to customized financial software like Quicken. Creating and managing a list of some type with a spreadsheet, however, is a fairly straightforward process, and is a good way to get comfortable with Excel.

Step 1: Enter Column Headings

Begin your list by adding headings in the first row of the spreadsheet for each category (field) of information the list will contain. For a class list, these could include last name, first name, address, city, state, zip, phone, parent name, etc. It is a good idea to separate fields like first and last names into different columns, because this can provide more flexibility if the spreadsheet is used later within a mail merge. It also provides more flexibility when list data is sorted.

After entering information in a cell, press the TAB key to record that information in the cell and move the cursor to the next cell.

Step 2: Enter the Data

After the column headings are entered, type the data into the cells below. Each row of the spreadsheet should contain information about a single entity. In the case of a class list, each row will contain data about one student. Do not worry about entering data in alphabetical or another order. The list data can be sorted later (see Step 4).

Step 3: Format the Cells

Most likely, some of the columns and rows in your spreadsheet will need to be resized. This can be done in a variety of ways, including use of the FORMAT menu once the appropriate cell row or column is selected. A graphical way to resize rows and columns is to move the cursor between two column or row headings until it changes from an icon that looks like a plus symbol, to an icon that looks like a horizontal line with arrows extending up and down from its center. Click and hold down the mouse button, and drag the row or column to the desired height or width.

If desired, text can be formatted to "wrap" within a cell so a string of text wider than the column width is visible within the cell borders. To do this:

1. Select the cell(s) to format by clicking or shift-clicking on them.
2. From the FORMAT menu, choose CELLS.
3. Click the ALIGNMENT tab, and under TEXT CONTROL click WRAP TEXT.

Many other advanced formatting settings are available within the cell format dialog window. These include fonts, sizes, alignment options, and borders. The orientation of the cell contents can be changed, from the horizontal default to vertical, or something in between.

The AUTOFORMAT option under the FORMAT menu should also be explored, as it provides a fast way to polish the visual appeal of a list with pre-designed borders and font styles.

Step 4: Sort the List

Like a table created in Microsoft Word, data within an Excel spreadsheet can be sorted in ascending or descending order by different columns. To sort data in a spreadsheet:

1. Select all the cells to be sorted by either shift-clicking them or using a keyboard shortcut to select all cells (control-A in Windows or command-A on a Macintosh).
2. From the DATA menu, choose SORT.
3. Select the columns to sort the data by, and choose either ascending or descending order.
4. If a header row is present (usually the first row, containing titles like Last Name, First Name, etc), make sure the "header row" radio button is selected at the bottom of the dialog window.
5. Click OK to sort the data.

The same student list can be sorted in different ways and subsequently printed, generating lists from the same file for different purposes.

For example, you can sort the list by last name, locker number, birthday, or any other desired column. If parent interviews are scheduled, the you can sort the list by the appointment date column.

If an interview date changes, the data can be resorted, and a chronological list easily re-created with the updated information.

Step 5: Add Calculation Fields

Even if a teacher is just using a spreadsheet to create and manage a class list rather than create a complicated budget, calculation fields can still be used with a minimum of expertise.

Spreadsheet calculations are performed by entering a FORMULA into a cell, which performs a mathematical operation on data contained in other cells and shows the result. All available functions within Excel are accessible by choosing the INSERT menu and selecting FUNCTION. Formulas in Excel begin with the equals sign, and are followed by a combination of references to other cells (like A5 or C9), mathematical operators, and functions.

For a class list, one of the most useful formula functions is the COUNTIF function. This function counts the number of times a particular string of text (a word or phrase) occurs in a spreadsheet column or row. For example, COUNTIF can be used to count how many times the word 'No' is displayed in the column titled 'Paid for class field trip.'

To use the COUNTIF function:

1. Select the cell the formula will be inserted into.
2. Select INSERT - FUNCTION from the menu, and from the STATISTICAL function category (on the left) choose COUNTIF (on the right).
3. Specify the range (the cells you want to count) by clicking the small box with a red arrow on it next to the range field.
4. The COUNTIF window will disappear. Click and drag to select the cells you want to count.
5. Press the ENTER key to make the range selection.
6. Next to CRITERIA, type the equals sign followed by the text string you want to search for.
7. Click OK. The result of your formula using the COUNTIF function should be displayed in your selected cell.

It is usually helpful to enter a label to the left of the cell containing a formula, to clearly indicate what the number means.

Another basic function helpful on class lists is SUM. Access and use it like COUNTIF, except the cell range is specified next to NUMBER 1 in the dialog window. Use the SUM function to find the total amount of student fundraiser money turned in, the number of absences a student has, or any other group of fields containing numbers.

Mathematical functions like SUM and AVERAGE can be used to create sophisticated spreadsheets used for tracking student attendance and grades. While most classroom teachers will prefer to use commercial gradebook or attendance software for these purposes, some school districts have found an affordable alternative to these software purchases in customized Excel spreadsheets designed by teachers. Some are available for download from the Internet, either as shareware or freeware. An example is the "Excel-lent Gradebook" by David Burger. Four other examples are available.

Step 6: Hide Columns before Printing

A list within Excel may contain information not appropriate to print or share with students or parents. In this case, entire columns or rows of information can be temporarily HIDDEN from view for display or printing.

For a parent conference, all rows except one student's can be hidden so the parent just sees the grades for his/her child. For a class list, student addresses and phone numbers can be hidden and only name and permission form information displayed, if a list with just those columns is needed for printing.

To hide rows or columns in Excel:

1. Click or shift-click on the column headings (A, B, etc) or row headings (1, 2, etc) you want to hide.
2. From the FORMAT menu, choose either ROW or COLUMN (as appropriate to your previous selection.)
3. Choose HIDE.

Rows or columns you selected should now be hidden from view, but they are still part of your spreadsheet. To reveal hidden columns or rows:

1. Shift-click to select cells in front of and behind the hidden cells.
2. From the FORMAT MENU, choose either ROW or COLUMN (as appropriate).
3. Choose UNHIDE.

Step 7: Print with Options

When printing a page from Excel, note in the print dialog box (that appears after selecting FILE - PRINT) several options are available specific to Excel.

The entire workbook (the complete file) can be printed, only the 'active sheet' (the sheet selected from the tabs in the lower left corner), or simply the cells selected by shift-clicking before choosing FILE - PRINT.

These print options can further enable educators to limit the information printed from an Excel file, and thereby customize it to fit their needs.

Conclusions

Many computer users never learn to use true "database software" because of the list management features included in spreadsheets like Excel, described in this article. "Flat" data designs like those possible in Excel are more limited in their advanced uses than actual databases (created in Access or FileMaker Pro,) but can still serve valuable functions. These can be basic like a class list, or complex like a gradebook program offering a variety of assessment methods and options.

Since the updated release of Microsoft Office for Macintosh in 1998, Microsoft Excel files have been fully "cross-platform." That means the same Excel files created on a Windows computer can be shared with a Macintosh user (provided both have Excel installed on their computers), and vice versa. Macintosh users need only remember to add the Windows file extension for Excel files (.xls), so Windows computers will recognize and properly open the files within Excel. If unusual fonts are used within the Excel document, they may appear different on another computer if those same fonts are not installed, but generally layout and appearance of Excel files should be the same across platforms.

Once a list of student information or other data is created in Excel, a TEMPLATE FILE can be created. This file, when opened, will be formatted and already have some information entered. An example might be a file in which student last names and first names are entered, but other columns are blank and ready for data entry. Whether the Excel file is used as a student list or a gradebook, use of template files can be invaluable and save further time for teachers. More information about templates and using them for both administrative purposes and within instruction is available in the article "Teaching with Templates."

Some educators, even those with considerable technology literacy, are intimidated by the thought of using a spreadsheet. By attempting the techniques described here with the assistance of an encouraging and more experienced peer, such perceptions can be changed. Technology integration can only become a reality when teachers are not only provided with computer hardware and software, but also frequent opportunities for training and mentoring from fellow educators in methods like these.

Email: Wesley Fryer
Wesley Fryer is the Director of Distance Learning for the College of Education at Texas Tech University. He provides instructional technology training and support to K-16 educators as a consultant and through his free web site, 'Tools for the TEKS.'

Texas Computer Education Association
Copyright 2002, TCEA. Reprinted with permission.