How to Create a Calendar using Microsoft Excel Pivot Table
Microsoft Excel Table data placed correctly on Pivot Table Fields and with a combination of Date and Time Functions can be used to create a Calendar. Microsoft Excel Pivot Table is a power full data analysis tool that can be used to quickly summarize Microsoft Excel Data stored in rows and columns. Data Can also be stored in table formats.
One does not need advanced skills to create a Pivot Table (Though some familiarity of Microsoft Excel Functions is required
Before we create the calendar, your Pivot Table fields stored in columns must be formatted as a DATA TABLE
The Data Table will provide a one column for each PivotTable layout area (Rows, Columns, Values, Filters). To format your data as table, enter all the fields on the first row, select then press CTRL + T or go to the home tab then click “Format as Table”
The Pivot Table field above will be mapped as follows: The WeekNo will go to the Rows, The WeekDay to the Columns, The Day goes as Values (sum), while the Month will go in as Filters.
Follow the following steps:
Step 1: Open a blank Excel Workbook, then enter the Year start Date: for my case is 01/01/2019 – then go to the Fill -> Series, choose column to
populate column wise, then enter the step number as 1 and the stop value as the last date of your start date for this example we are using the start date and the end date as 12/31/2019
Hit the OK Button.
Step II: On the column B, we want to use the Function WEEKNUM enter and use the Column A (Date) to get the WeekNO on column B (Entered as =WEEKNUM([@Date])
Step III: In Column C, we find the WeekDay. Here we can use the function WEEKDAY but this will return the number 1 to 7 to represent the day of the week. Instead we use the function TEXT and use the Date format “ddd” to return the day short form. This is enter as =TEXT([@Date],”ddd”) on Column C to return “Mon”,”Tue”,”Wed” and so forth.
Step IV: Use the Function DAY on column D to return the Days of the Month as 1,2,3,4… to the last day of the date you select.
This is entered as =DAY([@Date])
Step V: In the last column, we would like to return the Month with the format DDD YYYY e.g. Jan 2019 or April 2019
Enter on Column E the formula =TEXT([@Date],”mmm yyyy”). The good thing with formatting your data as table, is that you won’t need to copy all the way down – table columns auto-populate on enter
Step VI – The Pivot Table:
- Select anywhere inside your data set, then Go to Insert on the Tabs then click Pivot Table.
- Click and drag to insert the WeekNo field into the Rows layout area, the WeekDay field ot the Column area, the Day field to the Values area and the Month field to the Filters area. Your Pivot Table should look like the image below:
The next steps are just formatting and creating filters for the month you need. First step is to remove filed headers by unchecking the Pivot Tables > Field Headers command.
If by default your Pivot Table has Grand Totals on the right as show in the image above, go to Design > Grand Totals and toggle the field off.
Finally, go to the Month filter, check “Select Multiple Items”, then select a single month like March 2019
Written by David K. Kandie (Twitter: @ExcelMaestro)
Want to Learn Excel? Join our monthly or Online Master Classes