{"id":428,"date":"2018-10-17T01:55:11","date_gmt":"2018-10-17T01:55:11","guid":{"rendered":"http:\/\/www.alandaletraining.com\/software-tips\/?p=428"},"modified":"2018-10-17T01:55:11","modified_gmt":"2018-10-17T01:55:11","slug":"colour-alternating-rows-in-an-excel-worksheet-option-1","status":"publish","type":"post","link":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/","title":{"rendered":"Colour alternating rows in an Excel worksheet – Option 1"},"content":{"rendered":"

In a large spreadsheet that has many columns of data it can be difficult following a specific row as you scroll to the right.\u00a0 This is especially true if you have zoomed out so more data fits on the screen and the rows are smaller than usual.<\/p>\n

Wouldn’t it be great if alternating rows in the workbook were coloured so you could more easily run your eye along a row?\u00a0\u00a0You can do this with conditional formatting.<\/p>\n

Select the rows you want coloured then:<\/p>\n

    \n
  1. On the Home tab in the Styles group, click on the Conditional<\/em> Formatting<\/em> button<\/li>\n
  2. Select New Rule…<\/em><\/li>\n
  3. In the New Formatting Rule<\/em> dialog box, select the last option in the list: Use a formula to determine which cells to forma<\/em>t<\/li>\n
  4. Add the following formula to the Format values where this formula is true <\/em>text box:\u00a0=MOD(ROW(),2)=1
    \n\"\"<\/li>\n
  5. Click on the Format<\/em> button and select a colour from the Fill tab<\/li>\n
  6. Click on OK twice<\/li>\n<\/ol>\n

    Notes:<\/p>\n

      \n
    • You can paste the formula in step 4 above, including the =,\u00a0 into the text box, if you’d like<\/li>\n
    • In case you are wondering, the ROW function returns the row number and the Mod function divides it by 2.\u00a0 In the formula above, odd rows will be coloured.\u00a0 Change the 1 to a 0 and your workbook would look the same but even rows would be coloured.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

      In a large spreadsheet that has many columns of data it can be difficult following a specific row as you scroll to the right.\u00a0 This is especially true if you …<\/p>\n","protected":false},"author":4,"featured_media":429,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-428","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel"],"yoast_head":"\nColour alternating rows in an Excel worksheet - Option 1 - Alandale Training Software Tips<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Colour alternating rows in an Excel worksheet - Option 1 - Alandale Training Software Tips\" \/>\n<meta property=\"og:description\" content=\"In a large spreadsheet that has many columns of data it can be difficult following a specific row as you scroll to the right.\u00a0 This is especially true if you ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Alandale Training Software Tips\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-17T01:55:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"373\" \/>\n\t<meta property=\"og:image:height\" content=\"366\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"John Chandler\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"John Chandler\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Colour alternating rows in an Excel worksheet - Option 1 - Alandale Training Software Tips","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:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/","og_locale":"en_US","og_type":"article","og_title":"Colour alternating rows in an Excel worksheet - Option 1 - Alandale Training Software Tips","og_description":"In a large spreadsheet that has many columns of data it can be difficult following a specific row as you scroll to the right.\u00a0 This is especially true if you ...","og_url":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/","og_site_name":"Alandale Training Software Tips","article_published_time":"2018-10-17T01:55:11+00:00","og_image":[{"width":373,"height":366,"url":"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg","type":"image\/jpeg"}],"author":"John Chandler","twitter_card":"summary_large_image","twitter_misc":{"Written by":"John Chandler","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/#article","isPartOf":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/"},"author":{"name":"John Chandler","@id":"https:\/\/www.alandaletraining.com\/software-tips\/#\/schema\/person\/4354e1f8ae425698db32a15825a8ecb4"},"headline":"Colour alternating rows in an Excel worksheet – Option 1","datePublished":"2018-10-17T01:55:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/"},"wordCount":230,"image":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg","articleSection":["Excel"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/","url":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/","name":"Colour alternating rows in an Excel worksheet - Option 1 - Alandale Training Software Tips","isPartOf":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/#primaryimage"},"image":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg","datePublished":"2018-10-17T01:55:11+00:00","author":{"@id":"https:\/\/www.alandaletraining.com\/software-tips\/#\/schema\/person\/4354e1f8ae425698db32a15825a8ecb4"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.alandaletraining.com\/software-tips\/2018\/10\/17\/colour-alternating-rows-in-an-excel-worksheet-option-1\/#primaryimage","url":"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg","contentUrl":"https:\/\/www.alandaletraining.com\/software-tips\/wp-content\/uploads\/2018\/10\/New-Rule-dialog-box-Colour-rows.jpg","width":373,"height":366},{"@type":"WebSite","@id":"https:\/\/www.alandaletraining.com\/software-tips\/#website","url":"https:\/\/www.alandaletraining.com\/software-tips\/","name":"Alandale Training Software Tips","description":"Weekly Software Tips","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.alandaletraining.com\/software-tips\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.alandaletraining.com\/software-tips\/#\/schema\/person\/4354e1f8ae425698db32a15825a8ecb4","name":"John Chandler","sameAs":["http:\/\/AlandaleTraining.com"],"url":"https:\/\/www.alandaletraining.com\/software-tips\/author\/john1973\/"}]}},"_links":{"self":[{"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/posts\/428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/comments?post=428"}],"version-history":[{"count":1,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/posts\/428\/revisions"}],"predecessor-version":[{"id":430,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/posts\/428\/revisions\/430"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/media\/429"}],"wp:attachment":[{"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/media?parent=428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/categories?post=428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.alandaletraining.com\/software-tips\/wp-json\/wp\/v2\/tags?post=428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}