I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows for multiple datasets and have the conditional formatting function on each individually.

Currently, I have it working in the initial dataset, but it breaks when copying more below it. My conditional formatting is currently (with two additional formats for silver and bronze)


I've tried pretty much every variation in absolutes and relatives, as well as using RANK without success.

Here is what I've done. The first set is working, but the copied second doesn't

Here's a link to it as well https://docs.google.com/spreadsheets/d/1kIYfF2cCDfWTYVebFsXxveh2f9GoOjydAG2XOiFw2v0/edit?usp=sharing

  • Russell, this can be done with some very complex conditional formatting. I would not be able to explain it well here. If you'd like to change your sheet link to "Anyone with the link can edit," I'd be happy to implement the CF after which you can study it if you like.
    – Erik Tyler
    Commented Jul 10, 2020 at 5:54
  • I've made it so that anyone can edit. Thank you for the help.
    – Olivia
    Commented Jul 10, 2020 at 18:55

1 Answer 1


Russell, I've gone in and made a new sheet ('Erik CF') wherein I've implemented the conditional formatting. Here is a brief explanation:

1.) I removed unused columns and rows. You should always do this, since it speeds up processing, especially for full-column array formulas (which are used in my conditional formatting setup).

2.) The four CF formulas are applied to the entire scored range (i.e., C3:E). This is what makes the formulas so complex: you don't need to apply them separately to different ranges, but they must also locate each block of scores.

3.) To achieve this, I've included many INDIRECT calls within the formulas. INDIRECT allows us construct a string that we can then use as a live range. Thing is, those live ranges are unknown. So I assessed that the markers of where a live score range begins and ends is a number preceded by a null cell and a number followed by a null cell, respectively. So the CF formulas create forward- and backward-looking QUERYs and then use VLOOKUP to find those nulls for any given score (i.e., the closest null before it and after it). Those positions are then used to Frankenstein together the INDIRECT ranges for every score.

4.) The three main formulas (i.e., those that apply gold, silver, bronze) are essentially the same "custom formula." The only difference is the very last number in the very long formula, which assigns the LARGE value of 1, 2 or 3.

That said, for future reference here on StackExchange, here is the formula for applying first place/gold to the scores in range C3:E (where, again, changing the last number to 2 or 3 would find second place and third place):


As complex as that formula is, it is easy to modify, in that all you'd need to do to apply it to any range is change every "C3" to the upper-leftmost cell in the range to which you're applying it.

  • Thanks Erik, this works perfectly on the example sheet. However, I've made quite a few changes since I originally posted, one of which creates an issue. Currently most data ranges don't have a blank space between them, instead it's become primarily strings from formulas (with one column having a checkbox). This means the "/" in the VLOOKUPs isn't effective. I'll most likely come to a solution as I learn the formula you made, or worst case scenario I can format the sheet again (although as you mentioned unnecessary rows = bad), but perhaps you have a simple way of addressing this?
    – Olivia
    Commented Jul 11, 2020 at 20:51
  • Hi, Russell. Do keep in mind that sites like this are designed to provide short-term guidance and specific help rather than "free unlimited work." In addition, I'm sure you can imagine how it might seem putting work into helping solve an issue only to be met with "but I changed everything." My best advice is to start a new post with access to a new editable sheet that accurately represents your new setup in order to have the best chance at someone helping you. In the meantime, if the answer I provided solved your posted problem given your posted sheet, please mark it as the correct answer.
    – Erik Tyler
    Commented Jul 11, 2020 at 23:02
  • Good point, I hadn't intended it that way. Thanks again.
    – Olivia
    Commented Jul 12, 2020 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.