First a bad news: I am leaving to India tomorrow and that means there will not be many blog posts in the next week or so. Once I reach home and get an internet connection, the updates should be regular.

Now the good news: free excel calendar template for year 2009 (well, it works for any year all the way up to 9999)

Go ahead and download it, change the year number in the first sheet to 1981 or something and see the magic.

More importantly, the workbook is unlocked so you can see what formulas are being used. There is a worksheet named “mini” which calculates the calendar dates and weekdays for the year specified by you. Once that is done, I have used references to create a 12 month view in the first sheet.

There are 12 more sheets each for one month in the year. These sheets use a combination of named range based references along with cell() formula to automate the whole thing. Oh yeah, they also use camera tool outputs to show you previous and next month calendar views.

So, download, share it with a colleague or use it as you wish: Excel Calendar for year 2009 - Download and Print Free

PS:  I have tested this in Excel 2003 and I guess it should work the same way in 2007. Let me know if you see something funny.

Of course, you will make a visualization out of it so that anyone can know where the tickets are issued most.

Check out this interactive visualization of parking tickets from NY Times.

One thing they should have done is, instead of showing absolute tickets in each block, they should have showed total tickets in each block vs. total number of cars parked in that block. That way we would know which spots are more prone to tickets when your meter times out.

Nevertheless great work NY Times

How to add camera tool to standard toolbar?

In order to use camera tool, you must add the tool to a tool bar in excel menu area. Here is how you can do that:

1. Go to menu > tools > customize
2. In the dialog go to “Commands” tab and select “tools” in categories.
3. Scroll down in the commands area until you see a little camera tool
4. Now drag and drop this in your tool bar as shown below

How to use excel camera tool?

We will use camera tool to create a micro-chart in excel.

1. First make a normal chart.
2. Now select the cells surrounding the chart
3. Click on camera tool
4. Now click any where in the worksheet and excel places a snapshot of the range you have selected
5. Resize it until you get the microchart effect.
6. Bingo !
7. Btw, excel adds a border to the camera tool output. You can remove it by using drawing tool bar

Bonus tip: Alternatives to camera tool

Another alternative to camera tool is to use the image and indirect references technique we have learned in conditionally hide or show charts post.

Read earlier spreadcheats as well.

Here is a simple question you should ask when you set out to make a new chart.

Are you making a BLOW chart?

Blow? what blow.. you may ask. BLOW stands for Blinding Light of Obvious Wisdom. In other words, a blow chart tells nothing. It is there just for the heck of it.

I remember cramming my summer internship report with tons of charts just so that it would look like a heavily analyzed output. There were so many blow charts. But later I realized the mistake and started using charts only when they enable readers to do some analysis or provide some information.

What is your take on blow charts? Have you ever made them or you always avoid them?

PS: while you are at it, check your chart’s underwear as well.

We had 33 posts and 331 comments (highest ever). We also had 150,000 page views, 50% more than last month and highest ever. Our RSS subscriber base is now at 1750 and growing everyday. This is a great feeling and I couldn’t have done any of this without your constant support and motivation. I am thankful to each and everyone of you for helping me learn and share whatever I can.

Here is a list of best posts in November:

• Select & Show One Chart from Many (38 comments)
• Excel Formula Helper
• Say thanks with an excel tip (22 comments)
• How to say same thing in 14 different ways? (6 comments)
• vlookup(), match() and offset() - explained in plain english [spreadcheats] (11 comments)
• Advanced Data Validation Techniques in Excel [spreadcheats] (8 comments)
• Ads that are also Infographics - 10 Dazzling Examples (1 comment)
• Separating digits from a number [excel formulas] (11 comments)

Support PHD:

If you enjoy the content featured here Share it with a friend or Bookmark this on Delicious or Stumble it!. It will make my day

You can keep up to date with PHD by subscribing to our RSS Feed or E-mail News Letter

Suggest:

Feel free to drop your suggestions, comments thru contact form in the About PHD page or email at chandoo.d at gmail.com. I *love* hearing from you

Clean up data using magic and copy-paste

Unclean data is probably the most time consuming part of any analytical work. I have spent countless hours cleaning few hundred rows of data only to spend another 5 minutes to get the insights. The cleanupdata.com promises to automate the drudgery. All you have to do is paste the unclean data and tell the website how you want to process it and it would do the dirty work.

Learn Excel 2007 through video

If you are an excel beginner and would like to see some videos to learn basic concepts like writing formulas, using auto-fill, the above site is a great place to start.

Make a chart and win Tufte books

Nathan at FlowingData is giving away 2 Edward Tufte’s books: The Visual Display of Quantitative Information, 2nd edition and Visual Explanations. All you need to do is make a good chart of US Immigration data that is shown in his post.

Filter data by Months using Advanced Filters and VBA

DailydoseofExcel presents a cool and simple vba script that would help you filter data by month. (you can also do this by using advanced filter and setting month start and end date as criteria)

What is a OLAP cube?

XLCubed blog explains the basics of OLAP (online analytical processing) and cubes. This is useful if you have never or rarely used pivot tables and need to understand when to use them.

Subscribe to PHD and stay upto date on all things excel and charting

Have a fantastic week ahead.

As told earlier I have finished my 10 k run today (actually 13 k). Even though running 10 kilometers is not a big deal for many people, it is a HUGE deal for me and I thought I would write a small post sharing my experience of crossing this important milestone.

1. Ice cream: I love ice cream. But I know damn well that even a scoop a day is dangerous for me in the long run. So my wife put in a quota system for my ice cream binges. If I ran 3-4 times in a week, she would allow me to have a few scoops in the weekend. Now I don’t feel guilty when digging in to that black raspberry chocolate chip ice cream.
2. Ipod: Like most working people, I also dont have much time to listen to good music. I use running as an excuse to escape to melodious world where songs and lyrics would motivate me. When I am running I carry only 2 things, My keys and an ipod. No phones, no internet or anything. Just music and the road.
3. Nikon D40: Believe it or not, a digital SLR camera is the motivation for both of us to go from usual night walks to a running couple. That is right! When we wanted to buy an SLR, we decided to link the camera with running. For every kilometer each of us runs, we would allocate a dollar to the SLR fund. With in 3 months we had 450 or so tick marks on the calendar and we bought the camera. But once we are used to running the motivation wasn’t really necessary.
4. Commitment: A friend called me today morning and asked me if I wanted to go skiing. I said a no even though I know he would be disappointed. I didn’t want to fail myself when I said I would run 10k.
5. The Sheer Joy of Running

If you had never ran or thinking of running this is probably the time to start. I am lousy example, but if I can run 13k then you can too. When I first started running, I ran 0.5 km and panted and vouched to never to go near anything that looks like a treadmill. But thanks to motivation from friends, I have discovered amazing benefits of running. I hope you will too.

At this moment I am very angry and frustrated about the terror strikes in Mumbai. I am not sure how a person can do something like this to others. I do not believe in any god in particular, but now I am praying to god asking for little sanity and humanity in us.

I would also like to take a moment and tell you how much I am thankful for having discovered a passion through this blog. I have learned so much in last few months just by sharing what I know and interacting with people.

So to express the gratitude, I am sharing a simple excel tip. Please feel free to share your tip to tell how thankful you are for everything you have.

• Ctrl + 1 - opens the dialog box for formatting a cell. I use this all the time

Now your turn!

PS: expect delay in posts due to holidays

So here is a quick and dirty trick. Assuming your dashboard ends in row 40, select row 41 and go to menu > window > freeze panes. That way you will ensure that the dashboard remains in viewport no matter what.

Problem 1: You have 2 lists of possible values and you want a way to switch between both

PS: Many thanks to Alex who proposed this idea and solution through e-mail.

You have a cell where user can enter any value from 2 lists. But you don’t want to overload the in-cell drop down list with tons of values, and rather prefer a simpler approach like this:

Solution: Use an IF() formula in validation criteria

The solution is to use an if() formula to determine which one of the two ranges should be used to validate cell contents.

• Select the cell where you want to have this type of validation
• Go to menu > data > validation
• In the criteria area, select “allow” as “list”
• In the source area, specify a formula like this: =IF($B$7="Full List",Full-list-range,Partial-list-range)

That is all, you now have a data validation list that can change its source based on user preference.

Problem 2 : You would like to change a list’s values based on what is selected in another list

PS: Many thanks to Catherine for asking this question through email

You have a status tracking spreadsheet where each employee enters the status for each of the projects they are working on. They enter the status by first selecting the department and then selecting a project (from that department).

So how do you do this in Excel?

Solution: Use OFFSET and MATCH to determine which range to use

Remember the offset() and match() formulas we discussed in the last spreadcheats?  Assuming the list of projects for each department is in a range B10:C22 with column B having the department name and column C having the project name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem!) to determine which range to use for project cell drop-down.

• For the department cell, we can use simple list validation with values as “Marketing, Ops, Sales, IT”
• For project cell, go to data validation (menu > data > validation) and specify a formula like this:
  =OFFSET(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1)
• What is above formula doing? It is fetching a sub-range from the by finding where the first entry for the selected department is, returning x number of rows from that point, where x = no. of projects in that department.

That is all. You now have a list drop-down that changes values based on what is selected in an earlier cell.

Still having doubts?

Feel free to download this example workbook containing a tutorial on Advanced Data Validation in Excel and poke around to learn.

Subscribe to PHD to become supergood in day to day Excel in 30 days

Our spreadcheats series of posts will help you become good & productive in day to day MS Excel usage. Please subscribe to my RSS feed to get fresh new posts on excel