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.
In the popup window, you will see some default formats in the dropdown 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 chose.
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”.
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 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.
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.
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”.
You will then see your cells highlighted based on your choices.
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.
