How to summarize a column (in Excel)
Task
When provided with a dataset in which you want to focus on one column, how would you compute descriptive statistics for that column?
Related task:
Solution
Let’s assume you have some data in a single column of an Excel workbook. We show the first 10 rows (out of 27) for some example data below, but we assume you are applying what we cover here to your own real data.
To compute descriptive statistics, you will need the Data Analysis Toolpak. If you’ve never enabled it before, see these instructions from Microsoft on how to do so.
On the Data tab, click the Data Analysis button, shown below.
From the list of tools it provides, choose Descriptive Statistics, as shown below, then click OK.
Highlight all the cells in your column as input (excluding the column header, if any), then check the “Summary statistics” checkbox, as shown below. Then click OK.
Excel will create a new sheet that reports your column’s mean, median, variance, and more, as shown below.
To get a report of the unique values in your column and the frequency of each, you can use a pivot table. Highlight your column of data, then on the Insert tab, choose Pivot Table, as shown below.
Drag your column’s name from the list of pivot table fields down into both the Rows and Values areas, as shown below.
From the drop-down under Values, choose “Value field settings…” and change Sum to Count, as shown below. Then click OK.
Your pivot table will now contain the desired report. The first few rows with our example data look like the following. Most data points appear only once, but 6 appears twice and 34 appears three times.
Content last modified on 24 July 2023.
See a problem? Tell us or edit the source.