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 […]

Excel Tip: Find and Conditionally Format Greatest Range Difference

This is a template that I used today in working through a statistics problem. In the video we will cover -finding the greatest and smallest values in a range with MAX and MIN-a simple IF statement-conditional formatting across a selection of cells (advanced! few analysts know how to do this).

Highlight Every Cell Containing a Formula

Checking for data accuracy is tricky in Excel.  In this blog, I have been presenting some of my tips.  This is a great one for more visually-oriented users. We have a simple inventory model below.  Even in this simple model, there are many formulas, dependent cells, and inputs.  How is the shrink calculated?  Did someone hard-code the […]

Build your own Heatmap in Excel

Conditional formatting is a great tool in Excel.  It sounds fancy, but if you’ve ever color-coded anything before, you know how conditional formatting works.  Given the rules you provide Excel, it will format things accordingly. Many conditional formatting features have been added to recent editions of Excel.  My favorite is the Color Scale.  This feature allows you to look […]