Author: Oscar Cronquist Article last updated on April 15, 2020

pivot table calendar1 1

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes it lightning-fast and easy to navigate.

The image above shows the calendar with dates horizontally and month. Above the calendar are two slicers, they allow you to select what year and month to show.

Calendar Events are displayed vertically to the left of the dates, duplicate events are merged into one distinct event. An X shows the date and the given event.

There are no VBA macros or UDFs in this workbook, it is all powered by the Pivot Table and a few formulas. The events and the dates are located on another worksheet in an Excel Table.

Instructions

I saved my calendar data on another worksheet named "Data". The following step describes how to convert the calendar data to an Excel Table.

Create an Excel Table

pivot table calendar data

The reason I am using an Excel Table in this example is that they are easy to reference. They are called "structured references" and don't change when data is added or deleted. You need to adjust regular cell references when data is added or deleted, using Excel Tables make this problem go away.

You will often add data to the calendar so the Excel Table will be a huge time saver. We will link the Pivot Table data source to the Excel Table in a later step. The steps below describe how to set up the data for the calendar.

  1. Create a new worksheet, I named my worksheet "Data".
  2. Type header names shown in the picture below.
  3. Create an Excel Table.
    1. Select headers.
    2. Go to tab "Insert" on the ribbon.
    3. Click "Table" button and a dialog box appears.
      Excel Table dialog box
    4. Click check box "My table has headers".
    5. Click OK to apply settings and create an Excel Table.

Populate Date column

pivot table calendar

The Calendar needs a record for each date or you won't see dates that have no events. It is easy to add many dates to the calendar in no time, see steps below.

  1. Select cell A2, see image above.
  2. Type the first date 1/1/2013.
  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.

Add formulas to Excel Table

pivot table calendar add formulas to data sheet

The next steps demonstrate how to add formulas to the Excel Table, they extract the year, month and weekday from the date in column A.

The first formula in cell B2 extracts the year from the corresponding date on the same row. The Pivot Table will use this value to populate a slicer that will be located above the Pivot Table calendar.

  1. Select cell B2.
  2. Type:
    =YEAR([@Date])
  3. Press Enter.

[@Date] is a structured reference pointing to a value in column A on the same row as the formula.

When you press Enter after typing the formula in cell B2 the Excel Table will automatically copy the formula in cell B2 to cells below in the Excel Table. This is another great feature that saves you time.

  1. Select cell C2
  2. 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]))

This formula will add a number before the month name, this to make sure that the months are in the correct order when populated in the slicer.

  1. Select cell D2
  2. Type:
    =INDEX({"Mo";"Tu";"We";"Th";"Fr";"Sa";"Su"},WEEKDAY([@Date],2))

The following formula creates a blank in column E if the date is missing in column A.

  1. Select cell E2
  2. Type:
    =IF([@Date]<>""," ","")

This formula returns 1 in column F if the event is equal to a blank (space character).

  1. Select cell F2
  2. Type:
    =IF([@Event]<>" ",1,"")

Insert Pivot Table

pivot table calendar insert pivot table

A Pivot Table is a feature in Excel that is perhaps the most powerful of all features but also least known. It allows you to quickly summarize and analyze data, it is incredibly fast and easy to work with.

The image above shows an empty Pivot Table placed on a worksheet, the task pane to the right allows you to quickly configure the Pivot Table. The task pane appears automatically when you select any cell in the Pivot Table and disappears when you go outside the Pivot Table.

    1. Go to a new sheet, I named it "Calendar".
    2. Go to tab "Insert" on the ribbon.
    3. Click "Pivot table" button located on the ribbon.
      pivot table calendar1
    4. Select your Table and a where to put the Pivot Table.
    5. Click OK button.

Configure Pivot Table settings

pivot table calendar2

The Task Pane contains fields representing column header names in your Excel Table. Click and hold on a specific field and then drag to the desired area. Detailed instructions below.

    1. Click any cell in the Pivot Table.
    2. The PivotTable Field list appears to the right.
    3. Left-click and hold on "Date" field, drag it down to Column Labels area. Release left mouse button. See image above.
    4. Repeat with "Month" field.
    5. Click and drag "Event" to Row Labels area.
    6. Click and drag "Value" to Values area.

Change Value field setting

pivot table calendar configure pivot table

The fields appear in the desired area when you release the left mouse button. They now have a black arrow pointing down next to the field name.

You can click this arrow with left mouse button to access more settings for a given field. A context menu or pop-up menu appears, click a menu item to open a dialog box or perform an action.

    1. Click on the black arrow next to Value in Values area, see image below.
      pivot table calendar4
    2. Click on "Value field settings...".
    3. Go to "Summarize value field by" tab.
    4. Select Sum, see image below.
      pivot table calendar3
    5. Click OK button.

Field "Count of Value" changes to "Sum of Value" in the Values area.

Change cell formatting

pivot table calendar format date cells

    1. Select all dates displayed on the pivot table.
    2. Right-click on selected cells and a pop-up menu appears.
    3. Click "Format cells..." and a dialog box shows up.
    4. Go to tab Number.
    5. Select category "Custom".
    6. Type D.
    7. Click OK button to apply changes.

Create slicers

pivot table calendar slicers

Slicers let you control what the Pivot Table will show, click an item in the slicer to select it and the Pivot Table changes accordingly.

Thee are two buttons next to the slicer name, the first one lets you select multiple items in the slicer. The second button clears the selection.

    1. Click any cell on the pivot table to show tab "Pivot Table Analyze" on the ribbon..
    2. Go to tab "Pivot Table Analyze" on the ribbon.
    3. Click "Insert slicers" button and a dialog box appears.
    4. Select Year and month.
    5. Click OK button.
    6. Move slicers above the pivot table.
    7. Click 2013 and 01 - January and the Pivot Table changes showing only events for January 2013.

Change pivot table field settings (cell width)

Change cell width to 21

    1. Select all date columns in the Pivot Table.
    2. Click with left mouse button and hold on a line separating the column letters.
    3. Drag with mouse until width is 21.

Autofit column widths on update

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

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

Use text in a Pivot Table

A Pivot Table is designed to work with numbers, however, there is a workaround that allows you to display text.

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

    1. Select all cell values, see image below.
      pivot table calendar6
    2. Right click on selected values.
    3. Click on "Format Cells..." to open the "Format Cells" dialog box.
    4. Select category Custom, see image below.
      pivot table calendar7
    5. Type: [>=1]"X";[=0]"";
    6. Click OK.

 

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

Change Pivot Table design

pivot table calendar8

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

Optional - Refresh pivot table automatically

You need to refresh the Pivot Table each time you edit or add/delete values in the Excel Table. Right-click on any cell in the Pivot Table to open a context menu.

Click on "Refresh" and the Pivot Table recalculates using the new values in the Excel Table. There is a workaround that lets you skip this, however, it requires a small macro. See link below.

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

Animated image

pivot table calendar3

Download Excel file


Pivot-table-calendar.xlsx

Recommended articles