Amazing Ways To Format Cells in Excel 2007

Posted on January 31, 2008 at 6:01 am

If you are used to using older versions of Excel, the conditional formatting options in Excel 2007 will amaze you. Now, you can use conditional formatting to format every cell in a range based on your own criteria (and there are a lot more formatting options to choose from). For example, if you have a profit sheet that you want to color code all profits greater than $200 as green and all profits less than $200 as yellow and all losses as red, then it could take a while to do manually – especially if you have thousands of entries. Instead, use conditional formatting to do the work for you.

Conditional formatting enables you to format significant amounts of data quickly and easily – while still being able to distinguish different types of data. You can create rules for the formatting options that will allow Microsoft Excel to auto-format for you. Here are the three simple steps followed in this example.

1. Select the cells you want to format.

2. Click the Conditional Formatting button under the Home menu, Styles section.

3. Select your rules. For this example, we imposed three rules. The first was that any value greater than $200 was green.

The same logic was applied to the second and third rules. The second rule was that anything between $0 and $200 was formatted yellow. The third rule was that anything less than $0 was formatted red.

Here is what a portion of the finished spreadsheet looks like.

If you do not like these formatting options, Microsoft Excel 2007 has many different new Conditional Formatting options that you can use. For example, you can insert Icons like colored arrows, bar charts like in the second example, or even a range of automatically selected colors like in the last example.

If you later decide that you don’t want your cells to be conditionally formatted, all you have to do is clear the formatting. To do this, select the Conditional Formatting button and select Clear Rules. Then, select whether you want to clear the rules from only the selected cells or from the entire worksheet.

Top Five Reasons to Use Conditional Formatting in Excel 2007

1. To make your data more visually appealing.

2. To make your spreadsheets easier to understand at a glance.

3. To identify certain types of numbers for help in problem solving.

4. To assist you in drawing conclusions from your data.

5. To visually display to the user what is “good” or “bad” by using green and red.

Using conditional formatting can help improve your spreadsheets. They will be more visually appealing and will enable the user to see what the values are at a glance.

This is a guest post by Karen Worthy, a tech enthusiast.

Technorati Tags: Microsoft, Microsoft Excel 2007, Excel, formatting, conditional formatting, workbook, spreadsheet, cell, data analysis, rules for conditional formatting

If you enjoyed this post, make sure you subscribe to my RSS feed!

» Filed Under MS Office Tips

5 Responses to “Amazing Ways To Format Cells in Excel 2007”

MJ said on : January 31st, 2008 at 5:46 pm

Thanks for the info Karen !
A useful article.

Sabrina said on : February 2nd, 2008 at 5:16 am

Thanks for the info!

Fedmich said on : February 11th, 2008 at 9:53 pm

Thanks.

Jamie said on : February 19th, 2008 at 3:06 pm

Hi
I was wondering… with the new Excel can you convert the conditional formatting to hard coded formatting? So the format is embedded rather than based off of the conditional? I REALLY want to be able to do this and if I knew it did I’d go buy 2007 today!

Thanks!

Recap of last month’s best posts on Online Tech Tips Says:

[...] Amazing Ways To Format Cells in Excel 2007If you are used to using older versions of Excel, the conditional formatting options in Excel 2007 will amaze you. [...]
February 1st, 2008 at 10:06 am