Excel: Compare cells with top or bottom values


Excel compare: YOUR OBJECTIVE

You have created an Excel data table and want to analyze the records that are below the average value.

 

In this learning module “ Excel compare”you will learn how to highlight cells where the value is above or below the average value using Conditional Formatting and what you have to pay attention to when doing this.

 

Excel compare:HOW DOES IT WORK?

The Conditional Formatting function allows you to visually highlight all cells where the value meets a particular condition. This allows you to show which cells within a cell range contain the highest or lowest value, or what percentage of the cells contain the highest or lowest value and which cells are above or below the average value of the particular cell range.

In the example below, all cells in the Occupancy rate column that are below the average value will be highlighted in red. First select the cell range that you want to highlight by using the conditional formatting:

 

Then click the  button in the Styles group on the Home tab to open the following drop-down menu: 

 

Then select thecommand:

 

A submenu opens:

 

This option allows you to highlight the top ten values within the selected cell range by default. You can change the number of top values from 1 to 9999 in the corresponding dialog box.

This option highlights the 10% of all the selected cells that contain the top values. This percentage value can be changed from 1 to 9999% in the corresponding dialog box.

This option allows you to highlight the bottom ten values within the selected range of cells by default. You can change the number of bottom values from 1 to 9999 in the corresponding dialog box.

This option highlights the 10% of all selected cells that contain the bottom values. This percentage value can be changed from 1% to 9999% in the corresponding dialog box.

This option will format all cells within the selected cell range where the value is above the average value.

This option will format all cells within the selected cell range where the value is below the average value.

In the example below, highlight all cells where the value is below the average.

Select the option:

 

This opens the Below Average dialog box:

 

All cells will be formatted in red text and light red background by default. Clicking the arrow button opens a drop-down menu, that apart from six different formatting suggestions, also provides an option that allows you to change the appearance of the cells according to a Custom Format… in terms of numbers, fonts, borders and fill.

 

 

In the live preview see how your format will affect the selected cell or range of cells:

 

Excel compare: HINT

If you do not like either one of the six formatting suggestions, select the Custom Format… option at the bottom of the drop-down menu which allows you to specify the appearance of numbers, font, borders and fill.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen