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 (216).
-
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