Tag Info

Hot answers tagged

3

The formula does not have to be that complicated. Keep in mind that conditional formatting formulas can use relative references. When the format is applied to another cell, the references will be relative to the current cell. Select cell M10 and enter the conditional format using this formula: =OR(M10<J10,M10<K10,M10<L10) Note that there are no ...


3

Assuming the groups are showing on Sheet1, the list of names and dates is on sheet 2: select the table with the groups, including blank cells, starting in A1 click Conditional Formatting > New Rule > Use a formula to determine enter the formula posted below, click the Format button and select the fill Formula: ...


2

To format the cells if their date is before the current day (Sheet 1 = groups, Sheet 2 = dates and names): Go to sheet 1, select all the data (A1:D7 in your example) Click Conditional Formatting > New rule > Use a formula to determine which cells to format Click the Format button and change the format to whatever you want (bearing in mind that this one is ...


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

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

There may be neater solutions, but you shouldn't need to concatenate first and last names to make a "FullName" column. Preview of our output (the white cells on the right show what output our formula will produce to drive the conditional formatting): Here's our formula =COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2) ...


2

Make sure that the referenced cell contains valid numbers. To check this, put the formula =Mechanic!$C$8 in a blank cell. I.e., make sure C8 in worksheet named Mechanic contains 2 and C7 contains 4. If they do contain the correct values then make sure they are formatted as number and not text. If these two are taken care of you should be good to go.


2

To apply conditional formatting across multiple sheets within a workbook will take some work. There is no "point & click" solution that I have been able to find. Here are a couple solutions to help you find your way. The bottom line; you will have to do some experimenting to make this work for your specific application. Using Defined Names - Excel ...


2

Sure. Select cell B2 and go to Home tab > Styles group > Conditional Formatting > New Rule > Use formula to determine which cells to format Enter this formula: =AND(A2="John likes green apples",B2="John likes green apples") Set the set the format to fill green. Repeat for each additional color and rule.


2

The Microsoft Fuzzy Lookup Add-in does a great job for this kind of thing. Sadly, it won't work for Mac users, but does for PC users running Excel 2010 or later. Download the add-in. (Make sure a Fuzzy Lookup tab appears on the ribbon.) Copy all data in your first text column into a new tab/workbook. Insert a table for that column only (IE use the Insert ...


1

Select the range you want to apply the conditional formatting rule to and go to Conditional Formatting on the Home ribbon, then New Rule.... From there choose Use a formula to determine which cells to format and enter the formula below. =AND(COUNTA($A1:A1)=1,A1="") Here A1 is the top-left cell of the selected range. Adjust the formula to fit your needs. ...


1

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


1

If you are using merge fields, you can do something like this: { IF { =Sum(above) } <> 100 { = Sum(above) \*Charformat } { = Sum(above) \*Charformat } } And format each field {} with the color you want.


1

Excel will not allow a bare reference to another sheet in a conditional formatting formula. To get around this, you can create a Named Range to represent the data on the other sheet. Conditional formatting allows references to named ranges, even if they are found on a different sheet. Create Named Range: Use Named Range in Conditional Formatting ...


1

If my understanding of your task is correct, it cannot be done using the 3-color scale with formulas only. You need to use relative references, which are not acceptable in formulas of the 2/3-color scale and icons set. There are two possible solutions: The one you used. If the 1st tick in each rows should always be red, 2nd orange and so on, then you can ...


1

If I understand what you are asking, you can accomplish this with two conditional format rules which use formulas. You didn't state what version you are using, so you will have to use whatever method you need. The first rule will use the following formula: =AND(NOT(ISBLANK($L1)), $A1="Flag set") This checks to see that L is NOT blank AND A1="Flag set". If ...


1

Select cell A1 (if you don't, these directions will not work due to the relative reference you create in step #3...which is the key to being able to apply these conditions to all the entire worksheet) Create a new Conditional Formatting rule of the Use a formula to determine which cells to format type In the Format values where this formula is true: text ...


1

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)


1

Use the following formula in the conditional formatting. I assumed row 1 is the header row. =MOD(ROW(),10)-1=1


1

Select the cells in column A. Create a new conditional format based on a formula (Click Conditional Formattting > New Rule > Use a formula to determine which cells to format). Use this formula =COUNTIF($B:$B,A1) Click the Format button and select a format for example a fill color. Click OK.


1

The way I always make Gantt chars in Excel is to put a formula in all the cells that results in a character in that cell and then use conditional formatting to format the cell. You can "hide" the characters by making them (in the conditional formatting) the same color as the background. So, for example, if you have starting week numbers in column B and ...


1

there is all sorts wrong, unfortunately! ADDRESS gives you the address of the cell, not its value... so you are comparing whether an address is bigger/smaller You have both checks as >= rather than one greater and one less than You have a mix of , and ; (but I guess that is probably just editing onto this site!) To enter the formula: go into cell ...


1

Use the following formula in the conditional formatting for Actual Arrival Time column cells =IF(HOUR(C3)=HOUR(B3),IF(MINUTE(C3)-MINUTE(B3)>5,TRUE,FALSE),IF(HOUR(C3)>HOUR(B3),TRUE,FALSE)) where column C has actual times and column B schedules times To set this up go to HOME > Conditional Formatting > New Rule > Select "Use a formula to determine which ...


1

The first posted answer is definitely correct: the solution is to use conditional formatting.  Beyond that, it gets a bit hard to understand.  So this answer may, at some level, be technically equivalent to Brian’s, but with more of an emphasis on clarity; this answer: Identifies the rows and columns in the text in addition to the image. Displays dates in ...


1

I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values. The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.


1

Three items down in the same menu you have New Rule, choose the second option where you can set a format for the cell (border, font-color, background,etc.) according to a range of values. There is no way to change font or font size from here. You will need a macro in VBA, like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim wk As Workbook Set ...


1

I have tested this on Excel 2013: [Green]"+"0.00%;[Red]"-"0.00% NOTE: actually my excel is in another language with different decimal separator, so it looks like this: [Green]"+"0,00%;[Red]"-"0,00% but I assume yours use the dot as decimal.



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