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,...
- 5,378
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-...
- 393
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.
...
- 5,390
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 ...
- 21.1k
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).
...
- 21.1k
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 ...
- 71
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)....
- 2,341
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 ...
- 51
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 ...
- 4,761
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,761
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.
- 41
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