Excel - 3 Methods to Shade Every Other Row

Posted on:  07/05/2022
Excel - 3 Methods to Shade Every Other Row

Highlighting or shading every other row in Excel is a great way to make your data easier to read. I'll show three methods for applying color to alternate rows.

Shade every other row in Excel

Share every other row in Excel

Method 1 is my least favorite, but I see users doing this a lot. Method 2 is one of my favorite features in Excel, but for the most control, use method 3. Method 3 is Conditional Formatting and I use the MOD Function and the ROW function. The MOD function returns the remainder of one number divided by another. The ROW function tells you what row you are in.

Recent articles

YouTube Video

Chapters:

  • 00:00 Introduction
  • 00:22 Method 1
  • 00:47 Method 2
  • 01:27 Method 3
  • 05:27 Closing

Method 1 - Manually select every other row

This is my least favorite method. Use the CTRL key to select every other row. It is time comsuming and if you want your data, it will be incorrect.

Recent Word articles

Method 2 - Create a Table

Convert your range to a Table in Excel. CTRL + T is the shortcut key or use the Home Tab, and Styles group and the command Format as Table

Tables are a great feature in Excel. 

Format as Table in Excel - Shade every other row

Format as Table in Excel - Shade every other row

Method 3 - Use Conditional Formatting

Method 3 is Conditional Formatting and I use the MOD Function and the ROW function. The MOD function returns the remainder of one number divided by another. The ROW function tells you what row you are in.

MOD Examples:

  • =MOD(7,2) will return 1. Seven divided by 2 is 3 with 1 remaining.
  • =MOD(7,3) will return 1. Seven divided by 3 is 2 with 1 remaining.
  • =MOD(7,4) will return 3. Seven divided by 4 is 1 with 3 remaining.
  • =MOD(4,2) will return 0. Four divided by 2 is 2 with 0 remaining.

ROW Examples:

  • =ROW(A7) will return 7
  • =ROW(D6) will return 6.
  • =ROW() will return 3, if you are in row 3 will return 3.
MOD and ROW functions in Conditional Formatting

MOD and ROW functions in Conditional Formatting

Disney Event with Chris Menard in September 2022

Recent Excel articles

Excel Status Bar - Tips Every User Should Know

Excel Status Bar - Tips Every User Should Know

How to SORT Data in Excel | 7 Tips

How to SORT Data in Excel | 7 Tips

Excel - Data from Picture - Microsoft 365 New Feature

Excel - Data from Picture - Microsoft 365 New Feature

Excel - using the Scenario Manager to track house payments with skyrocketing mortgage rates

Excel - using the Scenario Manager to track house payments with skyrocketing mortgage rates

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories