CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
Zack
Zack

Reputation: 2497

How to alternate colors based on first column of google sheet

So I have a spread sheet that has a bunch of data in it... The first column is the state, second is a name. What I want to do is have the rows alternate blue/red/blue/red (to make it easy to read) but I don't want it to alternate EVERY row like evens/odds. What I want is for each state to have a different color. So all the rows with AL blue, then CA red, CT blue, etc... This is just a basic example. Obviously here I could just hardcode the 50 states, but is there any way to automate this process so that basically every time the state changes, I switch colors? 

State    Name
AL       John
CA       Bill
CA       Joe
CA       Chad
CT       Mary
VA       Beth
VA       Dani

Upvotes: 1

Views: 1181

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34420

In Google Sheets you can count how many unique names there are down to the current cell and test if it's an odd number

=isodd(countunique(A$2:A2))

Apply this to the range as a custom formula in conditional formats with the first fill colour.

Then apply 

=iseven(countunique(A$2:A2))

as another rule with the second fill colour.

In Excel it's more difficult to do this, would end up with something like

=ISODD(SUM(--(FREQUENCY(MATCH(A$2:A2,A$2:A2,0),MATCH(A$2:A2,A$2:A2,0))>0)))

and

=ISEVEN(SUM(--(FREQUENCY(MATCH(A$2:A2,A$2:A2,0),MATCH(A$2:A2,A$2:A2,0))>0)))

Note this only works if the data is sorted by state as in your test data.

Upvotes: 5

NiVeR
NiVeR

Reputation: 9806

You can use Format as a table with the following procedure:

  • Open the worksheet.
  • Select the cell range that you want to shade, or press Ctrl+A to select the whole worksheet.
  • Click the Home tab.
  • In the Styles group, click Format as Table, and then click the style of formatting that you want.
  • In the Format as Table dialog box, click OK.

Also, as an alternative you can use Conditional Formatting.

Upvotes: 0

Related Questions