Power Query

Power Query (Data > Get Data, in Excel and Power BI) runs M code; type these in the Advanced Editor. Clean here, analyze in Excel.

Load a table from the workbook

Excel.CurrentWorkbook(){[Name="Sales"]}[Content]

Format the range as a table named Sales first.

Set column types

Table.TransformColumnTypes(Source, {{"Sales", type number}})

Wrong types are the most common reason averages fail.

Drop rows with a blank value

Table.SelectRows(Typed, each [Sales] <> null)

Mean, median, mode

List.Average(Clean[Sales])
List.Median(Clean[Sales])
List.Mode(Clean[Sales])

Table[Column] turns a column into a list.

Sample SD

List.StandardDeviation(Clean[Sales])

A sample estimate, the same as STDEV.S.

Average by group

Table.Group(Clean, {"Region"}, {{"Average sales", each List.Average([Sales]), type number}})

Quick profile of every column

Table.Profile(Clean)

Min, max, average, SD, count, and nulls per column.

Months across columns into rows

Table.UnpivotOtherColumns(Clean, {"Store"}, "Month", "Sales")

Most statistics need one row per observation.