Tutorial: grouping data by month in Excel

The amount of FFP2 and FFP3 masks distributed each month in the UK.

When you have a set of temporal data in Excel, in can sometimes be useful to analyse this data on a month by month basis. The image above, for example, shows how many FFP2 and FFP3 were distributed in the UK each month since the start of the pandemic.

The original data, as recently released by the UK government, does not include these kinds of aggregate statistics. It lists the amount of distributed personal protective equipment (PPE) items separately for each day.

This tutorial will show you how to get from the raw data to the summary as shown above.

Step 1: download the data

Download the data to follow along with the tutorial.

The data is released in OpenDocument format, which you should be able to open using Microsoft Excel. Go ahead and do that.

Step 2: transpose columns and rows

Once you’ve opened the document in Excel, you’ll notice that it contains three different spreadsheets. The one we’re interested in is called Table_1.

What you’ll find on this sheet is a list of different PPE items, with each column containing how many items were distributed on a specific day. The date is listed at the top of each column.

Given that we want to analyse this data on a month by month basis, what we’ll need to do is group these numbers into aggregates for each month. Excel has a neat feature for this, which is called Pivot Tables.

The workflow for creating a pivot table is outside the scope of this article, so if you’re not familiar, please refer to the official documentation.

Unfortunately if we try to create a pivot table for this sheet, it will not yield the result that we expect. You’ll most likely get an error message like this:

Excel expects our data to be organised as a list with labelled columns.

In order for Excel to understand our data, it must be arranged in a way where each column has a label. The labels for our data are currently organised in rows. We’ll need to transpose the data so that rows become columns and vice versa.

Here’s how to do that:

1. Select all the data in the spreadsheet by pressing Command (Ctrl on Windows) + A on your keyboard.
2. Create a new sheet using the button with the plus sign next to the row of sheet names.
3. Find the paste button and click the little arrow next to it. Select the Transpose option.

Selecting Transpose will paste the data on your clipboard and transpose it at the same time.

Step 3: create a pivot table

You should now have a spreadsheet where each column represents a PPE category. The first column might be completely empty. If that is the case, go ahead and delete it.

The second column now contains a list of dates. Let’s rename this column to Date. This is done by editing the text in the first row of the column.

Select any cell in the data and insert a new pivot table using the Pivot Table button. Choose the option to place the pivot table in a new worksheet.

In the field editor, drag the date field to Columns. You’ll notice that Excel creates a Months field for us when we do this.

Drag the Total field to Values. You should now see the total amount of distributed PPE items for each month.

This is what we wanted, but what if we’d also like to know how many gloves were distributed in April for instance? We won’t be able to gather this from the totals.

Let’s drag the Gloves field to Values as well. As you do this, you’ll notice that the data doesn’t look like you might expect it to. This is because Excel has grouped the data for each month into two categories. One for gloves, and one for the totals.

This behaviour is reflected in the Columns box, which now contains a values field. All we need to do is drag this field to Rows, and the table is organised the way we want it to be.

The end result.

Bonus: grouping by week

It could be that you’d like to see if the number of distributed PPE items went up following a certain event. In that case, you’d likely want to examine the data on a slightly more granular level. One way of doing this, is to group the data by week instead of month.

These are the steps to do so:

1. Go back to the sheet with the transposed source data.
2. Insert a new column right after the Date column and call it Week.
3. Select the first empty cell in this column and paste the following formula =WEEKNUM(B2). B2 should reference the first cell in the list of dates. If it does not, adjust formula accordingly.
4. Right click the cell and select Format Cells… Select General and click OK. The cell should now display the week number for the date in the previous column.
5. Hover over the bottom right corner of the cell until your mouse pointer becomes a black cross. Double-click to fill the rest of the column with the same formula.
6. Go to the pivot table again and click Refresh in the toolbar. The list of fields for the pivot table should now contain the field we just created.
7. Drag this field to Columns, and drag the Months field to Filters. This way you’ll be able to filter on month while seeing the data for each week.