Hot answers tagged

5

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


4

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


4

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). Afterwards, it will always show exactly what you type. type a ' (single straight quote) in front of your input. Excel will keep the display exactly as typed (but ...


4

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 as a row is hidden the row beneath the hidden returns one greater than it would. To see how it works place SUBTOTAL(3,$A$2:$A2) in an empty column. Then ...


3

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 that it won't affect the existing row and column heights on your worksheet. It's a bit of a hack, but will get the job done. Use Excel's Camera Tool To use ...


3

Highlight your date column. Choose "Conditional Formatting" > "Highlight Cells Rules" > "Between" Next, enter "1/1/2017" as a start date and "12/31/2017" as an end date. Finally, this is the result. This will highlight any date from the year 2017.


3

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 recommended IFERROR(<original formula>,"") - is to make a conditional formatting on the cell, where you use the formula =ISERROR(cell) and the resulting ...


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

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


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

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 (D1<83)+(F1<...


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

You will need to use vba for this I believe. It used to be a real pain to get out conditional formatting colours even with vba but they have since added a new function. The below will copy the colour over in cells A1 to A3. This does not remove the conditional formatting so you will either have to do that manually or add something to the macro to do it. ...


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

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

If you highlight column J and use Conditional Formatting's "Use formula to determine which cells to format" with the following formula, it should work. =COUNTIF(E:E, J1) This will count any cells in column J that match column E, then apply the formatting you choose. Don't forget to set the formatting you want if the formula is true.


2

AFAIK, there's no perfect solution for this kind of task. The best practice depends on the contents of your sheet - especially, if you want to keep formulas, too. Generally, you can drop the rules regarding conditional formatting by saving the sheet as HTML, and re-loading the HTML into Calc. Of course, this will drop stuff like functions, too - just ...


2

Are you looking for this: =((TODAY()-A1)>30)*(B1="")*(A1<>"")


2

You can use conditional formatting to achieve this. You will use a formula to calculate todays date against the value in the cell (Which must be of a date format). The formula would be along the lines of: =(TODAY()-13)>A1 Depending on how many days you want to compare against, and which cell has your date. Note, this will trigger blank cells as they ...


2

With conditional formatting: Select the row containing your data go to: Home - conditional formatting - new rule - use a formula to decide... if your data is in row 1, starting at A1, then enter this formula: =COUNTIF($A$1:A1,A1)>1 specify the condition you need (italic, font colour...) Update If you want to format multiple rows based on hear row'...


2

Having the formula pointing on $B$3 will always be true or false, if $B$3 is Monday or not. You need to make the row a relativ reference, ie the formula should be something like =IF($B3="Monday";TRUE;FALSE). The omitted $ in front of the row will make this formula look into the first column in every row, thus making a border around all mondays. In order ...


1

Use conditional formatting, "use a formula to decide which cell to format": =AND(A1<TODAY()-30,B1="") Update based on comment: =OR(AND(A1<TODAY()-30,B1=""),A1="")


1

Yes, but only via code/macro of some sort. You'd iterate through the .Cell collection of the table, check to see that each cell has text and if so, if the value of the text converted to numeric is < 0, then set the cell's .Shape fill to whatever you like. Pass a reference to the table to this, for example: Sub FormatTheTable(oTbl As Table) Dim x As ...


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

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

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

I am not aware of any inbuilt functions that check the individual colour of each character within a cell however you can write one in VBA. This function will iterate through each character and return true if one of the characters is red. Please note: This code only works on the standard red that is available, it will not work on all shades. 'Only works on ...


1

You can use the following formula: =WEEKNUM($A6,21)-WEEKNUM(DATE(YEAR($A6),MONTH($A6),1),21)+1=x where x: 1 - 5.



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