How to Automatically Highlight Alternate Rows in Microsoft Excel Using Conditional Formatting

Improving Data Legibility with Zebra Striping

Reading wide spreadsheets containing dozens of columns can cause visual fatigue. When scanning across long horizontal records, your eyes naturally wander between adjacent rows, leading to costly data entry mistakes and misread financial figures. Applying alternate row shading—frequently referred to as zebra striping—dramatically improves scanning speed and visual clarity. While converting a dataset into an official Excel Table provides built-in banding, formatting tables directly can interfere with custom array formulas, pivot sources, or existing sheet layouts. Using conditional formatting with a mathematical row formula allows you to automate dynamic zebra shading across any range while preserving complete layout flexibility.

Table Formatting vs Conditional Formatting

Microsoft Excel offers two primary ways to create banded rows: official Excel Tables (Insert > Table) and Conditional Formatting rules. Converting data into an official table introduces automatic filter buttons, structured references, and strict syntax requirements that may not align with complex financial models. In contrast, applying a conditional formatting rule evaluates cell coordinates dynamically. If you insert new rows, delete records, or paste external numbers, the mathematical shading automatically recalculates and shifts instantaneously without leaving unshaded gaps or requiring manual reformatting.

Understanding the MOD and ROW Formula Logic

Dynamic zebra striping relies on two fundamental Excel functions working in tandem: ROW() and MOD().

• The ROW Function: Entering =ROW() returns the numerical index of the current row (for example, row 5 returns 5).
• The MOD Function: The MOD(number, divisor) function returns the remainder after dividing a number by a divisor. Dividing any integer by 2 produces a remainder of either 0 (for even numbers) or 1 (for odd numbers).

By evaluating =MOD(ROW(), 2) = 0, the rule evaluates to TRUE for every even row (2, 4, 6, 8, etc.) and FALSE for every odd row. When set as a conditional formatting trigger, Excel applies your chosen background fill exclusively to rows returning TRUE.

Applying the Conditional Formatting Rule Step-by-Step

You can apply custom zebra striping to any spreadsheet range in Microsoft Excel on Windows, macOS, or Excel for the web.

1. Open your spreadsheet in Microsoft Excel.
2. Select the cell range you wish to format (for example, A2:M100). Avoid selecting entire column letters unless you intend to shade a million empty rows.
3. On the Home tab of the Excel ribbon, click the Conditional Formatting button.
4. Select New Rule from the drop-down menu.
5. In the New Formatting Rule dialogue, click Use a formula to determine which cells to format.
6. In the formula field labeled Format values where this formula is true, type:

   =MOD(ROW(), 2)=0

   (If you prefer shading odd rows instead of even rows, type =MOD(ROW(), 2)=1).
7. Click the Format button to open the Format Cells window.
8. Navigate to the Fill tab and select a soft, subtle background colour (such as light grey, soft blue, or pale mint) that maintains clear contrast with dark text.
9. Click OK to close the Format dialogue, then click OK again to apply the rule.

Your selected grid will immediately render with perfectly balanced alternating row highlights.

Highlighting Alternate Columns Instead of Rows

If your spreadsheet is structured horizontally with continuous time periods or quarterly metrics running across columns, you can substitute the ROW() function with COLUMN() to create vertical zebra striping.

1. Highlight your horizontal data matrix.
2. Open Conditional Formatting > New Rule > Use a formula.
3. Enter the following column formula:

   =MOD(COLUMN(), 2)=0

4. Choose your desired fill colour and confirm with OK to generate alternating vertical column stripes.

Creating Multi-Row Shading Bands

For dense datasets where grouping rows in blocks of two, three, or five provides better visual grouping, you can adjust the formula logic using the INT() function. To highlight records in alternating blocks of three rows:

=MOD(INT((ROW()-1)/3), 2)=0

This formula divides the row count by three and rounds down, creating alternating three-row colored blocks followed by three unshaded rows, ideal for displaying multi-line transactional entries.

Managing and Removing Conditional Formatting Rules

To modify your shading colour or remove the rule entirely:

1. Click Conditional Formatting on the Home tab and select Manage Rules.
2. Set the Show formatting rules for drop-down menu to This Worksheet.
3. Select your zebra striping rule and click Edit Rule to change colours, or click Delete Rule to restore standard unshaded cells immediately.