Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Excel includes a powerful feature that allows you to dynamically change the formatting of individual cells based on the results being displayed in that cell. For instance, you could make the text in the cell larger and red if a result is less than a certain threshold. Likewise, you could color the background of a cell based on the result of a formula.
To take advantage of conditional formatting, follow these steps:
Figure 1. The Conditional Formatting dialog box.
Figure 2. The Format Cells dialog box.
If you are using Excel 95 you don't have access to conditional formatting. You can, however, create your own custom formats for cells so that you can change at least the text color of a result if it falls outside a specific bound. For example, let's say you wanted to have your result appear in red text if it is less than 100. You could do this in the following manner:
Figure 3. The Number tab from the Cells option of the Format menu.
You should note that this approach is a quick solution for conditional formatting using custom number formats, and that there is much more to successfully applying such formats. You can find out more by referring to Excel's on-line help system, which does a pretty good job discussing custom number formats. You can also use custom number formats like this not just in Excel 95, but in all subsequent versions of Excel, as well.
It is possible to get very creative with conditional formatting. However, it is not the answer to every formatting need. If you want to be even more creative (you know--bordering on outlandish), you can always develop a macro that will examine all the cells in your sheet or a specific range of cells you select and then change formatting in any way you wish.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2665) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Change Formatting Based On Your Data! Conditional formatting provides a way for you to adjust the appearance of your data based on the data itself. Discover how to put this amazingly powerful feature to work for you, today. This comprehensive volume is available in two editions. Check out Excel Conditional Formatting today!