• 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.

    Viewing 0 reply threads
    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.

    Viewing 0 reply threads
    Reply To: Conditional Formatting: Top 4 as a Percent (Excel

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: