{"id":300872,"date":"2021-07-16T18:00:00","date_gmt":"2021-07-16T15:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel\/"},"modified":"2021-07-16T18:00:00","modified_gmt":"2021-07-16T15:00:00","slug":"how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel\/","title":{"rendered":"#How to Highlight Top- or Bottom-Ranked Values in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a246d0fa10e3\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a246d0fa10e3\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel\/#Apply_a_Quick_Conditional_Formatting_Ranking_Rule\" >Apply a Quick Conditional Formatting Ranking Rule<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel\/#Create_a_Custom_Conditional_Formatting_Ranking_Rule\" >Create a Custom Conditional Formatting Ranking Rule<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Highlight Top- or Bottom-Ranked Values in Microsoft Excel&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel Logo\" width=\"1200\" height=\"675\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Automatically highlighting data in your spreadsheets makes reviewing your most useful data points a cinch. So if you want to view your top- or bottom-ranked values, conditional formatting in Microsoft Excel can make that data pop.<\/p>\n<p>Maybe you use Excel to track your sales team\u2019s numbers, your students\u2019 grades, your store location sales, or your family of website\u2019s traffic. You can make informed decisions by seeing which rank at the top of the group or which fall to the bottom. These are ideal cases in which to use conditional formatting to call out those rankings automatically.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Apply_a_Quick_Conditional_Formatting_Ranking_Rule\"><\/span>Apply a Quick Conditional Formatting Ranking Rule<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Excel offers a few ranking rules for conditional formatting that you can <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly in just a couple of clicks. These include highlighting cells that rank in the top or bottom 10% or the top or bottom 10 items.<\/p>\n<p>Select the cells that you want to apply the formatting to by clicking and dragging through them. Then, go to the Home tab and over to the Styles section of the ribbon.<\/p>\n<p>Click \u201cConditional Formatting\u201d and move your cursor to \u201cTop\/Bottom Rules.\u201d You\u2019ll see the above-mentioned four rules at the top of the pop-out menu. Select the one that you want to use. For this example, we\u2019ll choose the Top 10 Items.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734673\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/HomeConditionalFormattingTopBottomRules-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"On the Home tab, click Conditional Formatting for Top or Bottom Rules\" width=\"637\" height=\"500\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Excel im<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/social-mediaa\/\" data-internallinksmanager029f6b8e52c=\"1\" title=\"Social Media\" target=\"_blank\" rel=\"noopener\">media<\/a>tely applies the default number (10) and formatting (light red fill with dark red text). However, you can change either or both of these defaults in the pop-up window that appears.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-734672 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/ConditionalFormattingTopDefaultRule-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Conditional formatting defaults for Top 10 Items in Excel\" width=\"600\" height=\"465\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the left, use the arrows or type in a number if you want something other than 10. On the right, use the drop-down list to choose a different format.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734671\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/ChangeConditionalFormattingTopDefaultRule-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click the drop-down to choose a different format\" width=\"351\" height=\"232\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click \u201cOK\u201d when you finish, and the formatting will be applied.<\/p>\n<p>Here, you can see that we\u2019re highlighting the top five items in yellow.\u00a0Since two cells (B5 and B9) contain the same value, both are highlighted.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-734674 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/ConditionalFormattingTopYellowRule-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Conditional formatting top 5 items in yellow in Excel\" width=\"600\" height=\"465\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>With any of these four quick rules, you can adjust the number, percentage, and formatting as needed.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Create_a_Custom_Conditional_Formatting_Ranking_Rule\"><\/span>Create a Custom Conditional Formatting Ranking Rule<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>While the built-in ranking rules are handy, you might want to go a step further with your formatting. One way to do this is to select \u201cCustom Format\u201d in the above drop-down list to open the Format Cells window.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734676\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/HomeConditionalFormattingTopBottomRulesCustom-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Custom Format\" width=\"351\" height=\"234\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Another way is to use the New Rule feature. Select the cells that you want to format, head to the Home tab, and click \u201cConditional Formatting.\u201d This time, choose \u201cNew Rule.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734677\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/HomeConditionalFormattingNewTopBottomRule-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"On the Home tab, click Conditional Formatting, New Rule\" width=\"611\" height=\"489\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When the New Formatting Rule window opens, select \u201cFormat Only Top or Bottom Ranked Values\u201d from the rule types.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734682\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/FormatOnlyTopBottomRankedValues-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Pick Format Only Top or Bottom Ranked Values\" width=\"650\" height=\"402\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>At the bottom of the window is a section for Edit the Rule Description. This is where you\u2019ll set up your number or percentage and then select the formatting.<\/p>\n<p>In the first drop-down list, pick either Top or Bottom. In the next box, enter the number that you want to use. If you want to use percentage, mark the checkbox to the right. For this example, we want to highlight the bottom 25%.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734681\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/FormatValuesThatRankInThe-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Pick Top or Bottom and enter the value\" width=\"650\" height=\"403\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click \u201cFormat\u201d to open the Format Cells window. Then, use the tabs at the top to choose Font, Border, or Fill formatting. You can apply more than one format if you like. Here, we\u2019ll use an italic font, a dark cell border, and a yellow fill color.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734680\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/FormatValuesThatRankFormatOptions-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select the formatting\" width=\"502\" height=\"500\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click \u201cOK\u201d and check out the preview of how your cells will appear. If you\u2019re good, click \u201cOK\u201d to apply the rule.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734685\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/FormatValuesThatRankInThePreview-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Review the conditional formatting preview and click OK\" width=\"650\" height=\"403\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll then see your cells immediately update with the formatting that you selected for the top- or bottom-ranked items. Again, for our example, we have the bottom 25%.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-734684 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/ConditionalFormattingBottomYellowRule-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Conditional formatting bottom 25 percent in yellow in Excel\" width=\"233\" height=\"475\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you\u2019re interested in trying out other conditional formatting rules, take a look at how to create progress bars in Microsoft Excel using the handy feature!<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/734670\/how-to-highlight-top-or-bottom-ranked-values-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Highlight Top- or Bottom-Ranked Values in Microsoft Excel&#8221; Automatically highlighting data in your spreadsheets makes reviewing your most useful data points a cinch. So if you want to view your top- or bottom-ranked values, conditional formatting in Microsoft Excel can make that data pop. Maybe you use Excel to track your sales team\u2019s&#8230;<\/p>\n","protected":false},"author":1,"featured_media":300873,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-300872","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/300872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=300872"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/300872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/300873"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=300872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=300872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=300872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}