How to group a pivot table by day of week (video)

Abstract

If you've used pivot tables at all, you understand the addictive power they give you for grouping dated information. You can group by year, by month, by quarter, and even by day and hour. But if you want to group by something like day of week, you'll need to do a little prep work first.

Transcript

Pivot tables are very good at grouping dated information. You can group by year, by month, by quarter, and even by day and hour. But if you want to group by something like day of week, you'll need to do a little prep work in the source data first.

Let's take a look.

Here we have some raw data for subscriptions to an email newsletter. Each row shows the date that a user subscribed. 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. With the Date field ungrouped, we get a new row in the pivot table for each row in the source data, which isn't very helpful.

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? To do that, we'll need to add a new column to the source data, and generate the day name with a function called TEXT.

First, I'll add a column called "weekday".

Now let's use TEXT.

The TEXT function lets you apply a number format to numeric values and get the result formatted as text.

You can use the TEXT function to extract parts of a date as text, or whenever you want to join a number with other text, and preserve a certain number format.

For example, I can use the TEXT function with the format "ddd" in double quotes to get abbreviated day names for each date.

Note that because we're in a table, Excel uses structured references and copies the formula down in one step.

If I want the full day name instead, I can just add another "d" in the format section.

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.

We can easily see exactly how many new subscribers we had on each day of the week.