50
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 ...

29
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 ...

16
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 ...

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.
...

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 ...

12
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
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 ...

8
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)....

7
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 ...

7
votes

Accepted

### LibreOffice Calc: conditional formatting based of column / cell value

I am using LibreOffice Calc 5.1.6.2.
Just like my highly respected preceding poster described:
Select the range of cells to be conditionally formatted (in my case A1:F44);
Select Menu Format -> ...

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($...

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 ...

5
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 ...

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 ...

5
votes

Accepted

### Excel - Overflow the text of a cell to an adjacent cell that contains a formula

The short answer is: You can't.
The long answer is that only truly empty cells will allow text from the next cell to the left to spill over and display in that cell.
This is just how Excel works.
...

5
votes

### How to apply a single conditional formatting rule to multiple non-contiguous columns in Excel?

You can define several ranges and append them with commas in the "Applies to" box of the dialog.
If you use semicolons in formulas, then use a semicolon here, too.
Also, it is not advisable to set ...

4
votes

### Color Cell Based On Text Value

From: http://www.mrexcel.com/forum/excel-questions/861678-highlighting-rows-random-colors-if-there-duplicates-one-column.html#post4185738
Sub ColourDuplicates()
Dim Rng As Range
Dim Cel As Range
Dim ...

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

### 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). ...

4
votes

Accepted

### Conditional formatting on the first x number of rows, regardless of filter or sort, in Excel

Use this formula in the Conditional Formatting:
=SUBTOTAL(3,$A$2:$A2)<11
Make sure it applies to the entire dataset.
The formula returns the row number relative to the visible row number. Thus ...

4
votes

Accepted

### Apply Custom Number Formatting to Error values

No, that is not possible.
Because Microsoft decided so, and probably because it is dangerous for less qualified users - they would not see the error anymore.
What you can do - if you don't want the ...

4
votes

### Excel color scale with outlier

You can set "minimum value" for conditional formatting, this case Excel will consider everything below that to be coloured as minimum and won't calculate with the whole range:

4
votes

### Highlight max absolute value in a row of data

You can calculate the maximum absolute value without an array formula. For data in A1:A10,
=ABS(A1)=MAX(-MIN($A$1:$A$10),MAX($A$1:$A$10))
The maximum absolute value will be the larger of the minimum ...

4
votes

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

Use a custom formula of:
=ISERROR($C1)

4
votes

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

Or use the "Format only cells that contain" option and change the first drop down from "Cell Value" to "Errors"

4
votes

Accepted

### Custom negative sign symbol in Excel?

Use this Custom format:
General;"!"General;0
It will change the negative to !

4
votes

### Conditional Formatting in Excel with Formula Current Cell

Using relative references to refer to the current cell
In a conditional formatting formula, you can refer to the current cell by using the relative form of its usual address. e.g. If you want to ...

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

#### Related Tags

conditional-formatting × 839microsoft-excel × 660

worksheet-function × 181

microsoft-excel-2010 × 109

microsoft-excel-2007 × 59

microsoft-excel-2016 × 45

microsoft-excel-2013 × 44

vba × 41

formatting × 37

libreoffice-calc × 23

colors × 20

macros × 17

pivot-table × 16

spreadsheet × 16

cell-format × 16

google-spreadsheets × 14

microsoft-outlook × 13

highlighting × 13

libreoffice × 12

calendar × 12

microsoft-word × 8

openoffice-calc × 8

charts × 7

text-formatting × 7

vlookup × 7