Excel Tip: Excluding Diagonals from Conditional Formatting

Download the exercise file here. 

This afternoon I created a correlation matrix which I then conditionally formatted to give me a heatmap-like take on the data:

Going to Data | Conditional Format | Color Scales, I decided that green would be good and red bad (Logical, right? Thanks, Excel.).

Notice a problem? The 1’s across the diagonal are skewing the relative intensity of our colors. Of COURSE they are going to be the highest value as 1 is the highest possible correlation, anyway! Not only that, they are superfluous information.

Here’s a solution.

1. Copy and paste your correlation matrix below the original

2. Using an IF statement, we will replace the diagonals with a dash (not uncommon formatting for correlation matrices). We’ll do this by using the ROW and COLUMN functions – when the row and column numbers are equal, convert to a dash.

IF(ROW(B2)=COLUMN(B2),”-“,B2)

3. Fill that formula through your matrix. Notice the color change! Excel does not format what is not a number, and you have a more useful conditional format.

Subscribe to my mailing list.

Leave a Reply

%d bloggers like this: