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:
- How to Shade Every Other Row in Excel Using Conditional Formatting
- How Do I Alternate Groups of Rows with Different Colours Using Conditional Formatting?
- How to Make Excel Alternate Color Based on Value Types?
- How to Shade Every Other Row in Excel Using Tables
- How Do I Alternate Row Colors in Excel by Group Using Tables?
- FAQs
- 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:
- Select the range of cells you want to shade alternatively. Select all the cells if you need them.
- Click Conditional Formatting, in the Home Tab under the Styles Group.
- In the drop-down menu, select New Rule.
- In the Select a Rule Type box, select the “Use a formula to determine which cells to format” option.
- 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.
- Choose an appropriate formatting style.
- 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.
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:
- 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.
- 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 one of the above formulas in the Format Values when this formula is true box.
- 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:
- 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.
- 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.
- Enter the formula $X2=1 in the Format Values when this formula is true box in conditional formatting.
- Choose an appropriate formatting style and Click OK.
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:
- Select the data you want to highlight alternatively.
- Click the Format as Table button under the Home tab in the Styles group.
- Under the drop-down menu, select any of the formatting styles from Light, Medium and Dark.
- In the next dialogue box, check your data range and check the tick box if you have headers in your data.
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.
If you need to shade every other column, uncheck Banded Rows and check Banded Column in the Table Design Options.
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.
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:
- Right-click on any one of the Table styles under the Design Tab and click on Duplicate.
- In the Modify Table Style window, enter a custom name to your style.
- 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.
- Repeat the same process for the Second Row Stripe option and Click OK.
- Customize any other formatting options available here, if required.
- Apply your newly created custom style to your table by clicking on it from the Table Styles menu.
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.
