Alternating Row Colors

Matt Wiese

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.

Joel Johnson

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!

 

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

Thanks Joel! Works like a charm!

Claire Diener

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

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

Thank you for this. Very helpful.

Chris Mondeau

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

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.

Kirstine

Thanks, this did the trick for me! 

Alexis Wilson

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

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.

Alexis Wilson

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?

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

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.

Bill Cohn

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

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