Advanced filtering and conditional formatting

Question

Advanced filtering and conditional formatting

Joseph Horling 5

Hi,

I am working on a project that involves advanced filtering and conditional formatting. So far I have made two columns for conditional formatting when I apply the criteria is applied. When I do the advanced filtering both columns are highlighted. The advance filtering is working, but I only want the column or columns that I put the criteria in to be highlighted. Is there a way to do this? I appreciate your help. Joe from Michigan. Can you use this email address: <PII removed>@comcast.net thank you.

I could not add the file but did add an image

0 comments

2 answers

Your answer

Answer 1

Thomas4-N 22,380 Microsoft External Staff Moderator

Hello Joseph Horling,

I think the missing piece is that Advanced Filter selects the rows, but it does not tell conditional formatting which criteria columns you filled in. Giving each column its own formatting rule is a start, but each rule also needs to check whether that column’s criterion cell contains a value. Otherwise, both columns can still highlight.

You mentioned adding an image, but I can’t see it in the thread. Could you try attaching it again? If it still doesn’t appear, could you share the cell addresses of your criteria and the formulas shown under Home > Conditional Formatting > Manage Rules? With those, we can work out the exact rule for each column without changing your working filter.

Joseph Horling 5 Reputation points

2026-09-28T18:42:07.7166667+00:00

Hi Thomas4-N,

Thanks for responding. I could not upload the file. Here is an example of the formula I used: Specific Text: containing: $B$8 (the criteria) applies to: $B$13:$B$10. I did it to the other columns like that too. I also have columns that contains numbers but not quite sure how to conditional format that area. Anyway, if we can do the text part that would be great. Hope that makes sense. Thanks again
Thomas4-N 22,380 Reputation points Microsoft External Staff Moderator

2026-09-30T07:32:45.0733333+00:00

Hello Joseph Horling,

Thanks for the follow-up. If I understand correctly, B8 contains the text you use to filter column B. You want matching cells in column B highlighted when B8 has a value, but you do not want another text column highlighted when its own criteria cell is empty. Is that the behavior you’re aiming for?

If so, you could test a formula-based conditional formatting rule for column B. If B13 is the first cell in the data range you want to format, use:

=AND($B$8<>"",ISNUMBER(SEARCH($B$8,B13)))

The first part, $B$8<>"", keeps the rule off when B8 is empty. The second part checks whether the text in B8 appears in B13. Apply the rule only to your column B data range.

For another text column, its rule would need to refer to that column’s own criteria cell and first data cell. I haven’t seen those addresses, so I don’t want to guess them.

This is just a suggested test, so if I’ve misunderstood the situation, feel free to correct me.
Joseph Horling 5 Reputation points

2026-10-01T00:18:58.9033333+00:00

Hi Thomas,

That worked! One more question if I may. What if I have a column of numbers instead of text. Do I use a different formula for that?
Thomas4-N 22,380 Reputation points Microsoft External Staff Moderator

2026-10-01T14:06:25.7966667+00:00

Hello Joseph Horling,

I'm glad the text rule worked.

For the number column, do you want to highlight numbers that equal the number in its criteria cell, or are you using a condition such as “greater than” or “contains these digits”?

If you mean an exact match, the rule would compare each number with that column’s criteria cell. For example, if the criterion is in C8 and the data starts in C13, apply this rule to the column C data range:

Excel =AND(ISNUMBER($C$8),ISNUMBER(C13),C13=$C$8) C8 and C13 are examples, so replace them with your actual cells. The ISNUMBER checks keep blank cells from being highlighted, including when the criterion is zero.

If you mean a different kind of number match, let me know which one and I can adjust the rule.
Joseph Horling 5 Reputation points

2026-10-01T21:35:23.5266667+00:00

Hi Thomas,

The equal the number in the criteria cell works, but I would like to have a formula using the condition greater than or less than highlighted. Thanks,
Thomas4-N 22,380 Reputation points Microsoft External Staff Moderator

2026-10-02T14:54:37.4766667+00:00

Hello Joseph Horling,

I'm glad the equal-number rule worked. If your criterion cell contains a plain number, you can keep the same rule and change only the comparison.

Using the same example, where C8 holds the criterion and C13 is the first number to highlight:

Greater** than **C8

=AND(ISNUMBER($C$8),ISNUMBER(C13),C13>$C$8)

Less** than **C8

=AND(ISNUMBER($C$8),ISNUMBER(C13),C13<$C$8)

Apply the rule to your number column, and replace C8 and C13 with your actual cells. The ISNUMBER checks prevent blank or non-number cells from being highlighted.

If you’re entering something like >50 in the criterion cell rather than just 50, let me know, since that needs a different formula.
Joseph Horling 5 Reputation points

2026-10-02T18:09:11.0933333+00:00

Yes need >50 formula. But I am keeping all your formulas for reference. Thank you
Thomas4-N 22,380 Reputation points Microsoft External Staff Moderator

2026-10-03T14:30:52.7033333+00:00

Hello Joseph Horling,

That needs a different rule because >50 is text, not a plain number.

Using our example where the criterion is in C8 and the data starts at C13, try this conditional-formatting formula for values greater than the number in C8:

=IFERROR(AND(ISNUMBER(C13),LEFT($C$8,1)=">",C13>VALUE(MID($C$8,2,99))),FALSE)

Apply it only to your numeric data column. If you enter <50 instead, change both > signs in the formula to . Replace C8 and C13 with your actual cells if they differ.

Let me know if you need anything else.
Joseph Horling 5 Reputation points

2026-10-05T12:48:00.2066667+00:00

Hi Thomas,

Sweet the formula worked for both < and >. How do I apply these formulas if I need to do an "OR" criteria? THanks, Joe.
Thomas4-N 22,380 Reputation points Microsoft External Staff Moderator

2026-10-08T08:35:37.4133333+00:00
Hello Joseph Horling,

If I understand this correctly, you now want an OR, but I’m not quite sure which kind you mean:

two conditions in one criterion cell, like >50 or <10

two separate criterion cells for the same column

highlight if any of several columns match

If you mean the first case, you can wrap the working tests in OR. Using the same example as before, C13 is the first data cell, C8 is one criterion, and D8 is the second:

=OR( IFERROR(AND(ISNUMBER(C13),LEFT($C$8,1)=">",C13>VALUE(MID($C$8,2,99))),FALSE), IFERROR(AND(ISNUMBER(C13),LEFT($D$8,1)=">",C13>VALUE(MID($D$8,2,99))),FALSE) )

Apply that only to the number column, and replace C8, D8, and C13 with your actual cells. The IFERROR / ISNUMBER parts are there so blank cells don’t get highlighted.

If your OR is something else, like both > and < in the same cell, or two different columns, tell me which layout you’re using and I can adjust it.
Joseph Horling 5 Reputation points

2026-10-08T14:43:00.2333333+00:00

Thomas,

I am looking at two different rows. Like B8 for one criteria and C9 for the other criteria. Is that an AND? Thanks, Joe

Answer 2

Yes. Standard Excel filtering and Advanced Filter are separate from conditional formatting, so if the conditional formatting rule applies to both columns, both columns will stay highlighted after the filter is applied.

To highlight only the column or columns tied to the criteria, set up separate conditional formatting rules and limit each rule’s Applies to range to only that specific column.

Steps:

Select the first column that should be highlighted.
Go to Home > Conditional Formatting > Manage Rules.
Click New Rule or select an existing rule and use Edit Rule.
If needed, use Use a formula to determine which cells to format.
Make sure the rule’s Applies to range includes only that one column, not both columns.
Repeat for the second column with its own rule and its own Applies to range.

If the filter criteria are complex across multiple fields, use Data > Advanced and define a separate criteria range for the fields being filtered. Advanced Filter supports criteria across multiple fields, but it does not control which cells are highlighted; the highlighting is controlled by the conditional formatting rule scope.

If the current rule was created over both columns at once, Excel will keep formatting both columns whenever that rule evaluates as true. Editing the rule scope is the key fix.