How to highlight/conditional formatting top n values in Excel

In Excel, conditional formatting is a feature that allows users to apply formatting to cells based on specific conditions or criteria. By using conditional formatting, the user can color, highlight, or format cells dynamically, that depends upon the values, formulas, or rules the user defines.

This feature is useful for some data points, patterns, or trends in the Excel worksheets, this makes the data easier to interpret and analyze the data. Instead of manually scanning through cells or applying to format individually. This feature allows the user to automate the process and apply formatting dynamically as the data changes.

Benefits of formatting data include

• Validation of data to make sure that the formatting data includes the integrity and validated data only. It ensures that the data should be free from all inconsistency.

• It also helps in cleaning the data, by highlighting all the available redundant data values. Using conditional formatting allows users to set values properly according to their requirements.

• Consolidating data by merging it with different sources, identifying the overlapping records, and making an informed decision about data.

• Data analysis includes generating useful insights from the available data.

To understand more precisely, assume that there is a class of 60 students and in the class, the user wants to calculate the top 3 students with the highest marks. The same work can be done within minutes by simply using the top n rows option, very easily and effectively. This article illustrates the two common examples to clarify the task briefly to the user.

Example 1: To determine the top n number of rows available in excel.

Step 1

In this example, the user will learn the process of applying conditional formatting to any number of values. To do so, firstly consider a spreadsheet, with two columns. The first column contains data to store the product name, while the second column contains the quantity value. A snapshot of the same is provided below:

Step 2

Go to the “Home” tab, and then click on the “Conditional Formatting” option, and then “Top/Bottom Rules”. After that select the first option “Top 10 items…”. A snapshot for the same is provided below:

Step 3

The above step will open a “Top 10 items” dialog box. This dialog box, allows the user to set the number of records in the first input field. As for this case will try to find the solution for the first 5 rows, therefore, set 5 to the position, and in the with section set any color value according to requirement. Here, will be using the option “Yellow fill with the dark yellow text”. Finally, click on the “OK” button. A snapshot of the same is provided below:

Step 4

After clicking on the “OK” button. the top 5 records will be displayed on the console without any issue.

Example 2: To highlight the bottom 10 rows available in Excel.

Step 1

In this example, also will be using the same excel sheet. The first column stores the product name, and second column stores the quantity value.

Step 2

In this step, will select the “Home” tab, and then click on the “Conditional Formatting” option. Further, select the “Top/Bottom rules”, and then select the “Bottom 10 items…”. A snapshot of the same is provided below:

Step 3

The above step will display the “Bottom 10 Items” dialog box. In this dialog box, select the option value as 5, and in with the label, select the value as “Light Red Fill with Dark Red Text”, and finally click on the “OK” button. snapshot for reference is provided below:

Step 4

The final highlighted values are given below:

Conclusion

This article contains a stepwise explanation for all the guided steps. The first and most important benefit is that any number of top values can be created without any extra overhead, processing data, and generating result is a minute task if the user is clear about the data implementation.