Coloring One Record in Each Group in a Range

To color one record in each group in a range, use Conditional Formatting:

1. Select the range of cells (or select the entire column).
2. From the Format menu, select Conditional Formatting.
3. In the Condition 1 box, select Formula Is.
4. Enter the following formula (make sure you are careful about the Absolute and Relative references): =COUNTIF($A$2:A2,A2)=1
5. Click Format, and select the Patterns tab.
6. Choose any color, and then click OK twice.
Screenshot // Coloring One Record in Each Group in a Range
Coloring One Record in Each Group in a Range

Users are saying about us...

  1. If you have more than one column of data, then in Step 2, first press CTRL+SHIFT+RIGHT ARROW to select all the columns of data, and then press CTRL+SHIFT+DOWN ARROW to select all the rows of data. Then continue with the remaining steps.

  2. I want a function through which I can set or change the format of a cell entry. Although I can do it through “conditional formatting” option, but it permits only three conditions. I want more options.

  3. If I want that the entire row to be colored whenever a cell has a certain value, say if cell has the value “A”, then the entire row should be colored red, and so on. How can I achieve this task.

  4. can anyone help me in this. I have a range of cells with numbers. I use count function to count the range. But I want a cell to be counted or added to count function ONLY when I FILL COLOR to a particular cell. is this possible?

  5. “””If I want that the entire row to be colored whenever a cell has a certain value, say if cell has the value “”A””, then the entire row should be colored red, and so on. How can I achieve this task.””

    I am also searching for this answer. Any help for this novice would be appreciated…”

  6. “Try this:

    {=INDEX(Range,MATCH(TRUE,(Range)>0,0))}

    This assumes, at least implicitly, that ‘Range’ is one-dimensional.

    This is an array formula, so enter it without the curly brackets, using Shift-Ctrl-Enter. “

  7. “I am using a nested (if(and( formula that is giving me a value of “”false””. However, the conditions are clearly met by the last if/and statement and not met by any of the previous if/and statements of which there are five in total. What could be the source of this problem?

    Thank you very much for your help. “

  8. “Probably the reason is that things are not as ‘obvious’ as they may appear.

    I suggest you take your nested IFs and pull them out into separate cells (take them to a calculation sheet if necessary).

    It is generally bad form to nest IF statements to deep anyway, since they become very difficult to ‘read’ and de-bug (as you have discovered).

    Once you have done that, you will find that one specific logical test is not behaving as you expect. If you still can’t work it out, post that specific test / data back here, and hopefully we can help. “

  9. “Lets say I have a set of numbers in cel A1 and I have a description in cell G1. If I type the words test in G1, I want A1 to become a color. How would I do this?

  10. “Just use the conditional formatting feature (select the ‘FORMULA IS’ option).

    The help facility explains it in detail quite neatly. “

  11. “Was trying to find a way to colour cells automatically; never even knew that this function existed- was lucky to have found this site on the web.

    Just what I needed “

  12. If you have more than one column of data, then in Step 2, first press CTRL+SHIFT+RIGHT ARROW to select all the columns of data, and then press CTRL+SHIFT+DOWN ARROW to select all the rows of data. Then continue with the remaining steps.

  13. I want a function through which I can set or change the format of a cell entry. Although I can do it through “conditional formatting” option, but it permits only three conditions. I want more options.

  14. can anyone help me in this. I have a range of cells with numbers. I use count function to count the range. But I want a cell to be counted or added to count function ONLY when I FILL COLOR to a particular cell. is this possible?

  15. “Probably the reason is that things are not as ‘obvious’ as they may appear.

    I suggest you take your nested IFs and pull them out into separate cells (take them to a calculation sheet if necessary).

    It is generally bad form to nest IF statements to deep anyway, since they become very difficult to ‘read’ and de-bug (as you have discovered).

    Once you have done that, you will find that one specific logical test is not behaving as you expect. If you still can’t work it out, post that specific test / data back here, and hopefully we can help. “

  16. Lets say I have a set of numbers in cel A1 and I have a description in cell G1. If I type the words test in G1, I want A1 to become a color. How would I do this?

  17. “Was trying to find a way to colour cells automatically; never even knew that this function existed- was lucky to have found this site on the web.

    Just what I needed “

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube