- Excel is a spreadsheet program.
- Spreadsheets are used by people who need to manipulate numbers, such as accountants and managers, even professors, engineers and students.
- Excel workbooks have the file extension *.xls.
- You can open an existing spreadsheet by locating it in the Windows Explorer and double clicking it or choosing File, Open.
- The contents of a spreadsheet are primarily in cells.
- Cells are organized into columns (lettered) and rows (numbered).
- Cells can contain constants (values or text) or formulas.
- You put things into a cell by highlighting it and typing, pasting, etc
- VERY IMPORTANT: You empty the contents of a cell by highlighting it and hitting the delete key or choosing Edit, Clear.
- Cells can contain numbers (Excel wouldn't be much good otherwise).
- You can change the way that numbers are displayed by formatting the cell.
- Cells can contain words and symbols (text) to label numbers or provide other information.
- Excel can perform calculations on numbers and/or manipulations on text using formulas.
- A cell that contains a formula always starts with the equal sign ("=").
- It is formulas that give Excel its power.
- Formulas can contain
- values: =1+2, and/or
- cell references: =A1+6*B2
- Formulas can use simple operators (eg, +,-, *, /) or they can use complex functions (eg, PMT(rate, nper, pv, fv, type)).
- Functions can be typed in by hand or they can be inserted by the function wizard
- Choose Insert, Function or type or hit "=" and select from the drop down list that shows up to the left of the formula bar.
- The address or reference of a cell is given by its column and row: B5 is the cell in column B, row 5.
- Addresses or references are very important: They allow you to refer to the contents of another cell in a formula.
- Excel can display either formula results or the formulas themselves.
- You can toggle between the two modes by hitting CTRL `. (Note: the key that has "`" on it also has the tilde (~). It is usually in at the left end of the top row of keys on the keyboard.)
- Excel is three dimensional.
- A set of rows and columns is called a worksheet.
- Each worksheet has a name (if only "Sheet 1").
- Each *.xls file is called a workbook and contains at least one worksheet.
- Excel can also contain other objects:
- Charts and Graphs
- Text boxes
- Pictures
- Controls (things like combo boxes, etc)
- and more…
- There are multiple ways to have Excel carry out a command:
- Menu bar menus: File, Edit, View, Insert, Format, Tools, Data, Window, Help
- Right click menus: A short list of commands relevant to whatever object is highlighted (eg, a cell).
- Tool bars
- Shortcuts:
- The usual: CTRL C (copy), CTRL X (cut), CTRL V (paste), CTRL S (save)
- Some special to Excel: CTRL 1 (format cells)
- Use File, Page Setup… to control how a worksheet will print.
- Portrait or landscape orientation
- Shrink or expand selection on printed page
- Margins
- Headers and footers
- Titles (ie, rows or columns repeated on each printed page)
- Use Tools, Options… to change a number of settings that affect how Excel looks and works:
- View: show or hide gridlines
- Edit: which way the highlight jumps when you hit enter
- Calculation: update automatically or manually
- A set of cells is called a range.
- A range can be specified by two addresses, eg, B2:D9, giving the top left and the bottom right cell of the range.
- In this case, the range contains cells in three columns (B, C, and D) and eight rows (2-9); 24 cells in all.
- Ranges can also be named using Insert, Name, Define
- Many functions use ranges
- Sum(B2:D9), Average(Grades)
- Data can be shifted around in Excel by copying and pasting, moving or filling.
- You can copy the contents of a cell or range, then paste it into another cell or range.
- This also copies the cell formats.
- You can cut and paste or drag and drop (the original cells will then be empty and formatless).
- You can fill a range using the "drag handle," the tiny little square in the lower right corner of the cell highlight.
- Point at the handle. The cursor will change from a big hollow cross to a little solid cross.
- Click and hold the left mouse button.
- Drag the mouse over the range you want to fill.
- Absolute versus relative references.
- If you have a relative cell reference (eg, "B3" in a formula), that reference tells the formula where to look relative to the cell that contains the formula.
- Suppose you had the formula "=A1+B2" in the cell B1. The references tell the formula to add the contents of the cell one column to the left and of the cell one row down.
- If you copied that formula to cell D1, it would become "=C1+D2".
- Note: Relative references do not change when you move a cell.
- Alternatively, you can use an absolute reference (eg, "$B$2").
- An absolute reference stays the same when you copy it to somewhere else.
- "=$A$1+B2" in B1 becomes "=$A$1+D2" when copied to D1.
- Relative and absolute references can be toggled by hitting F4 (the function key in the top row) when entering or editing a formula.
- Formatting is primarily applied to cells, but some formatting can also be columns, rows, and so forth.
- To format a cell (or cells):
- Highlight the cell you wish to format
- Select Format, Cells… (or hit CTRL 1)
- Apply formatting under Number, Alignment, Font, Border, Pattern or Protection tabs.
- Hit OK.
Top | ENV* K105 home | Environmental ET home
Anthony G Benoit
Room 205 (860) 885-2386
abenoit@trcc.commnet.edu
Minor editorial revisions