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

  • Excel Charts - three methods for filtering Chart Data

    Filtering data in Excel charts is easy to do. The method you use should be based on the amount of data you have. In this video, I show three methods of filtering chart data. Method 1 is using Chart Filters. This is the easiest method.

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

  • Live Captions in Microsoft Teams with speaker attributions

    Recently Microsoft Teams updated their live caption enhancement to a newer version. We tested the enhanced live caption feature, discussed Teams on the web, punctuation and how live captions work in recorded videos.

  • Pop-out Chat in Microsoft Teams

    Teams now allow you to instantly pop-out your one-on-one or group chat into a separate window. After popping out the chat, you can drag it, place it on another monitor, resize, or close the window as you wish. Pop-out chats are a great way to get a lot done when you’re in a meeting or in a call. I demonstrate in this short video how to have a meeting in Teams and then pop-out two chats.

  • Teams Channel Calendar now available - New Feature

    Channels in Teams can have their own calendar. This is a great new feature that allows Teams members to quickly see upcoming channel meetings all in one place.

  • Teams vs. Outlook - Schedule a Teams meeting

    Scheduling a Teams meeting can be done in Outlook or Teams. We examine the pros and cons of using each app to schedule meetings.

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite 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.

Categories