• ## Conditional Formatting: Top 4 as a Percent (Excel

Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Conditional Formatting: Top 4 as a Percent (Excel

Author
Topic
#447522

I looked at the information in post 682277 and it’s close… but not quite what I was looking for. I confess to knowing very little about VBA, so let me pose a question here and if there’s a reference on the boards, feel free to point me to it. I have a feeling that what I’m trying to accomplish will require a macro.

I have a spreadsheet with several (5-6) individual sheets. These are used to track inventory by make and model across different car dealerships. For the market being reviewed, I would like to highlight the top four as a percent of dealer inventory, which are in vertical columns. Additionally, horizontally across the sheet, the dealer with the largest number of any given model would be highlighted a different color.

I’ve attached a sample worksheet that shows the concept. Conditional formatting is too limited to accomplish this task, it would seem.

Author
Replies
• #1091679

You can use conditional formatting for this – see the second sheet in the attached workbook (I left the first for comparison). I set the conditional formatting formula for B6:20 to

=AND(B6>0,B6=MAX(\$B6,\$D6,\$F6,\$H6,\$J6,\$K6,\$L6,\$N6,\$P6,\$R6))

and that for C6:C20 to

=C6>=LARGE(C\$6:C\$20,4)

then used Copy and Paste Special with the Formatting option to copy the formatting to the rest of the table.

• #1091683

Thanks Hans, worked a charm. I didn’t realize I could use complex formulas in conditional formatting, but then it wouldn’t have mattered either, since I would still have had to ask how to identify the information the way I wanted! Time to start learning some advanced Excel features.