I85PREP
⌘ K
ICDL Advanced Spreadsheets study materials
ICDL Advanced Spreadsheets study resources

ICDL Advanced Spreadsheets practice questions

One question at a time. Build your ICDL Advanced Spreadsheets understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Formatting

Which custom number format code displays the value 1234.5 as 1,234.50?

  1. 0000.00
  2. #.##
  3. #,##0.00 Correct answer.
  4. 0.00

Explanation

The #,##0 part inserts a thousands separator and the .00 part forces two decimal places, so 1234.5 is shown as 1,234.50. The placeholders do not change the stored value, only how it is displayed.

Question 2Formatting

A cell holds the value 0.25 and is formatted with the Percentage category using 0 decimal places. What does the cell display?

  1. 2.5%
  2. 250%
  3. 0%
  4. 25% Correct answer.

Explanation

Percentage format multiplies the stored value by 100 and appends a percent sign. With zero decimal places, 0.25 therefore displays as 25%.

Question 3Formatting

Which tool copies the formatting of a selected cell to another cell while leaving that cell's content unchanged?

  1. Copy and Paste
  2. Clear Formats
  3. Format Painter Correct answer.
  4. Cut and Paste

Explanation

Format Painter picks up the full set of formats from a source cell and applies them to the cells you click next. The content of the destination cells is not overwritten.

Question 4Formatting

Which alignment option makes a long text entry continue on several lines inside the same cell?

  1. Shrink to Fit
  2. Center Across Selection
  3. Merge and Center
  4. Wrap Text Correct answer.

Explanation

Wrap Text breaks the entry into multiple lines within the existing column width and increases the row height as needed. The text itself and the font size stay the same.

Question 5Formatting

In a custom number format such as 0.00;[Red]-0.00, what does the section after the semicolon control?

  1. How text entries are displayed
  2. How zero values are displayed
  3. How negative numbers are displayed Correct answer.
  4. How positive numbers are displayed

Explanation

Custom formats use sections in the order positive; negative; zero; text. The second section therefore governs negative values, here adding a minus sign and red font colour.

Question 6Formatting

Which feature stores a named combination of font, fill, border and number format that can be applied to cells in one click?

  1. Conditional Formatting
  2. Cell Styles Correct answer.
  3. Themes
  4. Table Styles

Explanation

Cell Styles are named, reusable sets of formatting attributes such as Heading 1, Total, Good and Neutral. Applying a style applies every attribute it contains at once.

Question 7Formatting

Which command keeps the top row of a worksheet visible while you scroll down through long data?

  1. Freeze Top Row Correct answer.
  2. Group
  3. Hide
  4. Split

Explanation

Freeze Top Row locks the first row in place so that it stays on screen while the rest of the sheet scrolls. It is found in the Freeze Panes drop-down on the View tab.

Question 8Formatting

Several adjacent cells each contain a value and are then merged into one cell. What happens to those values?

  1. The values are placed in a comment
  2. Only the upper-left value is kept Correct answer.
  3. The values are concatenated
  4. The values are added together

Explanation

Merging keeps only the content of the upper-left cell of the range. The other values are discarded, which is why Excel warns before merging cells that contain data.

Question 9Formatting

Which custom format displays negative numbers in parentheses, for example (1,200) instead of -1,200?

  1. (#,##0)
  2. #,##0;(#,##0) Correct answer.
  3. 0.00-
  4. -#,##0

Explanation

The first section formats positive numbers and the section after the semicolon formats negative numbers, so positives stay plain while negatives gain parentheses. The stored value is unchanged.

Question 10Formatting

You want every cell in C2:C40 that is greater than 5000 to be filled light red. Which conditional formatting rule type do you use?

  1. Data Validation
  2. Highlight Cells Rules > Greater Than Correct answer.
  3. Top/Bottom Rules > Top 10 Items
  4. Icon Sets

Explanation

Highlight Cells Rules compare each cell against a fixed value or text and apply a chosen format when the test is true. Greater Than with the value 5000 and a light red fill achieves the result.

Question 11Formatting

Several conditional formatting rules apply to the same range and more than one is true. Which rule is applied first?

  1. The rule created earliest
  2. The rule with the smallest range
  3. The rule listed highest in the Manage Rules dialog Correct answer.
  4. The rule that uses a formula

Explanation

Conditional formats are evaluated in priority order, and the list in Manage Rules shows the highest-priority rule at the top. Priority can be changed with the up and down arrows in that dialog.

Question 12Formatting

Which conditional formatting type draws a horizontal bar inside each cell whose length represents the cell value?

  1. Data Bars Correct answer.
  2. Color Scales
  3. Icon Sets
  4. Top/Bottom Rules

Explanation

Data Bars fill a proportion of the cell width in proportion to the value, giving an in-cell bar chart effect. Longer bars represent larger values relative to the range.

Question 13Formatting

Which custom format code displays the month as a three-letter abbreviation such as Jan?

  1. mmm Correct answer.
  2. m
  3. mmmm
  4. mm

Explanation

Each m in the code stands for a month digit or name, so mmm produces the abbreviated month name. The surrounding codes for day and year are unaffected.

Question 14Formatting

You switch a workbook to a different built-in theme. Which of the following is affected?

  1. The theme fonts and theme colours used by formatted cells Correct answer.
  2. The names of the worksheets
  3. The calculation mode of the workbook
  4. The stored numeric values

Explanation

A theme supplies a coordinated set of fonts, colours and effects, and cells that use theme colours or theme fonts update when the theme changes. Directly applied custom colours are unaffected.

Question 15Formatting

Which alignment option reduces the displayed size of the text so all of it fits within the current column width?

  1. Wrap Text
  2. Shrink to Fit Correct answer.
  3. Merge and Center
  4. Increase Indent

Explanation

Shrink to Fit keeps the entry on one line and scales down only the on-screen text until it fits the column. The font size stored for the cell stays the same.

Question 16Formatting

A column heading must run diagonally upward from bottom-left to top-right. Which setting achieves this?

  1. Wrap Text on the Alignment tab
  2. Text direction right-to-left
  3. Center Across Selection
  4. An Orientation angle on the Alignment tab Correct answer.

Explanation

The Orientation control on the Alignment tab rotates cell text by a chosen number of degrees or stacks it vertically. A positive angle tilts the text upward from left to right.

Question 17Formatting

You need to remove conditional formatting from a range but keep the manual fills, borders and all the data. Which action does this?

  1. Clear Rules from Selected Cells Correct answer.
  2. Clear Formats from the Home tab
  3. Remove the cells' borders
  4. Delete the rows and re-enter them

Explanation

The Clear Rules command removes conditional formatting rules only, leaving manually applied formats and cell contents intact. Deleting rules also removes their formatting from the affected cells.

Question 18Formatting

Which custom format makes the number 12 display as 12 kg?

  1. "kg"0
  2. 0 [kg]
  3. 0 "kg" Correct answer.
  4. 0;kg

Explanation

Text that should appear literally alongside a number is placed in double quotation marks after the digit placeholders. The space inside the quotes keeps the unit separated from the number.

Question 19Formatting

You edit the definition of the cell style named Total so that it becomes bold. Which cells change appearance?

  1. No cells until the style is re-applied by hand
  2. Only the cells that are currently selected
  3. All cells that use the Total style Correct answer.
  4. Every cell on the worksheet

Explanation

A cell style is a live definition, so editing it updates every cell to which that style is applied. Cells formatted manually with bold are not linked to the style and stay as they are.

Question 20Formatting

A conditional formatting rule with the formula =$B2="Yes" is applied to the range A2:D20 to highlight whole rows. Why is the column locked while the row stays relative?

  1. To speed up recalculation of the sheet
  2. Because absolute references are required in all rules
  3. So that each row tests its own value in column B Correct answer.
  4. To make the rule apply only to column B

Explanation

Relative and absolute references are resolved against the top-left cell of the applied range. Locking the column keeps the test in column B while the relative row lets the reference shift down for each row.

Question 21Formatting

A custom number format contains four sections separated by semicolons. What does the fourth section control?

  1. Text values Correct answer.
  2. Positive numbers
  3. Zero values
  4. Negative numbers

Explanation

The sections of a custom format are read in the fixed order positive; negative; zero; text. The fourth section therefore determines how text entries are displayed.

Question 22Formatting

A cell displays 1.23 because it is formatted to two decimal places, but the stored value is 1.2345. What value does a SUM formula that includes this cell use?

  1. The full stored value 1.2345 Correct answer.
  2. The rounded value 1.23
  3. Zero until the format is removed
  4. The text shown in the formula bar

Explanation

Number formatting changes only what is displayed, never the value stored in the cell. Calculations such as SUM therefore use the full precision of 1.2345.

Question 23Formatting

In the Manage Rules dialog, what does ticking Stop If True for a conditional formatting rule do?

  1. Blocks lower-priority rules when that rule is true Correct answer.
  2. Pauses the rule until the workbook is reopened
  3. Deletes the rule after it runs once
  4. Stops the worksheet from recalculating

Explanation

Rules are evaluated from the top of the priority list downwards. Stop If True ends the evaluation once that rule is satisfied, so rules of lower priority are not applied.

Question 24Formatting

Which custom format hides zero values while still displaying positive and negative numbers?

  1. #,##0;-#,##0;0
  2. 0.00
  3. 0;-0;"zero"
  4. #,##0;-#,##0;; Correct answer.

Explanation

Leaving the third section empty means nothing at all is shown for a zero value. The first two sections still display positive and negative numbers normally.

Question 25Formatting

Which custom format displays 1500000 as 1500 by scaling the value down for display only?

  1. #,##0,,
  2. #,##0, Correct answer.
  3. 0.0#
  4. #,##0

Explanation

A comma placed directly after the digit placeholders at the end of the format divides the displayed number by one thousand. The stored value is not changed, only what the cell shows.

Question 26Formatting

Which tool copies only the formatting of a selected cell so it can be applied to another cell or range?

  1. Cell Styles gallery
  2. Copy and Paste Special
  3. Format Painter Correct answer.
  4. Conditional Formatting

Explanation

Format Painter transfers formatting attributes such as font, fill, border and number format without copying the cell's contents. It can be applied to a single cell or dragged across a range.

Question 27Formatting

Which alignment option displays long text on multiple lines inside a single cell without changing the column width?

  1. Shrink to Fit
  2. Center Across Selection
  3. Wrap Text Correct answer.
  4. Merge Cells

Explanation

Wrap Text breaks the text at spaces and increases the row height so all content is visible within the current column width. It does not reduce font size or combine cells.

Question 28Formatting

A column is too narrow to show its longest entry. Which command adjusts the column width to fit the contents automatically?

  1. Hide and Unhide Columns
  2. Freeze the top row
  3. AutoFit Column Width Correct answer.
  4. Increase the row height

Explanation

AutoFit Column Width sets the column width to the widest displayed entry in that column. Row height affects vertical size, and freezing panes affects scrolling.

Question 29Formatting

Which custom number format displays the value 0.256 as 25.6%?

  1. 0.0
  2. #.0%%
  3. 0.0%%
  4. 0.0% Correct answer.

Explanation

In a custom format, the % sign both multiplies the stored value by 100 and appends the percent character, so 0.256 becomes 25.6%. The 0.0 part fixes one decimal place.

Question 30Formatting

Which border setting places a line around the outside edge of a selected range while leaving the interior cells without borders?

  1. Inside Borders
  2. Thick Bottom Border
  3. All Borders
  4. Outside Borders Correct answer.

Explanation

Outside Borders draws a border on the outer perimeter of the selection only. All Borders also draws lines between interior cells, while Inside Borders draws only interior lines.

Question 31Formatting

Which custom number format displays the number 5 as 05?

  1. 0
  2. #.##
  3. 0.0
  4. 00 Correct answer.

Explanation

Each 0 in a custom format is a required digit placeholder, so 00 forces at least two digits and pads single-digit values with a leading zero. The # placeholder is optional and does not add leading zeros.

Question 32Formatting

Which custom number format displays the date 7 March 2024 as 2024-03-07?

  1. mm-dd-yy
  2. dd/mm/yyyy
  3. mmmm d, yyyy
  4. yyyy-mm-dd Correct answer.

Explanation

yyyy gives a four-digit year, mm gives a two-digit month and dd gives a two-digit day, separated by hyphens. This produces the year-first form 2024-03-07.

Question 33Formatting

Which fill option applies a smooth transition between two or more colours across a cell or range?

  1. Pattern fill
  2. No background fill
  3. Gradient fill Correct answer.
  4. Solid colour fill

Explanation

Gradient fill blends colours gradually across the selected cells, optionally changing the direction and style of the blend. Solid fill uses one colour, and pattern fill repeats a small pattern.

Question 34Formatting

Which conditional formatting rule type adds a small symbol such as a green circle, amber triangle or red diamond to each cell based on its value?

  1. Colour Scales
  2. Top/Bottom Rules
  3. Data Bars
  4. Icon Sets Correct answer.

Explanation

Icon Sets assign one of a group of icons to a cell according to thresholds, giving a visual rating. Data Bars use bar length, and Colour Scales shade the cell background.

Question 35Formatting

Which conditional formatting rule highlights the ten highest values in a range?

  1. Top 10 Items Correct answer.
  2. Above Average
  3. Duplicate Values
  4. Greater Than

Explanation

The Top 10 Items rule ranks values in the selected range and formats the highest specified number of items, which can be set to 10. Above Average compares to the average rather than ranking the highest values.

Question 36Formatting

Which conditional formatting rule formats cells whose content appears more than once in the selected range?

  1. Duplicate Values Correct answer.
  2. Less Than
  3. Between
  4. Text That Contains

Explanation

Duplicate Values detects repeated entries in the range and applies the chosen format to every occurrence. Text That Contains looks for a specified text string instead of repetition.

Question 37Formatting

Which command removes number formats, fills, borders and fonts from selected cells while leaving their contents in place?

  1. Clear Formats Correct answer.
  2. Delete Cells
  3. Clear Contents
  4. Clear All

Explanation

Clear Formats removes only the formatting layer from the selection, so the underlying values and formulas remain. Clear All removes both contents and formatting.

Question 38Formatting

In a custom number format, what does the code [Red] do?

  1. It changes the cell background fill colour to red.
  2. It sets the font colour for that section. Correct answer.
  3. It converts the displayed value into a negative number.
  4. It adds the word Red as text after the displayed number.

Explanation

A colour name in square brackets sets the font colour for that section of the custom number format, for example [Red]0.00. It does not affect the cell fill or the stored value.

Question 39Formatting

A workbook contains a named range Data that refers to A1:A20. A conditional formatting rule is applied to A1:A20. Which formula highlights only the cell containing the largest value in Data?

  1. =MAX(Data)>0
  2. =A1>MAX(Data)
  3. =A1=MAX(A1:A20)
  4. =A1=MAX(Data) Correct answer.

Explanation

The formula compares each cell to the maximum of the fixed named range Data, so the reference does not shift between cells. The left-hand A1 is relative, allowing each cell in the applied range to be tested.

Question 40Formatting

Which statement describes how the Accounting number format differs from the Currency format?

  1. It always removes all decimal places from values.
  2. It displays every negative value in red text.
  3. It stores all numbers as text instead of numbers.
  4. It aligns currency symbols at the left edge. Correct answer.

Explanation

Accounting aligns currency symbols at the left edge of the cell and the decimal points in a column, which differs from Currency, where the symbol sits immediately beside the number. Both formats can show decimals and negative values.

Question 41Formulas and Functions

In a spreadsheet formula, which cell reference stays unchanged when the formula is copied to another cell?

  1. $A$1 Correct answer.
  2. A1
  3. A$1
  4. $A1

Explanation

A dollar sign before both the column letter and the row number makes the reference absolute, so copying the formula never alters it. A$1 fixes only the row, and $A1 fixes only the column, so those are mixed references.

Question 42Formulas and Functions

Which function returns the number of cells in a range that contain numeric values?

  1. COUNT Correct answer.
  2. SUM
  3. COUNTBLANK
  4. COUNTA

Explanation

COUNT tallies only cells holding numbers and ignores text, logical values and empty cells. The other functions either widen the test to any non-empty cell or add the values instead of counting them.

Question 43Formulas and Functions

In the formula =IF(B2>=60,"Pass","Fail"), what role does B2>=60 play?

  1. It is the text shown in the result cell
  2. It is the value returned when the test fails
  3. It is the logical test being evaluated Correct answer.
  4. It is the range the formula adds up

Explanation

The IF function takes a logical test first, then the value for a true result and the value for a false result. The expression B2>=60 produces TRUE or FALSE and therefore occupies the first argument slot.

Question 44Formulas and Functions

What result does the formula =ROUND(12.3456,2) return?

  1. 12
  2. 12.35 Correct answer.
  3. 12.3
  4. 12.346

Explanation

The second argument of ROUND states how many decimal places to keep, so the value is rounded to two decimals. The third decimal digit is 5, which rounds the second decimal up.

Question 45Formulas and Functions

Cell C1 contains the formula =A1+B1. The formula is copied one row down to C2. What formula does C2 contain?

  1. =A2+B1
  2. =$A$2+$B$2
  3. =A2+B2 Correct answer.
  4. =A1+B1

Explanation

Relative references shift by the same offset as the formula itself, so moving down one row advances every row number by one. Both references therefore become row 2.

Question 46Formulas and Functions

Which function returns the largest numeric value in a range?

  1. MAX Correct answer.
  2. MIN
  3. COUNT
  4. AVERAGE

Explanation

MAX scans the range and returns the highest numeric value it finds, ignoring text and blanks. Its counterpart for the smallest value is MIN.

Question 47Formulas and Functions

In a VLOOKUP formula, what does a fourth argument of FALSE mean?

  1. The closest match below the value is returned
  2. The search runs to the left of the key column
  3. The first column must be sorted ascending
  4. An exact match is required Correct answer.

Explanation

The fourth argument controls match type: FALSE (or 0) demands an exact match and returns an error if none is found. TRUE (or 1) performs an approximate match on sorted data.

Question 48Formulas and Functions

Which operator is used inside a formula to define a range running from cell A1 through cell B10?

  1. the colon (:) Correct answer.
  2. the ampersand (&)
  3. the comma (,)
  4. the semicolon (;)

Explanation

The colon is the range operator, so A1:B10 refers to every cell in the rectangular block between the two corners. Other symbols join text or separate arguments.

Question 49Formulas and Functions

The formula =$A1*B$1 sits in cell C1 and is copied to cell D2. Which formula appears in D2?

  1. =$A1*B$1
  2. =$A2*B$1
  3. =$B2*C$2
  4. =$A2*C$1 Correct answer.

Explanation

Copying one column right and one row down shifts every relative part by that offset while absolute parts stay fixed. $A1 becomes $A2 and B$1 becomes C$1.

Question 50Formulas and Functions

Which argument order does the SUMIF function use?

  1. =SUMIF(range, sum_range, criteria)
  2. =SUMIF(sum_range, criteria, range)
  3. =SUMIF(criteria, range, sum_range)
  4. =SUMIF(range, criteria, sum_range) Correct answer.

Explanation

SUMIF tests the first argument (the range) against the criteria in the second, then adds the corresponding values from the optional sum_range. Putting the criteria before the range it applies to is a common error.