Flashcards for Excel Features
Operator precedence
Within parentheses first; left to right with * and / before + and -
2016: 210-211. 2019: 209-210
Strategy for developing a long formula
Create a piece at a time, verifying results of each piece before proceeding
Function SMALL (range, k)
Function LARGE (range, k)
Smallest value in a range: SMALL (range, 1)
2016: 414-415. 2019: 417-41
Function COUNT (range)
Count nonblank entries in a range
2016: 302. 2019: 250-251
Replace blank cells with a value and color fill
[Find&Select] -> [GoToSpecial] -> [Blanks] -> select color -> enter value -> [Ctrl][Enter]
Insert column to left
Highlight column, right-click -> [Insert]
2016 & 2019: 66-67
Rearrange columns
Click column header of column to move. In Home tab, [Clipboard], right-click column header of column to move to, right-click the column header of column to move -> Cut. Click column header to right of where column is to go. Right-click and select [Insert Cut Cells] from shortcut menu
2016: 751. 2019: 751
Rescale values, e.g., from 5 to 10 or from 15 to 10
Multiply original by the multiplier (whole number or fraction), e.g., for 5 to 10, multiply by 2, for 15 to 10, multiply by 2/3
Average a range omitting smallest value
=(SUM(range)-SMALL(range,1))/ (COUNT(range)-1)
Absolute and relative references
Relative reference: A8 Absolute column only: $A8 Absolute row only: A$ Absolute column & row: $A$8 Press [F4] to cycle choices
2016: 222-225. 2019: 221-223
Copy/propagate formula to bottom of range
Highlight formula cell(s) and move cursor to lower right corner. When cursor changes to a bold +, double-left click.
Shortcut: Display format Cells
[Ctrl]1
Hardcoding (sure way to make errors!)
Typing values rather than using references to cells containing parameters
2016 & 2019: 241, 824-825
Parameters
Values that can vary over time, i.e., interest rates, weights. Using formula references to these values is parameterization.
2016 & 2019: 824-825
Built-in functions
Examples: average, minimum, maximum, count, small, IF
2016 & 2019: 212-214, 219-221, 1033-1051
Excel: IF function
Access: IIF function
IF(logicalTest, [valueIfTrue], [valueIfFalse])
When logicalTest returns a value of TRUE, [valueIfTrue] goes in the cell. When logicalTest returns a value of FALSE, [valueIfFalse] goes in the cell.
2016: 329. 2019: 295-296
Nested IFs and IIFs
In an IF/IIF function, [valueIfTrue] and [valueIfFalse] arguments can be IF/IIF functions (64 nested levels for Excel; unlimited for Access)
VLOOKUP Function
VLOOKUP(lookupValue, tableArray, ColIndexNum, rangeLookup)
rangeLookup = FALSE for exact match
rangeLookup = TRUE or omitted for approximate match
Excel 2016: 330-332. Excel 2019: 316-318, 323-324
VLOOKUP table format for [tableArray] argument
First column: Values sorted ascending for looking up
Columns after first: Values to extract
Change column width
Place and hold cursor on right border of column header and drag border to desired column width.
Blanks ignored in calculations
Excel ignores blank cells, in calculations and in formulas unless a function specifically treats them otherwise. Variants of the COUNT* function will identify blank cells.
Excel 2016: 297-303. 2019: 250-251
Macro
Saved sequence of keystrokes for later execution to enable users to reduce time to perform repetitive tasks.
Excel 2016: 907-927. 2019: 924-935
Create macro
- Practice keystrokes
- Enable Developer tab: FILE tab -> Customize Ribbon, check DEVELOPER
- In DEVELOPER tab -> Record Macro
- Name macro (no spaces
- Enter shortcut key (uppercase letter) -> OK
- Enter keystrokes for macro
- In DEVELOPER tab -> Stop Recording
Execute macro
[CTRL] [SHIFT] letter
