Back to COSI 118 - Home Microsoft Excel 97
Presented by Valerie Chu, Ph.D.
Friday, July 9, 1999

• General Overview
• The Components of a Spreadsheet
• How to Enter Data
• Formulas
• Sorting
• A Few Tips on Printing

1.    The Components of a Spreadsheet

• A spreadsheet consists of rows and columns.
• Rows are numbered from 1 to 65,536 (2¹⁶).
• There are 256 columns lettered A to Z, AA to AZ, and so on until IV.
• The intersection of a row and a column results in a cell.
• Each cell has its own address, beginning with the column letter and ending with the row number.
• For example, if a cell is in row 29 and column Y, then its address is Y29.
• There is only one active cell at a time.
• The active cell is the one with a thick black border around it.
• Before you start entering data or formulas, check to make sure that you have the right active cell.
• There are two types of data that can be stored in a cell: text and numbers.
• Text consists of letters, numbers, and symbols. It cannot be used in computations.
• Examples of text: titles, names, social security numbers, and phone numbers
• Numbers are stored as numerical values. They can be used in computations.
• Examples of numbers: test and quiz scores, amounts of money, and dates
• In addition to text and numbers, formulas can also be entered into cell. (We will cover that later.)

2.    How to Enter Data

• You can change column widths and row heights to fit to your needs.
• Here are three steps for entering data.
• Click inside the correct cell to make it active.
• Start typing your data for that cell. (The cursor will be in the formula bar at the top of the screen.)
• To store the data, hit ENTER or one of the arrow keys, click on another cell, or click the check mark beside the equals sign. To cancel, hit Esc or click the red X.
• If you need an extra column or row, then use the Insert function. You can also delete an unwanted row or column.
• To insert a row or column, select the row or column after the space where you want the new row or column to be. Right click anywhere on the row or column and select Insert.
• To delete an unwanted row or column, select it. Then right click somewhere on the selected row or column and click on Delete.
• You can use special effects to make your spreadsheet look more appealing.
• For text data, you can use word processing techniques to make it look more attractive.
• For numerical data, boldface, italics, etc. can only be applied to the whole cell.
• Alignment inside cells can also be changed to create a better-looking worksheet.
• You can left align, center align, or right align inside a cell using the appropriate button on the Formatting toolbar.
• For titles, you can center across many cells. Select the cells you want to center the title in including the title cell. Then right click on the selection and click on Format Cells… Get to the Alignment tab. Change the Horizontal alignment to Center Across Selection. Finish by clicking OK.
• To make a cell contain and display a certain type of number, use the Format Cell dialog box.
• First, select the cell or cells you want to format.
• Then right click inside the selection and click on Format Cells…
• Make sure you are on the Number tab and select a format.
• Click OK to confirm your selection.
• When you have descriptive column and/or row headings, you can use Freeze Panes to show them at all times.

3.    Formulas

• Not only does Excel organize data into rows and columns, but it also can perform many operations on them.
• All formulas begin with an equals sign.
• At the lowest level of computations lie these five operators: addition (+), subtraction (–), multiplication (*), division (/), and exponentiation (^).
• Excel’s order of operations is the same as that of mathematics.
• Instead of using the contents of a cell, use cell addresses to construct formulas.
• To avoid typos, you can use the mouse to select the cells you want.
• Here are some frequently used functions.

AVERAGE	COUNT	IF	MAX
MIN	NOW	STDEV	SUM

• IF is a very useful function.
• Its syntax is: =IF(condition,value_if_true,value_if_false)
• To automatically create a letter grade, use ifs inside other ifs.
• Another useful function is NOW.
• Its syntax is: =NOW()
• It returns the current date and time on your computer.
• To change its format, use the Format Cells dialog box (see above).
• Warning: AVERAGE drops blank cells as if they are nonexistent.
• To save a lot of time and effort, use Excel’s Fill capabilities to quickly copy a common formula.
• Relative addresses vs. absolute addresses
• When you copy a formula to other places, the absolute address remains the same while the relative address will point to a different cell.

4.    Sorting

• Sorting is a powerful tool that can save a lot of time and frustration.
• To sort an area…
• Select the area that you want to sort, including the data items that have to follow the things you sort.
• Click on Data then Sort…
• Select the primary sorting key and whether it should be ascending or descending.
• If there is a chance of a duplicate in the primary key, then select a secondary key.
• Finally, click OK.

5.    A Few Tips on Printing

• If you want to print just a certain part of a worksheet, then select it and use File | Print Area | Set Print Area. To clear the print area, just use File | Print Area | Clear Print Area.
• If a worksheet is wider than it is high, then you can chose to print it on its side. Just go to File | Page Setup, get to the Page tab, and select Landscape.

Back to COSI 118 - Home