Pivot Table calendar

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

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. Press with left mouse button on "Table" button and a dialog box appears.
      
   4. Press with left mouse button on check box "My table has headers".
   5. Press with left mouse button on OK to apply settings and create an Excel Table.

Populate Date column

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. Press 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

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

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. 1. Go to a new sheet, I named it "Calendar".
   2. Go to tab "Insert" on the ribbon.
   3. Press with left mouse button on "Pivot table" button located on the ribbon.
      
   4. Select your Table and a where to put the Pivot Table.
   5. Press with left mouse button on OK button.

Configure Pivot Table settings

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

1. 1. Press with left mouse button on any cell in the Pivot Table.
   2. The PivotTable Field list appears to the right.
   3. Left-press 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. Press with left mouse button on and drag "Event" to Row Labels area.
   6. Press with left mouse button on and drag "Value" to Values area.

Change Value field setting

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 press with left mouse button on this arrow with left mouse button to access more settings for a given field. A context menu or pop-up menu appears, press with left mouse button on a menu item to open a dialog box or perform an action.

1. 1. Press with mouse on the black arrow next to Value in Values area, see image below.
      
   2. Press with mouse on "Value field settings...".
   3. Go to "Summarize value field by" tab.
   4. Select Sum, see image below.
      
   5. Press with left mouse button on OK button.

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

Change cell formatting

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

Create slicers

Slicers let you control what the Pivot Table will show, press with left mouse button on 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. 1. Press with left mouse button on 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. Press with left mouse button on "Insert slicers" button and a dialog box appears.
   4. Select Year and month.
   5. Press with left mouse button on OK button.
   6. Move slicers above the pivot table.
   7. Press with left mouse button on 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. 1. Select all date columns in the Pivot Table.
   2. Press with left mouse button on 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. 1. Press with right mouse button on on a date in the Pivot Table.
   2. Select "PivotTable options..."
   3. Go to tab Layout & Format
      
   4. Uncheck "Autofit column widths on update".
   5. Press with left mouse button on OK button-

Recommended articles

Auto resize columns as you type
What's on this page Auto resize columns as you type - VBA Press with left mouse button on a cell […]

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. 1. Select all cell values, see image below.
      
   2. Press with right mouse button on on selected values.
   3. Press with mouse on "Format Cells..." to open the "Format Cells" dialog box.
   4. Select category Custom, see image below.
      
   5. Type: [>=1]"X";[=0]"";
   6. Press with left mouse button on OK.

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

Change Pivot Table design

1. 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. Press with right mouse button on on any cell in the Pivot Table to open a context menu.

Press with mouse 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.

Recommended articles

How to create a dynamic pivot table and refresh automatically
Table of Contents How to create a dynamic pivot table and refresh automatically Auto refresh a pivot table 1. How […]

Animated image

Get the Excel file

Pivot-table-calendar.xlsx

Recommended articles

• Create a PivotTable to analyze worksheet data
• How to Create a Pivot Table in Excel - Contextures
• Overview of Excel tables

Calendar category

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Monthly calendar template
Table of Contents Monthly calendar template Monthly calendar template 2 Calendar - monthly view - Excel 365 Calendar - monthly […]

Plot date ranges in a calendar
Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

Pivot table category

How to create a dynamic pivot table and refresh automatically
Table of Contents How to create a dynamic pivot table and refresh automatically Auto refresh a pivot table 1. How […]

How to use 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.

Excel categories

Comments (5)