Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to automatically color each row of data, so that the beginning and end of each day is more visible? Do you think I can do that with conditional formatting? Something like the following image:

enter image description here

Update: I need the solution to work even if there are some dates missing between two days. e.g. in the following data, 5-Oct-12 and 7-Oct-12 do not exist:

enter image description here

share|improve this question
Not what you asked for but much simpler and PERHAPS suitable 'visibility', might not colouring each new day be sufficient (ie colour+no colour, rather than colour1+colour2)? – pnuts Oct 19 '12 at 11:53
@pnuts color + no color would be fine too. I just need to distinguish different days. – Meysam Oct 19 '12 at 12:16
=A1<>A2 is sufficient to identify a date change - but you might not like that if you say added 9 & 10 Oct above, with one Value only each, since both dates would be highlighted (despite being immediately adjacent). – pnuts Oct 19 '12 at 12:25
@pnuts and =A1<>A2 is able to only highlight the first row of each group of days, which is not desired. – Meysam Oct 19 '12 at 15:38
If you use a border line above the row, rather than a coloured background, then this groups the dates neatly according to the first row of each group, and means that a singleton gets the same formatting as a group. Imperfect I know, hence not an answer, but probably the method I would use (I don't favour lots of background colours which reduce contrast and readability) – AdamV Oct 22 '12 at 8:36
up vote 1 down vote accepted

With a single helper column and no issues over missing dates or singletons:

in C1 type "TRUE" (or whatever row your column headers are in) In C2 use this formula:


So this checks if we are still on the same day, in which case use the same value as row above, otherwise "flip" between true and false.

Your conditional format then simply needs the 'formula' =$C2 and choose a nice light background shade of grey. I would also suggest a second conditional format using a formula of =$A2<>$A1 and use that to set a top border on the cells too to help further distinguish them, especially on printed material.

share|improve this answer

Format whole column blue and use =ISEVEN(A2) for the conditional format rule, with green highlighting.

share|improve this answer
Thank you, this works. But I should have asked my question more clearly. I updated it with a situation that ISEVEN(A2) does not cover because of some missing days. – Meysam Oct 19 '12 at 9:31

add another two columns (for example: D and E)to assist formatting:

Formula in D : =COUNTIF($A$2:A2,A2)

Formula in E : =COUNTIF($D$2:D2,1)

Conditional Formatting in A : =MOD(E2,2)=1, color blue

Conditional Formatting in A : =MOD(E2,2)=0, color green

share|improve this answer
Have you tested it? I doesn't seem to work for me. There is also no need to use RIGHT method. you can simply check for MOD(E1, 2). – Meysam Oct 19 '12 at 9:53
Sure, I have tested it works. You are right the RIGHT is not necessary, I just edited the formula that was for the question before you updated so the RIGHT kept. – Hans Chen Oct 19 '12 at 9:54
I hoped there was some solution without using extra columns. It seems that adding extra columns is inevitable though. – Meysam Oct 19 '12 at 10:18
I don't like extra columns either. I thought another possible solution is to make it by macro. – Hans Chen Oct 19 '12 at 10:35
If you have many rows, all those countifs will start to add up to a serious performance hit on recalculation – AdamV Oct 22 '12 at 8:47

You must log in to answer this question.

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