{"id":299586,"date":"2021-07-14T18:00:00","date_gmt":"2021-07-14T15:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-apply-a-color-scale-based-on-values-in-microsoft-excel\/"},"modified":"2021-07-14T18:00:00","modified_gmt":"2021-07-14T15:00:00","slug":"how-to-apply-a-color-scale-based-on-values-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-apply-a-color-scale-based-on-values-in-microsoft-excel\/","title":{"rendered":"#How to Apply a Color Scale Based on Values in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 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-6a407a6c6d573\" 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-6a407a6c6d573\" 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-apply-a-color-scale-based-on-values-in-microsoft-excel\/#Apply_a_Quick_Conditional_Formatting_Color_Scale\" >Apply a Quick Conditional Formatting Color Scale<\/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-apply-a-color-scale-based-on-values-in-microsoft-excel\/#Create_a_Custom_Conditional_Formatting_Color_Scale\" >Create a Custom Conditional Formatting Color Scale<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to <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 a Color Scale Based on 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>We see color scales representing all sorts of things: temperatures, speed, ages, and even population. If you have data in Microsoft Excel that could benefit from this type of visual, it\u2019s easier to implement than you might think.<\/p>\n<p>With conditional formatting, you can apply a gradient color scale in just minutes. Excel offers two- and three-color scales with primary colors that you can select from, along with the option to pick your own unique colors.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Apply_a_Quick_Conditional_Formatting_Color_Scale\"><\/span>Apply a Quick Conditional Formatting Color Scale<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Microsoft Excel provides you with several conditional formatting rules for color scales that you can apply with a quick click. These include six two-color scales and six three-color scales.<\/p>\n<p>Select the cells that you want to apply the formatting to by clicking and dragging through them. Then, head to the Styles section of the ribbon on the Home tab.<\/p>\n<p>Click \u201cConditional Formatting\u201d and move your cursor to \u201cColor Scales.\u201d You\u2019ll see all 12 options in the pop-out menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734941\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/HomeConditionalFormattingColorScales-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"On the Home tab, click Conditional Formatting, Color Scales\" width=\"430\" height=\"470\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>As you hover your cursor over each one, you can see the arrangement of the colors in a screen tip. Plus, you\u2019ll see the cells that you\u2019ve selected highlighted with each option. This gives you a terrific way to select the color scale that best fits your data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734940\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/ConditionalFormattingColorScalesPreviews-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Color Scales previews in Excel\" width=\"650\" height=\"270\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When you land on the scale that you want to use, simply click it. And that\u2019s all there is to it! You\u2019ve just applied a color scale to your data in a few clicks.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Create_a_Custom_Conditional_Formatting_Color_Scale\"><\/span>Create a Custom Conditional Formatting Color Scale<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If one of the quick rules above doesn\u2019t quite capture how you want your color scale to work, you can create a custom conditional formatting rule.<\/p>\n<p>Select the cells that you want to apply the scale to, go to the Home tab, and choose \u201cNew Rule\u201d from the Conditional Formatting drop-down list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734943\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/HomeConditionalFormattingNewRuleScales-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"On the Home tab, click Conditional Formatting, New Rule\" width=\"264\" height=\"467\" 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 All Cells Based on Their Values\u201d at the top.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734942\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/NewRuleScalesFormatBasedOnValues-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Pick Format All Cells Based on Their Values\" width=\"650\" height=\"446\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The Edit the Rule Description section at the bottom of the window is where you\u2019ll spend a bit of time customizing the rule. Start by choosing 2-Color Scale or 3-Color Scale from the Format Style drop-down list.<\/p>\n<p>The main difference between these two styles is that the three-color scale has a midpoint, whereas the two-color scale only has minimum and maximum values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734944\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/NewRule2-3-ColorScales-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"2-Color and 3-Color Scale setup\" width=\"650\" height=\"225\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>After selecting the color scale style, choose the Minimum, Maximum, and optionally, the Midpoint using the Types drop-down lists. You can pick from Lowest\/Highest Value, Number, Percent, Formula, or Percentile.<\/p>\n<p>The Lowest Value and Highest Value types are based on the data in your selected range of cells, so you don\u2019t have to enter anything in the Value boxes. For all other types, including Midpoint, enter the Values in the corresponding boxes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734946\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/3ColorScaleType-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select the Type for the color scale\" width=\"650\" height=\"413\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Finally, click the Color drop-down buttons to select your colors from the palettes. If you want to use custom colors, select \u201cMore Colors\u201d to add them using RGB values or Hex codes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734949\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/3ColorScaleColor-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose the colors for the scale\" width=\"650\" height=\"446\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll then see a preview of your color scale at the bottom of the window. If you\u2019re happy with the result, click \u201cOK\u201d to apply the conditional formatting to your cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734947\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/New3ColorScaleRuleWithData-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"3-Color scale conditional formatting in Excel\" width=\"650\" height=\"225\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The nice thing about a conditional formatting rule like this is that if you edit your data, the color scale will automatically update to accommodate the change.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-734948\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/3ColorScaleEditData-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"The color scale changes based on data edits\" width=\"535\" height=\"145\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For a similar way to display your Excel data, consider using the Data Bars conditional formatting rule to create a progress bar.<\/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\/734938\/how-to-apply-a-color-scale-based-on-values-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Apply a Color Scale Based on Values in Microsoft Excel&#8221; We see color scales representing all sorts of things: temperatures, speed, ages, and even population. If you have data in Microsoft Excel that could benefit from this type of visual, it\u2019s easier to implement than you might think. With conditional formatting, you can&#8230;<\/p>\n","protected":false},"author":1,"featured_media":299587,"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-299586","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\/299586","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=299586"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/299586\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/299587"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=299586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=299586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=299586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}