Discussion on:

10 cool ways to use Excel's conditional formatting feature

Excellent tips.

I agree. Excellent tips indeed! Thanks!

Do these apply to Pivot Tables as well?

A formula for alternating bands (number 7) can be much easier:
=ISEVEN(ROW()) vs =ISODD(ROW())
Or, more generically: =MOD(ROW()),2) = 0, =MOD(ROW()),2) = 1
The second version may be adjusted to alternate more than 2 formats.

I've used simple conditional formatting before now, but this is a very fine tutorial on what it is capable of and how to use it. Many thanks Susan.
PS would be nice if it could be in PDF format?

I took a screenshot myself - using "webscreencapture.exe" - available free here: http://www.webscreencapture.com/ , then used an image program to extract the part I wanted. Following that I made up a small webpage with easy links to the relevant parts of the image; thus it's easy to view offline.

Yeah, the article is excellent. I've only ever used Conditional Formatting in a small way before. Thank you, Susan, for this.

Alan

Thanks Alan, I think conditional formatting is underused -- it can do so much more than you might expect!

Why all the webscreencapture-- I just copy the article (selectively) and past it into Word. I can edit out comments that are unrelated, highlight points, etc.

Excellent article.
Can you please explain what you mean by 'unstable rule' in reference to 10: Find the smallest or largest duplicate?

I meant that used with a cell reference rather than a range name, it updates inconsistently and slowly.

I know this works, but I don't know why. This seems to be counting the number of lists that are in list, which should be one. Why does Excel interpret the first "list" as the list, but the second "list" as the current cell?
=MIN(IF(COUNTIF(list,list)>1,list,""))=A1

Excellent tips. I have used conditional formatting in the past but this really shows how much more we can do.

Will also share with others ... I came across a team member using a calculator to add numbers in an Excel spreadsheet displayed on his screen.

Sanjay
Outlook Tips for Your Business
http://blog.standss.com

Can anyone assist with 3 color scale in conditional formatting? I have a spreadsheet that I need to assign conditional formatting to certain cells. I am using Green, Yellow, and Red for value ranges. Thank you.

Hi, I'm new, and just dabble with excel. Is there a way to make my classroom jobs calendar show every kid's name a different color? So each time it says Johnny, he's blue, Jill is always green, and so on. On a related note, is there a way to make excel randomly populate my 11 job categories with my 34 student names, without having a kid with 2 jobs in one week? Like just autofill the calendar with their names. I do it by hand, but I know there has to be a way. Thanks!

Amazing, but some problem arises from 10 cool ways.
I am trying the discrepancies, but I didn't understand. Thanks once again for your valuable page.
Mathew

Is there a formula to update a cell fill color when it is updated or changed?

I'm either incredibly dense, or Excel is exceedingly poorly designed, but I've been struggling for half a day to find how I can colour cells that contain the same data the same colour. The problem is that my data isn't whole numbers or percentages - it's ranges (of age and income). So I'm trying to highlight every cell that has 18-24 the same colour, for example. Every single bit of help I've found is useless. I have tried the suggestions here, and the don't work - or I don't understand them.
Could someone explain to me how to do this? And explain it without using the program language which I'm illiterate to? The talk of formulas and ranges and things goes over my head because I have no idea what it means. I just want to know what to click and what to fill in so I can get on with my work efficiently, rather than having to do every single box manually.

Great post,

With the recent update to Excel, I've penned a quick post to outline some cool new features:

http://www.microsofttraining.net/b/exceltraining/

These tips are great and saved in my favourites, but do you know how I can get excel to highlight cells where the vlookup formula returns #N/A?

I want the "errored" cells to turn red so my users can easily spot the cells to work on, in the same way as those where I have a Countif with a return of 0 highlight in green.

Thanks!