How to color every other row in excel

Answer

To color every other row in Excel, use either Excel’s built-in table formatting or a conditional formatting rule that applies a background color based on row numbers. The process automates alternate shading across the dataset.

Using Built-In Table Formatting

Steps

1. Select the range of cells.

2. Press Ctrl + T on Windows or Command + T on Mac.

3. Confirm the range and choose My table has headers when your data includes headers.

4. Choose any style that includes banded rows from the Table Design tab.

5. Toggle Banded Rows to enable or disable alternate row shading.

Excel applies structured banding that updates automatically as new rows are added.

Using Conditional Formatting

Steps

1. Select the range that needs alternating colors.

2. Open Home > Conditional Formatting > New Rule.

3. Select Use a formula to determine which cells to format.

4. Enter the formula:

   $=MOD(ROW(),2)=1$
   $(=MOD(ROW(),2)=1$
   

   5. Choose Format, select a fill color, and confirm.

   The rule colors all odd-numbered rows. Replace 1 with 0 to color even rows instead.

   Windows vs. Mac Differences

   • Conditional formatting menus use identical names, but Mac places them in slightly different positions within the Home tab.

   • Table formatting shortcuts differ:

     • Windows uses Ctrl + T.

     • Mac uses Command + T.

   Choosing Color Patterns

   Excel applies one color per rule. Create a second rule when you need alternating colors with two specific shades. The second rule uses the complementary row condition:

             $=MOD(ROW(),2)=0$   

   Apply a different fill color to complete the alternating pattern.

   Removing Alternating Row Colors

   Steps

   1. Select the formatted range.

   2. Open Home > Conditional Formatting > Clear Rules.

   3. Choose to clear rules from the selected range or entire sheet.

   This removes all color logic and restores the default appearance.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report