Aggregating data in Excel

This tutorial will help you to group (also referred as aggregate) data in Excel using XLSTAT.

Dataset for grouping data

The data represent the sales amount of a supermarket chain on a weekly basis. Columns A-B contain the month and the week associated with each sales record. Columns C-F contain the sales amount for each store location (South, West, East, and North).

Goal of this tutorial

Grouping is a common task in database management. It allows us to obtain summary statistics (e.g. mean, sum, max, etc.) of one or more quantitative variables by groups. Here, we want to obtain the total amount of sales per month for each store location.

Setting up a data grouping with XLSTAT

1. Once XLSTAT is open, select the Data Management command as shown below.

2. The Data management dialog box appears.

Select columns B-F from Sheet1 in the field Data. Then select the method Group and the operation Sum. Headers are also included in our data selection, so we check the Variable labels.

3. Click on the OK button to select the grouping **key Month in order to compute the sum of sales amount per month. Click on the OK button. The XLSTAT report will be generated in a new sheet named Group.

Results from data grouping

XLSTAT generates the grouped table below including the total sales amount per month for each of the locations.