How to group a pivot table by day of week

Abstract

Pivot tables are very good at grouping dated information.

Let’s take a look.

Let’s quickly build a pivot table that summarizes new subscriptions by month.

For convenience, I’ll first convert the data into a table.

This will speed things up later, when we start working with the source data.

Now I’ll summarize the table with a pivot table.

I’ll also name the worksheets to keep things clear.

To start off, let’s add the Email Address field as a value in the pivot table.

This gives us a count of all email subscriptions, and tells us the pivot table is working correctly.

Next, let’s add the Date field as a row label.

To get a useful summary, we need to group the Date field.

When I group by Month, we get a nice, compact table.

But how do we group by day of week?

First, I’ll add a column called “weekday”.

Now let’s use TEXT.

TheTEXT functionlets you apply a number format to numeric values and get the result formatted as text.

Back in our pivot table, we need to refresh to pick up the new column.

Then I can add the weekday field to the pivot table.

Now the data is summarized by month and by day of week.