Pivot table calendar
Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table and two slicers.
Instructions
I am going to store my calendar data on another sheet named data.
Step 1 - Create an excel defined table
- Go to sheet data
- Type header names shown in the picture below.
- Create an excel defined table
- Select headers
- Go to tab "Insert"
- Click "Table" button
- Click box "My table has headers"
- 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.
Add dates
- Select cell A2
- Type the first date 2013-01-01 or 1/1/2013
- Click and hold on the black dot
- Drag down a few hundred rows depending on how many dates you want in your calendar
Recommended article
Create a drop down calendar in excel
This drop down calendar uses a "calculation" sheet and a named range. You can copy drop down lists and paste […]
Add formulas
- Select cell B2
- Type:
=YEAR([@Date]) - Select cell C2
- 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]))
- Select cell D2
- Type:
=INDEX({"Mo";"Tu";"We";"Th";"Fr";"Sa";"Su"},WEEKDAY([@Date],2))
- Select cell E2
- Type:
=IF([@Date]<>""," ","")
- Select cell F2
- 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.
How to automatically fill all blanks with missing data or formula
Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]
Step 2 - Insert the pivot table
- Go to a new sheet, I named it Calendar.
- Go to tab "Insert"
- Click "Pivot table" button
- Select your table and a location
- 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.
Step 3 - Pivot table settings
- Click a cell in the pivot table
- You can now see the PivotTable Field list to the right.
- Click and hold on date field, drag it down to Column Labels.
- Repeat with month field.
- Click and drag Event to Row Labels
- Click and drag Value to Values
Change Value field setting
- Click on black arrow near the Value in Values.
- Select value field settings...
- Go to "Summarize value field by" tab.
- Select Sum
- Click OK
Change cell formatting
- Select all dates on the pivot table
- Right click on selected cells
- Click "Format cells..."
- Go to tab number
- Select category "Custom"
- Type D
- Click OK
Step 4 - Create slicers
- Click a cell on the pivot table
- Go to tab "Options" on the ribbon
- Click "Insert slicers" button
- Select Year and month
- Click OK
- Place slicers above the pivot table
- Click 2013 and 01 - January
Step 5 - Change pivot table field settings (cell width)
Change cell width
- Select all date columns
- Change cell width to 21
Autofit column widths on update
- Right click on a date
- Select PivotTable options...
- Go to tab Layout & Format
- Uncheck "Autofit column widths on update"
- Click OK
Auto resize columns as you type
Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, […]
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)
- Select values
- Right click on values
- Click on "Format Cells..."
- Select category Custom
- Type: [>=1]"X";[=0]"";
- Click OK
Read more here: Displaying Text Values in Pivot Tables without VBA
Step 7 - Change pivot table design
- Select a cell on the pivot table
- Go to tab "Design" on the ribbon
- Select a pivot table style
Optional - Refresh pivot table automatically
How to create a dynamic pivot table and refresh automatically
David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]
Download excel file
How to create a dynamic pivot table and refresh automatically
David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]3 Responses to “Pivot table calendar”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Hi,
thank you for this awesome guide.
Is it possible to make this work if more than one event takes place per date? For instance a shipping and a manufacturing meeting on the same date? How would one go about doing that?
Thanks!
Oscar, thank you for this tutorial. I successfully created a pivot table calendar. However, mine does not show the individual days of each month, as does yours in Step 6 (showing 31 days in January). How do I create that view?
I too would love to know how to have 2 events on the same day other than that its a great table