ICDL Advanced Spreadsheets flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Custom number format sections

A custom format can have up to four sections separated by semicolons: positive; negative; zero; text. Each section applies to numbers matching that condition.

Absolute reference in conditional formatting

In a conditional formatting formula, a dollar sign fixes a row or column, so the rule evaluates against the same cell or range for every formatted cell.

Format Painter

Copies formatting from a selected cell or range to another location. Double-clicking the Format Painter button keeps it active for multiple non-adjacent selections.

Merge cells drawback

Merged cells keep only the upper-left value and can prevent sorting, filtering, and some pivot table operations; use Center Across Selection instead for display-only centering.

Flash Fill

Fills values by recognizing a pattern typed in adjacent cells, activated with Ctrl+E or Data > Flash Fill; it does not create a dynamic formula.

VLOOKUP exact match

Set the fourth argument to FALSE or 0 to require an exact match. Approximate match TRUE or 1 requires the first column sorted ascending and can return wrong results otherwise.

INDEX MATCH

INDEX returns a value at a row and column position; MATCH finds the position of a lookup value. INDEX(range, MATCH(value, lookup_range, 0)) can look left or right.

SUMIFS

Adds values that meet multiple criteria: =SUMIFS(sum_range, criteria_range1, criteria1, ...). All ranges must be the same size, and text criteria can use wildcards.

IF function

Returns one value when a logical test is TRUE and another when FALSE: =IF(test, value_if_true, value_if_false). Nested IFs can handle more than two outcomes.

#N/A error

Means a value is not available to a formula, commonly because a lookup value was not found. Check spelling, data types, and whether the lookup range includes the value.

Absolute reference $A$1

The dollar signs lock both column and row, so copying the formula keeps the reference fixed. $A1 locks the column only; A$1 locks the row only.

COUNTIFS

Counts cells that satisfy multiple criteria across ranges: =COUNTIFS(range1, criteria1, range2, criteria2, ...). Criteria can be numbers, text, wildcards, or comparison operators in quotes.

Scatter chart

Plots numeric x and y values as points to show the relationship or correlation between two variables. It is not appropriate for categorical comparison, where a column chart is used.

Secondary axis

Adds a second value axis to a chart so series with very different scales can be compared. Select the series, then choose Format Data Series > Secondary Axis.

Pie chart limitation

Shows proportions of a single data series and works best with few categories. It cannot plot multiple series or negative values effectively; use a bar chart instead.

Switch Row/Column

Chart command that swaps which data orientation is treated as series and which as categories, fixing charts where categories appear as separate series.

Chart title from cell

Select the chart title, type = in the formula bar, and click a cell; the title then updates automatically whenever that cell's text changes.

Excel Table structured reference

A formula can refer to a table column by name, such as =SUM(Table1[Sales]), which automatically expands as rows are added to the table.

PivotTable Values area

Holds the fields that are summarized. Value Field Settings choose Sum, Count, Average, Max, and similar functions, and can show values as percentages of totals.

Refresh PivotTable

Pivot tables do not update automatically when source data changes; use Refresh or Refresh All on the PivotTable Analyze tab to recalculate the layout.

Slicer

A floating button control that filters a table or pivot table by a field. It shows available items and can be connected to multiple pivot tables.

Remove Duplicates

Data tool that deletes rows with identical values in selected columns. It permanently removes data, so copy the range first if the original must be kept.

Advanced Filter

Filters a range using a separate criteria range with column headings, supports AND across a row and OR across rows, and can copy results to another location.

Data validation list

Restricts a cell to values from a comma-separated list or a range. The in-cell dropdown can be enabled, and invalid entries are blocked by the error alert.

Custom validation formula

A formula used for data validation must return TRUE or FALSE. References are relative to the active cell of the validated range, so dollar signs control the checked cells.

Circle Invalid Data

Data validation command that marks cells containing values that no longer meet their validation rule with a red circle, useful after rules are changed.

Dependent dropdown

A second validation list whose source depends on the first choice, often built with named ranges and the INDIRECT function or with a dynamic array FILTER.

Protect Sheet

Restricts editing of a worksheet. Locked cells cannot be changed while protection is on, but cells formatted as unlocked remain editable if allowed in the protection options.

Locked cell default

Every cell is locked by default, but locking has no effect until the worksheet is protected. Unlock input cells before enabling sheet protection.

Hide formulas

On the Protection tab of Format Cells, tick Hidden, then protect the sheet so formulas do not appear in the formula bar; the calculated results remain visible.

Print titles

Page Layout setting that repeats chosen rows or columns on every printed page. Set rows to repeat at top and columns to repeat at left in Page Setup.

Workbook structure protection

Prevents users from adding, deleting, hiding, or renaming worksheets. It is separate from sheet protection and is set from Review > Protect Workbook.