50
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,SUBSTITUTE(A1,"*","~*"))>1
44
It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected ...
30
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 of wildcard characters (*, ?, ~) in Excel formulas. The question unanswered before is if they are expected also in conditional formatting. Microsoft ...
22
#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 use ISNA(Range) to match on an error of this type.
Rather than "contains text" you want to create a new blank rule rather than the generic ones and then "Use a ...
18
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 it worked for me.
15
Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:
=MATCH(B2,$A$2:$A$287,0)
Then click and drag down.
A cell with #N/A means that the value directly next to it in column B does not show up anywhere in the entire column A.
Please note that you need to change $A$287 to match ...
14
If you're using Office 2010, under "File -> Print -> Page Setup -> Sheet (tab)",
the setting for "Print area" should be what you are looking for. In my example, only columns A:J will be printed; however, everything is shown when viewing on screen.
This is what the dialog looks like:
(Click image to enlarge)
This may also be accessible as
"Page Layout" → "...
14
This answer is copied straight from stackoverflow.com Alternating coloring groups of rows in Excel.
I use this formula to get the input for a conditional formatting:
=IF(B2=B1,E1,MOD(E1+1,2)) [content of cell E2]
Where column B contains the item that needs to be grouped and E is an auxiliary column. Every time that the upper cell (B1 on this case) is ...
14
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.
The formula looks as follows:
=SUMPRODUCT(--(("~"&A2)=("~"&$A$2:$A$4)))>1
And the result is:
13
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-value or paste-formula. On the destination right click and the Paste Options section will offer 123 (values) and f (formulas). Don't copy/paste formatting as that ...
13
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 row is hidden)
= IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" )
You can put this formula in a column that may be hidden, and it will still work.
In ...
12
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 be 0 (in default format Monday is 2)
11
Building on brettdj's answer, because I found the linked article quite difficult to follow:
Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
Insert the following formula: =ISFORMULA(INDIRECT("rc",FALSE))
If you want the rule to apply to the whole worksheet, $1:$1048576 as the range to apply to. Otherwise, ...
11
It's unclear if this is a bug; but Excel seems to be making the entire cell clickable just because there's a HYPERLINK function in the formula in order for it to work. Right-clicking on the cell & selecting Remove Hyperlink does not seem to work either.
I can't seem to find a non-VBA solution to prevent Excel from creating those "phantom" hyperlinks, ...
11
I tend to use an IF() rule for this sort of thing. The trick is then to get the '$' signs right. If column C is the original data and BO is the calculated value, then select the BO column and add a conditional formatting formula rule of:
=IF($C2=$BO2,1,0)
Note there is no $ sign beside the row number, which causes it to apply to each row in turn.
9
Select column M. Check which cell is highlighted (let's assume
M1)
Conditional Formatting \ New Rule
Use a formula to determine which cells to format
Type in the following formula =J1="paid" (using the highlighted
cell's row number - remember M1)
Choose your formatting, then validate.
Repeat as needed for the other conditions and formats.
for your other ...
8
You need to anchor the column references in the formula for the conditional format.
Assuming that your data range begins in A1, you would set a conditional format on the range A1:F1 with a criterion formula $A1<>$A2 and your chosen format.
Then, you would copy the formatting of the row down the range (using Paste Special Format).
8
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 validation; F, J, and N in your example.
Go to the Format menu and choose Conditional Formatting
In the Conditional format rules pane, change the "Format cells if..." ...
7
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.
Put the cell(s) that you don’t want to print outside the print area.
If that’s good enough for you, you’re done.
But you might want the excluded cell(s) to ...
7
If your Applies to field is set to A5:ZZ5 (or $5:$5 to highlight the entire row), your formula should be
=$J5="T"
Note where the $ is placed.
In my example below, my formula is set to =$J1="T" and my Applies to field is set to $A$1:$Z$6.
7
Select column BO and use the following:
=$C2<>$BO2
7
When I need a range that shouldn't change under any circumstances, including moving, inserting, and deleting cells, I used a named range and the INDIRECT function.
For example, if I want a range to always apply to cells A1:A50, I defined a named range through the Name Manager:
In the Name Manager, add a new range (click New), and in the Refers To: field, ...
7
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).
This will evaluate to a positive number
if this row is part of a duplicate value group
and a negative value if it isn’t
(i.e., if Column A contains a unique ...
7
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 format. In the field Format
values where this formula is true, enter =ISNA($A2).
Click Format to set the cell formatting, then select OK.
In the Conditional ...
6
With the aid of a UDF (user defined function) you can use Conditional Formatting:
Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function
And then use IsFormula in the format condition
6
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 unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your ...
6
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 and paste the formatting to rows 3 and 4. Rinse and repeat, Copy 4, past 4, copy 8, paste 8, copy 16, paste 16. Once you've got a decent amount like 16, ...
6
You can do this with formula-based conditional formatting.
Select the cells you want to apply the formatting to.
On the Home tab, click on "Conditional formatting" → "Manage rules..."
Click "New Rule..."
Select "Use a formula to determine which cells to format"
In the formula field, enter the following:
=SEARCH("Due in", $C1) > 0
Instead of C1, use ...
6
Yes you can use conditional formatting
FOLLOWUP (From Comments)
For multiple cells, select the relevant range and simply remove the "$" sign before the row number to make the range relative. See Screenshot below
6
Select the C5 cell and go to Home, Conditional Formatting. Choose "Manage Rules" and create a New Rule.
The new rule type is "format only cells that contain". The Rule Description should be "Cell Value equal to =$A$5". Then set the formatting you'd like on the cell.
Only top voted, non community-wiki answers of a minimum length are eligible
