Conditional Formatting with Percentages

%3CLINGO-SUB%20id%3D%22lingo-sub-2235380%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20with%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2235380%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Workbook%20that%20is%20full%20of%20data%20but%20I%20need%20a%20row%20highlighted%20based%20on%20the%20top%205%25%20of%20the%20percentages%20in%20the%20last%204%20columns%20of%20my%20sheet%20(AL%3AAO)%20***%20I%20attached%20a%20sample%20of%20the%20data%20that%20I%20have.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20rest%20of%20my%20sheet%20is%20full%20of%20number%20data%20but%20I%20am%20not%20sure%20how%20to%20apply%20it%20without%20taking%20the%20numbers%20from%20the%20other%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20all%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2235380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2235561%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2235561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684459%22%20target%3D%22_blank%22%3E%40mvargas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20rows%202%20to%2015.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMAX(IFERROR(%24AL2%3A%24AO2%2C0%25))%26gt%3B%3DPERCENTILE.INC(IFERROR(%24AL%242%3A%24AO%2415%2C0%25)%2C95%25)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab%2C%3C%2FP%3E%0A%3CP%3ESelect%20a%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am working with Excel 2019 with Windows 10. 

 

I have a Workbook that is full of data but I need the entire row highlighted based on the top 5% of the percentages in the last 4 columns of my sheet (AL:AO) *** I attached a sample of the data that I have. 

 

The rest of my sheet is full of number data but I am not sure how to apply it without taking the numbers from the other columns.  I need that when there 95%-100% in the those columns to highlight the entire row. I hope that makes sense

 

I appreciate all your help!

 

 

3 Replies

@mvargas 

Try the following:

 

Select rows 2 to 15.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=MAX(IFERROR($AL2:$AO2,0%))>=PERCENTILE.INC(IFERROR($AL$2:$AO$15,0%),95%)

 

Click Format...

Activate the Fill tab,

Select a color.

Click OK, then click OK again.

@Hans Vogelaar 

It didn't work. 

I need to highlight Greater or Equal to 95% if its in any of the columns (AL-AO)

Just how it looks in the second green highlighted row.

 

This highlighted some that are below the 95%. 

EXCEL_4jSleSmpBV.png

 

 

@mvargas

I interpreted your question differently. Change the formula to

 

=MAX(IFERROR($AL2:$AO2,0%))>=95%