Outliers Function for Office 365 Excel Desktop App
I do not understand why Excel - Desktop or Web - does not have a function to identify and flag Outliers (statistical unusually high or low data values) in a column of data? Outliers in data skew any data analysis results whether we are talking about simple data visualization or more advanced regression and other modeling. For example, it could be a spreadsheet function where you enter "=Outlier" in a cell and then select range of data. Or, it could show up in the list of Excel Functions under the Formulas toolbar tab at the top of Excel. Or, it might be in the form of a .xlsm template with a macro button on spreadsheet that a user can click like the sample file I have attached.
Hi AdamV. Yes. You could easily, I am sure, add this type of feature to the Excel toolbar somewhere. But the important thing for me is that, in additional to using colours to highlight high and low outlier values (I like your suggestion of different colours for each) ---- a new column(s) of data is added so that high and low outliers can be explored against other fields (columns of data) in your dataset using pivot tables, boxplots, and other data visualization and exploration techniques. That's what I tried to do in the templates I developed. Thanks for commenting.
How about adding something to the standard conditional formatting gallery, with option to refine what "outlier" means in the conditional formatting rules dialog box?
Eg "Highlight outliers" > choose between more than 2 standard devations from the mean by default, or twice the inter-quartile range from the median. Dialog box allows for other definitions such as 3 sd or 1.5 x IQ range.
Bonus: highlight the outliers differently if they are high/low (similar UI as current one to choose colour scales or icons)
It is an array fed by an algorithm with logic as to how to walk through the array of data and flag outliers based on a statistical test. So, no matter what your array of data is, you can feed it through algorithm and get result.
Celia Alves commented
I guess it would work as a dynamic array function, spilling the list of values down the column when now than one outlier was found.