Flashcards for Excel
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-418
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
Minimize and debug worksheet errors
Excel 2016: 232-234, 701-729. 2019: 423-442
Sort data
- Highlight data range with headers
- DATA tab -> Sort -> check “My data has
headers” - Set Column pulldown menu to first sort field
- For within level sorting, -> Add Level and
select field - Reset sort order if needed
Importing and cleaning data
Excel 2016: 733-762. 2019: 587-598
Create pivot table
- Highlight data range including headings
- INSERT tab -> PivotTable -> OK
- In PivotTable Fields, select field for Row
Labels - Drag fields to ∑ Values
- For each ∑ Values field, pull down menu and
select from Value Field Settings - Format columns as needed
Excel 2016: 763-821. 2019: 655-677
Function CONCATENATE
Put cell contents together:
CONCATENATE (comma-separated arguments)
Excel 2016: 248-249. 2019: 189, 208, 210, 225
Circular reference
A cell referencing itself directly or indirectly through a sequence of formulas, creating a logic error
Excel 2016: 233-234. 2019: 230-231.
Functions MIN(range) and MAX(range)
MIN (MAX) returns the minimum (maximum) value in the range
DATEDIF
DATEDIF(start_date, end_date, units) returns the number of time units between start_date and end_date where units can be “m” for months and “d” for days. Although the function is no longer documented, it still works in Excel.
Function DAYS
DAYS(end¬_date, start_date) returns the number of days between two dates, introduced in Excel 2013. Note that the order of the arguments is reversed from the convention for DATEDIF.
