How you can Spotlight Best- or Backside-Ranked Values in Microsoft Excel


Routinely highlighting information for your spreadsheets makes reviewing your most dear information issues a cinch. So if you wish to view your top- or bottom-ranked values, conditional formatting in Microsoft Excel could make that information pop.

Perhaps you employ Excel to trace your gross sales staff’s numbers, your scholars’ grades, your retailer location gross sales, or your circle of relatives of site’s visitors. You’ll make knowledgeable choices by way of seeing which rank on the peak of the crowd or which fall to the ground. Those are ultimate circumstances through which to use conditional formatting to name out the ones scores robotically.

Observe a Fast Conditional Formatting Rating Rule

Excel provides a couple of rating regulations for conditional formatting that you’ll be able to observe in simply a few clicks. Those come with highlighting cells that rank within the peak or backside 10% or the end or backside 10 pieces.

Make a selection the cells that you need to use the formatting to by way of clicking and dragging thru them. Then, cross to the House tab and over to the Types segment of the ribbon.

Click on “Conditional Formatting” and transfer your cursor to “Best/Backside Laws.” You’ll see the above-mentioned four regulations on the peak of the pop-out menu. Make a selection the one that you need to make use of. For this situation, we’ll make a selection the Best 10 Pieces.



Excel right away applies the default quantity (10) and formatting (mild purple fill with darkish purple textual content). Then again, you’ll be able to exchange both or either one of those defaults within the pop-up window that looks.


At the left, use the arrows or sort in a bunch if you need one thing rather than 10. At the proper, use the drop-down listing to select a special structure.

Click the drop-down to choose a different format

Click on “OK” whilst you end, and the formatting might be implemented.

Right here, you’ll be able to see that we’re highlighting the end five pieces in yellow. Since two cells (B5 and B9) comprise the similar worth, each are highlighted.

Conditional formatting top 5 items in yellow in Excel

With any of those four fast regulations, you’ll be able to modify the quantity, proportion, and formatting as wanted.

Create a Customized Conditional Formatting Rating Rule

Whilst the integrated rating regulations are to hand, you may need to cross a step additional along with your formatting. A method to do that is to make a choice “Customized Structure” within the above drop-down listing to open the Structure Cells window.



Differently is to make use of the New Rule function. Make a selection the cells that you need to structure, head to the House tab, and click on “Conditional Formatting.” This time, make a selection “New Rule.”

On the Home tab, click Conditional Formatting, New Rule

When the New Formatting Rule window opens, make a choice “Structure Most effective Best or Backside Ranked Values” from the rule of thumb varieties.

Pick Format Only Top or Bottom Ranked Values

On the backside of the window is a bit for Edit the Rule Description. That is the place you’ll arrange your quantity or proportion after which make a choice the formatting.

Within the first drop-down listing, pick out both Best or Backside. Within the subsequent field, input the quantity that you need to make use of. If you wish to use proportion, mark the checkbox to the correct. For this situation, we need to spotlight the ground 25%.

Pick Top or Bottom and enter the value

Click on “Structure” to open the Structure Cells window. Then, use the tabs on the peak to select Font, Border, or Fill formatting. You’ll observe greater than one structure in case you like. Right here, we’ll use an italic font, a gloomy mobile border, and a yellow fill colour.

Select the formatting

Click on “OK” and take a look at the preview of the way your cells will seem. For those who’re excellent, click on “OK” to use the rule of thumb.

Review the conditional formatting preview and click OK


You’ll then see your cells right away replace with the formatting that you simply decided on for the top- or bottom-ranked pieces. Once more, for our instance, we’ve got the ground 25%.

Conditional formatting bottom 25 percent in yellow in Excel

For those who’re all in favour of testing different conditional formatting regulations, check out find out how to create development bars in Microsoft Excel the use of the to hand function!