# Make your data pop with conditional formatting

You've got your numbers wrangled and organized just right, but they still don't jump out at the people who look at your worksheet. You want to give them the big picture at a quick glance. This is where conditional formatting can come to your rescue.

The embedded workbook below shows three examples of conditional formatting. The first two examples use easy to apply built-in formats, and the third example uses a rule based on criteria that you define and apply by using a formula.

If you click the View full-size workbook icon in the Microsoft Excel Web App black bar (just above), the workbook will be shown in a new browser window (or tab), and you'll see an Excel Download button above the worksheet grid.

If you have Excel installed on your computer (Excel 2007 or later), download the workbook to your computer and then open it in Excel. In the workbook, you'll be able to see the conditional formatting rules applied to each of the data ranges by doing the following:

1. Select the range, click Conditional Formatting on the Home tab, and then click Manage Rules.

2. Select the following cell ranges to examine their conditional formatting rules:

• For the city temperatures, select B2:E3.
• For the student grade point averages, select B7:B20.
• For the East regiion actuual expenses, select B24:E24.
• For the North region actual expenses, select B26:E26.
• For the South region actual expenses, select B28:E28.
• For the West region actual expenses, select B30:E30.

3. In the Conditional Formatting Rules Manager dialog box that appears, click Edit Rule.

Learn even more about conditonal formatting with these articles and videos on Office.com:

And be sure to search for the keyword "conditional" to see some great blog posts from the Excel product team.

-- Gary Willoughby

• First off, I love Excel 2010 for sure, and I've been using it ever since it existed.

BUT! I create 6 worksheets of data, about 18,000 rows by up to 15 columns...all heavily formatted, and some conditional formatting.

But it gets so slow*, I can hardly bear being there while it makes minor changes, such as,say, inserting a single extra row.

I'm doing this on a Lenovo laptop (i7 2.67 ghz + 3 Mb RAM, Win 7 Professional 32-bit

Is the problem of painful, endless slowness resolvable by way of more RAM, say 8 gb (lor more?)...is it the processor, which seems awful busy in Task Manager, but rarely at 100% while this is going on?

Would RAID O speed everything up, or not? AN SSD...

I'm at a loss and I've checked the web and cannot in any real sense find a solution, and I'd be happy to buy new hardware, even a desk top, but would be the key elements in a new machine?

* By planning a little better, I've reduced the work book file size to 20 gb from 60 gb, but this does nothing to speed it up.

• Hi, Thekkat -- glad you're loving Excel 2010. Sounds like in addition to your conditional and other formatting, you probably have a lot of formulas in your worksheets. Your laptop sounds plenty powerful, so I doubt that's a problem. You may want to try turning off automatic recalculation when you work with this workbook. You can change this setting by clicking the "File" tab in Excel, and clicking "Options." In the "Options" dialog box, click "Formulas" in the left pane, and in the "Calculations" section, under "Workbook Calculation," select "Manual." You can turn this option back on when you work with other, smaller workbooks (Automatic Calculation is the recommended setting).

Something else you might want to check: In the same "Options" dialog box, click "Advanced," and under the "Formulas" section, make sure that "Enable multi-threaded calculation" and "Use all processors on this computer" are both selected so that Excel will take advantage of all your computer's power.

If all this doesn't help, you can post your question in the Microsoft Answers forum for Excel 2010, here: answers.microsoft.com/.../office_2010-excel

• Thanks for the advice, the perosnal attention is much appreciated.

Having you affrim what you did now let's me move on with my question to the MS Answers forum.

Thank you again,

Sincerely,

Thekkat

• is it possible to use the traffic lights with text? for example: if cell contains "green", then I want the green traffic light.

• @aaron: You've probably noticed that icon sets aren't as easy to work with as background or font coloring...they work best with numbers. A workaround for you could be to insert a column next to your column that contains your text, and use a nested IF formula to return either a 1, 0, or -1 value, depending on whether the cell contains "green," "yellow," or "red." Assuming your text is in A1, here's the formula you'd use:

=IF(A1="green",1,IF(A1="yellow",0,-1))

Then, fill the formula down the column and apply the conditional formatting traffic light icon set. Excel should, by default, assign green to 1, yellow to 0, and red to -1. Finally, edit your rule to show the icon only in the cell (hiding the number). Hope this helps!

• I am sorry if I am posting this in the wrong place.  This is the first place I saw on conditional formatting.

I have a question about Excel 2007 and conditional formatting.  I created a “countdown” sheet to my retirement and I would like the days to turn to red when I am a week out.  Below is the formula I used to count down and display the days

=IF(DATEDIF(\$B\$1,A22,"y")=0,"",DATEDIF(\$B\$1,A22,"y")&" years ")&IF(DATEDIF(\$B\$1,A22,"ym")=0,"",

B1 one is =today() and A22 is the date I retire.  There are other deadline dates between that I am counting down to.  Any suggestion would be great.

Thanks in advance for any help.

• @iscromain: How about using a simpler formula that returns the number of days? Then,create a conditional formatting rule that applies the color you want, based on the number of days being less than 8.

=DATEDIF(\$B\$1,A22,"d")

If you'd like a community of users to weigh in on this with lots of creative solutions, I encourage you to post your question at the Microsoft Answers Forum: answers.microsoft.com/.../excel