How to auto format a table in Excel?

Question

I have a table in Excel. In each row, I want to highlight the columns that correspond to the maximum value in that row.

For example, if I have this table:

0 1 2 3

4 5 3 5

8 9 3 4

I want to highlight 3 in row 1, 5 in row 2 and 9 in row 3.

How can I do that automatically?

THANKS!

Answer 1

In Excel 2008, you can use Conditional Formatting in the Styles section of the Home Tab. Choose "Highlight Cells Rule", then "More Rules...". That opens the "New Formating Rule" dialog. Choose "Format only top or bottom ranked values". Then choose Format values that rank in the "Top" 1 and leave the % of selected range box UNchecked. Then click the Format button to pick the format that you want.

If you want to be able to do this fully automatically, what you can do is record a macro that does the actions described above and use that to repeat for all rows of your table.

Answer 2

I think you could do this with conditional formatting, and you wouldn't require code. Just create a formula that looks to see if the cell value is equal to MAX(range_name) and highlight the cell in whatever way you choose.

Answer 3

This is somewhat roundabout, but works for me. Add an extra column (E) somewhere, so you end up with this for data:

   A   B   C   D   E
1  0   1   2   3   =MAXA(A1:D1)
2  4   5   3   5   =MAXA(A2:D2)
3  8   9   3   4   =MAXA(A3:D3)

Then in a cell, add a conditional format:

For A1: =IF(A1 = $E$1, 1, 0)

Use the format painter to copy this to the other cells in the row. Then copy to the column, modify each column so the $E$1 reference is corrected, and copy to those rose as well

Ugly, but gets the job done. Probably a quicker way to do it with VBA.