Mastering Data Analysis with Excel: Techniques for Spotting Anomalous Highs and Lows

Quick Links

• Set Up a Quick Conditional Formatting Rule
• Create a More Detailed Formatting Rule

If you want to find numbers that are above or below average, you don’t have to calculate that average and then review the values for those higher or lower. With conditional formatting in Excel, you can automatically highlight those numbers.

Along with a quick way to highlight values above or below average, you can go a step further and see those that are equal to and higher or equal to and lower. Depending on what you want to display, let’s look at how to use conditional formatting in Microsoft Excel for averages.

Set Up a Quick Conditional Formatting Rule

Open the sheet in Excel you want to work with and select the cells you want to use. You can choose a column, row, cell range, or non-adjacent cells.

Go to the Home tab and click the Conditional Formatting drop-down arrow in the Styles section of the ribbon. Move your cursor to Top/Bottom Rules and you’ll see Above Average and Below Average in the pop-out menu. Pick the one you want to use.

In the pop-up window, you’ll see a few default formats in the drop-down list. Select the one you want and click “OK.”

You’ll then see the cells that apply to the rule you set formatted with the option you picked.

Create a More Detailed Formatting Rule

The quick formatting rule above is handy for a fast way to see numbers above or below average. But you may want more detail or different formatting. By creating a rule from scratch, you can tailor it to fit your needs.

Related: How to Apply a Color Scale Based on Values in Microsoft Excel

Select the cells and go to the Home tab. Click the Conditional Formatting drop-down arrow and select “New Rule.”

At the top of the pop-up window, choose Format Only Values That Are Above or Below Average.

At the bottom of the window, use the Format Values That Are drop-down menu to select the average you want to highlight. You can pick from options like above, below, equal to or above, equal to or below, or use a standard deviation.

Click here

Select “Format” and then choose how you want to highlight those cells. You can use the tab for Font to pick a font color, style, or format or use the Fill tab to pick a fill color or pattern for the cells. You can also use a combination of formats if you like. Click “OK” when you finish.

You’ll then be back to the Formatting Rule window where you’ll see the Preview of the formatting you just selected. If you’re happy with the rule, pick “OK.”

You’ll then see your cells highlighted per your choices.

You can apply more than one rule to the same set of cells if you wish. For instance, you may have one rule to highlight cells above average using one format and another for cells below average using a different format.

Related: How to Manage Conditional Formatting Rules in Microsoft Excel

Conditional formatting in Excel is a convenient way to make other types of values stand out too. You can use it to highlight top- or bottom-ranked values , spot certain dates , or find duplicates in your sheet .

Also read: