Calendar Analytics for Excel 2010

Calendar Analytics allows us to connect with our Calendar in Outlook through Microsoft Exchange, and the result is that we get different interpretations and views of our meeting statistics, and appointments inside Microsoft Office Excel 2010.

What can we do with this free add-in?

We can automatically retrieve all the time that we have spent in our calendar.

We can view and analyze all the time of the meetings, either by days, hours, category and business priority..

We cam view information about past meetings and meetings which are programmed to be held in the future.

We can adjust and manipulate the views with the use of Slicers, DAX functions (Data Analysis Expressions) and Table References.

Only us and whoever else is sharing their calendar, has the access to analyze the calendar details.

Calendar Analytics is an executable file, where once we install it an Excel workbook is created on our desktop named Calendar Analytics.xlsx”. The file we can download at the following address:

We have to take in consideration that Calendar Analytics only works with the latest edition of Microsoft Excel 2010 and we have to have the add-in  Microsoft PowerPivot for Excel 2010 installed. To download PowerPivot for Excel 2010, we can visit the following address and depending on the version and language of Office we are using we download the appropriate.

Calendar Analytics takes advantage of Exchange Web Services (EWS-Exchange Web Services). Exchange Web Services works with Exchange Server, Exchange Online as well as hybrid environments.

Let us see how we can use Calendar Analytics:

We have to locate the Excel file on our desktop and open it. Once opend the Excel screen should look like the image below, and to be consisted of the same spreadsheets.


On the spreadsheet What or Who we press the Refresh Calendar button, and instantly the Calendars Analytics wizard appears named User’s Email. In the text box of the window, named Email Address we type in our own E-mail and we press the Next button.


At the next step of the wizard named Date Selector, we select the range of dates that we want to view in our spreadsheet. For example, at the Start Date I have adjusted at March 1st, 2011 and at the End Date I have adjusted at May 31st, 2011. Once we have adjusted the dates we press the Next button to move on to the next step.


On the next step of the wizard named Alias List, we can add a colleague (Alias), only if we have the rights to access his or hers calendar. As shown at the image below we only see my name written. If I wanted to add an Alias I would have pressed the Add button and then typed in the address, and if I wanted to remove someone that was included I would have selected the contact and pressed the Remove button. Once we have finished what we want to do, we then press the Next button to move to the other step of the wizard.


Straight away a Status window appears, which informs us that it is Connecting to the server and processing the data. At this point, we will have to wait for a while until the process is completed.


Once the process is completed the next Information window appears, which guides us for the next steps that need to be followed and which are the following:


1. We have to select the ribbon named PowerPivot, and from the area of the ribbon named Excel Data, we have to select the command Update All, as shown in the image below.


The PowerPivot window appears, where we are informed about the preparation of the PowerPivot window, where we have to wait also a little bit.


2. Once the procedure is completed we close the PowerPivot window and return to the workbook Calendar Analytics.xlsx. In this workbook we must select the Tab Data, and at the area of the ribbon named Connections we must select the command Refresh All from the drop down menu.


After a few moments all the data from our Outlook Calendar has been imported in the Calendar Analytics Workbook, as we can see at the next image where my name is written under the label Alias.

In the spreadsheet named What, we see the total amount of our meetings with all the statistics, we also see our Past and Future meetings which are visualized in charts and many more information.


The Charts and the Slicers in our spreadsheet are quite a few in the What spreadsheet. They are all about either the months, days, hours of our meetings.


In the spreadsheet Who, we have some statistical facts about our meetings, such as the Top Ten Attendees and the Top Ten Organizers. Selecting details on the Slicers on the right of the spreadsheets, automatically the charts are updated depending on what we have selected.


In the Details spreadsheet, all the details of our meetings appear, like Who is the Organizer, and also the Start and End Time of our meetings. Again the slicers appear to the right of the spreadsheet where we can select one to visualize our data differently.


In the Categories spreadsheet, we only see the list of names of our meeting categories of our Calendar.


About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on May 18, 2011, in Excel 2010 English, Microsoft Office 2010 English and tagged , , , , , , , . Bookmark the permalink. Comments Off on Calendar Analytics for Excel 2010.

Comments are closed.