How to highlight values ​​above or below average in Excel

Microsoft Excel logo on a green background

If you want to find numbers that are above or below the average, you don’t have to calculate that average and then review the values ​​of those that are above or below. 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 one step further and see those that are equal and greater or equal and less. Depending on what you want to show, let’s see 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 dropdown arrow in the Styles section of the ribbon. Move your cursor to Over/Under Rules and you’ll see Above Average and Below Average in the pop-up menu. Choose the one you want to use.

Top and bottom rules in the Conditional Formatting menu

In the popup window, you will see some default formats in the dropdown list. Select the one you want and click “OK”.

Format options for a quick ruler

You will then see the cells that apply to the rule you set formatted with the option you chose.

Conditional formatting for numbers above average

Create a more detailed formatting rule

The quick format rule above is useful for quickly seeing numbers above or below average. But you may want more details or a different format. By creating a rule from scratch, you can adapt it to your needs.

RELATED: How to apply a value-based color scale in Microsoft Excel

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

New rule in Conditional Formatting menu

At the top of the pop-up window, choose Format only values ​​that are above or below average.

Conditional formatting rule above or below average

At the bottom of the window, use the Format values ​​that are dropdown menu to select the average you want to highlight. You can choose from options such as above, below, equal to or greater than, equal to or less than, or use a standard deviation.

Values ​​to apply to the new rule

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

Font formatting and padding

You will then return to the Format Ruler window where you will see the preview of the format you just selected. If you are satisfied with the rule, select “OK”.

Conditional formatting rule preview

You will then see your cells highlighted based on your choices.

Conditional formatting for numbers above average

You can apply more than one rule to the same set of cells if you want. For example, you might have one rule for highlighting 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 highlight other types of values ​​as well. You can use it to highlight top or bottom ranking values, detect certain dates, or find duplicates in your sheet.

Leave a Comment