Excel PivotTable - Summarize data by Month or Day of the week

Posted on:  03/05/2021
Excel PivotTable - Summarize data by Month or Day of the week

If you need to find out what day of the week is the most profitable or what day of the week do we receive the most support calls, Excel's PivotTables can handle it. PivotTables will group dates automatically. If you have any date field - invoice dates, order dates, hire dates - Excel will group by year, quarter, and month for you. No formula's needed.

Image 1 is Excel starting on January 1. The data went to December 31. In Image 2, is Excel's PivotTable automatically grouping the months in column A. 

If you want to group by day of the week, we can use the TEXT function in Excel and group by the day of the week. In this example, I use PivotTable and the Text function to find out what day of the week do my YouTube videos get the most watch time in hours.

Other Excel articles

YouTube video

TEXT Function in Excel

The TEXT function returns a number or date in a given number format, as text. I use the text function to get the date formatted as a three-letter date in the video. Example: 1/1/2020, which is a Wednesday, I showed as Wed. 

TEXT Function Arguments

The text function has two arguments. Both arguments are required. 

=TEXT(Value,Format Text)

  • value - The number to convert.
  • format_text - The number format to use.

TEXT Function Examples

Below are some TEXT function examples for dates. Month is the letter "M", day is the letter "D", and year is the letter "Y".

We are going  to use the month of September 2021 in our example

The letter D

Date d dd ddd dddd
9/1/2021 =Text(A2,"d") would return 1 =Text(A2,"dd") would return 01 =Text(A2,"ddd") would return Wed =Text(A2,"dddd") would return Wednesday

 

The letter M

Date d dd ddd dddd
9/1/2021 =Text(A2,"m") would return 1 =Text(A2,"mm") would return 01 =Text(A2,"mmm") would return Sep =Text(A2,"mmmm") would return September

 

Other Excel articles

Create a PivotTable

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

  1. Click inside your data. Make sure your data doesn't have any blank rows or columns and only a single header row.
  2. Click the Insert tab and select PivotTable.
  3. Your Table/Range will be selected. Make sure it is correct. You can make the PivotTable in a new worksheet or existing worksheet. To create the PivotTable on a new worksheet click OK.

     

Build your PivotTable

The PivotTable Fields pane appears on the left. There are four areas:

  • Filters
  • Columns
  • Rows
  • Values

Add fields to the PivotTable

  1. Your header rows from your data are called PivotTable fields.
  2. Drag and drop the fields to create a PivotTable. You can check the fields also to create a PivotTable. You can move fields by dragging to another area.

 

Microsoft Teams articles

Chris Menard

Chris is a Training Specialist for a global law firm. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories