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

laura.buchanan · 02/16/21

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

Can that be done in smartsheet?

thx,

劳拉

Kelly Moore · 02/16/21

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 · 06/22/21

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([email protected], 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 · 06/22/21

Thanks so much!

Sue Hill · 03/17/23

Not answered---=IF(ISEVEN(MATCH([email protected], X:X)), 1) gives #CIRCULAR REFERENCE

Samscho · 04/18/23

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.

Screen Shot 2023-04-17 at 8.24.31 PM.png

Kelly Moore · 02/16/21

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 · 06/22/21

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([email protected], 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 · 06/22/21

Thanks so much!

Jeremy Schwenk · 04/07/22

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 · 03/17/23

Not answered---=IF(ISEVEN(MATCH([email protected], X:X)), 1) gives #CIRCULAR REFERENCE

Genevieve P. · 03/20/23

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 · 03/20/23

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

Samscho · 04/17/23

@John Pudarthank you for sharing this. =IF(ISEVEN(MATCH([email protected], 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 · 04/18/23

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 · 04/18/23

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.

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

Best Answers

Answers

Help Article Resources

Categories