Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Alternating Row Colors

Matt Wiese
edited 12/09/19 in Archived 2015 Posts

Hello,

I am trying to format my entire sheet with colored rows that alternate. The Excel formula that perfomrms this is :

 

=MOD(ROW(),2)=0

 

This would be an awesome feature but I can't seem to fiund anywhere in the UI. Thanks for your help.

Comments

  • This would be a nice feature.  Too bad that isn't a standard option.  I've wanted that as well.

     

    One way to work around this is to create a "color" column that you could hide in the future.  In the top field put '0' and in the second row put the equation if(color1 = 0, 1, 0).  Fill in the equation for the remaining lines.  As you add rows to the bottom smartsheet's auto formula function will automatically fill it in, even if it is hiden.

     

    So you will get a sequence of 0, 1, 0, 1, 0, 1?

     

    After that is setup, set up a conditional formating rule based on your color column.  and you get something like this picture below.  Once it is set up, you shouldn't have to worry about it, unless you erase the formula.  May not work in your situation, but hey, it's an idea!

     

    Capture.PNG

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    Another way I do this is to format the first two rows as I want them, row one has a fill and row two is no fill, for example. Then copy the cells in the first two rows (not using copy-row) then select the rest of the sheet rows and paste-special-format. The only problem with this is that if you insert or delete rows it can messe up the fills. I do this on sheets where the rows are pretty static.

  • Matt Wiese
    edited 10/07/15

    Thanks Joel! Works like a charm!

  • I tried Joel's method and although it worked great for the existing rows I had, when I added rows (to the bottom) the formula didn't continue. Is there a way to turn SS auto formula function back on?

    Thanks!

  • Joel Johnson
    edited 10/12/15

    Hello Claire,  I have had that issue before.  What I have done is just delete the bottom rows. And start fresh with a new row.  Not sure why that works, but it does.  Maybe at some point there was infomration on the row or something...

     

    Oh, and the formula doesn't fill in untill you have inputed information in a cell on the bottom row and then leave that cell. 

  • Deanna Vandermeer
    Deanna Vandermeer Overachievers Alumni

    Thank you for this. Very helpful.

    Smartsheet Overachievers Alumni

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭
    edited 05/11/18

    Hello All!

    I've had the same request from some of my colleagues, and came up with this. We're able to have alternating colors that don't change when sorting by using the following"

     

    Alternating Row Colors

    GUID is an Auto-Number column, and Color has the following formula:

    =IF(RIGHT(((COUNT(GUID$1:GUID1)) / 2), 2) = ".5", 0, 1)

    And a conditional formatting rule set to highlight the row on Color = 0

    Conditional Formatting

    So long as Row1 has a highest or lowest sorting value or character in the columns you want to sort by (Animal, or Number in my case) the colors should stay alternating.

    This doesn't work in reports yet, but something I'm working on.

    Sorted By Animal.JPG

    Sorted By Number.JPG

  • Kirstine
    Kirstine ✭✭✭✭✭✭

    Thanks, this did the trick for me! 

  • This is a great idea but I'm struggling a little with getting it right. Did you manually enter the 1's and 0's for the entire column or is there something I'm missing? I tried dragging the formula down but that gave me random 1's and 0's instead of alternating. 

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    Hi Alexis,

    I had my columns sorted A>Z by the GUID, then dragged the formula "=IF(RIGHT(((COUNT(GUID$1:GUID1)) / 2), 2) = ".5", 0, 1)" down to the bottom of my range. I think doing the sort fist then applying the formulas was what made it lock in, so to speak.

  • Thank you for your speedy response! Unfortunately I didn't see it until now smiley

    I think what is happening is the formula is assigning "1"s to all even numbered rows and "0"s to all odd numbered rows. In my sheet, I'm only trying to alternate the colors of my parent rows, but because of the child rows the parent rows don't alternate between even and odd numbered rows. If that makes sense?

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    I think I understand. One way you could do it is by using the hierarchy() function in conjunction with conditional formatting. Here' a sheet with the formulas and rules that allow you to change the color of parent rows using the method above:

    https://app.smartsheet.com/b/publish?EQBCT=a0d21f8a05754a17955376a66ec25448

    and a link to the help article:

    https://community.smartsheet.com/discussion/using-hierarchy-formulas-conditional-formatting-color-code-rows-sheet

     

    Cheers!

  • Tim L
    Tim L
    edited 03/29/19

    This thread is fairly old by now but it seemed like the best place for me to post this solution for folks who were trying to do this in the past and those who may come looking for this in the future.

    I kind of smushed together the solution from @cmondo above and the solution from @Rob Hagan on the "determine row number" thread linked here:

    https://community.smartsheet.com/discussion/formula-determine-row-number

    Here is what I did, it requires three columns that I hid after generating the formulas:

    1. Create text column "rowcalc1" and fill all rows with formula "=1+0".



      (This ensures that on a fill-down that auto-incrementing does not occur, keeping all values at 1.)

       
    2. Create text column "rowcalc2" and fill down from row 1 with formula "=COUNT([rowcalc1]$1:[rowcalc1]1)".



      (This calculates the difference between the first row and the current row.)

       
    3. Create text column "rowcalc3" and fill down from row 1 with formula "=IF(RIGHT(([rowcalc2]1 / 2), 2) < 1, 1)".



      (This divides the rowcalc2 value by 2 and grabs the last two digits of the result. An odd number will always produce a ".5" result. If the result is less than 1, put a 1 in the rowcalc3 column.)

       
    4. Create a conditional formatting rule that shades the entire row in light gray (or a color of your choosing) if the rowcalc3 value is 1.

    Hopefully that is thorough enough but if anything is unclear, please let me know.

    EDIT: I forgot to mention that this will respect added rows, deleted rows, and row sorts due to the three-tier calculation "correcting" itself.

  • Long way to go to get shading, but thank you.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Bill,

    Take a look at this post for other methods that might be better.

    https://community.smartsheet.com/discussion/different-color-row

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

This discussion has been closed.