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
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
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
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 × 840microsoft-excel × 660
worksheet-function × 180
microsoft-excel-2010 × 109
microsoft-excel-2007 × 59
microsoft-excel-2016 × 45
microsoft-excel-2013 × 44
vba × 40
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