Free Microsoft Excel 2013 Quick Reference

Conditional Formatting: "Use a Formula to determine which cells to format," ?

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated


Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Mortgage:" #.00 "Per Sq. Ft."

or

"Rent:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

I frequently use conditional formatting to shade alternate rows.

I use
(1) Home>Conditional Formatting>Use a formula to determine which cells to format;
(2) Type in "=MOD(ROW(),2)=1";
(3) And apply a light gray shade so that it will work on the screen and when I print from my old B&W LaserJet.

Every time I do this I have to search for the correct formula, then go through the steps to create a new rule. I grant that this doesn't take a huge amount of time but it'd be much easier if I could some how save the rule for use in any workbook.

Is it possible to do so?

Thanks for any suggestions.

Hey Everyone,

I was wondering if anyone could help me out with this conditional formatting problem I think I am so close!!! I know how to do it as a traditional "if/and" statement, but I just don't know how to input it into the Format Values where this formula is true:
box.

This is the formula which I have if I was just trying to do it 'normally' instead of conditionally formatting it. In plain English, if the value in cell A1(relative) is greater than the value anchored in A34 and The values in Sheet2 in cell A1(reletive) is greater than the value anchored in A34 (also Sheet2), then highlight the cell.

ie.

=IF(AND(A1>=$A$34,'Sheet2'!A1>='Sheet2'!$A$34),"Highlight","None"))

But how do I format it properly to put into a Conditional Formatting "Use a formula to determine which cells to format" box

Thanks in Advance for any guidance!

I want to color an entire row green where the value in column C = "Support".
I have selected condtional format > use a formula to determine which cells
to format but am not getting consistent results so something must be
incorrect with the formula.

Thanks

Lisa

hi all.

I wrote the following function in VBA:

Function isFormula(check_cell As Range) As Boolean
    isFormula = check_cell.HasFormula
End Function
I then use this formula with Conditional Formatting->Use a formula to determine which cells to format-> isFormula(A1)

It works fine for one cell, but if I copy this format into 30 cells my spreadsheet slows down incredibly so that even just moving from cell to cell has a lag.

Is there a way to make this faster? Using Excel 2007...

Thanks

If the number in the cell to the left is larger, then make this cell RED.

If the number in the cell to the left is smaller, then make this cell GREEN.

What formula would I use under the conditional formatting tool to make the above two scenarios work? Thanks in advance for your help!

Hello,

I have the following conditional formatting statement that I am having trouble trying to figure out:
I currently have the working statement 
I wanted to add that extra statement of logic but am unsure on how to do that.
This formula was added into the "use a formula to determine which cells to format under "format values where this formula is true."
thanks,

-vargs

In the attached sample I have tried to find a way to change the background color of a cell depending on the content. But empty cells seem to frustrate my text string conditions. I'm also perplexed by the way Excel 2007 adds it's own quotes to text string entries. I have to constantly re-edit a condition to remove extra quotes.

I'm using the 'Format only cells that contain' rule to apply the CF across a range that defines a given row. That rule lets me place a formula in the cell reference box - but they do not appear to function as they did in 2003.
I also tried the 'Use a formula to determine which cells to format' rule with no better result.

Row 2 has no ConFor, it is supplied as an example of what I'd like to see.
Row 4 shows how Cell Value <> "A" conditionally formats empty cells. The rest of the rows are my efforts to ignore the empty cells.

Row 6 almost works, but row 7 shows how a different 'not between' value set fails.
I want to ignore a designated character, and empty cells.

Am I up against my ignorance - or a bug?
Tried a less detailed post here http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If I missed an answer to this I am sorry. My searches did not find what I wanted to know.

Hi,

Is it possible to set a value to an EMPTY cell with conditional formatting?

I am trying this:

On Conditional Formatting:
1) Use a Formula to determine which cells to format
2) =IF(J5=0;"OK";"NotOK") or =IF(ISBLANK(J5);"OK";"Not OK")

Regards,
Elio Fernandes

Kindly look at the attached.

I want to use a formula to determine what I have on column C, but with a standard formula I can simply drag down without having to change the reference for each section. The different General Ledgers (GL) are moved within headers regularly and I have to be determining under which header each GL belongs to. Note that no one GL will appear in more than 1 section, and note as well that there may be A general section header and then a subsection which is where the GLs will be listed. I want the subsection header (or the header label that is right before the first number is shown).

Is there a formula you guys can think about. I can't think of any...

Hi,

I'm trying to make a KPI table which would help me to evaluate my employees but i'm stuck on Conditional Formatting.
From the the attach you can see that i have 4 columns (at the end of column is total):

Evaluation 1
Evaluation 2
Evaluation 3
Evaluation 4

Then total in percentage.

What i want to do is that, whenever on total in percentage is on F11 example up 50.00%, than cell B17 should be colored on red (but number 1 on B17 should stay as it is only the cell color should change, and so on colors can be different)

I searched over about this but i found nothing helpful (except use a formula to determine which cells to format, which still doesn't solve my issue)!

Thank you in advance

KPI.xlsx

Hello all. I am attempting to conditionally format cells in a column of data. I want to shade any cell that is not the same value as the cell immediately above it. For instance, I want the conditional formatting to shade ONLY THE FIRST cell that contains the 460.03 value in the example below. I can't quite figure out how to make this happen. I tried to use Conditional Formatting>Highlight Cells Rules>More Rules>Use a formula to determine which cells to format, and then I just plugged in a < with reference to the cell immediately above, but I couldn't get that to work. I also tried to upload my Excel file, but couldn't get that to work either (probably due to my own ineptitude). Thought I would try without uploading anyway, for now.
541.08 541.08 541.08 460.03 460.03 460.03
All of these cells contain formulas, if it makes any difference.

Thanks

On my spreadsheet (example attached) i need to have my numbers (on right) highlighted if a number equals a number drawn (on left).

I've attempted using a number of formula in the conditional formatting dialogue box (using 'use a formula to determine which cells to format'

TIA

Apologies - dinner bell rang

I'd like to change the column reference in a cell address. I have set up a vlookup list in which the names of the month = a specific letter (ex: January = E).

Can I have a formula in which a cell address (ex: J9) references that letter to determine which column in another worksheet is referenced?
For example, in a formula that reads: ='7765 Act'!R24 can I replace the "R" with with the result shown in the cell where the vlookup is?

The vlookup table looks like this:
April I
August N
December S
February F
January E
July M
June K
March G
May J
November R
October Q
September O

what I want to do is have the table look up the month, for example December, and change the formula to cell S24 instead of the current R24 for November.

Thanks for whatever help you can give, Happy Holidays!
Richard

Okay, I hate to even post because there are so many conditional formatting questions and answers out there, but everyone has a different way they want to do things, and I can't find a way to make mine work. I'm able to get some things working, but at the same time, it will also be highlighting cells that don't meet the criteria.

Here's what I need.

I have a list columns A-M containing data, C and M are date fields. C will always have a date, and M will only have a date when the row has been "Approved."

If the date in C is greater than 12 days away, I would like the entire row to turn yellow.

If the date in C is less than 12 days away AND the corresponding cell in M is blank, I need the entire row to turn red.

I have managed to get the C cell that is greater than 12 days away to turn yellow by using the "Format only cells that contain" rule with Cell Value - Greater Than - =TODAY()+12

However, it only highlights the cell. I tried using the "Use a formula to determine which cells to format" but couldn't get the syntax right.

I've also messed with the ISBLANK to try to get the row to turn red, and had some close calls, but for some reason it would highlight a few rows that actually had data in them.

Thanks for the help!

Greg (broscup)

I have a sheet in which I made four conditional formats and four custom number formats.
The conditional formats are each applied to cell C90. I used New Rule > Use a formula to determine which cells to format.
The Equation for the first is:
The Equation for the second is: 
	

	
And so on.

The custom number format corresponding to the first one is:
The custom number format corresponding to the second one is:
And so on.

When I try them out, they don't work the first time you try. When I change the value of E91 from 3 to 4, C90 still has "(#3)".
If I again put 4 into it and hit enter, C90 will change to "(#4)".
OR
If I enter 5 into C90 after having 4 in there, it will then change to "(#4)"
But if I go straight from 3 to 5, it will stay at "(#3)"
I tried assigning different fills to go along with each custom number format. The fills change when they are supposed to.
I also tried assigning pre-defined number formats to the cells and they are also not working correctly
There must be some sort of problem with number formats not updating right away.

This is a really simple problem, probably with a simple answer if it is possible, but hard to explain.

Say I have a formula (A1 * B1) + (C1 * B1)
This isn't the actual formula I'm using, but It'll do as an example.
Now instead of just always being B1 in the formula I want this cell to be variable. I would like to have a value in D1, say for example 5. Which will then enter the formula so that instead of accessing B1 it will access B5. Then whenever I change the value of D, say to 25, the B5 cell becomes B25 which will be accessed instead.

The way I tried to express this in formula was (B(D1), thinking that D1 would be replaced with its value which with the B would then become a cell address.

Hope that makes sense, hope more so someone can help me.

I probably should have called it 'Can I use a cell that contains a value, in a formula, to access a different cell'


1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A to select the List.
2. Select Home -> Conditional Formatting (in Styles Group) -> Top/Bottom Rules -> Top N Items.
3. In Format cells that rank in the Top select a number, select the formatting from with dropdown list, and click OK.
4. Repeat the steps above to format the Bottom N Numbers.

1. Select a column in the List.
2. Follow the steps above.
 To color rows where maximum/minimum values are ,in a specific column:
1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A.
2. Select Home -> Conditional Formatting (in Styles Group) -> New Rule.
3. Select Use a Formula to determine which cells to format.
4. In Format values where this formula is true insert the formula =MAX($F:$F) =$F1, click the Format button, select the Fill tab, choose a color and click OK.
5. Add a new rule while changing the formula to calculate the minimum number,
=MIN($F:$F) =$F1.

Be sure to distinguish between Absolute reference and Relative reference when entering the formulas.

Very basic formatting question. I'm trying to highlight a certain cell or row based upon a rule (comparison of the sum of two cells to another cell).
Trying format a row's color base upon a simple comparison (if,then) in that row. Example:

=if F10+G10>H10, highlight or fill the entire row red

I'm using 2007 right now. I can't get the conditional formatting tool to do this with a rule "use a formula to determine which cells to format." Not sure what formula to put into the box when creating this rule.
(Also, I am clicking the format button and choosing a fill of red, but what cells or rows is it setting this rule for (what cells or rows will be filled red))?
Ideas? Thank you so very much!

I am using 2007.

I want to highlight an entire line (from column A to M) of information IF the numbers contained in columns F thru M are greater than 20.

What I tried to do was, I highlighted A thru M and then did conditional formating then chose "Use a formula to determine which cells to format."

Then I put in the box.... =(F4+G4+H4+I4+J4+M4)>20

and I then changed the color to a light gray.

When I enter that, it turns part of the line gray and doesnt really change when I enter values over 20.

Am I doing something wrong or is there another way to do that?

Thanks.

Is it possible to use a formula to create a reference to a variable
named range? For example, I want a given cell to refer to a named
range. However, the specific named range it would reference is
dependent on today's date. I have named ranges for each weekday, i.e.
Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the
reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
docs to describe how to accomplish this.

TIA,
Rose

Hi,

I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

I hope this makes sense -this is my first post!

Regards

Andrew.Mileage Rate.xlsx

ok, let's see if I can explain this in a non-confusing way. I have a workbook with several tabs and each tab contains essential the same data. Anyway, each column is associated with a month of the year. The workbook is a tracking record of all our employee trainings and what we do is type in the date that we've received the training report. They only have to complete this training every other month and so only every other month is filled.

Here's what I would like it to do.

First, coloring Key: Purple means the report is due that month, green means we've received it.

Say I have four months: April, May, June, July

-If April is filled (with a date) then the cell in June-on the same row-should be colored purple (meaning that June is when the next report is due).

-If May is filled with a date, then the adjacent June cell should be blank (meaning no report is required that month.

-Then, if June has been highlighted as purple because a report is due that month, as soon as a date is entered in, the cell color should change to green.

-And, because I don't want the entire report to be purple until data is entered: If both April and May are blank, then June should also be blank.

-and if possible, if no data is entered after a certain date, the cell color turns red.

I've got the purple to work and be blank if something in the cell before it with the following:
Using a formula to determine which cell to format: =LEFT(F3,1)="" (This is when I'm in cell G3)

How do you have it recognize if something is filled with data without putting a range in, and when there's no data, instead of ""?

Lot's of questions, but only one big one. Any help would be appreciated. I do know that if I did =LEFT(G3,1)="" (again, when in cell G3) it'll turn blank if I type data in G3, but it won't turn blank if there's data in F3.

Is it possible to use a formula to create a reference to a variable
named range? For example, I want a given cell to refer to a named
range. However, the specific named range it would reference is
dependent on today's date. I have named ranges for each weekday, i.e.
Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the
reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
docs to describe how to accomplish this.

TIA,
Rose