How to Highlight Values Above or Below Average in Excel

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.

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

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

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.

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

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.

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.

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”.

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

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.