Can we make a calendar using Pivot Tables?!?
Of course we can. Today let us learn a simple technique to create calendar style reports using Pivot tables.
Thanks to Rob for inspiration
Before making any progress, let me thank Rob from PowerPivotPro for the inspiration. Recently he wrote an article explaining how to use PowerPivot & DAX formulas to create calendar charts in Excel. I applied similar technique to Pivot tables.
Demo of Pivot Calendar
See a quick demo of pivot calendar chart before learning how to do this.
Creating a Pivot Table Calendar
Step 1: Set up an entire year of dates in a list
Lets assume, we want to make the calendar for year 2012. So write that in a cell (G3). Now, in a range of 366 cells, generate all the dates for the year (2012) using simple formulas.
- First date will be =DATE(G3,1,1)
- Next 365 dates will be previous date + 1
Step 2: Calculate Day, Month, Year and Weekday
Using DAY(), MONTH(), YEAR(), WEEKDAY() calculate the day, month, year and weekday for each of the 366 days.
Step 3: Determine the week number in a month
Now comes the tricky part. We need to find out which row each date should be displayed. First take a look at this illustration.
The logic for calculating row numbers is very simple:
- First day of a month is always in row number 1.
- If a day is not Sunday, we just use previous row number
- On Sundays, we just increment the previous row number and use it.
All of this can be expressed in a simple IF formula =IF(D7=D6,IF(F7=1,G6+1,G6),1)
- D7 contains this month, D6 is previous day’s month
- F7 contains weekday, will be 1 for Sunday and 7 for Saturday
- G6 contains previous row number (weeknum)
Step 4: Dealing with Leap years
So far we are good, except for a minor glitch. Certain years have 366 days (for example 2012) while others dont. That means, depending the year, we need to either use 365 rows or 366 rows of our data while generating the pivot report. To do this, we create a named range tblDates that refers to below formula:
=IF(Calcs!$D$3,Calcs!$B$5:$G$371,Calcs!$B$5:$G$370)
Note: D3 is TRUE when an year is leap year.
Step 5: Create pivot table that shows calendar
Now, we need to create a pivot table from the range tblDates.
Set up your pivot table like this:
Step 6: Add a slicer
To enable users to select a particular month interactively, just add a slicer on months. For this,
- Select any cell in the pivot table and go to Options Ribbon > Insert Slicer
- Select Month as field to insert a slicer.
- Adjust slicer properties to show items in 6 columns (Slicer Options Ribbon > Columns)
- Done!
At this point, you can interactively select a month & see the corresponding calendar.
Related: More examples on Slicers
Further Enhancements
Now that the basic Pivot Calendar is ready, try these ideas:
- Use a spin button / slider control to interactively adjust the year. Remember, when you do this, you need to refresh the pivot table in background using a simple macro.
- Adjust week start to Monday: Likewise, you can modify your formulas to adjust weekstart to Monday or any other day you fancy.
Using Pivot Calendar as a Chart
Of course, having a mere pivot calendar is not much fun. But you can apply this idea to create a calendar chart. See this:
Calendar Chart Demo:
How to create this Calendar chart?
To keep things simple, lets understand how to create this chart with just one metric – Employee productivity.
- Once the pivot calendar is ready, we add extra rows between each line in the calendar.
- Now, lets say, we have our employee productivity details listed by date in a table.
- Then, using lookup formulas, we fetch productivity for each day in the cell below.
- Once all the values are fetched, just select all these cells and add conditional formatting > color scale to them.
- Format the color scale settings so that you get desired colors.
- And you are done!
More on Conditional Formatting
Video Explaining Pivot Calendar & Chart
Like this concept? Watch below video to understand how the whole thing is made.
[watch this video on our youtube channel]
Download Pivot Calendar Template
Click here to download pivot calendar & calendar chart templates. Play with them. Plug your own values and see what happens.
PS: You need Excel 2010 to view this file. Please enable macros to get full effect.
Do you like Pivot Calendar Idea?
I am very excited to try this out in a client project sometime soon. I think a set up like this can be used when analyzing monthly data like employee attendance, vacations, productivity, shipments, meeting schedules, project milestones etc. Since such data is represented in calendar format in real life, your audience would find calendar metaphor easy to understand. That said, any data like KPI trends, sales, visits, calls etc. should always be represented as a line /bar charts rather than calendar charts.This way, we can spot trends quickly and understand data better.
What about you? Do you like this idea? Are you planning to use a pivot calendar / calendar chart sometime in future? Please share your thoughts using comments.
Calendars & Similar ideas:
Please go thru below links to learn more about calendars & visualizing data:
26 Responses to “FIFA Worldcup Excel Spreadsheets [Roundup]”
Nice roundup! Do you know of any one-page spreadsheets which will be updated by an administrator after each game? Would be nice to be able to print out the latest results whenever I feel like checking them as I probably won't be following closely every day.
I actually haven't tried any of the above ones yet, but I thought I'd mention this one that I found which makes a nice one-page form you can fill in dynamically. http://exceltemplate.net/sports/world-cup-2010-schedule-and-scoresheet/
I would like to recommend you these one: http://www.anotagol.com/
You can choose your interface language (english, spanish, italian, portuguese, german or french) and your country for the timezone of match. I like it very much.
An awesome online world cup calendar in flash.
http://www.marca.com/deporte/futbol/mundial/sudafrica-2010/calendario-english.html
Got one more tracker in excel (one page)
http://cid-b09e57e6e960505c.office.live.com/browse.aspx/.Public
[...] Passend zu gerade laufenden Fußball-WM gibt es auf Chandoo.org alles wissenswerte über Excel-Anwendungen für den Fußball-Fan. [...]
Great!!!
I strongly recommend this :
http://www.en.excel-soccer-2010.de/downloads
Chandoo how you found this ...
@Rohit.. really beautiful file. I missed it during my research. Now, I recommend it. 🙂
Hi Chandoo - thanks for the recommandation 🙂 - Regards
[...] Excel, then print it on the other side of your Match Schedule from step 2 above. There are several other Excel spreadsheet templates you can download, but this is probably the only one-page version you can find; plus, it [...]
Does anybody know how to re-create this(?): http://www.marca.com/deporte/futbol/mundial/sudafrica-2010/calendario-english.html
...or do you know where a template can be found? I am DYING to have something like this on my site. When I found it, I had been looking for the longest time for a circular calendar. I found a couple that weren't adequate. Then I stumbled upon this one and my eyes nearly popped out of my head. If anyone can lead me in the right direction, I would be eternally grateful!
Thanks in advance!
Robert
@Robert...
Doing something like that is a lot of work. You can probably get it done with some hired help from a flash developer.
@Robert, the World Cup flash in the Spanish Marca newspaper is impresive, but not much as my own animated spreadsheet with the Goals of 2010 World Cup South Africa in Excel that I just published into my blog:
http://pedrowave.blogspot.com/2010/06/goals-of-2010-world-cup-south-africa-in.html
Download from here:
http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/Goals%20South%20Africa%20Animated.xlsx
And start to enter the goals of the rest of matches.
Has anyone seen, or made, a Spreadsheet where you can record the scorers and see a 'top scorers' chart. Would be a nice enhancement
@Neil... checkout this one http://www.inflexionary.com/sports/world-cup-2010-excel
it uses macros to fetch scores from web (and provides very comprehensive analysis too)
@All.. Thanks for the comments. I have updated the post with few more links now.
Hi,
Check this dashboards too:
http://dashboards.org/world-cup-dashboards-and-visualizations/
😉
[...] Here is a collection of FIFA World Cup Spreadsheets if you are more in to that sort of thing. | [...]
[...] Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]
[...] World Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]
Hey, you missed ours! It has everything you need and more, but not a whole pile of silly extras (National Anthems, etc). I'll be making another one for the 2014 world cup. We had over 4000 hits on it!
@Michael Harwood.
Where is it then? You should have posted a link
Sie sollten an einem Wettbewerb teil zu nehmen für einen der besten Blogs im Web. Ich werde empfehlen Sie diese Seite!
Google translation: You should take part in a contest for one of the best blogs on the web. I will recommend this site!
[...] and welcome to the forum, Maybe these similar spreadsheets might give you a few initial ideas: FIFA Worldcup Excel Spreadsheets [Roundup] | Chandoo.org - Learn Microsoft Excel Online If you have specific areas / formulae / layout choices for parts of your spreadsheet that you are [...]
Calling all football fans around the globe! The biggest football festival will kick off on the 12th June 2014 and everyone is placing their bets of who will have the honour of lifting the golden trophy.
Use our free interactive Excel templatel to predict the World cup finalists ! No macros !
http://www.spreadsheet1.com/world-cup-2014-free-excel-prediction-template.html
I also made a Worldcup-tracker, with MS Access, which can also generate reports in Excel
e.g. a match-schedule with locations on y-axis and dates on x-axis, see:
http://worktimesheet2014.blogspot.com.es/2014/05/excel-with-match-schedule-for-2014-fifa.html
and:
http://worktimesheet2014.blogspot.com.es/2014/05/match-access-app-to-track-world-cup.html
where can i find raw data in excel file format of fifa world cups (1930-2014)
@Vivek
Have a read of: http://chandoo.org/forum/threads/goal-of-world-cup.17637/
The location is mentioned in Somendra's comments
Free XLSX Prediction Spreadsheet for World Cup 2018 Russia!
https://www.spreadsheet1.com/fifa-world-cup-2018-russia-free-prediction-templates-for-excel.html