{"id":12118,"date":"2012-01-14T20:35:17","date_gmt":"2012-01-14T20:35:17","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1217"},"modified":"2012-01-14T20:35:17","modified_gmt":"2012-01-14T20:35:17","slug":"shade-alternate-rows-using-conditional-formatting","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/","title":{"rendered":"Format Alternate Rows using Conditional Formatting"},"content":{"rendered":"

Dear Readers,<\/p>\n

In this article I am going to explain you, how you can format the Alternate Rows of Excel using Conditional Formatting.<\/a> For example, in Excel Sheet, if you want alternate rows to be shaded in different color. This you usually need when you are preparing some report and want your report looking very nice.
\nFollow the below Steps to do so:<\/p>\n

Steps to Shade Alternate Rows (Automatically)<\/h3>\n

Step 1.<\/strong>Select the Area which you want to be shaded. If you want the whole Sheet to be formatted then Select the whole sheet.
\nStep 2.<\/strong> Go to the Home Tab in the Menu Ribbon (Excel 2007) –> Click on Conditional Formatting Option
\nSte 3.<\/strong> Click on Add New Rule
\nSte 4.<\/strong> Select the Format Type as “Use a Formula to Determine Which cells to format”<\/p>\n

\"\"<\/a>

Shading Alternate Rows<\/p><\/div>\n

Ste 5.<\/strong> Type this formula =MOD(ROW(),2)<\/strong>
\nSte 6.<\/strong> Click on Format Button and change the format, how you want.
\nSte 7.<\/strong> Click OK
\nSte 8.<\/strong> Click OK<\/p>\n

\"\"<\/a>

Shading Alternate Rows<\/p><\/div>\n

Now you can see that in your Selected Area, all the Alternate Rows will be having the same Formatting what you had chosen in the above dialog.<\/p>\n

Brief Explanation: <\/h2>\n

In the above formula I have used MOD<\/strong> and ROW()<\/strong> function. ROW()<\/strong> Function returns the Row Number like 1, 2, 3, 4 etc.
\nNow the MOD()<\/strong> Function will return Alternatively: 1, 0, 1, 0.
\nAnd because of that Alternatively the Shade of the row will be formatted as shown above picture.<\/p>\n

How is it different than Simple Formatting by Selecting the Alternate Rows?<\/h3>\n

1. In simple formatting if you want to delete any row in between then the whole formatting will messed-up and you need to format it again till end (alternatively) but using conditional formatting as soon as you delete any row rest all will be automatically formatted alternatively.
\n2. Get rid of alternate selection and then filling color and all in it to look good… give this task to conditional formatting.. it will do \ud83d\ude42 You just enjoy the formatting done automatically.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"

Dear Readers, In this article I am going to explain you, how you can format the Alternate Rows of Excel using Conditional Formatting. For example, in Excel Sheet, if you want alternate rows to be shaded in different color. This you usually need when you are preparing some report and want your report looking very […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672],"tags":[],"class_list":["post-12118","post","type-post","status-publish","format-standard","hentry","category-excel-formula"],"yoast_head":"\nFormat Alternate Rows using Conditional Formatting - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Format Alternate Rows using Conditional Formatting\" \/>\n<meta property=\"og:description\" content=\"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-14T20:35:17+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Format Alternate Rows using Conditional Formatting\",\"datePublished\":\"2012-01-14T20:35:17+00:00\",\"dateModified\":\"2012-01-14T20:35:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\"},\"wordCount\":345,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png\",\"articleSection\":[\"Excel Formula\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\",\"name\":\"Format Alternate Rows using Conditional Formatting - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png\",\"datePublished\":\"2012-01-14T20:35:17+00:00\",\"dateModified\":\"2012-01-14T20:35:17+00:00\",\"description\":\"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Format Alternate Rows using Conditional Formatting\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Format Alternate Rows using Conditional Formatting - Let's excel in Excel","description":"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/","og_locale":"en_US","og_type":"article","og_title":"Format Alternate Rows using Conditional Formatting","og_description":"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.","og_url":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-01-14T20:35:17+00:00","og_image":[{"url":"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Format Alternate Rows using Conditional Formatting","datePublished":"2012-01-14T20:35:17+00:00","dateModified":"2012-01-14T20:35:17+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/"},"wordCount":345,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png","articleSection":["Excel Formula"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/","url":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/","name":"Format Alternate Rows using Conditional Formatting - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png","datePublished":"2012-01-14T20:35:17+00:00","dateModified":"2012-01-14T20:35:17+00:00","description":"Sometimes we need all the Rows of an Excel Sheet to be shaded in a particular format. Even if we delete any row or cell, formatting should remain in same manner. By using Simple Conditional Formula, we can do this in Excel.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Shading-alternate-rows.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/shade-alternate-rows-using-conditional-formatting\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"Format Alternate Rows using Conditional Formatting"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12118"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12118"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12118\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}