51 votes

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

It is indeed because * is treated as a wildcard. The way around it, is to use a formula to insert a tilde (~) in order to escape the asterisk (*) for your conditional formatting: =COUNTIF(A:A,...
30 votes
Accepted

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

After further research of the behavior and documentation, I can answer the question from high-level perspective: This is rather an undocumented behavior than a defect. Other answers reminded us of use ...
  • 12.7k
28 votes

Find Items in one column that are not in another column

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and ...
  • 281
26 votes
Accepted

How do I add conditional formatting to cells containing #N/A in Excel?

#N/A isn't "text" as far as Excel is concerned, it just looks like it. It is actually a very specific error meaning that the value is "Not Available" due to some error during calculation. You can ...
  • 86.7k
17 votes
Accepted

Excel function determine if a cell is visible

Instead of subtotal using a sum on another column, you can use subtotal using counta to see if a (known-non-blank) cell is hidden or not. For example, if column A will normally be visible (unless the ...
  • 1,000
16 votes

Excel conditional formatting fragmentation

Inserting and deleting rows does not cause conditional formatting to get fragmented. The cause is copy/pasting between cells or rows using the standard copy/paste. The fix is to always use paste-...
14 votes

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

If you Google for excel asterisk wildcard conditional formatting you'll find someone with the same issue. There the proposed solution is to use a custom formula to check if the value is a duplicate. ...
13 votes
Accepted

Conditionally format a cell if it contains formula

There is a very simple way to do this, tested in Excel 2016. Highlight your range you wish this to apply to, let's say from A3:W20. Go into conditional formatting and select NEW RULE | USE A FORMULA ...
  • 146
12 votes
Accepted

Highlight All Mondays in Orange Excel

You've two issues in your formula: your formula should refer only to one cell (the one which is active when you write your formatting) default day numbering of WEEKDAY function is 1 to 7, it'll never ...
10 votes
Accepted

Conditional Formatting in Excel with Formula Current Cell

Suppose the data range to conditionally format is A2:A10. Select first cell A2. From Home TAB, Click Conditional Formatting, Manage Rules, New Rule. Under use formula to determine which cell to ...
  • 8,828
8 votes
Accepted

Alternate Row Colors of Duplicate Data in Excel

Set B2 to =IF(A2=A3, 1, -2) and set B3 to =IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2))) and drag it down to B30 (or the last row that contains data, whatever that is).  ...
8 votes
Accepted

how to highlight the cell with the max value

In a quick test, I was able to accomplish what it sounds like you are wanting with the following. Using CTRL + Left Mouse Click, Highlight one rows worth of cells that you want included in the ...
  • 2,474
7 votes

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

The easiest way to accomplish this is with copy/paste incrementally. First, format 1 row the way you want it. Then copy and past the formatting only to ONLY a second row. Now copy BOTH rows 1 and 2 ...
7 votes
Accepted

Color Cell Based On Text Value

Copy the column you want to format to an empty worksheet. Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon. To the right of your ...
  • 94
7 votes

How to prevent cells from printing in Excel

This solution doesn’t require you to take special action before and after each time you need to print the file.  It builds on the idea of using a “Print Area”, as suggested by Terence and Don Livezey. ...
  • 71
7 votes

Excel Conditional Formatting if one column is equal to another

Select column BO and use the following: =$C2<>$BO2
  • 121
7 votes
Accepted

How can I modify the "Applies To" property of a Cell's Conditional Formatting rule using VBA?

There is a ModifyAppliesToRange method designed for this purpose. You will also need to convert strRange to a Range object. Replace .FormatConditions(1).AppliesTo = strRange with .FormatConditions(1)....
6 votes

Highlight max absolute value in a row of data

Conditional formatting does not like CSE array formulas. Also you need the formula to resolve to TRUE/FALSE and not return a number. Use a formula like this: =AGGREGATE(14,6,ABS($A$1:$A$4),1)=ABS($...
  • 21.9k
6 votes

Excel conditional formatting per row

From my understanding, you want to do a conditional formating of every cell in column x1 if it's value is higher than it's corresponding cell in column x2. To do it : First, setup your conditional ...
6 votes

Highlighting duplicate rows based on 2 columns

This can be done simply by using the COUNTIFS() function: Select the appropriate cells in the table, making sure that A2 is the active cell, and enter the following for the conditional formatting ...
  • 4,307
5 votes

Excel function determine if a cell is visible

As an addendum to Phrogz's answer, if you need to check whether a cell in a column is hidden, try either of the following, Conditional Formatting =CELL("width",TargetCell)=0 This updates ...
5 votes
Accepted

how to get text instead of TRUE:FALSE in excel

Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be ...
5 votes

Highlight All Mondays in Orange Excel

Your formula might have worked when all those days are Mondays. In the Conditional Formatting, you need to enter the formula for one cell. It will be automatically applied to all others, relative or ...
  • 9,873
4 votes

Vertical Histogram in Excel with Conditional Formatting

I was interested in something similar and was able to achieve it. Though I didn't use conditional formatting, I think this example will show you how to achieve what you want. Put the numbers 1,2,3,2,...
  • 133
4 votes
Accepted

Excel - conditional formatting in Shapes?

No, but you can fake it You can fake it but it takes some setup. It will work unless you need really big shapes. Here are the steps for creating n different conditional format shapes in Excel 2010. ...
4 votes

How do i right align an icon set arrow under conditional formatting so that both number and arrow are on right side of cell neatly together?

You can align the icon if when you setup the formula check the box "Show Icon only". With the icon only showing, the Centering of the content of the cell will apply to the Icon.
4 votes
Accepted

How do I apply conditional formatting to a table from a graph?

I don't believe it's possible to apply a colour to numbers in a chart data table. However, I may have a workaround for you that means you can use a formatted table stored on a different worksheet, so ...
  • 4,150
4 votes

Excel - Format cell showing always the first digit

Your requirement implies that you want to treat the input as text and not as number. There are two ways to accomplish this: Change the format of the cell to 'Text' (before you enter the data). ...
  • 9,873

Only top scored, non community-wiki answers of a minimum length are eligible