Flashcards for Excel

Question 1

Operator precedence

Answer 1

Within parentheses first; left to right with * and / before + and -

2016: 210-211. 2019: 209-210

Question 2

Strategy for developing a long formula

Answer 2

Create a piece at a time, verifying results of each piece before proceeding

Question 3

Function SMALL (range, k)

Function LARGE (range, k)

Answer 3

Smallest value in a range: SMALL (range, 1)

2016: 414-415. 2019: 417-418

Question 4

Function COUNT (range)

Answer 4

Count nonblank entries in a range

2016: 302. 2019: 250-251

Question 5

Replace blank cells with a value and color fill

Answer 5

[Find&Select] -> [GoToSpecial] -> [Blanks] -> select color -> enter value -> [Ctrl][Enter]

Question 6

Insert column to left

Answer 6

Highlight column, right-click -> [Insert]

2016 & 2019: 66-67

Question 7

Rearrange columns

Answer 7

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

Question 8

Rescale values, e.g., from 5 to 10 or from 15 to 10

Answer 8

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

Question 9

Average a range omitting smallest value

Answer 9

=(SUM(range)-SMALL(range,1))/ (COUNT(range)-1)

Question 10

Absolute and relative references

Answer 10

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

Question 11

Copy/propagate formula to bottom of range

Answer 11

Highlight formula cell(s) and move cursor to lower right corner. When cursor changes to a bold +, double-left click.

Question 12

Shortcut: Display format Cells

Answer 12

[Ctrl]1

Question 13

Hardcoding (sure way to make errors!)

Answer 13

Typing values rather than using references to cells containing parameters

2016 & 2019: 241, 824-825

Question 14

Parameters

Answer 14

Values that can vary over time, i.e., interest rates, weights. Using formula references to these values is parameterization.

2016 & 2019: 824-825

Question 15

Built-in functions

Answer 15

Examples: average, minimum, maximum, count, small, IF

2016 & 2019: 212-214, 219-221, 1033-1051

Question 16

Excel: IF function

Access: IIF function

Answer 16

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

Question 17

Nested IFs and IIFs

Answer 17

In an IF/IIF function, [valueIfTrue] and [valueIfFalse] arguments can be IF/IIF functions (64 nested levels for Excel; unlimited for Access)

Question 18

VLOOKUP Function

Answer 18

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

Question 19

VLOOKUP table format for [tableArray] argument

Answer 19

First column: Values sorted ascending for looking up
Columns after first: Values to extract

Question 20

Change column width

Answer 20

Place and hold cursor on right border of column header and drag border to desired column width.

Question 21

Blanks ignored in calculations

Answer 21

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

Question 22

Macro

Answer 22

Saved sequence of keystrokes for later execution to enable users to reduce time to perform repetitive tasks.

Excel 2016: 907-927. 2019: 924-935

Question 23

Create macro

Answer 23

1. Practice keystrokes
2. Enable Developer tab: FILE tab -> Customize Ribbon, check DEVELOPER
3. In DEVELOPER tab -> Record Macro
4. Name macro (no spaces
5. Enter shortcut key (uppercase letter) -> OK
6. Enter keystrokes for macro
7. In DEVELOPER tab -> Stop Recording

Question 24

Execute macro

Answer 24

[CTRL] [SHIFT] letter

Question 25

Minimize and debug worksheet errors

Answer 25

Excel 2016: 232-234, 701-729. 2019: 423-442

Question 26

Sort data

Answer 26

1. Highlight data range with headers
2. DATA tab -> Sort -> check “My data has
   headers”
3. Set Column pulldown menu to first sort field
4. For within level sorting, -> Add Level and
   select field
5. Reset sort order if needed

Question 27

Importing and cleaning data

Answer 27

Excel 2016: 733-762. 2019: 587-598

Question 28

Create pivot table

Answer 28

1. Highlight data range including headings
2. INSERT tab -> PivotTable -> OK
3. In PivotTable Fields, select field for Row
   Labels
4. Drag fields to ∑ Values
5. For each ∑ Values field, pull down menu and
   select from Value Field Settings
6. Format columns as needed

Excel 2016: 763-821. 2019: 655-677

Question 29

Function CONCATENATE

Answer 29

Put cell contents together:
CONCATENATE (comma-separated arguments)

Excel 2016: 248-249. 2019: 189, 208, 210, 225

Question 30

Circular reference

Answer 30

A cell referencing itself directly or indirectly through a sequence of formulas, creating a logic error

Excel 2016: 233-234. 2019: 230-231.

Question 31

Functions MIN(range) and MAX(range)

Answer 31

MIN (MAX) returns the minimum (maximum) value in the range

Question 32

DATEDIF

Answer 32

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.

Question 33

Function DAYS

Answer 33

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.