Microsoft Excel 2007 - Conditional Formatting
Highlight Data That Meets Certain Criteria
Help your readers to visualise your numerical data by employing conditional formatting.
What Is Conditional Formatting?
Conditional formatting makes your spreadsheets respond to the data they contain and is a useful tool for visualising numerical data. You can draw attention to particular cells when their contents satisfy certain conditions, by making those cells display differently. To apply a conditional formatting rule to a cell or range of cells, select the cells and then click Home > Styles > Conditional Formatting. There are a variety of different rules you can apply and we'll just run through them here:
Highlight Cell Rules
Examples of this kind of rule are that data will be highlighted if it is greater than a certain value, falls within a certain range or contains a particular text string.
Top Bottom Rules
Examples include cells that are in the top 10, bottom 20% and data that is above or below average.
Displays a graph in the form of data bars that visually represent the data's relative size.
Applies a background colour to a cell based on the relative size of that cell's data.
Icons are displayed in a cell, which are dependent on the cell's value.
This allows you to explicitly specify your own logic for a rule.
Deletes all conditional formatting rules that had previously been applied to the selected cells.
Displays the Conditional Formatting Rules Manager dialog box, which allows you to create new rules and also edit and delete existing rules.
A Simple Example
Let's use a simple example to put our conditional formatting prowess to the test. First of all let's generate some random numbers by typing =RAND() * 5000 in cell A1 and then pressing enter. That's one random number right there. Now hover over the bottom right corner of the cell until the cursor changes to a little '+' and drag that handle to the right. On releasing the mouse, that formula is applied to the selected cells and they all now contain random numbers between 0 and 5000. Now drag that handle downwards to cover more rows and generate more randowm numbers.
With those cells still selected, we can now apply some conditional formatting by clicking Home > Styles > Conditional Formatting > Color Scales. Choose a colour scheme and note that the colours at the top of the scale will represent the higher values in your cells whereas the colours at the bottom of the scale will represent the smaller values in your cells. When the formatting is applied, the background colour of each cell represents how big that cell's random number is.
If you like, press ctrl-z to undo the formatting and experiment with the other formatting rules available.
Excel 2007 Topics
- Excel Spreadsheets
- Navigating Excel 2007
- Excel Tables
- Excel Pivot Tables
- Formulas In Excel
- Document Themes
- Conditional Formatting
- Naming Cells
- Protecting Workbooks
- VBA Excel
- Excel Download
- Microsoft Office 2010
- Excel Password
- Excel Merge Columns
- Excel Macro
- Excel Shortcut
- Freeze Panes
- Excel Pie Chart