Electronic Spreadsheet: Formulas, Functions, Charts, Sorting and Filtering
Formula rules
- Every formula begins with = .
- Operator precedence: ^ before * and /, before + and −; use brackets to force order.
- A relative reference (A1) shifts when copied; an absolute reference ($A$1) does not. Mixed forms ($A1, A$1) lock only one part.
Functions to know
SUM, AVERAGE, MAX, MIN, PRODUCT; COUNT counts numeric cells while COUNTA counts non-empty cells; COUNTIF and SUMIF apply one condition; IF(condition, value_if_true, value_if_false); VLOOKUP(lookup_value, table_array, col_index, FALSE) for an exact match.
Sorting and filtering
Sorting can be multi-level (Class, then Marks descending). Filtering hides rows temporarily; it does not delete them. Advanced filter can copy results to another location.
Charts
Column or bar for comparison, line for trend over time, pie for parts of one whole (a single series only), scatter for correlation between two numeric variables.
Worked example
Marks are in B2 and the pass mark is 33. =IF(B2>=33,'Pass','Fail') returns Pass for 45. To grade: =IF(B2>=75,'A',IF(B2>=60,'B',IF(B2>=33,'C','Fail'))). Reasoning: IF checks conditions in order, so the highest band is tested first; reversing the order would label 80 as B, because 80 also satisfies '>=60'.
Traps
Numbers stored as text stay left-aligned and are ignored by SUM; #DIV/0! appears when dividing by an empty cell; forgetting $ when copying a rate column; using a pie chart on a multi-series table; using COUNT instead of COUNTA for names; sorting on one column when a two-level sort was asked.