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.
[tags]Microsoft, Microsoft Excel 2007, Excel, formatting, conditional formatting, workbook, spreadsheet, cell, data analysis, rules for conditional formatting[/tags]
» Filed Under MS Office Tips
Related Posts
- How to Use Conditional Formatting with Excel 2010
- How to Securely Password Protect an Excel File
- Use Excel’s Watch Window to Monitor Important Cells in a Workbook
- Link Cells Between Tabs and Workbooks In Excel 2010
- How to use Sparklines in Excel 2010
Comments
11 Responses to “Amazing Ways To Format Cells in Excel 2007”
-
Pingbacks
-
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
Pingbacks
-
ScreenHunter - Free screen capture program Says:
[...] Technorati Tags: screenshot, screen capture, ScreenHunter, ScreenHunter 5.0 Free, software review, web capture, image capture, internet browser image capture, screen, image, freeware, free download [...]
May 16th, 2008 at 5:02 am
Pingbacks
-
Microsoft Word: AutoText and AutoCorrect Says:
[...] Technorati Tags: Microsoft, MS Word, AutoCorrect, Auto Correct, AutoText, Auto Text, spelling correction, Word, Excel, Access, PowerPoint [...]
August 13th, 2008 at 4:58 pm
Thanks for the info Karen !
A useful article.
Thanks for the info!
Thanks.
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!
Is there any way to apply conditional formatting to a cell based on another cell’s value?
can the conditionally formatted cell (data + color) be mail merged into Word?
Good article. But the article makes it sound like before you had to do this manually, and that is not true; you could already conditionally format every cell in a range based on your own criteria in Excel 2003. Granted, Excel 2007 now gives you many nicer and fancier formatting options.
Question: Can I format a cell to reject certain characters? For example, if I type in a social security number, can I have the cell reject the dashes so that the SSN is only numbers? Thanks.