Excel Basics
From Extension Collaborative Wiki
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
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.
