Conditional Formatting

Conditional Formatting Interview with follow-up questions

1. Can you explain what conditional formatting is in Excel and how it can be used?

Conditional formatting automatically applies visual formatting — colors, fonts, borders, icons, or data bars — to cells based on their values or on a formula you define. It updates dynamically whenever the underlying data changes.

Common use cases:

  • Highlight cells above or below a threshold: mark sales figures below target in red, above target in green.
  • Color scales: shade a range from red (low) to green (high) to create an instant heat map.
  • Data bars: display a proportional bar within each cell, giving a quick visual comparison across rows.
  • Icon sets: show traffic light icons, arrows, or star ratings based on value ranges.
  • Highlight duplicates or unique values: quickly spot repeated or one-off entries in a column.
  • Date-based rules: flag overdue deadlines, highlight items due this week, or mark dates in the past.
  • Text rules: highlight cells containing a specific word or matching a pattern.
  • Top/Bottom rules: automatically highlight the top 10 values, bottom 5%, or above-average entries.

Where to find it: Home tab > Styles group > Conditional Formatting.

Formula-based rules (most powerful): Using New Rule > Use a formula to determine which cells to format lets you apply formatting based on any logic — comparing columns, checking related cells, using AND/OR, etc. For example, to highlight entire rows where column C is blank: select A2:E100, add a rule with formula =$C2="".

Managing rules: Home > Conditional Formatting > Manage Rules shows all rules on the sheet, lets you edit, delete, or reorder them. Rules are evaluated top to bottom — the Stop If True checkbox stops further rules from applying once a match is found.

↑ Back to top

Follow-up 1

What are some examples of when you might use conditional formatting?

There are several examples of when you might use conditional formatting in Excel:

  1. Highlighting cells that are above or below a certain threshold, such as sales figures that are below a target.

  2. Identifying duplicate values in a column or range of cells.

  3. Applying color scales to visualize the distribution of data.

  4. Creating data bars to compare the magnitude of values.

  5. Using icon sets to represent different levels of performance or progress.

These are just a few examples, and the possibilities are endless depending on your specific needs and data analysis requirements.

Follow-up 2

What are the steps to apply conditional formatting to a range of cells?

To apply conditional formatting to a range of cells in Excel, follow these steps:

  1. Select the range of cells that you want to apply conditional formatting to.

  2. Go to the 'Home' tab in the Excel ribbon.

  3. Click on the 'Conditional Formatting' button in the 'Styles' group.

  4. Choose the desired formatting option from the dropdown menu, such as 'Highlight Cells Rules' or 'Top/Bottom Rules'.

  5. Select the specific rule or criteria that you want to apply.

  6. Customize the formatting options, such as the colors, icons, or data bars.

  7. Click 'OK' to apply the conditional formatting to the selected range of cells.

Note: The steps may vary slightly depending on the version of Excel you are using.

Follow-up 3

How can you remove conditional formatting from a worksheet?

To remove conditional formatting from a worksheet in Excel, follow these steps:

  1. Select the range of cells that have conditional formatting applied.

  2. Go to the 'Home' tab in the Excel ribbon.

  3. Click on the 'Conditional Formatting' button in the 'Styles' group.

  4. Choose the 'Clear Rules' option from the dropdown menu.

  5. Select the specific type of conditional formatting you want to remove, such as 'Clear Rules from Entire Sheet' or 'Clear Rules from Selected Cells'.

  6. Click 'OK' to remove the conditional formatting from the selected range of cells.

Note: This will remove all conditional formatting rules applied to the selected range of cells.

Follow-up 4

Can you apply multiple conditional formatting rules to the same set of cells?

Yes, you can apply multiple conditional formatting rules to the same set of cells in Excel. When applying multiple rules, Excel evaluates each rule in the order they are listed, and applies the formatting of the first rule that meets the specified criteria. This allows you to create complex conditional formatting scenarios by combining different rules and criteria. For example, you can highlight cells that are both above a certain threshold and contain specific text. To add multiple rules, simply repeat the steps for applying conditional formatting and customize the criteria and formatting options for each rule.

2. How can you use conditional formatting to highlight cells that contain specific text?

To highlight cells that contain specific text using conditional formatting:

Method 1 — Built-in "Text that Contains" rule (quickest):

  1. Select the range you want to format (e.g., A2:A100).
  2. Go to Home tab > Conditional Formatting > Highlight Cells Rules > Text that Contains…
  3. Type the text you want to match (e.g., "Pending").
  4. Choose a formatting style from the dropdown (or click Custom Format for full control).
  5. Click OK.

Excel highlights all cells in the selection that contain that text anywhere within the cell value (it is a partial match — "Pending review" would match "Pending").

Method 2 — Custom formula rule (more control):

  1. Select the range.
  2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter a formula such as:
=ISNUMBER(SEARCH("Pending", A2))

SEARCH is case-insensitive. Use FIND instead if you need case-sensitive matching.

  1. Click Format, choose the fill/font/border, then click OK twice.

Why use the formula approach?

  • You can match against a value in another cell rather than a hard-coded string: =ISNUMBER(SEARCH($D$1, A2)) — change D1 to update all highlighted results instantly.
  • You can combine conditions: =AND(ISNUMBER(SEARCH("Pending",A2)), B2>TODAY()).
  • You can apply the format to an entire row by selecting multiple columns before creating the rule.
↑ Back to top

Follow-up 1

What if you want to highlight cells that do not contain a specific text?

If you want to highlight cells that do not contain a specific text, you can use the 'Text that Does Not Contain' option in the conditional formatting dialog box. Here are the steps:

  1. Select the range of cells you want to apply the conditional formatting to.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button.
  4. Choose 'Highlight Cells Rules' and then 'Text that Does Not Contain'.
  5. In the dialog box that appears, enter the specific text you do not want to highlight.
  6. Select the formatting style you want to apply to the cells that do not contain the specific text.
  7. Click 'OK' to apply the conditional formatting.

Now, any cells in the selected range that do not contain the specific text will be highlighted according to the formatting style you chose.

Follow-up 2

Can you use wildcards when setting up this type of conditional formatting?

Yes, you can use wildcards when setting up conditional formatting to highlight cells that contain specific text. Wildcards are special characters that represent unknown or variable characters. The two most commonly used wildcards in Excel are the asterisk (*) and the question mark (?).

  • The asterisk (*) represents any number of characters.
  • The question mark (?) represents a single character.

For example, if you want to highlight cells that contain any text starting with 'apple', you can use the wildcard 'apple*' in the conditional formatting dialog box.

Note: Wildcards can only be used with certain types of conditional formatting rules, such as 'Text that Contains' or 'Text that Does Not Contain'.

Follow-up 3

How would you change the formatting applied to these cells?

To change the formatting applied to cells with conditional formatting, you can modify the existing conditional formatting rule or create a new one. Here are the steps:

  1. Select the range of cells with the conditional formatting you want to change.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button.
  4. Choose 'Manage Rules' to open the 'Conditional Formatting Rules Manager' dialog box.
  5. In the 'Conditional Formatting Rules Manager', select the rule you want to modify or create a new rule.
  6. Click on the 'Edit Rule' button to modify the selected rule or click on the 'New Rule' button to create a new rule.
  7. In the 'Edit Formatting Rule' or 'New Formatting Rule' dialog box, make the desired changes to the formatting.
  8. Click 'OK' to apply the changes to the conditional formatting rule.

Now, the formatting applied to the cells with conditional formatting will be updated according to the changes you made.

3. Can you describe how to use conditional formatting to highlight duplicate or unique values in a range?

Excel has a built-in conditional formatting rule specifically for identifying duplicate or unique values.

Steps:

  1. Select the range you want to check (e.g., A2:A100).
  2. Go to Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
  3. In the dialog, use the first dropdown to choose either Duplicate or Unique.
  4. Choose a formatting style (or select Custom Format for a specific color/font).
  5. Click OK.

All duplicate values in the range are immediately highlighted. Choosing "Unique" highlights values that appear only once.

How Excel defines "duplicate" here: Any value that appears more than once in the selected range is treated as a duplicate — both the first occurrence and all subsequent occurrences are highlighted. This differs from some definitions where only the repeated occurrences (not the original) are flagged.

Formula-based approach for more control:

If you need to highlight only the second and subsequent occurrences of a duplicate (not the first), use a custom formula rule:

  1. Select the range (e.g., A2:A100).
  2. Home > Conditional Formatting > New Rule > Use a formula.
  3. Formula: =COUNTIF($A$2:A2, A2)>1

This uses an expanding range — at row 5 it becomes COUNTIF($A$2:A5, A5)>1 — so it only flags a value after it has already appeared above.

Finding duplicates across two columns:

To highlight values in column A that also appear in column B:

=COUNTIF($B$2:$B$100, A2)>0

Apply this formula rule to the A column range.

↑ Back to top

Follow-up 1

What happens if new data is added to the range after the conditional formatting rule is set up?

If new data is added to the range after the conditional formatting rule is set up, the conditional formatting will automatically adjust to include the new data. The formatting will be applied to any new duplicate or unique values that are added to the range.

Follow-up 2

How can you modify the rule to highlight unique values instead?

To modify the rule to highlight unique values instead of duplicate values, follow these steps:

  1. Select the range of cells where the conditional formatting rule is applied.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Choose 'Manage Rules'.
  5. In the 'Conditional Formatting Rules Manager' dialog box, select the rule that you want to modify.
  6. Click on the 'Edit Rule' button.
  7. In the 'Edit Formatting Rule' dialog box, change the rule type from 'Duplicate Values' to 'Unique Values'.
  8. Choose the formatting options you want to apply to the unique values.
  9. Click 'OK' to apply the modified rule.

This will update the conditional formatting rule to highlight unique values instead of duplicate values.

Follow-up 3

Can you apply this rule to multiple columns at once?

Yes, you can apply the conditional formatting rule to multiple columns at once. To do this, follow these steps:

  1. Select the range of cells that you want to apply the conditional formatting to, including all the columns you want to include.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Choose 'Highlight Cells Rules' and then select either 'Duplicate Values' or 'Unique Values'.
  5. In the dialog box that appears, choose the formatting options you want to apply to the duplicate or unique values.
  6. Click 'OK' to apply the conditional formatting.

This will apply the conditional formatting rule to all the selected columns in the range.

4. How can you use conditional formatting with dates in Excel?

Conditional formatting with dates lets you visually flag deadlines, overdue items, upcoming events, and date ranges — and it recalculates automatically each day because functions like TODAY() are dynamic.

Method 1 — Built-in date rules (quick):

  1. Select the date range (e.g., C2:C100).
  2. Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring…
  3. Choose from preset options: Yesterday, Today, Tomorrow, Last 7 days, Last week, This week, Next week, Last month, This month, Next month.
  4. Choose a format and click OK.

Method 2 — Custom formula rules (flexible, date-math powered):

Select the range, then Home > Conditional Formatting > New Rule > Use a formula.

Common formula examples:

Goal Formula
Highlight overdue dates (past today) =C2=TODAY(), C2<=TODAY()+7)
Highlight this month =MONTH(C2)=MONTH(TODAY())
Highlight weekends =WEEKDAY(C2,2)>=6
Highlight dates more than 30 days old =TODAY()-C2>30

Highlighting entire rows based on a date column:

Select the full data range (e.g., A2:E100), then use a formula that references the date column with a locked column: `=$C2

↑ Back to top

Follow-up 1

How would you set up a rule to highlight dates that are in the past?

To set up a rule to highlight dates that are in the past, you can follow these steps:

  1. Select the range of cells that you want to apply the conditional formatting to.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Choose the 'Highlight Cell Rules' option from the dropdown menu.
  5. Select the 'Less Than' rule.
  6. Enter the formula '=TODAY()' in the 'Format cells that are LESS THAN:' field.
  7. Choose the formatting style or color that you want to apply to the cells that meet the rule.
  8. Click 'OK' to apply the conditional formatting rule to the selected cells.

This rule will highlight any dates that are earlier than the current date.

Follow-up 2

What about dates that are within the next 30 days?

To set up a rule to highlight dates that are within the next 30 days, you can follow these steps:

  1. Select the range of cells that you want to apply the conditional formatting to.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Choose the 'Highlight Cell Rules' option from the dropdown menu.
  5. Select the 'Between' rule.
  6. Enter the formula '=TODAY()' in the 'Format cells that are BETWEEN:' field.
  7. Enter the formula '=TODAY()+30' in the 'AND' field.
  8. Choose the formatting style or color that you want to apply to the cells that meet the rule.
  9. Click 'OK' to apply the conditional formatting rule to the selected cells.

This rule will highlight any dates that are within the next 30 days, including the current date.

Follow-up 3

Can you use conditional formatting to highlight entire rows based on a date in one of the cells?

Yes, you can use conditional formatting to highlight entire rows based on a date in one of the cells. Here's how:

  1. Select the range of cells that you want to apply the conditional formatting to, including the column with the dates.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Choose the 'New Rule' option from the dropdown menu.
  5. Select the 'Use a formula to determine which cells to format' rule.
  6. Enter the formula that references the cell with the date, such as '=A1

5. Can you explain how to use the 'Format cells that are GREATER THAN' rule in conditional formatting?

The "Greater Than" rule in conditional formatting highlights all cells in a range whose value exceeds a number you specify.

Steps:

  1. Select the range you want to format (e.g., B2:B50).
  2. Go to Home tab > Conditional Formatting > Highlight Cells Rules > Greater Than…
  3. In the dialog box, enter the threshold value in the field on the left. You can type a literal number (e.g., 1000) or enter a cell reference (e.g., =$D$1) to pull the threshold from another cell — useful when the threshold changes frequently.
  4. Choose a formatting style from the dropdown on the right (yellow fill, red text, green fill, etc.), or click Custom Format… to define your own.
  5. Click OK.

All cells in the selection whose value is strictly greater than the threshold are immediately formatted.

Using a cell reference as the threshold:

Entering =$D$1 as the value means the rule always compares against whatever is in D1. Change D1 and all highlights update instantly — no need to re-edit the formatting rule.

Equivalent custom formula approach:

For the same effect with more flexibility (e.g., comparing against a calculation rather than a single cell), use New Rule > Use a formula:

=B2>$D$1

This approach also lets you apply the formatting to an entire row, not just the column with the values.

"Greater Than or Equal To": The built-in rule is strictly greater than (>). If you need >=, use the custom formula method: =B2>=$D$1.

Stacking rules: Multiple rules can be active on the same range. For example, combine a "Greater Than 1000" green rule with a "Less Than 500" red rule to create a three-zone color signal on a single column.

↑ Back to top

Follow-up 1

What other similar rules are available?

There are several other similar rules available in conditional formatting, including:

  • 'Format cells that are LESS THAN': This rule formats cells that are less than a specified value.
  • 'Format cells that are BETWEEN': This rule formats cells that are between two specified values.
  • 'Format cells that are EQUAL TO': This rule formats cells that are equal to a specified value.
  • 'Format cells that CONTAIN': This rule formats cells that contain a specified text or value.
  • 'Format cells that BEGIN WITH': This rule formats cells that begin with a specified text or value.
  • 'Format cells that END WITH': This rule formats cells that end with a specified text or value.

These rules can be accessed through the 'Highlight Cells Rules' option in the 'Conditional Formatting' menu.

Follow-up 2

Can you set up a rule to format cells that are less than a certain value?

Yes, you can set up a rule to format cells that are less than a certain value using the 'Format cells that are LESS THAN' rule in conditional formatting. Here's how:

  1. Select the range of cells you want to apply the rule to.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Select 'Highlight Cells Rules' and then choose 'Less Than'.
  5. In the 'Less Than' dialog box, enter the value that you want to compare the cells to.
  6. Choose the formatting style you want to apply to the cells that meet the condition.
  7. Click 'OK' to apply the rule.

This rule will format any cell in the selected range that is less than the specified value.

Follow-up 3

How would you modify the rule to format cells that are equal to a certain value?

To modify the rule to format cells that are equal to a certain value, you can use the 'Format cells that are EQUAL TO' rule in conditional formatting. Here's how:

  1. Select the range of cells you want to apply the rule to.
  2. Go to the 'Home' tab in the Excel ribbon.
  3. Click on the 'Conditional Formatting' button in the 'Styles' group.
  4. Select 'Highlight Cells Rules' and then choose 'Equal To'.
  5. In the 'Equal To' dialog box, enter the value that you want to compare the cells to.
  6. Choose the formatting style you want to apply to the cells that meet the condition.
  7. Click 'OK' to apply the rule.

This rule will format any cell in the selected range that is equal to the specified value.

Live mock interview

Mock interview: Conditional Formatting

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.