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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
