I85PREP
⌘ K
Computer Specialist in Spreadsheet & Database Level 1 study materials
Computer Specialist in Spreadsheet & Database Level 1 study resources

Computer Specialist in Spreadsheet & Database Level 1 practice questions

One question at a time. Build your Computer Specialist in Spreadsheet & Database Level 1 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 1Spreadsheet Fundamentals and Interface

In a spreadsheet, which part of the application window displays the address of the currently selected cell, such as C7?

  1. The sheet tab
  2. The formula bar
  3. The status bar
  4. The Name Box Correct answer.

Explanation

The Name Box sits at the left end of the formula bar area and shows the reference of the active cell or the name of the selected range. Typing a reference into it also jumps directly to that cell, which makes it a navigation tool as well as a display.

Question 2Spreadsheet Fundamentals and Interface

A user needs to enter today's date so that it updates automatically every time the workbook is reopened. Which approach meets this requirement?

  1. Pressing the keyboard shortcut that inserts a static date
  2. Typing the date manually each morning
  3. Entering the TODAY function in the cell Correct answer.
  4. Formatting an empty cell with the Date number format

Explanation

The TODAY function is volatile and recalculates whenever the workbook opens or recalculates, so it always returns the current system date. A manually typed date or a shortcut-inserted date is a static serial value that never changes on its own.

Question 3Spreadsheet Fundamentals and Interface

Which file format should be chosen to preserve multiple worksheets, cell formatting, and formulas when saving a workbook for later editing?

  1. Native workbook format (.xlsx) Correct answer.
  2. Comma-separated values (.csv)
  3. Plain text (.txt)
  4. Tab-separated values (.tsv)

Explanation

The native workbook format stores the full workbook structure, including every sheet, cell formatting, formulas, and defined names. Delimited text formats keep only the active sheet's displayed values and discard formulas and formatting.

Question 4Spreadsheet Fundamentals and Interface

What is the default result when a numeric value is typed into a cell that is narrower than the number?

  1. The value is truncated to fit the column width
  2. The cell displays hash marks instead of the number Correct answer.
  3. The value is rounded to fewer digits
  4. The column automatically widens to fit the value

Explanation

When a numeric entry cannot fit the current column width, the cell shows a row of hash marks to signal that the display is too narrow. The stored value is unchanged; widening the column restores the full display.

Question 5Spreadsheet Fundamentals and Interface

A worksheet lists 500 invoice amounts in column D. Which reference style allows a formula to sum only the rows where column B equals "Paid"?

  1. A relative reference such as D2
  2. A structured reference to a table column Correct answer.
  3. A mixed reference such as D$2
  4. An absolute reference such as $D$2

Explanation

When data is formatted as a table, structured references address whole columns by name, so a formula can aggregate a column while filtering on another column's values. This keeps the formula readable and automatically extends when rows are added.

Question 6Spreadsheet Fundamentals and Interface

Which action freezes the top row so it stays visible while scrolling down through a long worksheet?

  1. Hiding row 1 from the row context menu
  2. Applying a filter to the header row
  3. Setting row 1 height to zero
  4. Selecting row 1 and choosing Freeze Panes Correct answer.

Explanation

The Freeze Panes command locks rows above and columns to the left of the active cell so they remain on screen during scrolling. Placing the cursor in row 1 and freezing keeps the header row permanently visible.

Question 7Spreadsheet Fundamentals and Interface

A user types 007 into a General-formatted cell and presses Enter. What appears in the cell?

  1. 007
  2. 7 Correct answer.
  3. An error value
  4. 0.07

Explanation

In the General format, numeric entries are treated as numbers, so leading zeros are not stored and the cell displays 7. To keep the leading zeros, the cell must be formatted as Text before entry or the value entered with a leading apostrophe.

Question 8Spreadsheet Fundamentals and Interface

A formula in cell E5 contains the reference B5. The formula is copied down to E6. What reference appears in E6?

  1. B4
  2. B5
  3. $B$5
  4. B6 Correct answer.

Explanation

A reference without dollar signs is relative, so copying the formula one row down adjusts the row number by one, producing B6. The column letter stays the same because the copy moved vertically only.

Question 9Spreadsheet Fundamentals and Interface

Which operator has the highest precedence in a spreadsheet formula such as =2+3*4^2?

  1. Addition (+)
  2. Multiplication (*)
  3. Exponentiation (^) Correct answer.
  4. All operators are evaluated left to right

Explanation

Spreadsheet evaluation follows standard order of operations: exponentiation first, then multiplication and division, then addition and subtraction. Parentheses override this default sequence when a different result is needed.

Question 10Spreadsheet Fundamentals and Interface

A cell displays ###### in a currency-formatted column. What is the most reliable first step to make the value visible?

  1. Widen the column or reduce the decimal places Correct answer.
  2. Recalculate the workbook with F9
  3. Delete the cell and retype the number
  4. Change the cell format to Text

Explanation

Hash marks indicate the formatted value is wider than the column, so widening the column or reducing displayed decimals restores the visible number. The stored value is intact and does not need re-entry.

Question 11Spreadsheet Fundamentals and Interface

A worksheet has data in A1:C20. The user selects A1:C20 and applies Sort A to Z on column B. What is the key requirement for this sort to keep records intact?

  1. The cells must be formatted as Text
  2. Column A must be sorted first
  3. The header row must be deleted
  4. The range must include all columns belonging to each record Correct answer.

Explanation

Sorting rearranges entire rows within the selected range, so every column that belongs to a record must be included or the row data will be split apart. Selecting a single column and sorting is the classic cause of misaligned records.

Question 12Spreadsheet Fundamentals and Interface

Which paste option transfers only the calculated results of a formula range while discarding the underlying formulas?

  1. Paste Special with Formats
  2. Paste Special with Comments
  3. Paste Special with Values Correct answer.
  4. Paste Special with Formulas

Explanation

Paste Special with Values writes the current results as static data, removing any dependency on the source cells. This is the standard way to freeze calculated numbers before deleting or moving the original data.

Question 13Spreadsheet Fundamentals and Interface

A user wants the header row of a long table to print at the top of every page. Which setting accomplishes this?

  1. The Fit to one page wide scaling option
  2. A manual page break inserted after the header
  3. Print Titles with the header row specified as rows to repeat Correct answer.
  4. Centering the sheet horizontally on the page

Explanation

The Print Titles setting designates specific rows to repeat at the top of each printed page. This differs from a page break, which only controls where pages end rather than what content repeats.

Question 14Spreadsheet Fundamentals and Interface

A cell contains the formula =SUM(A1:A10). A user inserts a new row between rows 5 and 6 and enters a number in column A. What happens to the formula result?

  1. The formula expands to include the new row Correct answer.
  2. The formula still sums only the original ten cells
  3. The formula converts to a static value
  4. The formula returns an error

Explanation

When rows are inserted inside a referenced range, the range boundary adjusts automatically, so the sum now covers eleven cells. This behavior is a core advantage of referencing ranges rather than listing individual cells.

Question 15Spreadsheet Fundamentals and Interface

A user wants to enter the same value into cells A1, A5, and A9 at once. Which technique accomplishes this?

  1. Selecting all three cells with Ctrl and entering the value followed by Ctrl+Enter Correct answer.
  2. Copying A1 and pasting into A5 only
  3. Typing the value three separate times
  4. Using the Fill Down command from A1

Explanation

Selecting non-adjacent cells with Ctrl and confirming the entry with Ctrl+Enter writes the value into every selected cell simultaneously. This avoids repetitive typing and keeps the entries identical.

Question 16Spreadsheet Fundamentals and Interface

A workbook contains a formula referencing a cell on another sheet named Q1 Data. Which reference is correctly written?

  1. =Q1 Data!B4 Correct answer.
  2. =Q1 Data.B4
  3. =Q1 Data B4
  4. =Q1 Data:B4

Explanation

A cross-sheet reference places the sheet name before an exclamation mark, followed by the cell address. When the sheet name contains a space, the application adds single quotation marks around the name automatically.

Question 17Spreadsheet Fundamentals and Interface

A worksheet uses a formula =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE). What does the fourth argument control?

  1. The number of rows to search
  2. The worksheet where results are written
  3. Whether the match must be exact or approximate Correct answer.
  4. The column index of the value to return

Explanation

The fourth argument is the range_lookup flag: FALSE requires an exact match, while TRUE or omitted allows an approximate match on sorted data. Setting it to FALSE prevents incorrect matches when the first column is unsorted.

Question 18Spreadsheet Fundamentals and Interface

A formula =IF(B2>100,"High",IF(B2>50,"Medium","Low")) returns "Low" for B2 containing 50. Why?

  1. The first condition is false and the second condition is also false Correct answer.
  2. The formula contains a syntax error
  3. Text results are not allowed in nested IF functions
  4. The comparison operator should be >=

Explanation

The first test, 50>100, is false, so evaluation moves to the nested IF. The second test, 50>50, is also false, so the final else value "Low" is returned; the boundaries are strict greater-than comparisons.

Question 19Spreadsheet Fundamentals and Interface

A user applies a filter to a table and then uses SUBTOTAL with function number 109 to sum a column. What is the effect compared with SUM?

  1. SUBTOTAL sums only the visible cells in the first column
  2. SUBTOTAL converts the filtered rows into static values
  3. SUBTOTAL excludes rows hidden by the filter Correct answer.
  4. SUBTOTAL returns the same total as SUM regardless of the filter

Explanation

Function number 109 in SUBTOTAL ignores rows hidden by a filter, so the result reflects only the visible records. The plain SUM function includes filtered-out rows, which is why SUBTOTAL is preferred for filtered totals.

Question 20Spreadsheet Fundamentals and Interface

A user needs to prevent accidental changes to a worksheet while still allowing sorting and filtering of an existing table. Which sequence is appropriate?

  1. Convert the table to a plain range and lock all cells
  2. Hide the worksheet and share the workbook
  3. Protect the sheet with a password and allow sorting and filtering in the protection options Correct answer.
  4. Apply a conditional format that highlights edited cells

Explanation

Sheet protection blocks edits to locked cells, and the protection dialog offers checkboxes that permit sorting and filtering even while the sheet is protected. This preserves data integrity without removing useful table operations.

Question 21Data Entry, Formatting, and Worksheet Management

In a spreadsheet, which data-entry practice keeps a column of customer names cleanly sortable and filterable?

  1. Splitting first and last names into separate adjacent columns Correct answer.
  2. Adding trailing spaces to align names visually
  3. Typing each full name into a single cell with consistent spacing
  4. Merging name cells across two columns for each record

Explanation

Separating first and last names into distinct fields preserves one value per cell, which is the core rule of a tidy data table. Sorting and filtering then operate on a single consistent value, and merging cells or padding with spaces breaks that structure.

Question 22Data Entry, Formatting, and Worksheet Management

An operator must enter the value 007 as text so the leading zeros are preserved. Which entry method achieves this?

  1. Type 007 and apply the Currency format
  2. Type 7 and format the cell as Number
  3. Type 007 and press Enter
  4. Type an apostrophe followed by 007 Correct answer.

Explanation

A leading apostrophe tells the spreadsheet to treat the following characters as literal text, so 007 is stored and displayed unchanged. Numeric formats such as Number or Currency store the underlying value 7 and cannot restore leading zeros.

Question 23Data Entry, Formatting, and Worksheet Management

A worksheet must show the same title in row 1 across columns A through F without breaking the grid. Which action is appropriate?

  1. Type the title into each of the six cells
  2. Set row 1 height to double and type the title once
  3. Select A1:F1 and use Merge & Center Correct answer.
  4. Type the title into A1 and hide columns B through F

Explanation

Merge & Center combines the selected cells into one display area and centers the content, which is the standard way to span a title across several columns. The remaining options either duplicate data or hide working columns, both of which harm later sorting and analysis.

Question 24Data Entry, Formatting, and Worksheet Management

Which number format displays 1234.5 as 1,234.50 while keeping the stored value unchanged?

  1. General format
  2. Percentage format with two decimals
  3. Accounting format with two decimals Correct answer.
  4. Text format

Explanation

The Accounting format inserts a thousands separator and fixes two decimal places for display only, leaving the stored number intact. Text and General formats do not enforce a thousands separator with fixed decimals, and Percentage multiplies the displayed value by 100.

Question 25Data Entry, Formatting, and Worksheet Management

A user wants the header row to remain visible while scrolling through hundreds of data rows. Which worksheet setting accomplishes this?

  1. Insert a page break after the header row
  2. Apply Wrap Text to the header cells
  3. Set the header row to Print Titles
  4. Freeze Panes below the header row Correct answer.

Explanation

Freeze Panes locks rows above the active cell so they stay on screen during vertical scrolling. Print Titles only affects printed output, and Wrap Text or page breaks do not keep anything visible on screen.

Question 26Data Entry, Formatting, and Worksheet Management

Which action permanently removes selected rows and shifts the rows below upward?

  1. Applying a filter that excludes them
  2. Clearing the cell contents
  3. Hiding the rows
  4. Deleting the rows Correct answer.

Explanation

Deleting rows removes the row structures themselves and pulls the remaining rows up to close the gap. Hiding, clearing, and filtering all leave the row structures in place.

Question 27Data Entry, Formatting, and Worksheet Management

A user needs to copy cell formatting from one range to another without copying the values. Which tool is designed for this?

  1. Paste Special with All options
  2. Format Painter Correct answer.
  3. Fill Down
  4. Cut and Paste

Explanation

Format Painter copies only the formatting attributes of a source range to a destination. Cut and Paste, Fill Down, and a full Paste Special move or replicate values along with formatting.

Question 28Data Entry, Formatting, and Worksheet Management

Which alignment setting should be applied so that a long paragraph inside a single cell displays on multiple lines within that cell?

  1. Merge across columns
  2. Center horizontally
  3. Wrap Text Correct answer.
  4. Shrink to fit

Explanation

Wrap Text breaks the cell content into multiple lines inside the same cell and automatically adjusts row height. Centering, merging, and shrinking change position or size rather than wrapping the text.

Question 29Data Entry, Formatting, and Worksheet Management

A worksheet has a column of dates entered as text, so sorting places them alphabetically instead of chronologically. What is the most reliable fix?

  1. Apply bold formatting to the column before sorting
  2. Sort the text column using a custom list
  3. Re-enter or convert the values to real date serial numbers, then sort Correct answer.
  4. Widen the column so all dates are fully visible

Explanation

Real dates are stored as serial numbers, so chronological sorting requires numeric date values rather than text strings. Converting or re-entering the values as true dates restores correct ordering, while formatting or width changes cannot alter the underlying data type.

Question 30Data Entry, Formatting, and Worksheet Management

A user selects A1:C10 and applies a sort by column B. What is the most important prerequisite for the result to be meaningful?

  1. Column B must contain only numbers
  2. The range must be formatted as a table style first
  3. The selection must include a header row that is excluded from the sort
  4. Each row must represent one complete record so rows move together Correct answer.

Explanation

Sorting rearranges whole rows, so each row must hold one coherent record for the reordering to preserve relationships between fields. Header handling, data type, and table styling are helpful but secondary to record integrity.

Question 31Data Entry, Formatting, and Worksheet Management

A worksheet lists 500 orders and the user wants to display only orders where the Amount column exceeds 1000. Which approach is most appropriate?

  1. Sort the Amount column descending and read the top rows
  2. Apply conditional formatting highlighting amounts over 1000
  3. Apply an AutoFilter with a number filter of greater than 1000 Correct answer.
  4. Delete rows with amounts of 1000 or less

Explanation

An AutoFilter with a numeric criterion temporarily hides non-matching rows while leaving all data intact. Deleting rows destroys records, sorting alone does not hide anything, and conditional formatting only changes appearance.

Question 32Data Entry, Formatting, and Worksheet Management

A user pastes values from another source and several cells show a small green triangle in the corner. What does this indicator most likely represent?

  1. A protected cell that cannot be edited
  2. A cell with a comment attached
  3. A background error-checking warning about a possible inconsistency Correct answer.
  4. A formula error such as division by zero

Explanation

The green triangle is the error-checking indicator that flags potential problems such as numbers stored as text or inconsistent formulas. Errors, protection, and comments each use different visual cues.

Question 33Data Entry, Formatting, and Worksheet Management

A column of imported values shows left alignment and cannot be summed. What is the most likely cause?

  1. The cells are formatted as Currency
  2. The values are stored as text rather than numbers Correct answer.
  3. The column contains hidden rows
  4. The worksheet is set to manual calculation

Explanation

Text entries align left by default and are ignored by numeric functions such as SUM, which explains both symptoms. Currency formatting still stores numbers, hidden rows are still summed, and calculation mode does not change data types.

Question 34Data Entry, Formatting, and Worksheet Management

A user wants column widths to adjust automatically so that no entry is cut off. Which command accomplishes this?

  1. Format > Row Height with AutoFit
  2. Format > Column Width with a fixed value
  3. View > Zoom to 100 percent
  4. AutoFit Column Width Correct answer.

Explanation

AutoFit Column Width measures the longest entry in each selected column and resizes the column to display it fully. A fixed width, row height adjustment, or zoom change does not adapt to content length.

Question 35Data Entry, Formatting, and Worksheet Management

An operator needs to enter the same value into cells A1 through A50 quickly. Which method is most efficient?

  1. Type the value 50 times manually
  2. Merge A1:A50 and type the value once
  3. Copy A1 and paste it into each cell individually
  4. Type the value in A1, select A1:A50, and use Fill Down Correct answer.

Explanation

Entering the value once and using Fill Down replicates it across the whole selected range in one operation. Manual repetition is slow, and merging destroys the individual cells needed for later data work.

Question 36Data Entry, Formatting, and Worksheet Management

A user applies a red fill to cells that contain values below a target, but the underlying values must remain usable in formulas. Which feature satisfies this?

  1. Manual cell fill applied one cell at a time
  2. Paste Special with Formats only
  3. Conditional formatting with a cell-value rule Correct answer.
  4. Data validation with an input message

Explanation

Conditional formatting applies visual rules dynamically while leaving the stored values unchanged and fully usable in calculations. Manual fills do not update, Paste Special copies static formatting, and data validation restricts entry rather than coloring results.

Question 37Data Entry, Formatting, and Worksheet Management

A worksheet must accept only whole numbers between 1 and 12 in a month column. Which feature enforces this during entry?

  1. Conditional formatting with a highlight rule
  2. Protecting the sheet without a password
  3. Data validation with a whole-number between rule Correct answer.
  4. A custom number format of 00

Explanation

Data validation restricts what can be typed into a cell and can reject or warn about values outside the defined whole-number range. Conditional formatting only highlights, number formats only display, and protection without validation still allows any value.

Question 38Data Entry, Formatting, and Worksheet Management

After pasting a block of data, a user notices extra blank rows inserted between records. Which paste option best avoids carrying over unwanted structure from the source?

  1. Paste Values only Correct answer.
  2. Paste with Source Formatting
  3. Paste All
  4. Paste as Picture

Explanation

Pasting values only transfers the raw data and avoids importing source formatting or structural artifacts. The other options carry formatting or convert the data into a non-editable image.

Question 39Data Entry, Formatting, and Worksheet Management

A user hides rows 5 through 10, then selects the visible range A1:A12 and copies it. What happens to the hidden rows during the copy?

  1. The copy operation fails because of hidden rows
  2. Only visible cells are copied when using the visible-cells option Correct answer.
  3. Hidden rows are automatically deleted before copying
  4. Hidden rows are copied and pasted as visible data

Explanation

By default a copy includes hidden rows, so the user must explicitly select visible cells only to exclude them. The copy does not fail, and hidden rows are never deleted automatically.

Question 40Data Entry, Formatting, and Worksheet Management

A user wants each printed page to repeat the header row of a long table. Which setting is used?

  1. Set the header row to bold
  2. Insert a manual page break after row 1
  3. Freeze Panes at row 1
  4. Print Titles with rows to repeat at top Correct answer.

Explanation

Print Titles designates specific rows to repeat at the top of every printed page. Freeze Panes affects only the screen, bold formatting is cosmetic, and a page break does not repeat content.

Question 41Data Entry, Formatting, and Worksheet Management

A worksheet mixes currency, percentages, and dates in adjacent columns. Which formatting strategy best supports quick visual comparison?

  1. Convert every column to text so values align
  2. Apply the General format to all columns for consistency
  3. Apply a single custom format with two decimals to all columns
  4. Apply a format matched to each column's data type Correct answer.

Explanation

Matching the format to each column's data type makes currency, percentages, and dates immediately readable and preserves their numeric meaning. A single uniform format either misrepresents values or strips their semantics.

Question 42Data Entry, Formatting, and Worksheet Management

A user sorts a table by a column that contains both numbers stored as text and true numbers. What is the most likely outcome?

  1. Text-formatted numbers and true numbers sort in separate groups Correct answer.
  2. The sort is blocked until formats are unified
  3. All values sort in correct numeric order
  4. The text-formatted numbers are converted automatically

Explanation

Spreadsheets sort by data type first, so text-formatted numbers are grouped apart from true numbers rather than interleaved numerically. The sort still runs, and no automatic conversion occurs during sorting.

Question 43Data Entry, Formatting, and Worksheet Management

A user merges cells A1:D1 and later needs to sort the table by column C. Why does this cause a problem?

  1. Merged cells are excluded from filtering only
  2. Sorting automatically unmerges all cells in the workbook
  3. Merged cells cannot contain text longer than one line
  4. The merged range spans the sort key column and disrupts row alignment Correct answer.

Explanation

When a merged range covers part of the sort key column, rows can no longer be moved independently, so the sort cannot preserve record alignment. Spreadsheets typically warn or refuse, and the merge must be removed first.

Question 44Data Entry, Formatting, and Worksheet Management

A user needs to apply identical number formats, borders, and alignment to twenty non-adjacent ranges scattered across a sheet. Which method is most efficient and consistent?

  1. Rebuild each range's formatting manually from the Format Cells dialog
  2. Format Painter used once per range
  3. Select all ranges with Ctrl while holding the format, then apply settings once Correct answer.
  4. Copy the first range and paste it over each other range

Explanation

Multi-selecting non-adjacent ranges with Ctrl lets a single formatting command apply to all of them, guaranteeing consistency in one action. Repeated Format Painter use or manual rebuilding risks drift between ranges.

Question 45Data Entry, Formatting, and Worksheet Management

A worksheet shows a column of product codes where some codes appear right-aligned and others left-aligned. What does this mixed alignment most reliably indicate?

  1. The cells have different horizontal alignment settings
  2. The column width is too narrow for some entries
  3. The worksheet uses conditional formatting on the column
  4. The column contains a mix of numeric and text entries Correct answer.

Explanation

Default alignment follows data type: numbers align right and text aligns left, so mixed alignment signals mixed types. Explicit alignment settings, conditional formatting, and column width would not produce this pattern consistently.

Question 46Data Entry, Formatting, and Worksheet Management

A user wants the header row of a long table to stay visible while scrolling through rows on screen, without affecting printing. Which command should be used?

  1. Freeze Panes Correct answer.
  2. Zoom to Selection
  3. Page Break Preview
  4. Split

Explanation

Freeze Panes locks the chosen rows or columns in place so they remain visible while the rest of the sheet scrolls, and it has no effect on print output. It is purely a viewing aid applied to the active window. Splitting divides the window into panes that all scroll independently rather than keeping a fixed header.

Question 47Data Entry, Formatting, and Worksheet Management

While entering data, a user types 1/2 into a cell that had the General format and the entry is converted to a date. Which action keeps the cell as general and stores the text 1/2 exactly as typed?

  1. Set the cell alignment to Left
  2. Turn on Wrap Text for the cell
  3. Format the cell as Number with two decimals
  4. Format the cell as Text before typing the entry Correct answer.

Explanation

With the General format, an entry such as 1/2 is interpreted as a date and converted. Applying the Text format to the cell before entry forces the keystrokes to be stored as literal text rather than parsed. Changing number, alignment, or wrap settings does not prevent the initial interpretation.

Question 48Data Entry, Formatting, and Worksheet Management

A user needs to insert three blank rows between row 4 and row 5 of a worksheet that has formulas referencing cells below. Which method inserts the rows while letting the formulas adjust their references correctly?

  1. Delete row 5 three times and retype the data
  2. Press Enter three times at the end of row 4
  3. Type three blank rows into a spare area and paste them over row 5
  4. Select rows 5 through 7, then use Insert Sheet Rows Correct answer.

Explanation

Inserting whole rows through the Insert command shifts existing rows down and rewrites formula references so they continue to point at the intended cells. Pasting blank cells over data overwrites content instead of shifting it. Deleting rows removes data rather than making room.

Question 49Data Entry, Formatting, and Worksheet Management

A user selects the range B2:B20, which contains subtotal rows mixed with detail rows, and applies a fill color using the Format Cells dialog with a pattern. Later the user wants the color removed from the subtotal rows only. Which approach removes the fill while preserving all values and formulas?

  1. Select the subtotal cells and press Delete
  2. Select the subtotal cells and clear all formats for the entire sheet
  3. Copy the subtotal cells and paste them as values
  4. Select the subtotal cells and set their fill to No Color Correct answer.

Explanation

Fill color is a cell format, so setting the fill back to No Color removes only the shading and leaves values and formulas untouched. Delete clears cell contents, and pasting as values would destroy the formulas. Clearing formats across the whole sheet would strip unrelated formatting as well.

Question 50Data Entry, Formatting, and Worksheet Management

A workbook uses a custom number format of 0.00,;;"" on a column of measurements. A user reports that cells holding the value 0 appear blank while nonzero values display with two decimals. Which statement best explains this behavior?

  1. The format applies a conditional rule that deletes zero cells
  2. The format supplies separate sections for positive, negative, zero, and text values, and the zero section is empty so zeros display nothing Correct answer.
  3. The format converts zeros to text, which is why they disappear
  4. The format rounds every value to zero decimals and hides the result

Explanation

A custom number format can contain up to four sections separated by semicolons: positive, negative, zero, and text. When the zero section is left empty, cells containing exactly zero render with no characters even though the stored value is still 0. The underlying data is unchanged and still participates in calculations.