Archive for April, 2012

Two years ago, If someone told me I would be flying to Australia & get handsomely paid to do it, I would have rubbished the thought. You see, exactly two years ago, I quit my job to work on Chandoo.org for full time.

My main focuses then were,

1) to make sure my family would not starve or be deprived.
2) to make you awesome in Excel

Today, #1 does not keep me up in the night. Thanks to you. You have supported Chandoo.org, purchased our products, spread good word about our site. This removed all my doubts & financial worries. Now, most of my work day is focused on making you awesome in Excel.

Welcome back. In final part of Making a Customer Service Dashboard using Excel let us learn how to add macros & VBA code that makes our dashboard interactive.

As you can see, there are 2 important macros in this dashboard.

If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,

=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)

If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.

So, to test how well you know your IFs & Boolean functions, let me give you a small challenge.

When I announced my upcoming Australian Excel masterclass, I got quite a few mails (ok, just a few), all which are on these lines,

Wow, I would love to meet you when you are here. Let me know if you have sometime on an evening for some coffee or beer?

Down under, I must be popular.

After saying yes to a few invitations, I saw the hassle in arranging several individual rendezvous. So here is an open invitation to you all. If you like Chandoo.org or Australian brewed coffee, clear your calender on below dates,

• Sydney – 30th April
• Brisbane – 2nd June

I am yet to decide the venue & time. But preferably, we will meet near water around 5ish in the evening. If you want to join me & share your Excel stories (or just any stories), fill up below form.

Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Read on…

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…

Help

Welcome back. In third installment of our Customer Service Dashboard series, we will learn how to construct the charts in our dashboard. We will understand the sparklines, traffic lights & dynamic chart setup. To help you learn better, I have recorded a short video too. Go ahead and enjoy.

So what’s the cash with Facebook? Last time, we announced the launch of a new module on getting the cash flow statement in our financial modelling course and also discussed the procedure to create the cash flow statement. I had shared with you the template for financial statements for Facebook. I had asked you to implement the steps and create the cash flow model for Facebook!

We know that the devil is in the details. Last time we discussed the process and in this post, we would implement the cash flow statement for Facebook using the indirect method.

Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.

Hi readers,

Just a quick reminder. We are closing early-bird discount for my Australian Excel & Dashboards Masterclass on 16th April. So if you want to attend my training program in Sydney, Melbourne or Brisbane, you have very little time left to sign-up.

Click here to sign-up for Advanced Excel & Dashboards Masterclass.

Over Easter, while we were all busy eating our Easter Eggs, at the Chandoo.org/Forums, Slk213 was worried about how to retrieve the Nth number from a row of data which had gaps in it.

Today in Formula Forensics we will look at how to solve this problem.

If there was a challenge in any Finance 101 course at any B School, I think Creating the Cash Flow Statement would score the max. When I was pursuing my MBA, the biggest enigma for me was to go from the Balance Sheet and Income Statement to the Cash Flow statement via the indirect route. I could never get it right!

There were two challenges, the bigger was obviously unclear concepts plus I didn’t know how to play with Excel! I have worked hard on both and let me show you, how they can be used to get the right cash flow statement in no time!

Actually the concept is simple and excel makes it run on steroids!

Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Hello Friends…,

Happy Easter 2012

It is Easter time again. It has been our tradition to hide some Easter eggs so that you have fun discovering them. I usually hide the eggs in the blog post. But this year, I went one more step and created an Excel file with 3 hidden Easter eggs.

Go ahead and find the eggs. I am sure you will love them 🙂

Today Faseeh, from the Chandoo.org Forum, joins Chandoo.org as a contributor with his first post.
Faseeh, helps find a solution to Maljzm’s problem of looking up time values from a list and comparing them to determine if a value meets some criteria.