2013 Calendar – Excel Template [Downloads]

2013 Calendar – Excel Template [Downloads]:
Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

See more snapshots here: 2013 Calendar template snapshot 1, snapshot 2

Download 2013 Calender – Excel File

Click below links to download the calendar you want:

• 2013 Calendar & Daily planner [Works in Excel 2007, 2010 & 2013]
• 2013 Calendar Template [Excel 2007, 2010 & 2013]
• 2013 Calendar Template [Excel 2003]

How does this Calendar work?

This is the same file as 2012 calendar with the year changed. The new feature is event planner. So I will explain that alone.

• First take a look at either 2011 calendar or 2012 calendar pages to see how the basic calendar is constructed.
• In the daily events tab, we have full calendar for 2013 (or any other specified year) listed one day per row (so maximum rows are 366).
• Each day can be marked as one of the 4 types of events – Important , Anniversary, Holiday or Vacation.
• When you mark a day with either of these 4 events, in an extra column, a formula determines which event it is and places a number (between 1 to 5 – 1 for Important, 2 for Anniversary … 5 for no event)
• Back in the full calendar, we use conditional formatting to color a day based on what number is associated with it in daily planner.
  
  
• COUNTIF formulas are used to count how many days are marked as Important, Anniversary or Holiday and summarized in the full calendar view.

Go ahead and enjoy the download. The file is unlocked. So poke around the formulas and named ranges. Learn some Excel.

More Calendar Downloads:

Download these additional calendar templates and start your new year in awesome fashion!
2012 Calendar, 2011 Calendar, New Year Resolution Tracker, Picture Calendar Template
Techniques used: INDEX | OFFSET| INDIRECT | Array Formulas |  Using Date & Time in Excel | Conditional Formatting

People & Websites that Helped me in 2012 [Thank you message]

People & Websites that Helped me in 2012 [Thank you message]:
2012 has been the most awesome year since we started Chandoo.org.
The credit for this goes to our community of Excel users – that is you.
For practically every day of 2012, you have inspired me (and all of us at Chandoo.org) to learn something new, share and make you awesome. I know I say this many times, but I can never feel enough – Thank you for your support to Chandoo.org.
Apart from you, there are many other amazing people & companies that played a role in our success. In this post, let me highlight them & express my sincere gratitude.

People who helped me in 2012:

Teachers & Gurus:

Running a business, website & family requires a lot of motivation, fresh ideas & learning. Thanks to several wonderful teachers & inspiring individuals who gave me the strength I need this year.

Excel & Visualization Teachers:

Jon Peltier, Dick Kusleika, Debra Dalgleish, Mike Alexandar, Daniel Ferry, Robert Mundigl, Hui, Francis, Rob Collins, Bill Jelen and many more.

Chandoo.org Forum Members:

Hui, Sajan, NarayanK, BobHC, Faseeh, SirJB, Luke and all other regulars & Ninjas who contribute to my Excel knowledge everyday.

Business & Motivation

Andy Sernovitz, Darren Rowse, Yaro Starak, Avinash, Brandon Pearce, Alok, Pat, Chris, MrMoneyMustache, Jacob, Patrick and many more.

Technology

Amit Agarwal, John Gruber and many more sourced thru FlipBoard, Twitter & Pulse

Authors

As I was traveling quite a bit (1.5 months each in Thailand & Australia and few weeks with in India) I could not read as much as I wanted to.  But I did manage to read & learn from some amazing authors, books. Special thanks to these wonderful authors.

• Chris Guillebeau $100 Startup
• Sam Walton Made in America
• Daniel Kahneman Thinking Fast & Slow
• Benjamin Graham Intelligent Investor
• Bill Bryson Down Under
• John F. Love McDonalds – Behind the arches
• Danielle S. Fairhurst Business Analysis using Excel
• Rob Collins DAX Formulas for PowerPivot
• Bill Jelen PowerPivot in Excel

And many other…
Note: All the book links to Amazon are affiliate links. That means, if you purchase something after clicking on them, I get a few cents

Partners, Affiliates & Supporters

To make Chandoo.org successful, I collaborate with many great minds in this industry. Some of these remarkable people are,

Chandoo.org Partners

• Danielle at Plum Solutions (and her assistant Susan) helped me arrange first ever Excel & Dashboard classes in Australia.
• Paramdeep at Pristine Education (and their staff) helped us offer various financial modeling training programs thru Chandoo.org.
• Daniel Ferry at ExcelHero.com helped me offer Excel School training along with his academy so that many of you could become awesome in Excel.
• Ankush at ActiKnow Consulting for helping many of our clients with custom Excel solutions.

Our Affiliates:

This year was great for many of our affiliates too. Thanks to their support, we had more customers and they had more revenues. Some of our most prominent affiliates are,
Dashboard Spy, Francis, Daniel Ferry, Debra Dalgleish, Philip, Ken Puls, Oscar, Jimmy Pena, Victor Chan, Alan Murray and many more.

Our Supporters:

Many people selflessly spend their time & energy promoting our cause – to make you awesome. My sincere thanks and love goes to,
Hui, SirJB, BobHC, Luke, Faseeh, Sajan, Narayank and all our Forum regular members & Ninjas.
Special thanks to Fabrice, Robert, Oscar & others who regularly to link to us and spread the good word.

Press:

Special thanks to Kumara Swamy from Telegraph India for featuring me in an article this year.

Customers & Readers

This year, close to 5,000 of you blessed me with your product purchases from us. More than 55,000 of you are now part of our RSS / Newsletter community. Many more continue to join us each day. Thank you so much for inviting me in to your life & taking time to learn from us.
Many thanks to KPMG, Renault Nissan, Ecobank, SEEK, and many other corporate clients for supporting us this year.
I am also thankful to our Excel forum members.

Special thanks to

• Attendees of various live classes conducted by me this year in Perth, Sydney, Melbourne, Brisbane & Chennai.
• People in Sydney, Melbourne, Brisbane & Perth who met me when I was in Australia.
• People in Chennai who met me for coffee.
• Hui & Family, Danielle & Family for sharing some of my evenings very nice and memorable.

Our Staff

Most of what we did at Chandoo.org not even remotely possible without of staff. I am amazed at their level of commitment and support to our mission to make you awesome. My heartfelt thanks to,

• Ravindra: for helping with various training enrollments, emails & customer service
• Vijay: for teaching VBA
• Sameer: for answering student doubts in training programs
• Sujatha: for providing customer care & email support
• Pothi: for taking care of our server & site infrastructure
  
  
• Chittibadrayya: for taking care of all the book-keeping & accounting aspect of our business

Special thanks to Neel (our iPhone developer) too.

Websites & Companies that helped me in 2012

I am thankful to Microsoft for making Excel so awesome.
I am also thankful to,
Email & Productivity: Google, iPhone
Website, Hosting & E-commerce: WordPress, GoDaddy, Wishlist Member, KnownHost, Amazon, PayPal, E-Junkie, 2Checkout, EBS
Community & Connection: Twitter, Facebook, Youtube, Skydrive
Software: Paint.NET, Mozy, Notepad ++, Camtasia & Snagit, Skype
There are many other software, companies and websites that help me every day. I am really thankful to each and every one of these. Detailed listing here.

Last but not least…

There is someone else that deserve utmost thanks for everything I do at Chandoo.org.

• My family: Jo & kids support me and Chandoo.org in numerous ways. They shower me with love, humor and support everyday so that I can be awesome at what I do.
• All my close friends & relatives: for supporting me & encouraging me to do better.

PS… something for you:

Here is a nice little surprise for you. Go ahead and download it. Unlock the secret message.
PS: Incase you have difficulty downloading the file, see it in action here.

Merry Christmas & Happy New Year 2013

Merry Christmas & Happy New Year 2013:
Hello awesome readers of Chandoo.org,
We wish you a merry Christmas & very happy New Year 2013. May your holidays be filled with joy and warmth. And your new year with lots of hope, fun, strength and awesomeness.
I also want to tell you how thankful & fortunate I feel to have your support in this year. Your desire to learn Excel & become awesome at your work motivates me everyday to learn & share. 2012 is a memorable year because of you. Thank you.

About this year’s holiday card
Now that our kids are 3 years old, we have dozens of good family photos to choose for the holiday card. I picked this photo from Diwali this year (November 14). Of course Nishanth would not let go of that toy gun …

Holiday Schedule of Chandoo.org

As many of you are going to be away for holidays or annual vacation, we will be having very little activity on Chandoo.org. Schedule for next 2 weeks is,

• 22, 23, 24 & 25 December: No posts
• 26 December: Free Excel calendar template for year 2013
• 27 December: No post
• 28 December: People and websites that helped Chandoo.org in 2012 [ thank you message ]
• 31 December: Best of Chandoo.org, 2012
• 1 January: No post
• 2 January: How can we make you awesome in 2013 – Survey
• 3 January: No post
• 4 January: Chandoo.org plans for 2013

Our Forum:
Chandoo.org Forums will remain online and probably active during this holiday season. Although most regulars & ninjas would be away, you can post your Excel questions & hopefully someone will help you out.
Our Online Store:
Our online store will remain available and you can continue to purchase your favorite Excel training programs or templates. Please note that there will be 1 day delay in getting your login details for purchases made on Christmas day or New Year Day.

• Get Excel School, VBA Classes or PM Templates

Online Classes, Student Doubts & Questions:
All our online classrooms will be open in this holiday season. You can enjoy the video lessons & ask questions. Please expect 2 day delay for questions posted between 25 December to 1 January.
Emails:
If you send me an email in next 2 weeks, please expect some delay. We are expecting quite a few relatives & guests during this holidays and I am not hoping to spend too much time online. If you do not hear from me, send a reminder after 7th of Jan.

Once again…

On behalf of Chandoo.org family, staff & volunteers, I wish you a merry Christmas & happy New Year 2013. I hope & pray that your life is filled with happiness, wealth & health in this holidays & going forward.
Thank you.

Monitoring Monthly Service Levels using Excel Charts [Example]

Monitoring Monthly Service Levels using Excel Charts [Example]:
Recently, I wrote a tutorial on tax burden in USA chart.
Jared, One of our readers liked this chart very much. Jared works as a workforce scheduler and has data similar to our chart. So he applied the same technique to analyze monthly service levels for last 7 years & sent me the file so that I can share it with all of you.

Monthly service levels in last 7 years – Demo:

First take a look at the demo of Jared’s chart.

Recipe of this chart

This chart construction is similar to our Tax burden chart. Only addition is the cool scroll bar at bottom to see any month’s service level across years.

How does the scroll bar work?

1. If you have never used scroll bar or any other form controls, read our introduction to form controls page.
2. The chart has one extra series that shows selected month’s value and a bunch of #N/As.
3. Scroll bar is setup to have minimum 1, maximum 12 and is linked to a cell.
4. Based on scroll-bar selection, we turn on one of the months and make the rest of values NA()
   
   1. Using a simple IF formula
5. For this extra series, Jared added 100% negative error bar so that a nice drop line is shown when you select a month.

That is all.

Download Jared’s Example and get inspired

Click here to download this workbook. Play with it to learn more. Use this idea in your work and impress someone. Become awesome.

Do you like this example? Say thanks to Jared…

I really loved Jared’s creativity and simple solution. Not to mention his kindness to share this with me and all of you. This shows that by using easy features like scroll bars, slicers, regular charts we can create something that is stunning, meaningful and powerful – right inside Excel.
What about you? Do you like this example? If you learned something new, say thanks to Jared for sharing this with us.
PS: If you want to share your story of how you use Excel to do something awesome, please email me. I am eager to learn from your examples and share your stories on Chandoo.org.

Show hide list boxes using VBA

Show hide list boxes using VBA:
Rama, one of our readers emailed this:

Hello Chandoo I am very new to vba. Help me with this
Q) I Have Many List boxes In That I need to Hide Few Of them Using Check box
Example:If I have List boxes Like A,A1,B,B1
If I Check On Check box A(Captioned As A) It Should show A,A1 List boxes. If I Unchecked it Should Hide A,A1 List boxes
In a similar manner if i checked Check box B .It Should show B,B1 List boxes. If I Unchecked it Should Hide B,B1 List boxes

Show Hide list boxes by using a check box

We can use check box and a bit of VBA to do this easily. First see this demo:

How to show or hide list boxes – Video

Although the concept behind this is very simple, explaining it in a post will make it very long. So I made a 10 minute video. Please watch it below:
[Watch this on our youtube page]
For more on this technique – see Customer Service Dashboard article.
To insert check boxes & list boxes see this tutorial.

Download example workbook

Click here to download the example workbook to understand this technique better. Examine the code in module 1 & 2 to know more.

How do you hide / show things using VBA?

Selectively hiding or showing is a great way to enhance your models, dashboards or reports. I use this technique very often. Most of my dashboards, products etc. contain interactive help that user can see or hide with a click. In background, I use few lines of VBA to do this magic.
What about you? Do you face similar situations? How do you handle them? Share your VBA tips & ideas using comments.

Are you new to VBA?

If so, you have hit a treasure chest. Start with our Excel VBA page and get the basics. Once you are ready to take a deep dive, go thru dozens of VBA / Macro Examples.
And when you want more, consider joining our VBA classes.

Can you find that pattern? [Homework]

Can you find that pattern? [Homework]:
Hi folks…
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
Your Homework:
In a range we have some resource types & their billing rates.
In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.
See this diagram:

Download the data
Click here to download the data to write your formulas.
Notes:

• The file contains 3 named ranges – resIDs, resTypes, resRates for resource IDs, types & rates in that order. You can use these to shorten your formulas.

Post your answers:
Go ahead and solve this. Then come back and post your formulas in comments. Click here to post your answer.
Need clues?
Read wildcards in COUNTIF formula page to get some clues.
So go ahead and post your answers. I am waiting…

Highlight best week & month in a trend chart [tutorials]

Highlight best week & month in a trend chart [tutorials]:
When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,

What is the best month / week ?

To answer this question, we need to make a chart that looks like this:

How to highlight best week or best month in a chart?

Today, lets learn how to highlight portions of such charts that correspond to best week or best month.

First, an important rule

To highlight data: If you have all the numbers for this chart in a range A1:A100, and you want to highlight the maximum value (or top 10 values), you use conditional formatting.
But with charts: there is no such thing as conditional formatting. So we must imitate the effect. This is done by creating extra series of data (for best week, best month etc.) and formatting it accordingly.
So the rule is To highlight a portion of chart, we need to create another series for that portion and format it the way we want.

Step 1: Create a regular line chart from your data

Lets assume our original data is like this:

Select it and create a line chart to depict the trend of values.

Step 2: Calculate Weeknum

Weeknum will have the week number for each date. This is calculated by =weeknum(date)&”-”&year(date)

Step 3: Calculate Best week portion

For our analysis, lets assume that best week is the week with highest total sales. To do this:

1. Add one more column, lets call it weekly total. In this, sum up the total for each week.  The formula =SUMIF(weeknum, current-weeknum, values) will give this.
2. Now, find the maximum of this column using =MAX(weekly-total)
3. Add one more column – best week. This will have NA() for all values except the maximum week. The formula for this would be =if(weekly-total=max-weekly-total,value,na())

Step 4: Add the best week series to chart

Copy the best week column and paste it in your original chart.
At this stage, our chart has 2 series:

1. Original line corresponding to all dates
2. Best week line corresponding to only best 7 dates

Format this new series in any way you want. And your chart highlights best week.

Step 5: Follow similar process for Best month

To highlight best month, you need to calculate month, monthly total, max-monthly-total & best month values. Once they are ready, just add the best-month values to the chart and you are done!
Calculations Explained:
See this illustration to understand how the calculations for best week & month work.

Download Example Workbook

Click here to download the example workbook & play with it. The workbook contains 2 charts.

• Best week & month highlighted
• Best week & month highlighted along with drop lines

Examine the formulas & resources section of download file to learn more.

Do you highlight portions of charts?

Highlighting a portion of chart is very useful to draw user’s attention. I do this all the time in my dashboards & reports. Unfortunately, there is no automatic way to do this. So we resort to techniques like this.
What about you? Do you highlight portions of charts? What techniques do you use? Please share your ideas & tips using comments.
Also, read more on Dynamic Charts, Excel Tables & Interactive Highlighting in charts.