INLS261 Tools for Information Literacy

Formulas & Formats

use this example for formula demonstrations

all formulas start with the "=" sign, and are followed by cell addresses and mathematical operators

[image of a formula with descriptive labels]

= begins a formula. If the = sign is not present, formula entries are treated as text
(parentheses) are to establish precedent. The result is held as a subtotal for further calculations. Where parentheses are enclosed within parentheses, they are calculated from the inside set to the outside set.
cell reference is a location on the sheet containing a value to work with. Unless fixed by user input, all locations are vectors, relative to the location of the cell that contains the formula
- if you need to lock a formula reference to a specific location (or, said another way, to an absolute - not relative - cell reference), use the F4 key to append the $ sign to a cell reference to lock in a specific location
- example:
  - $A1 refers absolutely to row A but only relatively to column 1[column will vary]
  - $A$1 refers absolutely to row A and absolutely to column 1 [there is nothing relative about this reference]
arithmetic operators
- + addition
- - subtraction
- * multiplication
- / division
- ^ exponentiation
a numeric constant is a number that will remain the same until the value is changes in the future
functions are terms that describe built in formulas that perform specialized arithmetic
range reference is the starting and ending cells of a group of cells and enclosed in parentheses. they are separated by colons if the cells are contiguous, or with a comma if they are non-contiguous

to enter a formula in a cell, click on the cell, then transfer your attention to the formula bar

[MSExcel 2007 formulas can be entered in two locations]

click or type the = character, and then enter the formula. Remember to start all formulas and functions with the = sign
normally, formulas use cell references rather than numbers. Instead, place the cell address of the cell that contains the number you want into the formula. That way, if the number changes, the formula remains correct.
- for example, =A1+B1 would add the numbers in cells A1 and B1 and put the answer wherever you entered this formula
- ff you use cell addresses, you can change data later (e.g., data in B4), and then the number calculated by a formula will change automatically
however, you can use numbers in formulas
- = D8*1.06 means take the contents of D8 and multiply them by 1.06
when using the formula bar, you can point to the cell you want, type an operator, point to the next cell OR enter the formula using the cell reference addresses

(parentheses) - the result is held as a subtotal for further calculations. Where parentheses are enclosed within parentheses, they are calculated from the inside set to the outside set.
^ exponentiation
/ division or * multiplication - calculated in the sequence entered from left to right
- formulas are calculated left to right and parentheses are used to distinguish order
- using 15/3+2 for example:
  - 15/3+2 = 7
  - 15 divided by three = five, and five plus two = seven
  - or (15/3) + 2 = (5)+2 = 7
- but
  - fifteen divided by the result of three plus two = fifteen divided by five = three
  - or 15/(3+2) = 15/5 = 3
+ addition or - subtraction - calculated in the sequence entered from left to right

functions - these are calculated separately and included in the result depending on the adjacent arithmetic operators

click in cell with formula or in the formula box

[MSExcel 2007 places to enter formulas]

select cell(s) and use Standard Toolbar for fast formatting of currency/percentage & increased/decreased decimal. For additional formatting, select cell or cells & right click.

[MSExcel 2007 formatting cells]

[MSExcel 2007 format cells dialog box]

change appearance of date (Oct-10-2006 or 10/10/06 or, my favorite, 10-Oct-2006)

hold the CTRL key down while selecting worksheet by clicking on the name tab
to ungroup, right click sheet tab and select Ungroup Sheets or click on a sheet that has not been grouped
to move the order of worksheets, click & drag
to copy a worksheet, click, hold CTRL key while dragging
to insert, delete, or select all worksheets, right-click on a worksheet tab

select the cell where where the comment will be added and right click
- select Insert Comment
- click inside the comment box and type

[MSExcel 2007 adding a comment to a cell]

delete or format the comment
- right click the comment (the "red" comment marker)
- select the desired option

to check for source of errors, use the Auditing Toolbar

[MSExcel 2007 formula auditing tools]