Microsoft Campus Agreement ~ Google Spreadsheets ~ OpenOffice Calc
Start -> All Programs -> Microsoft Office -> Excel
Start -> Run -> excel.exe
| Workbook | corresponds to a file on your hard drive. Made up of one or more worksheets. |
| Worksheet | belongs to a workbook and is made up of rows and columns. |
| Column | belongs to a worksheet. Contains a set of vertically arranged cells. Identified by letters A, B, C, Z, AA, ABB, etc. |
| Row | belongs to a worksheet. Contains a set of horizontally arranged cells. Identified by numbers 1-... |
| Cell | belongs to one row and one column. Contains a value in the form of data or a formula. Referenced by a cell "address" made up of the column identifier (letter) following by the row identifier (number). For example, B6 or F22. |
| Numeric Data | numeric data is made up entirely of digits and may be added to, multiplied or divided. A grade is numeric data. A student number is not numeric data because it does not make sense to do calculation on a student number. |
| Character Data (Text) | character data may contain any combinations of symbol. May not be used in calculations. |
| Formula | a formula specifies a sequence of operations on cell contents. Operators (+, -, *, /) are used to create formulas. |
| Function | a function is a built-in formula provided by Excel. |
Before a cell can be modified or formatted, it must first be selected (highlighted).
| Cells to Select | Mouse Action |
|---|---|
| One cell | Click once in the cell |
| Entire row | Click the row label |
| Entire column | Click the column label |
| Entire worksheet | Click the Whole Sheet button located above the column of row labels and to the left of the row of column labels, or press <Ctrl>A |
| Cluster of cells | Drag mouse over the cells or hold down the <Shift> key while using the arrow keys |
| Movement Action | Key Combination |
|---|---|
| One cell up | up arrow key |
| One cell down | down arrow key or <Enter> |
| One cell left | left arrow key |
| One cell right | right arrow key or <Tab> |
| Top of the worksheet (cell A1) | <Ctrl><Home> |
| End of the worksheet (last cell containing data) | <Ctrl><End> |
| End of the row | <Ctrl>right arrow key |
| End of the column | <Ctrl>down arrow key |
| Any cell | Edit > Go To menu bar command |
File -> Save As (choose a location on the computer and give a name to your
workbook/file)
OR: press <Ctrl> S
The most common operators used for writing formulas are:
+ |
Sum |
- |
Substraction |
* |
Multiplication |
/ |
Division |
Functions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cells D1 through D10, you would type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)".
| Function | Example | Description |
|---|---|---|
| SUM | =SUM(A1:100) | Finds the sum of cells A1 through A100 |
| AVERAGE | =AVERAGE(B1:B10) | Finds the average of cells B1 through B10 |
| MAX | =MAX(C1:C100) | Returns the highest number from cells C1 through C100 |
| MIN | =MIN(D1:D100) | Returns the lowest number from cells D1 through D100 |
.
To avoid errors in typing a formula which is used throughout a column, use the autofill tool. Click once on the cell containing the formula, click the fill handle (+), drag the cells to include the desired number of cells and release the mouse button. The formula now applies to all the cells you selected.
Relative referencing: When you copy this formula to another cell, Excel automatically adjusts the cell reference to refer to different cells relative to the position of the formula.
Absolute referencing: When the cell reference must remain the same when copied or using Autofill. $ is used in combination with the column reference (letter) or with the row reference (number).
Select the cells you want to sort. Click on the sort button:
Select the cell. Go to Format -> Cells -> Number tab -> Number -> Decimal Places = 2.
Select the column tothe right of where you want the split to appear. Go to the Window menu, click Freeze Panes.
The original list is at (A1:A20): in the worksheet where you want the names to appear, click on the first cell and type in =, then use the mouse to click on the student in A1. Press <enter>. The name is now on your second worksheet. Use Autofill for the other names.
Select the table containing the data you want to use (click and drag). Click
on the Chart Wizard button:
Chose a chart type and a chart sub-type. Click Next. Click Finish (you can modify
the chart in many ways, see references below for more details).
Marching ants: press the escape key to remove them around
a selected cell.
######: too many numbers or text too long to be displayed.
Widen column to fix this.
#NAME?: you entered the name of the formula incorrectly.
#REF!: The formula refers to a cell that is not valid.
#DIV/0!: The formula is trying to divide by zero.
See a sample grade sheet with 100
to GPA automated conversion and other functions and formulas.
To learn more about Excel online: Catalyst Excel Workshop, official Microsoft Excel page.
To learn more about Excel at the library: Microsoft Office for Teachers (LB 1028.3 F48 2003), MS Excel 2003: Top 100 Simplified Tips and Tricks (HF 5548.4 M523 P43 2005).
When you're stuck: don't forget to use the Excel Help menu (contains lots of answers explained in simple language). The internet also has a ton of explanations: just google Excel and the name of the function you're trying to use to find examples.