Subscribe to DSC Newsletter

Conditional Formatting in Excel – Highest Number in Each Row

Conditionally formatting each row individually is an issue that I struggled with for some time and finally found an answer.  I have a table that lists 28 different activities by day of the week.  On the report I need to highlight the day with the highest count per activity.

The solution is to essentially conditionally format each row to highlight the highest number.  But who wants to take time formatting 28 rows?  Plus, there are several other cities to analyze.  So it’s really 28 rows times X number of cities.  Spending a lot of time formatting a report does not make sense to me.  There has to be an easier way to do it.  Well, there is.


In a column to the side of the table, insert the following equation and drag down for all 28 rows:  =MAX(B3:H3)



We use the =MAX() formula to find the highest count in each row.  Note:  hide this column when publishing the report.


Next, we want to use the =MAX() column to conditionally format each row.  Go to Conditional Formatting > Manage Rules > New Rule > Format only cells that contain.  Under Format only cells with:  choose Cell Value > equal to.  For this example, the formula is =IF($I3>0, $I3, "").  Choose your format for the cells and click OK.  Under Applies to click the selection box on the right and highlight your entire table.  For this example, we choose =$B$3:$H$30.  Click Apply then OK and you are done!


Click here to read the full article at Data Driven 365.



Views: 9250

Tags: analysis, conditional, conditionally, crime, excel, format, formatting, highlight, highlighting, rows, More…spreadsheet, table


You need to be a member of AnalyticBridge to add comments!

Join AnalyticBridge

On Data Science Central

© 2021   TechTarget, Inc.   Powered by

Badges  |  Report an Issue  |  Privacy Policy  |  Terms of Service