Archive for September, 2010

As some of you know, I run an online Excel Training Program called as Excel School. I have opened registrations for 3rd batch of this program on September 14th. Thank you very much for supporting this program wildly. In a few hours, I will be closing the registrations for Excel School.

Click here to sign up for Excel School

Read the rest of the post to learn more about Excel School and also download time across world chart template.

Here is a quick home work on excel conditional formatting. Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right). Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines. The data set is […]

Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]

If you are building financial models or any other type of excel based decision models, chances are, there will be multiple scenarios in your model. Whenever you have multiple scenarios, you may want an easy, intuitive way to select one of them. In this post, I will present an interesting scenario display & selection technique that I received by email from our reader Itay Maor.

Some of you know that I run an online excel training program called Excel School. If you want to join, click here. Only 8 days left.

I run excel school mainly to meet new students, understand their problems and learn new ways to solve them. But, Excel School also presents me with an interesting analytics challenges. In this post, I will share 2 pivot table based analytic techniques I used just yesterday to answer few questions I had about Excel School sign-ups.

Watch this 15 min. video to see how I analyzed the data

This is a guest post written by Paramdeep from Pristine. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you. This is Part 6 of 6 on Financial Modeling using Excel In this tutorial we are going to learn how to build assumptions & input sheets in our excel […]

We all have been there. You started to build a simple workbook to keep track of an ongoing project or dashboard or something equally complicated. Even before you realize the workbook has 23 sheets and 41 named ranges all going from one place to another, like flying spaghetti monster, only less awesome. Now, how do […]

Almost an year ago, my life took a pleasant, awesome turn. I became dad to two beautiful, naughty and energetic kids. We named them Nishanth & Nakshatra (meaning Moon & Star). Ever since they entered our world,  our lives have changed. They bring laughter, love, anxiety and curiosity to our life everyday. And this Friday […]

Here is a little experiment to freak out excel.

Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.”

Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.

A circular reference is created when you refer to same cell either directly or indirectly.

See Mona Lisa, in circular reference style.

Learn more about Excel Circular References, how to use them, examples, how to avoid them, how to deal with them in this article.

Hello reader, my friend & supporter,

I am happy to announce that Excel School is open and ready for registrations.

Visit Excel School page to learn more and sign-up.

In this post, you can find some information about the program and links to sign-up.

Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.

Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?

See the demo aside to understand and read on to master the trick.

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too. Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets […]

This post is about my Excel School online training program. Feel free to skip if you are looking for excel tips. Some of you know that I run an online Excel training program called Excel School. It helps excel newbies and beginners to learn about various powerful features and become proficient in them. We discuss […]

Sometimes when you are working with data, you may need to just copy and paste numbers from one range to another. Here is a handy little trick to achieve that.

Use Paste Special > Add operator to paste numbers only.

1. Select the source range, press CTRL+C
2. Now, go to target range, press ALT+ES to activate paste special dialog
3. Select “Add” operator (you can press d)
4. Click ok.

It is the labor day weekend in USA. That means, majority of our readers are busy barbecuing, enjoying the last few days of summer with their families and vacationing. Even though, I am not in US, I never miss an opportunity to sit-back and relax. So I thought of not posting anything until 8th of Sept.

Then, I realized, what the heck, only US has labor day. For rest of us, this is just another labor day.

So, we have a contest. I am giving away one $14.85 worth gift card to a random commenter.