Excel Slicer Calendar
Here is a calendar which you can manage your daily agenda with ease. It is very easy to make and use this excel slicer calendar. Lets see how it is done.
Tools used for this calendar:
As you are likely to know by now, when there is slicer, there is also pivot table. Since slicers are pivot table filters, they can’t be used without them. So for this tutorial, we are going to make a pivot table that holds daily tasks. Then we are going to apply slicers for month and day.
We need a data table as source data for our pivot table. This data table need to have date, month, day, task name, etc.
For this example, I made a list with fix amount of tasks. My list has dates from 01/01/2013 to 31/12/2013, 10 of each date. So it is 3650 rows in total. One row for each task of each day. Like you can see from above picture, there is 10 rows with 01/01/2013 as date and with different task number from 1 to 10. Enter “.” (dot) to all cells for tasks (to avoid them showing blank).
When your list is ready, insert a pivot table with your list as source data. Drag Task Nr and Task to rows area. Now we need to mess with pivot table options a little bit. Here is what you need to do:
- Click on Task Nr inside the field list. Select “Field Settings” from menu. Arrange options as shown below:
- Right click on pivot table and open Pivot Table Options. Arrange options as shown below:
And from Display tab, uncheck Show expand/collapse buttons option.
Also From Layout & Format Tab, make sure to uncheck autofit columns width on update.
Now that we set our pivot table, insert some rows on left side and make space for slicers. Click on the pivot table and add 2 slicers. One for month and one for day. Arrange their formatting and placement as you like. Make some final touch-up if necessary and you have yourself an excel slicer calendar.
You may have difficulty for sorting slicer buttons with calendar order. You can check this post if so (after making custom list, select custom lists and sort a to z from slicer options).
Here is the example workbook for you to check it yourself.