{"id":1891,"date":"2022-08-07T09:48:43","date_gmt":"2022-08-07T06:48:43","guid":{"rendered":"https:\/\/www.addictivetips.com\/?p=337935https:\/\/www.addictivetips.com\/microsoft-office\/skip-conditional-formatting-blank-cells-in-microsoft-excel\/"},"modified":"2022-08-07T09:48:43","modified_gmt":"2022-08-07T06:48:43","slug":"how-to-skip-conditional-formatting-blank-cells-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/tojikon.net\/en\/1891-how-to-skip-conditional-formatting-blank-cells-in-microsoft-excel\/","title":{"rendered":"How to skip conditional formatting blank cells in Microsoft Excel"},"content":{"rendered":"

Conditional formatting is applied based on a single criteria. A rule will only check whether a cell\u2019s value matches one condition and if it does, the formatting will be applied. Fortunately, you can apply multiple conditional formatting rules and you can use this to skip conditional formatting blank cells. Here\u2019s how.<\/p>\n

Skip conditional formatting blank cells<\/h2>\n

Open the Excel file that you\u2019ve applied or intend to apply conditional formatting to.\u00a0In the screenshot below, conditional formatting is applied to the C column so that all values that are less than 500 are highlighted in red.<\/p>\n

Select the column, or rows that you intend to apply the conditional formatting to. Go to Conditional Formatting>Manage Rules.<\/p>\n

\"\"<\/span><\/p>\n

Click the New Rule button in the rules manager and from the list of conditions, select \u2018Format only cells that contain\u2019 and select \u2018Blank\u2019 under the \u2018Format only cells with\u2019 dropdown. Click OK.<\/p>\n

\"\"<\/span><\/p>\n

You will return to the rule manager window. The new rule you added will be listed there. Select the \u2018Stop if true\u2019 option. After that you can add additional formatting conditions. If you have other formatting condition rules already in the list, make sure the one you created for blank cells appears at the top.<\/p>\n

To move it, select the rule and use the up arrow button to move it to the very top. Click Apply and all conditional formatting rules will skip blank cells within the column you selected.<\/p>\n

\"\"<\/span><\/p>\n

There\u2019s no condition on when you should add the rule to skip blank cells. You can add it before or after adding other rules. The only thing you need to make sure of is that the blank cell rule appears at the very top of the list and don\u2019t forget to check the Stop if true option.<\/p>\n

By default, this will keep empty cells free of all formatting however if you need the blank cells to be highlighted as well, you can give it a format when you create the rule. Click the \u2018Format\u2019 button next to the large \u2018No Format Set\u2019 box and select the fill color for the cell.<\/p>\n

This is a great way to highlight empty fields in your data and to have them formatted as soon as they\u2019re filled up. You can also use the order that the rules are applied in to apply more complex conditions and format cells accordingly e.g., you can apply a rule to highlight cells if their value is greater than a certain number, but less than another, or just find duplicate values.<\/p>\n","protected":false},"excerpt":{"rendered":"

Conditional formatting is applied based on a single criteria. A rule will only check whether a cell\u2019s value matches one condition and if it does, the formatting will be applied. Fortunately, you can apply multiple conditional formatting rules and you can use this to skip conditional formatting blank cells. Here\u2019s how. Skip conditional formatting blank …<\/p>\n","protected":false},"author":1,"featured_media":1892,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[23],"tags":[36],"_links":{"self":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts\/1891"}],"collection":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/comments?post=1891"}],"version-history":[{"count":0,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts\/1891\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/media\/1892"}],"wp:attachment":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/media?parent=1891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/categories?post=1891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/tags?post=1891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}