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
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!

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 copyrow) then select the rest of the sheet rows and pastespecialformat. 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.

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!

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.

Thank you for this. Very helpful.
Smartsheet Overachievers Alumni

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"
GUID is an AutoNumber 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
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.

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.

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
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?

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:
Cheers!

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/formuladeterminerownumber
Here is what I did, it requires three columns that I hid after generating the formulas:
 Create text column "rowcalc1" and fill all rows with formula "=1+0".
(This ensures that on a filldown that autoincrementing does not occur, keeping all values at 1.)
 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.)
 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.)
 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 threetier calculation "correcting" itself.
 Create text column "rowcalc1" and fill all rows with formula "=1+0".

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

Hi Bill,
Take a look at this post for other methods that might be better.
https://community.smartsheet.com/discussion/differentcolorrow
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.