different color per row

Netanel yosef

how i can to do an automatic different color per row ?

see example from excel:

Andrée Starå

Hi Netanel,

Check out this post: https://community.smartsheet.com/discussion/alternating-row-colors

Did you find a working solution there?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

There are definitely a lot of different solutions there. Wow!

 

What I generally do is use my usual Row ID column (which I usually put in place right away for a host of other reasons). Then I use another column called "Format" and use the ISODD function to look at the row number.

 

=IF(ISODD([Row ID]@row), "Format")

 

Then I use basic conditional formatting pointing towards my "Format" column.

.

You could even use the MOD function pointed at the Row ID to generate a host of different numbers from zero to whatever, and have that replicated over and over again.

 

Say you used 

 

=MOD([Row ID]@row, 3)

 

your format column would be along the lines of

 

1

2

0

1

2

0

1

2

0

1

2

so on and so forth.

 

This would let you establish a repetition of 3 different formats going down the rows.

.

There are a lot of options out there, but I think the most reliable would be duplicating the row number and leveraging odd vs even. That way it will hold up to sorting, deleting rows, and adding rows.

Yanina Kupava

Hi Paul,

Thank you for another great method in the alternating row color dilemma! I am researching the best method to use and am looking for one that can respond to filters as well. Your response mentions that it holds up to sorting, deleting, adding, etc. Have you tested it for filtering?

Most of the solutions rely on a tie to a row or task ID and therefore do not adjust when filtered.

Paul Newcome

Unfortunately I am not aware of a solution that would hold up to filtering. I have a few vague ideas, but nothing that has been tested out or anything. Basically incorporating checkboxes that are checked for what is displayed based on the filter and then running your row count formula(s) off of that to drive the conditional formatting.

Paul Newcome

@Yanina Kupava How many different filters do you need or would you like total flexibility to use any filter possible on the sheet?

Paul Newcome

@Yanina Kupava Do you have access to the Sheet Summary? If so, are you using it for anything? I have come up with a solution where we use 1 row at the top of the sheet as a "formatting" row and the Sheet Summary fields in place of multiple filters.

A couple of extra helper columns, and a solution can be built where you can designate different formats for alternating rows that holds up to sorting and filtering.

Kleder Silva

@Paul Newcome Can you please explain the step by step to this solution?

Paul Newcome

@Kleder Silva Which solution exactly are you referring to?

Kleder Silva

@Paul Newcome the one that would hold up to filtering

Paul Newcome

Do you have access to the sheet summary?

Kleder Silva

Yes I do

Paul Newcome

@Kleder Silva This is actually a multi-step solution that builds off of another solution that allows for dynamic filtering. We will set that up then move on to the formatting solution.

The first step is to set up your filtering to run through the Sheet Summary. To do this you will need to create a checkbox column that I will call "Filter" for this example.

Next you will add in fields that will reflect the columns you want to filter on. These fields will be manual entry.

In the checkbox column, you will enter a formula that will check the box if any column in that row matches the data you set your Sheet Summary Fields up on.

In the attached screenshot. I have my fields set up to filter on First Name, Last Name, and/or ID. You are able to enter data into any or all of the fields.

The formula for the checkbox column goes something like this...

=IF(AND(FIND([First Name]#, [First Name]@row) > 0, FIND([Last Name]#, [Last Name]@row) > 0, FIND(ID#, ID@row) > 0), 1)

The next step is to add a row at the top of the sheet then indent all other rows below it so that they are children of the first. You will see in the above screenshot that I labeled this "Formatting Row" in my [First Name] column.

You are going to want to manually check the box in the Filter column for this row so that it stays checked and does not contain the formula that is in the rest of the rows below it.

Then we move on to the [Row ID] column. In the top Formatting row, you will enter

=IF(Filter@row = 1, COUNTIFS(Filter$1:Filter@row, 1))

and dragfill it down the rest of the rows.

Next we move to the Format column. In row 1 (and dragfilled down), you would use:

=IFERROR(MOD([Row ID]@row, 2), "")

NOTE: This formula is for two alternating colors for the rows. If you wanted additional formats alternating such as 3, 4, etc... You would change the 2 to whatever number of formats you want.

Finally you will go into your conditional formatting rules and set up your formats for the different rows that you want to alternate. My example is pretty basic, but you can modify the Format formula and the Conditional Formatting rules to whatever works for you.

This solution holds up to sorting and filtering. Let me know if the above instructions are clear enough to get it set up or if you need any further explanation for any of the steps.

Kleder Silva

@Paul Newcome

It is Gorgeous! And yes, the instructions are very clear to me. Thanks for taking the time to elaborate this guide. You are a real MVP!

Paul Newcome

Happy to help! 👍️

Sue Hill

Now if you all could come up with a way to properly do this on reports... or have a way to color code alternating rows with a button that performs the coloring like a filter....