Conditional Highlighting

Question

Hi, I'm relatively new to using Excel and I'm making a spreadsheet tracking profit/loss etc.  I'm trying to add conditional highlighting to my columns however when I try to add the rule of highlighting cells greater than a value greater than 0 Excel then highlights everything with some form of input, including blank cells with formula.  I just simply want it to highlight red/green according to profit/loss but it even highlights cells with text?

I'd appreciate any suggestions

Thanks

Answer 1

There is seldom, if ever, need to use IF in a Conditional Formatting dialog.

All that is needed is a formula that returns either TRUE or FALSE

In the present case =AND(ISNUMBER(C1), C1>0) will do the trick

best wishes

Answer 2

OK, cool. Up until now, when I did conditional formatting the data has been all one type. But I see the same thing you did. If I checked for a > value it treated all letters a > anything.

I came up with this formula. With my data in column c I tested and developed this formula in D

=IF(ISNUMBER(C1),(IF(C1>1,TRUE,FALSE)),FALSE)

Then following the instructions in this tip:

https://www.myonlinetraininghub.com/excel-condi...

I pasted that formula into my conditional formatting test. It now only highlights cells containing numbers > 0. You can change the "0" to whatever

Is this what you are looking for.

************* Bonus Points ****************

If you want to use a slightly fancier function try converting to =IF(and( ... ))

(I wasn't able to get that syntax to work.

Answer 3

Select H2:H19

Open Cond Form dialog and apply two rules

Note the green on is =AND(H2>0,ISNUMBER(H2)) to prevent text being highlighted while the red one is simply =H2<0

best wishes

Answer 4

Yes, I selected the column i wanted to apply it to.  For example - 

I’ve selected the whole column and applied the rule to highlight cells when the cell value is greater than 0 however this then equates to excel highlighting pretty much the whole colum including the heading row, and any blank cells that contain formula rather than just the numerical cells that contain a value greater than 0?

Answer 5

Did you select the column you wanted to apply the formatting to?

Here are some articles on doing conditional formattting

2013- Excel Conditional Formatting with Formulas https://www.myonlinetraininghub.com/excel-condi...

March 6, 2013 by Mynda Treacy

Excel’s Conditional Formatting tool is diverse with loads of built in rules that you simply point and click to use, but I find more often than not that I need to use a formula based rule. I can sympathise if you’ve ever tried to use formulas in your conditional formatting and ended up tearing your hair out in frustration. Thankfully there are only 3 simple rules you need to know. Once you understand these rules you’ll never look back.

5 Excel Conditional Formatting Basics http://chandoo.org/wp/2009/03/13/excel-conditio...

Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a certain way. For eg. you can use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values..

Add, change, find, or clear conditional formats in Excel https://support.office.com/en-us/article/Add-ch...

Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends:

Format cells using a two-color scale	    Format cells using a three-color scale

Format cells using data bars	    Format all cells using an icon set

Format only cells that contain text, number, or date or time values

Format only top or bottom ranked values	    Format only unique or duplicate values

Format only values that are above or below average

Use a formula to determine which cells to format	    Find cells that have conditional formats

Change conditional formats	    Clear conditional formats

Learn more about conditional formatting

10 cool ways to use Excel’s conditional formatting feature http://www.techrepublic.com/blog/10things/10-co...

Excel’s conditional formatting feature can do a whole lot more than many people realize. Susan Harkins runs through 10 practical ways to put this tool to work.