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 Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.