Is it possible to use conditional formatting on every other row?

laura.buchanan

You know, like in excel, it is helpful to highlight every other row.

Can that be done in smartsheet?

thx,

Laura

Kelly Moore

Hey @laura.buchanan

To my knowledge, the short answer is no. Here's a possible work-around, if you aren't deleting or moving rows around.

You'll need a helper checkbox column to use in the conditional formatting. You'll also need the system generated autonumber (ie Row ID).

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

There is also an ISEVEN if that is your preference.

John Pudar

Hi @laura.buchanan,

Here's a method that will allow you to delete/move/sort rows at will and still maintain the every other row formatting:

Create an 'Auto-Number/System...' column and choose Auto-Number. In this case, I named the column "X."

Create a new Checkbox column and enter this as a column formula:

=IF(ISEVEN(MATCH(X@row, X:X)), 1)

Now just setup conditional formatting to shade any columns where the box is checked and you should be all set.

laura.buchanan

Thanks so much!

Sue Hill

Not answered---=IF(ISEVEN(MATCH(X@row, X:X)), 1) gives #CIRCULAR REFERENCE

Samscho

Sadly that's not the behavior I'm getting. If I start with a sheet of 5 rows, the auto-number/system column numbers 1-5. If I delete row 4, the auto-number/system column now shows 1,2,3,5 and refreshing doesn't reorder it. Here's how I have the auto-number column defined. I've tried with and without specifying "1" as the starting number.

Kelly Moore

Hey @laura.buchanan

To my knowledge, the short answer is no. Here's a possible work-around, if you aren't deleting or moving rows around.

You'll need a helper checkbox column to use in the conditional formatting. You'll also need the system generated autonumber (ie Row ID).

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

There is also an ISEVEN if that is your preference.

John Pudar

Hi @laura.buchanan,

Here's a method that will allow you to delete/move/sort rows at will and still maintain the every other row formatting:

Create an 'Auto-Number/System...' column and choose Auto-Number. In this case, I named the column "X."

Create a new Checkbox column and enter this as a column formula:

=IF(ISEVEN(MATCH(X@row, X:X)), 1)

Now just setup conditional formatting to shade any columns where the box is checked and you should be all set.

laura.buchanan

Thanks so much!

Jeremy Schwenk

Hi there - I'm attempting this method and when I sort it throws off the conditional formatting. I'm sure I'm missing something, any help would be appreciated. I tried locking and freezing the new columns but that didn't work.

Sue Hill

Not answered---=IF(ISEVEN(MATCH(X@row, X:X)), 1) gives #CIRCULAR REFERENCE

Genevieve P.

Hi @Sue Hill

You'll want to ensure this formula is in a separate column to the one you're referencing. The "X" column should be an auto-number column, and then there is a new column to house the formula, like so:

Cheers,

Genevieve

Sue Hill

I did perform this, working....figured it out later that day.

Samscho

@John Pudar thank you for sharing this. =IF(ISEVEN(MATCH(X@row, X:X)), 1) worked for me just like you said.

However if I delete a row from my sheet somewhere in the middle the auto-numbered list doesn't reorder itself automatically to close the gap from the now missing number. Is there any way to regenerate the list easily without deleting the column(s) and recreating them any time I have to delete a row? thanks!

John Pudar

Hi @Samscho - if you make sure that the "X" column is an 'Auto-Number/System...' column then you should be fine. You may need to refresh the sheet after a change to get the auto-numbering to update, which in turn will update the conditional formatting. But as long as the column you're referencing in the MATCH function is an Auto-Number/System column it should work for you.

Samscho

Sadly that's not the behavior I'm getting. If I start with a sheet of 5 rows, the auto-number/system column numbers 1-5. If I delete row 4, the auto-number/system column now shows 1,2,3,5 and refreshing doesn't reorder it. Here's how I have the auto-number column defined. I've tried with and without specifying "1" as the starting number.