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.

excel slicer calender

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.

9 Responses

  1. Brianna says:

    Can I have a template of this to download?

    • melih says:

      Hi,
      There is a direct link to the file named “example workbook” at the end of the post. You may download the file using that link.

      • Brianna says:

        Okay I found it. I have a screen shot regarding some basic questions.
        Just follow the link: http://i.imgur.com/XkTkzD8.png
        Here are my questions if the link doesn’t work.
        1. Under “tasks for current day” I’ll add one word into one of the rows below and it duplicates it into the other rows. How do I fix it so I can just add random appointment info into each individual row?
        2. Is there any way I can add more vertical rows to the “tasks for current day” table?

        • melih says:

          1. Since this is a pivot table, you cannot make changes directly on it. So you need to add tasks from “Tasks” sheet and than hit refreh to see them on your calendar.
          2. It is possibble to add more lines for tasks. That can be done by modifying “Tasks” sheet. But it is some heavy editting. You just need to add lines to “Tasks” sheet for everyday. Another modification of calendar interface may become necessary too (to fit new lines).
          I hope it is helpful. Best regards.

  2. Rasmus says:

    I have tried to get it to work, but for some reason the slicers wont work. They make no changes to the pivottable, but sometimes when you choose a different day, the month will change. The tasks are imported with Vlookup from a consolidated calender sheet and instead of a task nr. I have 11 customer names.

    I hope you can help me.

    • melih says:

      Hi,
      I just downloaded the file and added a task on 5th of august. When I refresh the pivot table and use slicers to check 5th of august on the calendar, I see my newly added task.
      For me, It works fine.
      Regards

  3. Tim says:

    How did you get the slicer arranged in a square like that? I’m using Excel 2010

  4. J says:

    Melih, thanks for the template looks great. Do you know if there is a way to add another feature to have the calendar populate for more that one person, so as a manager you could track what individuals on your team are doing on a daily basis? I tried adding another slicer and it does not come out in a user friendly format. Also tried adding a field to the pivot table – same result. Thanks so much for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.