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 check the scores for higher or lower values. With conditional formatting in Excel, you can automatically highlight these numbers.

As well as a quick way to highlight values ​​above or below average, you can take it a step further and show those that are equal and above or equal and below. Depending on what you want to display, let’s look at how to use Microsoft Excel conditional formatting for averages.

Set up a quick conditional formatting rule

In Excel, open the sheet you want to work with and select the cells you want to use. You can select a column, row, range of cells, or nonadjacent cells.

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

Top and bottom rules in the Conditional Formatting menu

In the pop-up window, you will see some default formats in the drop-down list. Select the one you want and click OK.

Formatting options for a quick rule

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

Conditional formatting for numbers above average

Create a more detailed formatting rule

The quick formatting rule above comes in handy for quickly seeing numbers above or below the average. However, you may want more detail or different formatting. By creating a rule from scratch, you can customize it to suit your needs.

TIED TOGETHER: 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 choose New Rule.

New rule in the Conditional Formatting menu

At the top of the pop-up window, select the “Format only values ​​above or below average” option.

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 or above, equal or below, or use a standard deviation.

Values ​​to apply to the new rule

Select “Format” and then choose how you want to highlight these 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 fill pattern for the cells. You can also use a combination of formats if you like. Click “OK” when finished.

Font and fill formatting

You will then return to the Formatting Rules window, where you will see the preview of the formatting you just selected. If you are happy with the rule, select “OK”.

Rule preview for conditional formatting

You will then see your cells highlighted according to your selection.

Conditional formatting for numbers above average

You can apply more than one rule to the same set of cells if needed. For example, you can have one rule to highlight cells with one format above average and another for cells with a different format that are below average.

TIED TOGETHER: 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 high or low ranked values, identify specific dates or find duplicates in your sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *