Back to Getting Started

Summarizing data

Most of you have probably used

=SUM(), =AVERAGE(), =COUNT(), =MAX()

to summarize data at some point in your spreadsheet careers. Some of you use Pivot Tables regularly to create more advanced reports.

All of us agree that manually dragging formulas is error-prone and Pivot Table builders can be confusing and slow to use.

At AlphaSheets we understand this, and that's why we offer an intuitive, point-and-click way to summarize your data right where it is. Meet Group and Summarize, the only two operations you'll ever need to create reports.


Let's dive in to an example.

1. Group

Suppose we have a list of whole foods, with three columns: name of the food, fruit or a vegetable, and organic or not.

We want to divide the list by type, and also by organicness. Here's how: 


Simply right-click the "type" column and click Group (and do the same with "organic").

This reorders the records in the table by the distinct values in that column, and moves the column so that it's next to the other grouped columns (if any).

Now suppose we only want to group by "organic". Easy - just Ungroup the "type" column:

     

2. Summarize

Combined with grouping, Summarize is a powerful way to break down your data.

Often, there are operations you want to apply to each group. For example, sum-per-group, average-per-group, etc.

Suppose we want to count the number of organic vs. inorganic foods. Simply right-click any other column, and click Summarize › Count all:


You can even nest groups and aggregates:

(TODO FIX!)


And that's it!

3. Reduce

Once you've grouped and summarized your data, delete unnecessary columns to produce breakdowns very quickly:


And that's it!

Additional information

Data types

Depending on the data type, there are different options for grouping and summarizing.

Type

Numbers

Dates

Strings

Group by...

Summarize by...

value (default)

value (default)

Date, Day of week, Week, Month, Year, Quarter

sum, average, min, max, median, standard deviation, variance, count, count non-empty

count, count non-empty

count, count non-empty



Back to Getting Started