## Hot answers tagged conditional-formatting

3

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.

3

(1) Copy the column you want to format to an empty worksheet
(2) Select the column, and then choose "Remove Duplicates" from the Data ribbon
(3) 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 lookup table.
(4) In a new ...

2

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 able to extrapolate the setup for False = No.
It's important to note that it will only change the apparent value in the cell and not the actual value. If you ...

2

Try this out. Highlight cell C3:D4 (as per example) and add conditional formatting with the following formula:
=AND(ROW(C3)-2=DAY($A$1),COLUMN(C3)-2=MONTH($A$1))
I have tested this and it works as described so let me know if you have any problems.
Note: this formula excludes year as a factor, so 1/1/2000 would highlight cell C3, as would 1/1/2015
Copy ...

2

Use relative formulas ($ or not) and set the Applies to range to the entire row.
Just make sure that the first row in your formula and the first row in your Applies to are the same.
(Screenshots show just some range example. Change to match your exact setup.)

2

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

2

You can use a conditional format to highlight every other set of unique values.
=MOD(SUM(--(OFFSET($A$1,0,0,MAX(ROW(A1)-1,0))<>OFFSET($A$2,0,0,MAX(ROW(A1)-1,0)))),2)
You can significantly shorten the formula if you have a maximum range you want to apply the formatting over, but it would be less robust.
=MOD(SUM(--$A$5:$A5<>$A$6:$A6),2)

2

I solved the same issue by creating a separate sheet and copying a link to the information in there. To do this, highlight the whole of the original sheet and copy it (Ctrl+C). Go to the new sheet and right click the first cell. Select the link icon in the Paste Options. Everything pasted will be unformatted, so you'll have to correct this. Empty cells ...

2

Rather than using Specific Text, try using Cell Value and equal to, then enter C for one rule and O for the other (do not enter equal or quotes when entering the letter, Excel does it for you). This will look for the only the letters that match.
Applied to the whole column, this ignores text of the Open / Closed heading.

2

Go to the cell.
Press CTRL+1 to open the Format Cell.
Go to the Number Tab
Under "Category", select "Custom"
Where it says "Type", enter 0## #### ####
This should ensure that you get your leading zero, and that the spaces are added.
Excel also has a format under the "Special" category just for Phone Numbers. If you click that, then click "Custom" it ...

2

This works in Excel 2010. I'm not sure if it'll work in Google sheets.
Select the range for which you want conditional formatting.
Add conditional formatting using the following formula: (be sure to change the row from 1 to whatever the first row of your selected area is)
=AND($D1<TODAY()+7,ISBLANK($I1))
Select whatever formatting condition you want ...

2

Your problem occurs because the formula you use looks at the columns with "relative reference". That is, unless you specify that the cells you want to check are always in columns F and G by using the dollar-sign ($), the cells the conditional formatting checks will be offset with the same amount of rows and columns as they are removed from the top left cell ...

2

As I understand your situation, there are three relevant columns:
a column with a completion date (let's say it's in column L)
column M, which may contain the string COMPLETED
a column where you want your output (let's say it's column N)
You use the formula =$M2="COMPLETED", which results in TRUE or FALSE. If I understand correctly, instead of ...

2

Let's suppose the letter (L/F) is in column B, then:
Select the first row.
Open Conditional Fomatting / New Rule.
On type of formula, select "Use a formula"
In the box put =$B1="F"
On the button Format select the format that you want.
With the row still selected use the Format Painter to copy this format to the other rows.

2

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

2

There is no way to move the icon set. However, you can achieve a visual effect that will help eliminate confusion with multiple columns displaying icons.
Highlight the column which you wish to bring the numbers and icon closer together.
Right Click.
Select Format Cells.
Click the Alignment tab if it does not appear by default.
Locate the Horizontal Text ...

2

In Conditional Formatting, apply the formula =OR(B1="",MATCH("?",1:1,-1)>0) to column A.
The MATCH portion will look for a question mark anywhere in any field in the row, not just a standalone question mark.

2

You can use the OR() function in your conditional formatting...
=OR( CELL=$A$1, CELL=$B$1, CELL=$C$1 ) = TRUE
etc
When you select the rule for your conditional formatting you shouldn't select the simple rule Equal To. You need to select More Rules and then Use a formula to determine which cells to format
--
Thanks to @User15 for the suggested ...

2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this:
=IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral"))
B1 = 0.83 (just my value for budget)
B2 = YTD value entered by user
B3 = MTD value ...

2

Here’s a tricky way (that might be considered elegant).
I’ll assume that < 83 is the “good” condition. Use this formula:
=CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good")
Logical expressions like D1<83 evaluate to one of the Boolean values
TRUE and FALSE, which are numerically equal to 1 and 0, respectively.
By adding ...

2

Use the formula =EXACT($S$5, K5) instead of SEARCH.
EXACT makes an exact comparison. SEARCH is useful to search a string inside another, that's why you are getting all those fake results.

2

Use relative references (no dollar sign), by writing down a rule for the first cell of range (ex. B2>A2)

2

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.
tl;dr Use 1 extra cell and 1 text box for every condition you want. Each text box is setup to show giant text with huge outlines and shadows, all the same ...

1

A conditional format will contain both fill and font color. You can't set the fill with one rule and a font color with another. The latter will set the fill color, too. You will need to create a new rule that has the logic you require to identify unique and duplicate (although it's not quite clear how a unique value can be a duplicate at the same time).

1

Create a new Conditional Formatting rule that applies to =$A:$G and use the following formula. Both Gs can be replaced with which ever end column you need.
=AND(COUNTA($A1:$G1)>0, ISBLANK(A1))

1

From a blank sheet:
In A1 enter =c1
In b1 enter 5
In c1 enter 5
Select b1, click conditional formatting in the ribbon, and New Rule
Click "use a formula to determine cells to format".
In the formula box type =$b$1=$a$1
Click the format button and change the color to red, Click ok
Click ok
Click ok
B1 should now have red text. Change the value in c1 ...

1

You can use a formula like this in column B
=IF(A1>=PERCENTILE($A$1:$A$10,0.4),1,0)
Depending on your specific data, you may need >= or just > - you can determine that when working with the data.

1

Yes, it is possible to colour the cells with only one rule, you can use logical OR() operator in conditional formatting
=OR(D1=A1,D1=B1,D1=B3)
here, D1 contains the value to be compared with A1, B1 and B3 cells value, if any one conditions met, you can set to change the colour of D1 to your liking.

1

You can use the OR Operator in your conditional formatting like below:
=IF(OR(A1 = B1,B1 =B3),"work”, "not work")
Hope this is helpful for you.

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