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.
Leave a Reply