Archive for June, 2013

As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.

So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas.

Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?

If so, you are not alone.

Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?

That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.

For example, you can write formulas like these:
1) SUM(mySales[no. of customers]) to find how many customers we had.
2) SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

Here is a question someone asked me in a class recently.

“I know how to use VLOOKUP to find a value based on search term. But I have a slight variation to it. I need to extract value below the cell VLOOKUP finds.”

This is simpler than it sounds.

We can use INDEX + MATCH formulas to do this.

Finally my Excel classes in USA are over. It was a lot of fun traveling to new cities, teaching Excel & dashboards to enthusiastic crowds and making new friends. As if that is not fun enough, we (Jo, kids & I) are going on a 2,000 mile, 2 week road trip starting today.

Although I am enjoying all this, I also feel bad for not taking enough time to share new tricks, ideas & techniques with you here. So, I have a wacky, wild & awesome plan for you. Join us on our road trip.

That is right. You can join me on our road trip and see what I see, learn some pretty cool Excel tricks, all while sipping coffee and stretching legs in the comfort of your office cubicle.

Chandoo.org wants your ideas.
Here’s your chance to let us know what you would like to see posted about, at Chandoo.org

As you may know, I am in USA and having a lot of fun exploring new areas, meeting people and conducting live classes. While all of this is enjoyable, I am also feeling guilty because I am unable to squeeze time to share Excel tips on the blog.

So to make it up to you, I recorded this short video (2 mins) with an Excel tip while bicycling in beautiful Euclid Creek Reservation in Cleveland on Friday. Watch it below.