# Excel - 3 Methods to Shade Every Other Row

Posted on:  07/05/2022

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.

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.

## 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.

## 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.

## 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.

## Recent Excel articles

#### 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