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

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

Can that be done in smartsheet?

thx,

Laura

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    John Pudar ✭✭✭✭
    Answer ✓

    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
    laura.buchanan ✭✭✭✭
    Answer ✓

    Thanks so much!

  • Sue Hill
    Sue Hill ✭✭✭✭
    Answer ✓

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

  • Samscho
    Samscho ✭✭
    Answer ✓

    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.


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    John Pudar ✭✭✭✭
    Answer ✓

    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
    laura.buchanan ✭✭✭✭
    Answer ✓

    Thanks so much!

  • 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
    Sue Hill ✭✭✭✭
    Answer ✓

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    Sue Hill ✭✭✭✭

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

  • Samscho
    Samscho ✭✭
    edited 04/17/23

    @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
    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
    Samscho ✭✭
    Answer ✓

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!