Excel Basics

2007-08-20T13:32:44Z

1 What is a spreadsheet?
2 What is a spreadsheet good for?
3 Basics of Using Excel

What is a spreadsheet?

Imagine a ledger book with 256 pages (worksheets) and each sheet had 256 columns and 65,536 rows
Each cell can contain a number, a formula, or text (a lot of text)
Formulas can use numbers (i.e. 2+3), or the contents of other cells.
There are many functions that will perform complicated calculations (i.e. calculate payments, or perform statistical analysis)

What is a spreadsheet good for?

Collecting and organizing information
Performing lots of calculations very quickly

See: Useful Excel Templates

Basics of Using Excel

Cell Notation

Every cell is identified by the worksheet, column and row in the following format: "Sheet1!A1" or "'Sheet 1'!A1"

(note the apostrophe's containing the sheet name if the name contains spaces)

When referring to a cell in the same sheet, you only need to specify the column and row (i.e. "A1")

Cell notation is not case sensitive and Excel will convert all columns to capital letters.

Moving Around

Mouse - click to move to a cell
Arrow keys - move in the specified direction
Tab - move to the right
Shift-Tab - move to the left
Enter - move down
Shift-Enter - move up
Home - move to the first column (same row)
Ctl-Home - move to cell A1 (same sheet)
End-Arrow key - if on an blank cell, moves to next non-blank cell, if on a non-blank cell moves to either the last contiguous non-blank cell or to the next non-blank cell if the adjoining cell is blank
Ctl-End - move to last row and column with non-blank cells
Page Up - move up one screen
Page Down - move down one screen
Ctl-Page Up - move to previous worksheet
Ctl-Page Down - move to next worksheet
Use "Go To" - Edit -> Go To... or F5 key
Type a cell reference in the Name Box on the Formula Bar

Data Entry

When you select a cell, you are in "Entry" mode - typing will replace the current contents of that cell.

Double-clicking on a cell or clicking in the formula bar or pressing the F2 key will put you into "Edit" mode. When in Edit mode, typing will insert what you type at the cursor position.

To enter a value other than a simple number (i.e. formula or function), you must start your entry with the equal sign "=". Otherwise, you will get exactly what you typed displayed in the cell.

Clicking on the red X on the formula bar or pressing the Esc key will cancel the current entry and return the cell to it's unedited state.

Clicking on the green check on the formula bar will accept the current edit/entry and leave the current cell selected.

When entering or editing non-value content, any of the movement actions (see above) will accept that entry and move you in the indicated direction.

When entering or editing a formula or function most of the movement actions will insert the cell reference of the cell moved to at the cursor position or move the cursor within the formula. The Enter key will accept the entry.

Simple formulas

Use the following keys for mathematical operators:

"+" addition
"-" subtraction
"*" multiplication
"/" division
"^" power or exponent

Example: "=2+3" (without the quotes) would display "5"

Operator Hierarchy

Recall from grade school math class the mnemonic "Please Excuse My Dear Aunt Sally" used to teach the hierarchy of mathematical operators or the order in which the formula is evaluated.

Parentheses
Exponents
Multiplication
Division
Addition
Subtraction

Practice Exercise

Open a new spreadsheet and insert the following: In cell A1, enter the number 2 In cell A2, enter the number 3 In cell A3, enter the number 4 In cell A4, enter the formula "=A1+A2*A3" (without the quotes)

Were you expecting the answer to be 20? If so, review the Operator Hierarchy.

The answer is 14 because the 3*4 is calculated first, then the 2 is added.

Edit cell A4 to insert parentheses as shown: "=(A1+A2)*A3" The answer for this formula is 20.

Notation for a range of cells

Frequently, you will need to refer to a range of cells for things like printing or the use in functions. Cell ranges are rectangular blocks identified by the opposite corners of the rectangle separated by a colon. For example: A1:C6 which is the same as A6:C1 or C6:A1 or C1:A6. Excel stores the reference with the top left corner first and the lower right next.

If you are referring to a range on a different worksheet, the syntax would look like: 'Sheet name'!A1:'Sheet name'!C6

Simple Functions

Functions can be entered by typing the function in the cell or by using the AutoPilot:Functions dialog window. The syntax of a function is: "=FUNCTION_NAME(argument1, argument2, argument3...)"

Each function has it's own arguments and the order is important. Some functions only need one or two arguments, and some have optional arguments.

The argument may be a number, a formula, another function, a cell reference or in some cases a reference to a range of cells.

Example 1: =SUM(A1:A3) would add all the numbers in cells A1 through A3
Example 2: =PMT(0.005,48,-15000) would calculate a loan payment amount for a loan of $15,000 at 6% (.5% per payment period) interest for 48 months. The numbers could be replaced with cell references that would contain those numbers as shown in Example 3. Example 3: =PMT(B5,B6,B7)

Practice Exercise

Continuing from the previous practice exercise, move to cell A5 and click on the Sum button (Σ). This will enter the formula: =SUM(A1:A4) into the cell. Press Enter to accept this.

Excel assumes that you want to sum all the numbers in the range above the cell you are editing. You can change the range of cells by dragging to select a different range (while the A1:A4 is highlighted) or type the desired range in it's place.

Erasing a cell or range of cells

Backspace will delete the contents of the active cell.
Delete will delete the contents of a range of cells (or the contents of just one cell)
Edit -> Delete... removes the cell from the workbook and prompts you for whether to replace it with the cells from below or from the right.
Edit -> Clear
- All - removes all formating, contents and comments
- Formats - removes the formatting of the cell and leaves the comments and contents
- Contents - same as the Delete key
- Comments - removes any comments associated with this cell

Copying Cells

When you copy a cell with a cell reference, the cell reference is relative to the cell. For example, if in cell B6, you have a formula that references cell C4, it is really referencing the cell one column to the right and two rows up. If you copy that cell to D8, the formula will now reference cell E6. In most cases, this is what you want it to do - why would you want the exact same formula in multiple cells?

Edit -> Copy, then Edit -> Paste
Ctrl-C, then Ctrl-V
Drag the small square on the lower right corner of the cell (pointer changes to a large "+" sign) to copy cells to adjacent cells

You can copy a range of cells and paste them into a single cell. The range will extend down and to the right of the cell you pasted into.

Using Absolute Cell References

To prevent the cell reference in a formula from changing when you copy the cell, preceding the column with a "$" sign will anchor that reference to the column. Preceding the row with a "$" sign will anchor that reference to the row. Preceding both will anchor that reference to the cell.

Examples:

Contents of cell B4	Results when copied to cell D8
=A1+A3	=C5+C7
=$A1+A3	=$A5+C7
=A$1+A3	=C$1+C7
=$A$1+A3	=$A$1+A3

Moving Cells

Moving cells does not change the cell's formula. In the example above the formula will still reference cell C4 after being moved from B6 to D8.

Edit -> Cut, then Edit -> Paste
Ctrl-X, then Ctrl-V
Drag the cell or selected range by positioning your pointer on an edge (the pointer changes to a hand)

AutoFill

Dragging a cell or selected range by the little square at the lower right corner AutoFills the cell to the range of cells you drag it across.

Dragging a single cell will copy that cell to the adjacent cells if it contains text, a number or a formula
Dragging a range of cells will do different things depending on the contents of the cells selected.

Contents of cell B4	Contents of cell B5	Results when B4:B5 is copied to cells B6:B8
3	4	5; 6; 7
3	6	9; 12; 15

Contents of cell B4	Results when B4 is copied to cells B5:B7
=C1	=C2; =C3; =C4
=C$1	=C$1; =C$1; =C$1
=$C1	=$C2; =$C3; =$C4
Jan	Feb; Mar; Apr
January	February; March; April

Printing

If you print a new worksheet, it will print all the non-blank columns and rows in the current worksheet unless you have set the default print area from File -> File Print Area -> Set Print Area or check the "Selection" radio button in the Print dialog window.

Watching: Excel Basics

Excel Basics

Contents

What is a spreadsheet?

What is a spreadsheet good for?

Basics of Using Excel

Cell Notation

Moving Around

Data Entry

Simple formulas

Operator Hierarchy

Practice Exercise

Notation for a range of cells

Simple Functions

Practice Exercise

Erasing a cell or range of cells

Copying Cells

Using Absolute Cell References

Moving Cells

AutoFill

Printing