{"id":399103,"date":"2022-01-26T00:00:00","date_gmt":"2022-01-25T21:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-count-colored-cells-in-microsoft-excel\/"},"modified":"2022-01-26T00:00:00","modified_gmt":"2022-01-25T21:00:00","slug":"how-to-count-colored-cells-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-count-colored-cells-in-microsoft-excel\/","title":{"rendered":"#How to Count Colored Cells 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-6a24966d65bb6\" 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-6a24966d65bb6\" 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-count-colored-cells-in-microsoft-excel\/#Count_Colored_Cells_Using_Find\" >Count Colored Cells Using Find<\/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-count-colored-cells-in-microsoft-excel\/#Count_Colored_Cells_Using_a_Filter\" >Count Colored Cells Using a Filter<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Count Colored Cells in Microsoft Excel&#8221;<\/strong><\/p>\n<div>\n<!-- UNCACHED CONTENT --><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" data-pagespeed-lazy-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\" data-pagespeed-lazy-src=\"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\" alt=\"Microsoft Excel Logo\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Using color in Microsoft Excel can be a terrific way to make data stand out. So if a time comes when you want to count the number of cells you\u2019ve colored, you have a couple of ways to do it.<\/p>\n<p>Maybe you have cells colored for sales amounts, product numbers, zip codes, or something similar. Whether you\u2019ve manually used color to highlight cells or their text or you\u2019ve\u00a0set up a conditional formatting rule to do so, the following two ways to count those cells work great.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Colored_Cells_Using_Find\"><\/span>Count Colored Cells Using Find<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This first method for counting colored cells is the quickest of the two. It doesn\u2019t involve inserting a function or formula, so the count will simply be displayed for you to see and record manually if you wish.<\/p>\n<p>Select the cells you want to work with and head to the Home tab. In the Editing section of the ribbon, click \u201cFind &amp; Select\u201d and choose \u201cFind.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739588\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/HomeFindSelectFind-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Find &amp; Select, then Find\" width=\"342\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When the Find and Replace window opens, click \u201cOptions.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739590\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/FindReplaceOptions-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Options\" width=\"650\" height=\"244\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>Known formatting<\/strong>: If you know the precise formatting you used for the colored cells, for example, a specific green fill, click \u201cFormat.\u201d Then use the Font, Border, and Fill tabs in the Find Format window to select the color format and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739591\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/FindFormatColorGreen-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Find Format window, select formatting\" width=\"582\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>Unknown formatting<\/strong>: If you\u2019re not sure of the exact color or used multiple format forms like a fill color, border, and font color, you can take a different route. Click the arrow next to the Format button and select \u201cChoose Format From Cell.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739593\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/FindReplaceChooseFormatCells-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Choose Format From Cell\" width=\"650\" height=\"250\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When your cursor transforms to an eyedropper, move it to one of the cells you want to count and click. This will put the formatting for that cell into the preview.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739592\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/EyeDropperPreviewFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click the cell to obtain the format\" width=\"633\" height=\"473\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Using either of the above two ways to enter the format you\u2019re looking for, you should next check out your preview. If it looks correct, click \u201cFind All\u201d at the bottom of the window.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-739595 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/FindAllColorGreen-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Find All\" width=\"650\" height=\"304\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When the window expands to display your results, you\u2019ll see the count on the bottom left as \u201cX Cell(s) Found.\u201d And there\u2019s your count!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739594\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ColorGreenCellsFound-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Number of cells found\" width=\"650\" height=\"401\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also review the exact cells in the bottom portion of the window, just above the cell count.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Colored_Cells_Using_a_Filter\"><\/span>Count Colored Cells Using a Filter<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you plan to adjust the data over time and want to keep a cell dedicated to your colored cell count, this second method is for you. You\u2019ll use a combination of a function and a filter.<\/p>\n<p>Let\u2019s start by adding the function, which is SUBTOTAL. Go to the cell where you want to display your count. Enter the following, replacing the A2:A19 references with those for your own range of cells, and hit Enter.<\/p>\n<pre>=SUBTOTAL(102,A2:A19)<\/pre>\n<p>The number 102 in the formula is the numerical indicator for the COUNT function.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> For other function numbers you can use with SUBTOTAL, take a look at the table on <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/support.microsoft.com\/en-us\/office\/subtotal-function-7b027003-f060-4ade-9040-e478765b9939\">Microsoft\u2019s support page for the function<\/a>.<\/p><\/blockquote>\n<p>As a quick check to make sure you entered the function correctly, you should see a count of all cells with data as the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739596\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/SubtotalFunctionCheck-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Check the Subtotal function\" width=\"420\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Now it\u2019s time 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 the filter feature to your cells. Select your column header and go to the Home tab. Click \u201cSort &amp; Filter\u201d and choose \u201cFilter.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739597\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/HomeSortFilterFilter-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"On the Home tab, click Sort &amp; Filter, Filter\" width=\"255\" height=\"325\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>This places a filter button (arrow) next to each column header. Click the one for the column of colored cells you want to count and move your cursor to \u201cFilter by Color.\u201d You\u2019ll see the colors you\u2019re using in a pop-out menu, so click the color you want to count.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739599\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/FilterByCellColorGreen-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Move to Filter by Color and pick a color\" width=\"396\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> If you use font color instead of or in addition to cell color, those options will display in the pop-out menu.<\/p><\/blockquote>\n<p>When you look at your subtotal cell, you should see the count change to only those cells for the color you selected. And you can pop right back up to the filter button and choose a different color in the pop-out menu to quickly see those counts too.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739601\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/CountedColoredCells-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Examples of counts for colored cells\" width=\"520\" height=\"389\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>After you finish getting counts with the filter, you can clear it to see all of your data again. Click the filter button and choose \u201cClear Filter From.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-739600\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ClearFilterCountColoredCells-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose Clear Filter From\" width=\"333\" height=\"270\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you\u2019re already taking advantage of colors in Microsoft Excel, those colors can come in handy for more than just making data stand out.<\/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\/739579\/how-to-count-colored-cells-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Count Colored Cells in Microsoft Excel&#8221; Using color in Microsoft Excel can be a terrific way to make data stand out. So if a time comes when you want to count the number of cells you\u2019ve colored, you have a couple of ways to do it. Maybe you have cells colored for sales&#8230;<\/p>\n","protected":false},"author":1,"featured_media":399104,"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-399103","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\/399103","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=399103"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/399103\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/399104"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=399103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=399103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=399103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}