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,...
cybernetic.nomad's user avatar
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 ...
miroxlav's user avatar
  • 13.4k
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 ...
Mokubai's user avatar
  • 92.3k
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 ...
Joe Dog's user avatar
  • 176
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. ...
Saaru Lindestøkke's user avatar
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 ...
Máté Juhász's user avatar
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 ...
Rajesh Sinha's user avatar
  • 9,147
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 ...
Poultron's user avatar
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)....
davidmneedham's user avatar
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 ...
robinCTS's user avatar
  • 4,367
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 -> ...
Pawel Debski's user avatar
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($...
Scott Craner's user avatar
  • 23.1k
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 ...
Gabriel Caron L'Écuyer's user avatar
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 ...
Andi Mohr's user avatar
  • 4,225
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 ...
Aganju's user avatar
  • 9,962
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. ...
teylyn's user avatar
  • 22.7k
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 ...
teylyn's user avatar
  • 22.7k
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 ...
Karmo's user avatar
  • 51
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.
user3907173's user avatar
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). ...
Aganju's user avatar
  • 9,962
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 ...
Scott Craner's user avatar
  • 23.1k
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 ...
Aganju's user avatar
  • 9,962
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:
Máté Juhász's user avatar
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 ...
Excellll's user avatar
  • 12.7k
4 votes

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

Use a custom formula of: =ISERROR($C1)
Scott Craner's user avatar
  • 23.1k
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"
Dan's user avatar
  • 41
4 votes
Accepted

Custom negative sign symbol in Excel?

Use this Custom format: General;"!"General;0 It will change the negative to !
Scott Craner's user avatar
  • 23.1k
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 ...
JRI's user avatar
  • 827

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