Excel Features and Functions Flashcards

Vocabulary flashcards covering core Excel features, functions, procedures, shortcuts, and spreadsheet concepts from the lecture notes.

Operator precedence

The standard hierarchy for calculations in Excel: operations within parentheses first, followed left to right with $*$ and $/$ before $+$ and $-\text{}$.

Strategy for developing a long formula

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

Function SMALL (range, k)

A function that returns the k-th smallest value in a range, such as $\text{SMALL(range, 1)}$ for the single smallest value.

Function LARGE (range, k)

A function that returns the k-th largest value in a range.

Function COUNT (range)

A function that counts nonblank entries in a range.

Procedure to replace blank cells with a value and color fill

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

Procedure to insert column to left

Highlight column, right-click, and select [Insert].

Procedure to rearrange columns

Click column header to move. In Home tab, [Clipboard], right-click column header to move -> Cut. Click column header to right of target location, right-click and select [Insert Cut Cells].

Rescale values

Multiply original values by a multiplier (whole number or fraction), e.g., to scale 5 to 10 multiply by $2$, or for 15 to 10 multiply by $\frac{2}{3}$.

Formula to average a range omitting smallest value

The formula written as $=(\text{SUM(range)}-\text{SMALL(range, 1)})/(\text{COUNT(range)}-1)$.

Relative reference

A standard cell reference such as A8 that adjusts relative to the destination cell when copied.

Absolute column reference only

A cell reference formatted as $\text{\$A8}$ where only the column reference remains fixed.

Absolute row reference only

A cell reference formatted as $\text{A\$8}$ where only the row reference remains fixed.

Absolute column & row reference

A cell reference formatted as $\text{\$A\$8}$ where both the column and row references remain fixed.

Cell reference cycle shortcut

Pressing [F4] to cycle through relative and absolute cell reference choices.

Display format Cells shortcut

The keystroke shortcut [Ctrl]1.

Hardcoding

Typing values directly into formulas rather than using references to cells containing parameters, which is a sure way to make errors.

Parameters

Values that can vary over time, such as interest rates and weights.

Parameterization

Using formula references to parameter values rather than typing static values directly.

Built-in functions

Predefined functions in Excel such as average, minimum, maximum, count, small, and IF.

Excel IF function / Access IIF function

Structured as $\text{IF(logicalTest, [valueIfTrue], [valueIfFalse])}$, returning [valueIfTrue] when logicalTest is TRUE and [valueIfFalse] when FALSE.

Nested IFs and IIFs

Functions where [valueIfTrue] and [valueIfFalse] arguments are themselves IF/IIF functions (allowing up to $64$ nested levels in Excel and unlimited in Access).

VLOOKUP Function

Structured as $\text{VLOOKUP(lookupValue, tableArray, ColIndexNum, rangeLookup)}$, where $\text{rangeLookup = FALSE}$ yields exact matches and $\text{rangeLookup = TRUE}$ (or omitted) yields approximate matches.

VLOOKUP table format

The format required for [tableArray] where the first column contains values sorted ascending for looking up, and subsequent columns contain values to extract.

Procedure to change column width

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

Blanks in Excel calculations

Excel ignores blank cells in calculations and formulas unless a function specifically treats them otherwise, though variants of the $\text{COUNT*}$ function will identify blank cells.

Macro

A saved sequence of keystrokes for later execution to enable users to reduce time needed to perform repetitive tasks.

Procedure to create a macro

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

Procedure to execute macro

Press [CTRL] [SHIFT] plus the assigned uppercase letter.

Procedure to sort data

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.

Procedure to create a pivot table

1. Highlight data range including headings 2. INSERT tab -> PivotTable -> OK 3. In PivotTable Fields, select field for Row Labels 4. Drag fields to $\sum \text{Values}$ 5. Select Value Field Settings for each field 6. Format columns as needed.

Function CONCATENATE

Puts cell contents together using comma-separated arguments, as in $\text{CONCATENATE(arguments)}$.

Circular reference

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

Functions MIN(range) and MAX(range)

Functions where $\text{MIN(range)}$ returns the minimum value and $\text{MAX(range)}$ returns the maximum value in a specified range.

DATEDIF function

An undocumented Excel function formatted as $\text{DATEDIF(start\_date, end\_date, units)}$ that returns the number of time units ('m' for months, 'd' for days) between two dates.

Function DAYS

A function introduced in Excel 2013 formatted as $\text{DAYS(end\_date, start\_date)}$ that returns the number of days between two dates, with argument order reversed from DATEDIF.