Quick, clean, and to the point

How to group a pivot table by day of week

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 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 name the worksheets quickly to keep things clear.

To start off, let's add the email address field as a value. This gives us a count of all email subscriptions, and tells us the pivot table is working correctly.

Now let's add the Date 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 compact table.

Pivot tables can group dates by year, month, and even quarter.

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 to apply a number format to numeric values and get the result formatted as text.

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

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

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

If I add another "d", I'll get full day names.

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

Then I can add weekday field to the pivot table.

Now the data is summarized by day of week, and we can 

Now we can see exactly how many new subscribers there were on each day of the week.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.