Part 7: Excel Conditional Formatting & Data Validation Quiz (20 MCQ)

Correct answer: B. To automatically change cell formatting based on rules and values

★ Key Takeaway: Conditional Formatting highlights important information automatically so you can spot patterns and issues faster.

Explanation: Conditional Formatting applies visual styles (like fill color, font color, or icons) when a rule is true. For example, you can highlight overdue dates in red or mark top-performing sales numbers in green without changing the underlying data.

Why other options are incorrect:

• A. To prevent users from entering invalid data – That describes Data Validation, which controls what users are allowed to type into a cell.
• C. To convert text into numbers – Converting text to numbers is a data-cleaning task, usually handled with formatting, Text to Columns, or functions like VALUE.
• D. To hide rows that do not meet a condition – Hiding rows is typically done with filtering, not Conditional Formatting.

Tip: Conditional Formatting is one of the fastest ways to make reports look professional. If rules feel confusing, a 1-on-1 Excel tutor can walk you through real examples step by step. Learn Excel faster with a 1-on-1 tutor on Fiverr →

Correct answer: D. Use Conditional Formatting with a formula rule, applied to the whole table

★ Key Takeaway: Use a formula-based Conditional Formatting rule when you need to check multiple conditions at once.

Explanation: “More than 30 days overdue” means the due date is earlier than TODAY()-30, and “still unpaid” means Status must equal "Unpaid". A common rule is: =AND($E2="Unpaid",$D2<TODAY()-30) Apply it to your full range (for example, A2:G200) so the entire row highlights when both conditions are true.

Why other options are incorrect:

• A. Use Highlight Cell Rules: A Date Occurring (Before Today) on column D – This would highlight all past dates, including invoices that are only 1–2 days late and even invoices that are already Paid.
• B. Use Data Validation (Date) to block dates older than 30 days – Data Validation controls what users are allowed to enter; it does not automatically highlight overdue invoices already in the sheet.
• C. Use Data Bars on column D to show which dates are older – Data bars visualize numeric size and are not designed to flag overdue logic like “older than 30 days” plus a status check.

Correct answer: A. List

★ Key Takeaway: Use Data Validation (List) to standardize entries and reduce typing mistakes.

Explanation: The List validation lets users pick from approved values instead of typing. This keeps data consistent, which is crucial for clean filters, PivotTables, and accurate reporting.

Why other options are incorrect:

• B. Whole number – This restricts the cell to integers, which does not create a text drop-down list.
• C. Date – This limits entries to valid dates (and optional date ranges), not a set of status labels.
• D. Text length – This controls the number of characters, not the allowed words or choices.

Correct answer: D. Highlight Cell Rules: Duplicate Values

★ Key Takeaway: Duplicate Values formatting is the quickest way to spot repeated entries in a list.

Explanation: The built-in Duplicate Values rule highlights duplicates (or uniques) instantly. This is especially useful for catching repeated emails, invoice numbers, or product codes before you send reports or import data elsewhere.

Why other options are incorrect:

• A. New Rule: Format only cells that contain blanks – This targets empty cells, not duplicates.
• B. Top/Bottom Rules: Bottom 10 Percent – This highlights low values, which is unrelated to duplicates.
• C. Color Scales – Color scales show relative high-to-low values, not repeated items.

Correct answer: B. To control what users can enter into a cell

★ Key Takeaway: Data Validation prevents bad data before it enters your spreadsheet.

Explanation: Data Validation sets rules like “Only whole numbers from 1 to 100” or “Only select from this list.” It is a professional way to reduce data-entry mistakes in shared templates and forms.

Why other options are incorrect:

• A. To color cells automatically based on rules – That is Conditional Formatting, which changes appearance but does not block entries.
• C. To summarize data into totals and counts – Summarizing is done with PivotTables or formulas like SUMIF(S) and COUNTIF(S).
• D. To automatically remove duplicates from a range – Removing duplicates is a data-cleaning action, not a validation rule.

Correct answer: C. Highlight Cell Rules: Greater Than

★ Key Takeaway: Use “Greater Than” to flag values that exceed a clear threshold.

Explanation: The Greater Than rule highlights any cell above a number you provide, making it easy to spot high sales, over-budget costs, or unusual values in large lists.

Why other options are incorrect:

• A. Highlight Cell Rules: Text that Contains – This searches for text patterns, not numeric thresholds like 10000.
• B. Data Validation: Whole number – Validation restricts allowed entries; it does not highlight existing values.
• D. Remove Duplicates – This deletes repeated entries; it does not highlight values above a threshold.

Correct answer: D. Use Conditional Formatting with a formula that references column E

★ Key Takeaway: Formula-based Conditional Formatting can format entire rows based on one cell’s value.

Explanation: You can apply a rule to the whole table (for example, A2:G200) using a formula like =$E2="Overdue". Excel checks the status in column E for each row and highlights the full row when the condition is met.

Why other options are incorrect:

• A. Use Data Validation (List) on column E – Validation helps users choose “Overdue” correctly, but it does not highlight the row.
• B. Use Color Scales on column E – Color scales are designed for numeric ranges, not text values like “Overdue.”
• C. Use Duplicate Values on column E – This finds repeated statuses, not the specific status “Overdue.”

Correct answer: B. To show a helpful note when the user selects the cell

★ Key Takeaway: Input Messages guide users before they make a mistake.

Explanation: An Input Message appears when the cell is selected, like “Enter a date in YYYY-MM-DD format” or “Choose a status from the list.” This improves data quality in shared templates because users get instructions right at the point of entry.

Why other options are incorrect:

• A. To change the cell color when a rule is broken – Color changes are handled by Conditional Formatting, not Data Validation messages.
• C. To remove invalid entries automatically – Data Validation blocks or warns; it does not automatically clean up invalid data already entered.
• D. To refresh PivotTables after data entry – Refreshing PivotTables is a separate action and not related to Data Validation.

Correct answer: C. Whole number (between 1 and 31)

★ Key Takeaway: Whole number validation is the simplest way to restrict integer ranges like days of a month.

Explanation: Whole number validation lets you set a clear numeric range, so users cannot type 0, 32, or text. This prevents small entry errors that can break schedules, formulas, or reporting.

Why other options are incorrect:

• A. List – A list drop-down could work, but it is slower to build and maintain than a simple numeric range rule.
• B. Custom – Custom validation is powerful, but it is not necessary for this straightforward requirement.
• D. Text length – Text length only controls characters, so “aa” could still be entered if it meets the length limit.

Tip: Data Validation makes Excel templates feel like real apps. If you want to build professional forms with validation rules, a tutor can help you design them cleanly and correctly. Get personalized Excel help from a Fiverr expert →

Correct answer: D. Icon Sets

★ Key Takeaway: Icon Sets turn numbers into quick visual signals, which helps people scan performance faster.

Explanation: Icon Sets can display arrows, flags, or traffic lights based on thresholds. For example, you can show a green up arrow for sales above target, a yellow symbol for near target, and a red down arrow for below target.

Why other options are incorrect:

• A. Color Scales – Color scales shade cells along a gradient but do not use icons like arrows or lights.
• B. Data Bars – Data bars add horizontal bars inside cells to show magnitude, not icons.
• C. Top/Bottom Rules – These highlight highest or lowest items, but they do not display icon-based indicators.

Correct answer: A. Date (between 01/01/2026 and 12/31/2026)

★ Key Takeaway: Date validation is the cleanest way to restrict entries to a specific time period.

Explanation: Using Date validation ensures users enter real dates and keeps them within your allowed range. This is useful for project schedules, attendance logs, and reporting periods where out-of-range dates can cause serious confusion.

Why other options are incorrect:

• B. List – A list would require you to list every date in 2026, which is not practical.
• C. Decimal – Decimal validation checks numeric values, not valid calendar dates.
• D. Text length – Text length does not guarantee the entry is a real date or within a year range.

Correct answer: B. It shows a warning or blocks entry when the user inputs invalid data

★ Key Takeaway: Error Alerts stop mistakes at the moment of data entry.

Explanation: Data Validation can show different alert styles, such as Stop (blocks invalid entry), Warning (allows but warns), or Information (gentle message). This is a professional way to enforce rules in shared sheets.

Why other options are incorrect:

• A. It changes the chart type when data is invalid – Data Validation does not control charts; it controls data entry rules.
• C. It highlights duplicate values automatically – Duplicate highlighting is a Conditional Formatting feature, not a validation alert.
• D. It converts invalid text into numbers – Excel does not automatically convert invalid entries through validation; it blocks or warns.

Correct answer: D. The paste action may overwrite or bypass the Data Validation rule, so invalid values can slip in unless you paste carefully and re-check

★ Key Takeaway: Data Validation is strong for controlled entry, but copying and pasting can introduce invalid data if you are not careful.

Explanation: When users paste data from another source, they may paste values in a way that bypasses the validation check or even replace the cell’s validation settings (especially if they paste with formatting). In real workflows, it’s smart to use paste methods that preserve validation and then verify the column using tools like “Circle Invalid Data.”

Why other options are incorrect:

• A. Conditional Formatting stopped working after paste – Conditional Formatting only changes how cells look; it does not control whether entries are allowed.
• B. Data Validation works only on numbers, not text – Data Validation supports lists, text length, custom formulas, dates, and more, not just numbers.
• C. Validation rules automatically convert invalid values into blanks – Data Validation does not auto-correct entries; it warns or blocks, depending on the Error Alert settings.

Correct answer: B. Top/Bottom Rules: Top 10 Items (set it to 5)

★ Key Takeaway: Top/Bottom rules help you spotlight best and worst performers instantly.

Explanation: The Top 10 Items rule can be changed to any number, including 5. This is useful when you want to quickly find top deals, highest commissions, or best-selling products without sorting the data.

Why other options are incorrect:

• A. Highlight Cell Rules: Between – “Between” needs a numeric range, but “top 5” depends on the dataset and changes as data changes.
• C. Duplicate Values – Duplicate values highlights repeated entries, not the highest values.
• D. New Rule: Format only cells with blanks – This targets empty cells, not top performance values.

Correct answer: C. New Rule: Format only cells that contain blanks

★ Key Takeaway: Blank-cell rules make missing required information visible at a glance.

Explanation: A “blanks” Conditional Formatting rule highlights empty cells so reviewers can quickly see what needs attention. This is practical for audit checklists, customer support logs, or any workflow where missing notes create confusion later.

Why other options are incorrect:

• A. Top/Bottom Rules: Bottom 10 Items – This targets low numeric values, not empty cells.
• B. Color Scales – Color scales apply gradients to numeric values and are not designed for highlighting blanks.
• D. Data Validation: List – A list controls what is entered; it does not automatically highlight missing entries.

Correct answer: D. Conditional Formatting Rules Manager

★ Key Takeaway: Rules Manager is where you edit, reorder, and troubleshoot Conditional Formatting rules.

Explanation: The Rules Manager shows all rules affecting a selection, the order they run, and the “Applies to” range. This is essential when multiple rules conflict, because rule order can change what you see on the sheet.

Why other options are incorrect:

• A. Text to Columns – This splits text into separate columns and does not manage formatting rules.
• B. Name Manager – This manages named ranges and named formulas, not Conditional Formatting order.
• C. Format Painter – Format Painter copies formatting but does not control rule priority or logic.

Correct answer: A. Decimal (between 0 and 1)

★ Key Takeaway: Use Decimal validation when you need numeric ranges that include fractions.

Explanation: A discount rate like 0.15 or 0.2 requires decimals. Decimal validation ensures entries stay between 0 and 1, preventing unrealistic values like 2 (200%) or negative discounts.

Why other options are incorrect:

• B. Whole number (between 0 and 1) – Whole number validation allows only integers, so it would block valid entries like 0.15.
• C. List – A list could restrict choices, but it is not ideal when you want flexible decimal inputs within a range.
• D. Text length – Text length does not confirm that the value is numeric or within a numeric range.

Correct answer: B. It prevents lower-priority rules from applying when the current rule is true

★ Key Takeaway: Stop If True helps you control rule conflicts by enforcing a clear priority order.

Explanation: If multiple rules apply to the same cell, rule order matters. “Stop If True” tells Excel: if this rule matches, do not continue applying rules below it. This is useful when one condition should override others, such as “Overdue” overriding a normal color scale.

Why other options are incorrect:

• A. It deletes the rule if it matches – The rule remains; it only affects whether additional rules are applied afterward.
• C. It blocks users from entering invalid values – Blocking invalid entries is the job of Data Validation, not Conditional Formatting.
• D. It sorts the data automatically when the rule is true – Conditional Formatting changes appearance; it does not sort data automatically.

Correct answer: C. Custom (using a formula)

★ Key Takeaway: Custom validation gives you the most control when standard rules are not enough.

Explanation: Custom validation lets you use a logical formula such as =AND(A1<>"",A1>0) (adjusted for your cell). This is common in professional templates where blank or zero values would break calculations or reporting.

Why other options are incorrect:

• A. List – A list restricts entries to specific items, but it does not naturally enforce “greater than 0” unless you list every valid number.
• B. Text length – Text length checks characters, not numeric meaning like “greater than 0.”
• D. Color Scales – Color scales are a Conditional Formatting style and do not restrict user entry.

Correct answer: A. Use Conditional Formatting: Clear Rules from Selected Cells

★ Key Takeaway: Clearing formatting rules removes highlights and icons but keeps your actual values untouched.

Explanation: “Clear Rules” removes the Conditional Formatting logic applied to the selection, returning cells to normal formatting (or whatever base formatting you already had). This is useful when you inherit a messy file and want to reset visual rules cleanly.

Why other options are incorrect:

• B. Use Remove Duplicates – This deletes repeated values from the data; it does not remove formatting rules.
• C. Use Text to Columns – This splits text into columns and does not affect Conditional Formatting rules.
• D. Use Data Validation: Clear All – Clearing validation removes entry restrictions, but it does not remove Conditional Formatting rules.