Highlighting the top three finishers in different colors (gold, silver, bronze?)

[JenniferMurphy]

I am aware of the Conditional Formatting option that will highlight the top N values in a range.

Is there an easy way to highlight each of the top 3 in a different color? Can I set the background fill to gold, silver, and bronze for 1st, 2nd, and 3rd?

I tried creating three rules, one for the top three, one for the top 2, and one for the top 1. It sorta worked, but the colors kinda merged.

 

[JenniferMurphy]

Is each column identical cell wise, but just different to other columns, or are there variations of formatting within the column?

The columns are all the same shape (C8:C16, E8:E16, H8:H16, etc.).

Some have a heavy border on all 4 sides. Some have it on only 3 sides. There are three different background fill colors, but it is the same in each column. There are two (maybe three) font sizes and boldness settings.

I think you are going to suggest that I bite the bullet and copy all formatting to one cell in each type of column, then fix that up and copy to the other columns that are similar. No?

It sounds like there is no to copy just the conditional formatting short of using the recorder and trying to put it all in a macro, which sounds like a half day or work.

 

[ChrisR]

Yeah, well I think your two (Sensible!) options are:-

1) Copy across the conditional formatting from the top cell of (Let's Say column A) so A1. Paste special formats into B1. Fix up B1 by putting the font/borders back how you want them in that columns. Then Copy and paste special formats B1 down the column B. Repeat for the other columns.

2) Copy and paste special formats the entire column to the new column (Just watch your $ references as they will need to move to the new column - assuming you want a top three in each column) and then just reformat each column manually afterwards (borders/font etc)

The onl issue with either of the above is if you have variations in each column. For instance . You have D1 has green Comin Sans 12 font. Then D2 is a different font/colour etc...........This would be a pain to do then.........

Unfortunately though, I don't honestly think it's possible without the use of macros/vba............Which for six columns would be more hassle than manually setting up the CF............

 

[JenniferMurphy]

OK, thanks.

Excel is such a hodge podge. It's been patched and tweaked and "fixed" so many times, it's a wonder it works at all. I sure wouldn't want the job of working on the Excel development team.

The right solution would be to provide named conditional formats, like the named styles in Word (only do it right). That way I could define the format and save it, then apply it to any cell anywhere. Then if I need to change it, which I always do, I only have to do to in one place.

I did discover another little Excel gotcha. If I use the Format Painter to copy formatting from one place to another, most of the formatting replaces the formatting in the target cell. Not so with conditional formatting. I tried to copy some of the work on this and it didn't take. Figuring I did something wrong, I did it again. Eventually, I checked the Manage Rules entry and discovered that there were about a dozen rules defined. Sheesh. Would a warning kill them? I get lots of worthless warnings. Why not a useful one for a change?

Anyway, thanks for the help.

 

[ChrisR]

No problem, the workarounds can be a little annoying. But it must be a nightmare for the development team to keep up withm, considering the power and scope of Excel.

 

[JenniferMurphy]

I got it working on the columns containing the data, but I can't get the highlighting to apply to column X based on the data in column Y.

I'm not allowed to upload documents, so I put a test doc in a DropBox folder. Here's the link where you can download it:

https://www.dropbox.com/sh/0cy3w6gqhypdldd/MdtRe8O-w5

I hope someone can tell me what I am doing wrong. I've been banging my head against this wall for way too long now.

 

[MARK858]

Make the "applies to range" cover both columns and make the formula for all the range absolute. See Columns K & L in attached and look at the format rules.

https://www.box.com/s/y9zdmr6g7qswb6cymtnj

 

[MARK858]

all the range

Should say Columns

 

[JenniferMurphy]

I downloaded the modified workbook. It looks like it's working. Changes to B4:B12 cause the correct highlighting in C, K, & L. Excellent.

However, there are a number of anomalies that I'd like to understand.

I made a little table of the rules. It's in that same DropBox folder as a PDF file. I also renamed the Excel workbooks to add the time stamp and our initials so we can keep track of who did what.

My questions:

1. The C range has 6 rules. The other three have only 3. Is that an oversight?
2. In the K & L ranges, the first argument to the Rank function is absolute. In the B & C ranges, it's relative.
3. In the B range and half of the C range, the Stop if True options is checked. Everywhere else, it's unchecked. What does this option do?
4. I made a copy of the sheet so I could make changes. When I did, some of the rules changed. These are shown in red. I did move the K & L columns over to E & F so they would be closer. This is not what caused the change. I redid the copy and checked before doing anything.

This is very arcane.

 

[JenniferMurphy]

OK. I think I have it working. That was about 8-10 hours of my life that I'll never get back.

Here's what I think matters:

1. Use absolute references everywhere: in the rule and in the Applies to section.
2. Select the entire range that the rules will apply to, if possible. It's way better to have one set of rules for the entire range, than separate sets for each column.
3. If the entire range cannot be selected all at once, select what you can, but then make sore to manually add the other pieces in the Applies to section. Separate non-contiguous sections with commas.
4. Make sure to arrange the cells in order, if that matters.

Thanks for everyone's help. I could never have done this on my own. My little spreadsheet is working like a charm.

 

[tusharm]

The easiest way to do this is to modify the range to which the c.f. applies. It's trivially simple. Select the CF dropdown | Manage RUles...

In the resulting dialog box, in the 'Applies To' column, modify the current range to include whatever other range you want the c.f. to apply to.

So, for example, if it currently applies to A3:B10 and you want it to also apply to D3:E10, modify the Applies To field so that it contains A3:B10, D3:E10.

Obviously, the c.f. formula (assuming you are using a formula) must be such that it works with D3:E10.

ChrisR,

I need to do this in 7-8 columns of data. I wold love to be able to set it up once and copy the formatting to the other columns. The problem is the other columns are not all formatted the same. Some have a background, they use different font sizes, and there are different border situations.

Is there any way to copy just the conditional formatting?