Alternating Colors on Sheets

Eli2025

Hello,

Does anyone know how to add alternating colors to the sheets without having to do it manually?

KPH

Hi @Eli2025

You can use conditional formatting to create alternating color rows. You will need to add a couple of columns to make this work.

1.Create an auto-number column

If you don't already auto-number your rows, add a column to do so. If you already have this column, skip to step 2.

2. Create a row number column

The auto-number column will number each row as it is added to the sheet. If you add another row between existing roes, the numbers will not change. See:

However, you can add another column with a column formula that will return the row number of the row where the automatically added number matches that on the current row. It is a bit of a brain teaser, but works well!

The formula here is:

=MATCH([Auto-Row]@row, [Auto-Row]:[Auto-Row], 0)

Where Auto-Row is the column with the auto-number in.

Step 3 Identify odd and even rows

Use the ISODD function within an IF to return a different value depending on whether the row number is odd or even. In this case, I used 1 for odd and 2 for even, using this formula:

=IF(ISODD([Row Number]@row) = 1, 1, 2)

To avoid redundant columns, you can just put the IF around the formula in step 2, like:

=IF(ISODD(MATCH([Auto-Row]@row, [Auto-Row]:[Auto-Row], 0)) = 1, 1, 2)

Make this a column formula.

Step 4 Create conditional formatting

You can now hide the columns in your sheet (if you don't want them to be visible/confusing) and set up your conditional formatting: