Doing Outlier Analysis in Excel: Conditional Formatting Color Scale vs. Box Plot Analysis Algorithm


Download the FREE Find Outlier Box Plot Analysis Excel Template and compare the accuracy of Excel's conditional formatting menu to a box plot data analysis algorithm for finding statistically significant, unusually high or low data points. Watch the soundless instruction video before using this template.


Do you see anything interesting when comparing the accuracy of Excel's color scale formatting to an Excel-based box plot algorithm applied to the "Count of Customer" Column E data?


Use the "Count of Customer" data provided in Column E of the free template after you download it. Click the Find Unusual Data! button and follow the instructions prompting you to select Column E. Then, watch as a new "Outlier Flag" column is added to your spreadsheet.


Review the following two links for help to interpret what the "High Outlier" yellow shaded values mean in the "Outlier Flag" data column of the template:


Now, go back to the downloaded template, and highlight Column E, the "Count of Customer" column: but, this time, select "Conditional Formatting"-"Color Scales" and choose any color scale to see how each cell in Column E is assigned a certain shade of color.


One thing you will notice, is that a "High Outlier" value in the "Outlier Flag" column may be reflected by more than one shade of color assigned to the value in the "Count of Customer" Column E by Excel's conditional formatting color scale menu.

0 Replies