Improve Table Visualization using OFFSET and Conditional Formatting

With our knowledge of the OFFSET function, here’s a tip on how to use that function combined with Conditional Formatting to make a table more legible by decluttering.

This tip can be applied on a table where the items of the main field are layed out on multiple lines, just like in the next picture.

Table before Conditional Format

It is possible to improve the readability of that table with formatting by separating the group of rows for each account with a border and by changing consecutive identical items to light gray.

Table after Conditional Format

Here are the rules that were applied to achieve this.

A few notes

• Rule #1 is applied to all cells in the table, whereas rule #2 is only for column A.
• This setup is valid for Excel 2007 or later. To adapt it for previous versions, in column A, you need to add a rule that combines rule #1 and rule #2 and apply borders and pale gray fonts. This is due to the fact that only one Conditional Formatting rule could be applied in older Excel versions (download the accompanying workbook for more details).
• I’ve put both the A1 and R1C1 formula in the table. I sometime find it easier to work in R1C1 while doing Conditional Formatting, the main reason being that I don’t have to bother with what is the active cell while editing the formula.
• I could have used a simpler formula without the OFFSET function like =$A2=$A3 instead. However, I prefer the OFFSET version since it is robust to row insertion.

You can download the accompanying workbook to see the table in action and with additional comments.

If you want to learn more about R1C1 reference style, I encourage you to read this post.