3

I would like to apply different colors to different consecutive text values. But unfortunately, applying color scale works only on numbers!

I’m doing this to easily identify the repeated values sorted by other column.

This image explains the idea that I want to achieve:

enter image description here

7
  • What condition are you trying to meet? Is it 'identical values in the second column get the same colour'? Do you care what gets what colour? How many values are there, or what's their range - that will effect how much colour differentiation you'll get Dec 4, 2014 at 22:41
  • Yes, it is the identical values in the second column! and I don't care about what gets what color, There are about 10'000 unique values. By default when you apply the scale color, you will get the same result in the image, but only for numerical values ! and if the second column contains text, it will not be included in the condition. I saw some answers about making a condition for each value and assign it a color! which is not practical in my case !
    – Nour
    Dec 5, 2014 at 10:22
  • Wait, is the issue that column A isn't being given a color based on Column B, or based on its uniqueness in Column A? Dec 5, 2014 at 14:38
  • @Raystafarian the image explain what I want to achieve, I want to easily identify groups of repeated values in all columns based on the second columns.
    – Nour
    Dec 7, 2014 at 9:54
  • @nour no, it is unclear. But I'm glad you found a solution, it's just best for all Q&As to be clear for future visitors. Dec 7, 2014 at 10:49

4 Answers 4

3
+50

You can use a conditional format to highlight every other set of unique values.

=MOD(SUM(--(OFFSET($A$1,0,0,MAX(ROW(A1)-1,0))<>OFFSET($A$2,0,0,MAX(ROW(A1)-1,0)))),2)

enter image description here

You can significantly shorten the formula if you have a maximum range you want to apply the formatting over, but it would be less robust.

=MOD(SUM(--$A$5:$A5<>$A$6:$A6),2)

enter image description here

4
  • So I can't comment on questions yet, but in your example you have repeated values that aren't next to each other, and the have the same color. Is this a requirement? If so my solution fails, and I believe any successful solution would require VBA. Something to consider though, the human eye would not be able to differentiate 10,000 colors unless they were next to each other. I would estimate you could get up to about 50 colors before the dark pink and light red get too close to tell apart.
    – Rick
    Dec 5, 2014 at 14:57
  • Perfect ! That is exactly what I want.
    – Nour
    Dec 7, 2014 at 9:45
  • This solution does work, but it really bogs down Excel in calculating when I apply it to a bunch of cells.
    – Mike
    Jul 9, 2015 at 15:53
  • @Mike Yes, there's no way to save or access intermediate results with this solution (or any purely conditional formatting solution) so for each cell it must examine all previous cells making it a O(n^2) runtime. To alleviate this problem, additional columns could be added (like in agtoever's solution) or VBA could be used.
    – Rick
    Jul 9, 2015 at 16:15
2

Yes it is possible because I just did it. First, you highlight the cells you want to format. Then you take this formatting rule and do it 3 separate times (or as many different times as you need it for each color scheme).

Your rule will be "Specific Text - ending with - INSERT YOUR UNIQUE ENDING NUMBERS HERE. I created a red/yellow/green pattern for mine based on 3 different values. Hope this helps! Remember to create a new rule 3 separate times and it will work.

enter image description here

2

Ok I made a solution for this problem by creating a hash from each cell value. The hash could be between 0 and 1. Then, the hash is binned it into 20 conditional formatting rules which each take a 0.05 wide chunk and give it a colour.

Distinct colours in excel

You can copy the conditional formatting rule from this spreadsheet I made:

https://github.com/gdmcdonald/Distinct_colours_excel

5
  • 1
    Welcome to SuperUser! How is this better than the accepted answer? Jul 1, 2022 at 7:51
  • Reproducibly colouring duplicate stings the same, and in 20 different colours makes it only 5% chance to have two colours next to each other the same if the string are different. Also, as I provided an excel file anyone coming here can just copy the conditional formatting with the paintbrush icon instead of setting it all up themselves. Jul 8, 2022 at 1:26
  • This is incredibly useful and deserves way more attention! As someone who's colorblind, I wonder if this could be iterated with more distinct colors – though the fact that they need to background colors under the same text color does limit that. I can change it for myself but as people use your resource that might be helpful! Thank you so much for this tool!
    – Unrelated
    Sep 16, 2022 at 22:17
  • If you want to make a excel spreadsheet with all the various colors you would use for a colorblind friendly palette then i can stick it together for you to make the conditional formatting? Then I would put that up as well. Sep 20, 2022 at 6:36
  • I'll do so in the near future!
    – Unrelated
    Sep 20, 2022 at 17:07
0

I have a solution that does not exactly do what you want, but based on your description, I think it serves your purpose. Follow these steps:

  • Select the cell second data item that you want to color.
  • Select "Conditional formatting", "Highlight cell rules", "Equal to..."
  • Select the first data item you want to color and remove the $'s from the cell reference (this is important!).
  • Select any formatting colors that you want (I selected the default "Light red fill with dark red text")
  • Copy (CTRL+c), select the entire column where your data is and paste only the formatting.

Now every item that is equal to the previous gets a color. This way you can see how large the "blocks" of equal values are. My sample output (based on the first records of your example):

sample output

Another, and a bit more complicated way to do this, is to create an extra column that alternate's 1's and 0's if a new number shows up. Put =0 as the first value, put this in the second (product number are in column B): =IF(B3=B2;C2;ABS(C2-1)) (note that ABS(<cell>-1 is a trick to create alternating 0's and 1's). Now you can color that column using conditional formatting:

alternative sample output

2
  • Your solution is good for me, the only problem is that the first cell of each group, is not formatted like the remaining similar cells, and the second solution allows to apply scale color only on the extra column, and I need the original column to be formatted. I would choose your answer if I didn't find Rick answer, but his answer worked exactly as I wanted. Thank you very much anyway.
    – Nour
    Dec 7, 2014 at 9:44
  • The formatting for the second example could be extended to the main data via equation based conditional formatting.
    – Rick
    Jul 9, 2015 at 16:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .