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. You can press with left mouse button on this arrow with left mouse button to access more settings for a given field. They now have a black arrow pointing down next to the field name. The fields appear in the desired area when you release the left mouse button. Press with left mouse button on and drag "Value" to Values area.Press with left mouse button on and drag "Event" to Row Labels area.Left-press and hold on "Date" field, drag it down to Column Labels area.The PivotTable Field list appears to the right.Press with left mouse button on any cell in the Pivot Table.Press and hold on a specific field and then drag to the desired area. The Task Pane contains fields representing column header names in your Excel Table. Press with left mouse button on OK button.Select your Table and a where to put the Pivot Table.Press with left mouse button on "Pivot table" button located on the ribbon.Go to a new sheet, I named it "Calendar".The task pane appears automatically when you select any cell in the Pivot Table and disappears when you go outside the Pivot Table. 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. It allows you to quickly summarize and analyze data, it is incredibly fast and easy to work with. The Pivot Table will use this value to populate a slicer that will be located above the Pivot Table calendar.Ī Pivot Table is a feature in Excel that is perhaps the most powerful of all features but also least known. The first formula in cell B2 extracts the year from the corresponding date on the same row.
Customizable calendar events excel 2013 free download how to#
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. Drag down a few hundred rows depending on how many dates you want in your calendar.It is easy to add many dates to the calendar in no time, see steps below. The Calendar needs a record for each date or you won't see dates that have no events. Press with left mouse button on OK to apply settings and create an Excel Table.Press with left mouse button on check box "My table has headers".Press with left mouse button on "Table" button and a dialog box appears.Type header names shown in the picture below.Create a new worksheet, I named my worksheet "Data".The steps below describe how to set up the data for the calendar. We will link the Pivot Table data source to the Excel Table in a later step. You will often add data to the calendar so the Excel Table will be a huge time saver. You need to adjust regular cell references when data is added or deleted, using Excel Tables make this problem go away. They are called "structured references" and don't change when data is added or deleted. The reason I am using an Excel Table in this example is that they are easy to reference. The following step describes how to convert the calendar data to an Excel Table. I saved my calendar data on another worksheet named "Data". The events and the dates are located on another worksheet in an Excel Table. There are no VBA macros or UDFs in this workbook, it is all powered by the Pivot Table and a few formulas. Above the calendar are two slicers, they allow you to select what year and month to show.Ĭalendar Events are displayed vertically to the left of the dates, duplicate events are merged into one distinct event. The image above shows the calendar with dates horizontally and month. The calendar is created as a Pivot Table which makes it lightning-fast and easy to navigate. This article demonstrates how to build a calendar in Excel. Author: Oscar Cronquist Article last updated on April 15, 2020