Conditional Formatting to Find Top 5 Numbers

by on May 2, 2017

Conditional formatting

In the previous article, we explore the way how to use conditional formatting to highlight a cell value based upon a single condition. If you have not read it yet, you can do so by clicking here.

In this article, you will learn how to use conditional formatting to find Top 5 Numbers in a list. Based on your requirement, you can increase/decrease the top numbers. The list can be of Sales, student marks, Expense list etc. In our case, it’s a daily sales report (image provided below) and our objective is to find out top 5 daily sales (amount wise) during the month of March for our detail analysis later on.

Step by step procedure to arrive at our stated objective:

Step 1: Select the cells containing sales. Go to home tab, click Conditional Formatting, go to the 2nd option which is “Top/Bottom Rules” and then select the “Top 10 Items” option. For your easy guidance screenshot is provided below.

Step 2: you will now have the below screen after selecting the above option. By default, it will Format the top 10. Write 5 in the box “Format cells that rank in the Top” and select “Custom Format” from the next box. Select Blue Color and click Ok on both the screens.

Conditional Formatting to Find Top 5 Numbers

Conditional Formatting to Find Top 5 Numbers

The final result is presented by the screen below:

Conditional Formatting to Find Top 5 Numbers

Conditional Formatting to Find Top 5 Numbers

And through the same procedure, you can also format the cells for Bottom 5 sales.

To Download the Excel file, please click here.

Please follow and like us:
0

Leave a Reply

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