Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Shade Every Other Row in Excel? (5 Best Methods)

How to Shade Every Other Row in Excel? (5 Best Methods)

Note: This guide on how to shade every other row in Excel is applicable for all Excel versions including Office 365. 

Shading alternate arrows in your Excel spreadsheet is one of the easiest ways to make it user friendly. It improves readability and reduces clutter in the spreadsheet. 

In this guide, I’ll walk you through the top two methods to achieve this in Excel. 

I’ll cover:

Table Of Contents
  1. How to Shade Every Other Row in Excel Using Conditional Formatting
  2. How Do I Alternate Groups of Rows with Different Colours Using Conditional Formatting?
  3. How to Make Excel Alternate Color Based on Value Types?
  4. How to Shade Every Other Row in Excel Using Tables
  5. How Do I Alternate Row Colors in Excel by Group Using Tables?
  6. FAQs
    • How do I remove alternate row shading in Excel?
    • How do I make every other row shaded in Google sheets?
  7. Let’s Wrap Up 



Related: 

How To Find Duplicates In Excel? The Best Guide

Excel Goal Seek—the Easiest Guide (3 Examples)

Create A Pivot Table In Excel—the Easiest Guide


How to Shade Every Other Row in Excel Using Conditional Formatting

To easily make Excel alternate row colour, use conditional formatting. 

You can follow these steps as shown here: 

  1. Select the range of cells you want to shade alternatively. Select all the cells if you need them. 
Select the Range of Cells
Select the Range of Cells
  1. Click Conditional Formatting,  in the Home Tab under the Styles Group.  
Select New Rule under Conditional Formatting
Select New Rule under Conditional Formatting
  1. In the drop-down menu, select New Rule.
  1. In the Select a Rule Type box, select the “Use a formula to determine which cells to format” option. 
  1. Enter the formula =MOD(ROW(),2) in the Format Values when this formula is true box.  



Here the Row function returns the row number. The Mod function then returns the remainder when the row number is divided by two.

This means, every odd-numbered row will return a value of 1, whereas every even-numbered row will return a value of 0. 

Hence, the conditional formatting will highlight every odd row with the chosen colour. 

  1. Choose an appropriate formatting style. 
  1. Click Ok. 

You have successfully shaded every other row in Excel. If you want to highlight the even-numbered rows, modify the formula to =MOD(ROW(),2=0. 

Shade every other Row in Excel
Shade every other Row in Excel

If you want to highlight alternative columns, modify the formula to =MOD(COLUMN(),2=0. 

If you want to shade every third row, modify the formula to =MOD(ROW(),3

Also Read: 

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

How Do I Alternate Groups of Rows with Different Colours Using Conditional Formatting?

Sometimes, you may need to alternatingly highlight groups of three rows in a sheet. Let’s see how to accomplish this. Just follow these steps:

  1. To highlight the first group and every other odd-numbered group, use this formula as the condition in Conditional Formatting.

    =MOD(ROW()-X,N*2)+1<=N, where N is the number of rows in each group and X is the row number where the data begins. 
Enter the formula in the Format values where this formula is true box
Enter the formula in the Format values where this formula is true box
Excel Highlights alternate groups of rows
Excel Highlights alternate groups of rows
  1. To highlight the second group and every other even-numbered group, use this formula as the condition in Conditional Formatting.

    =MOD(ROW()-X,N*2)>=N,  where N is the number of rows in each group and X is the row number where the data begins. 
Enter the formula in the Format values where this formula is true box
Excel Highlights alternate groups of rows
  1. Enter one of the above formulas in the Format Values when this formula is true box. 

  2. Choose an appropriate formatting style and Click OK. 

How to Make Excel Alternate Color Based on Value Types?

Let’s suppose, you have a list of sales data for different regions. Now, you want to highlight groups of different regions alternatively. How to do this using Excel conditional formatting?

Follow these steps:

  1. Enter the formula =MOD(IF(ROW()=2,0,IF(A2=A1,X1, X1+1)), 2)  where, X is the column where you enter this formula. 
Enter the modified Formula in an adjacent column.
Enter the modified Formula in an adjacent column.
  1. Drag this formula until the end of the data in Column X.

    The formula simply assigns 1s and 0s to groups of the same region type. 
  1. Enter the formula $X2=1 in the Format Values when this formula is true box in conditional formatting.
Enter the formula in the Format values where this formula is true box
Enter the formula in the Format values where this formula is true box
  1. Choose an appropriate formatting style and Click OK. 
Excel highlights groups of rows based on regions together
Excel highlights groups of rows based on regions together

You have successfully highlighted groups of rows based on value types.  

How to Shade Every Other Row in Excel Using Tables

It is possible to apply shading to alternative rows by using an Excel Table Style.  This method has the added benefits of tables, for example, table headers will be automatically displayed in the filter drop-down lists. 

To make Excel alternate row colour using tables, follow these steps: 

  1. Select the data you want to highlight alternatively.
  2. Click the Format as Table button under the Home tab in the Styles group. 
Click on the Format as Table button and select an appropriate style.
Click on the Format as Table button and select an appropriate style.
  1. Under the drop-down menu, select any of the formatting styles from Light, Medium and Dark.
  2. In the next dialogue box, check your data range and check the tick box if you have headers in your data. 
Select the Data Range
Select the Data Range

Your table appears by default with alternating shaded rows. This is because the Banded Rows option is selected by default in the Table Design options. 

Banded Rows are applied by default
Banded Rows are applied by default

If you need to shade every other column, uncheck Banded Rows and check Banded Column in the Table Design Options. 

Toggle Banded Rows/Columns On and Off under Table Design
Toggle Banded Rows/Columns On and Off under Table Design

The table will keep updating the shaded bands if you keep adding new rows of data.

If you don’t need the table formatting, just click on the Convert to Range option under the Tools group in the Table Design tab to covert the data into ranges. Note that you will lose the ability to highlight new rows of data if you convert the table into a range. 

Click on Convert to Range if needed
Click on Convert to Range if needed

How Do I Alternate Row Colors in Excel by Group Using Tables?

To change the pattern of the banded rows, i.e change the number of rows highlighted in each zebra brand, follow these steps: 

  1. Right-click on any one of the Table styles under the Design Tab and click on Duplicate.
Right-Click on any Table Styles and Select Duplicate
Right-Click on any Table Styles and Select Duplicate
  1. In the Modify Table Style window, enter a custom name to your style.
  2. Click on the First Row Stripe option under the Table Elements List.  Change the Stripe size as required. The stripe size is the number of rows that each zebra band highlights. 
 set Stripe Size for First Rows
Give a Custom Name and set Stripe Size for First Rows
  1. Repeat the same process for the Second Row Stripe option and Click OK.
Set Stripe Size for First Rows
  1. Customize any other formatting options available here, if required. 
  1. Apply your newly created custom style to your table by clicking on it from the Table Styles menu. 
Apply the Custom Style to your Table
Apply the Custom Style to your Table
Excel highlights the rows based on the set custom style
Excel highlights the rows based on the set custom style

Suggested Reads: 

Create An Excel Dashboard In 5 Minutes – The Best Guide

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Predict Future Values Using Excel Forecast Sheet – The Best Guide

FAQs

How do I remove alternate row shading in Excel?

If you are using a table that has alternate shading, just go to the Table Design tab and uncheck the Banded Rows option to remove alternate row shading.

If you are using conditional formatting to apply alternate shading, just clear the conditional formatting by doing this Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

How do I make every other row shaded in Google sheets?

1. Select the relevant range of cells. Ctrl+A for selecting all cells.
2. Click “Alternating Colours” under the “Format” tab.
3. Set the colour style for the Headers & footers. Uncheck them if not needed.
4. Your sheet, now has alternate colours.

Let’s Wrap Up 

In this guide, I have explained how to shade every other row in Excel in a step-by-step manner. I covered all the important techniques to do this, including small variations. If you have any doubts regarding this topic or any other Excel feature, please let us know in the comments below. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.  Click here for advanced Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Create Excel Drop Down List With Color?

Monday.com Dependencies – Tips and Types Explained [2022]

How to Insert Watermark in Word? 3 Easy Ways

How to Fix the Excel Circular Reference Error?

Is Your Excel Scroll Bar Missing? 4 Easy Ways to Fix It

Excel Crash Course – Learn Pivot Tables in 1 Hour

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)