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.
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!
This is what the above steps should look like (in Excel 2010):