Software

Use Excel 2007's Top/Bottom rules for quick data analysis

Make crucial data stand out in your spreadsheet by having Excel automatically mark particular ranges in color using conditional formatting rules. Here's how to set it up.

You can use Excel 2007's data visualization feature to keep tabs on when the data in a worksheet attain a certain value. For example, to discover which regions have achieved above-average sales this quarter, follow these steps:

  1. Select the cells you want to analyze.
  2. Click the Home tab.
  3. In the Styles group, click Conditional Formatting, then select Top/Bottom Rules.
  4. Click Above Average, then click OK.

Now all cells with above-average sales values will appear marked in red. To find out who the top 10% of your performers are, follow these steps:

  1. Select the columns of cells you want to analyze.
  2. Click the Home tab.
  3. In the Styles group, click Conditional Formatting and then click Top 10%.
  4. Click the drop-down arrow in the With box and select Custom Format.
  5. Click the Fill tab and select Yellow under Background Color.
  6. Click OK twice.

Now all values in the upper 10% of the range appear highlighted in yellow. You can set an entire spreadsheet with conditional formatting rules and watch the results change each time you enter new data.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox