Author: Oscar Cronquist Article last updated on August 28, 2017

Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table and two slicers.

pivot table calendar3

Instructions

I am going to store my calendar data on another sheet named data.

Step 1 - Create an excel defined table

  1. Go to sheet data
  2. Type header names shown in the picture below.
  3. Create an excel defined table
    1. Select headers
    2. Go to tab "Insert"
    3. Click "Table" button
    4. Click box "My table has headers"
    5. Click OK

Recommended article

Become more productive โ€“ Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Become more productive โ€“ Learn Excel Defined Tables

Add dates

  1. Select cell A2
  2. Type the first date 2013-01-01 or 1/1/2013
    pivot table calendar
  3. Click and hold on the black dot
  4. Drag down a few hundred rows depending on how many dates you want in your calendar

Recommended article

Create a drop down calendar

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down [โ€ฆ]

Create a drop down calendar

Add formulas

  1. Select cell B2
  2. Type:
    =YEAR([@Date])
  3. Select cell C2
  4. Type:
    =INDEX({"01-January";"02-February";"03-March";"04-April";"05-May";"06-June";"07-July";"08-August";"09-September";"10-October";"11-November";"12-December"},MONTH([@Date]))
  5. Select cell D2
  6. Type:
    =INDEX({"Mo";"Tu";"We";"Th";"Fr";"Sa";"Su"},WEEKDAY([@Date],2))
  7. Select cell E2
  8. Type:
    =IF([@Date]<>""," ","")
  9. Select cell F2
  10. Type:
    =IF([@Event]<>" ",1,"")

Add your own events in column E. Delete the formula in column E for a specific date and enter your own event in column E.

Step 2 - Insert the pivot table

  1. Go to a new sheet, I named it Calendar.
  2. Go to tab "Insert"
  3. Click "Pivot table" button
    pivot table calendar1
  4. Select your table and a location
  5. Click OK!

Discover Pivot Tables โ€“ Excelโ€™s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

Discover Pivot Tables โ€“ Excelโ€™s most powerful feature and also least known

Step 3 - Pivot table settings

  1. Click a cell in the pivot table
  2. You can now see the PivotTable Field list to the right.
  3. Click and hold on date field, drag it down to Column Labels.
    pivot table calendar2
  4. Repeat with month field.
  5. Click and drag Event to Row Labels
  6. Click and drag Value to Values

Change Value field setting

  1. Click on black arrow near the Value in Values.
    pivot table calendar4
  2. Select value field settings...
  3. Go to "Summarize value field by" tab.
  4. Select Sum
    pivot table calendar3
  5. Click OK

Change cell formatting

  1. Select all dates on the pivot table
  2. Right click on selected cells
  3. Click "Format cells..."
  4. Go to tab number
  5. Select category "Custom"
  6. Type D
  7. Click OK

Step 4 - Create slicers

  1. Click a cell on the pivot table
  2. Go to tab "Options" on the ribbon
  3. Click "Insert slicers" button
  4. Select Year and month
  5. Click OK
  6. Place slicers above the pivot table
  7. Click 2013 and 01 - January

Step 5 - Change pivot table field settings (cell width)

Change cell width

  1. Select all date columns
  2. Change cell width to 21

Autofit column widths on update

  1. Right click on a date
  2. Select PivotTable options...
  3. Go to tab Layout & Format
    pivot table calendar5
  4. Uncheck "Autofit column widths on update"
  5. Click OK

Auto resize columns as you type

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all [โ€ฆ]

Auto resize columns as you type

Step 6 - Use text in a pivot table

Pivot tables canยดt use text as values so you need to format values to show text. 1 = X and 0 = "" (nothing)

  1. Select values
    pivot table calendar6
  2. Right click on values
  3. Click on "Format Cells..."
  4. Select category Custom
    pivot table calendar7
  5. Type: [>=1]"X";[=0]"";
  6. Click OK

Read more here: Displaying Text Values in Pivot Tables without VBA

Step 7 - Change pivot table design

  1. Select a cell on the pivot table
  2. Go to tab "Design" on the ribbon
  3. Select a pivot table style

pivot table calendar8

Optional - Refresh pivot table automatically

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete [โ€ฆ]

How to create a dynamic pivot table and refresh automatically

Download excel file

Pivot table calendar.xlsx

Related articles