Archive for April, 2012

Thank you, I am flying to Australia today!

Published on Apr 27, 2012 in personal

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.

Continue »

Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]

Published on Apr 26, 2012 in Charts and Graphs, VBA Macros
Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]

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.

Continue »

An IF Formula Challenge for you

Published on Apr 25, 2012 in Excel Challenges, Excel Howtos
An IF Formula Challenge for you

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.

Continue »

Lets meet when I am in Australia

Published on Apr 24, 2012 in personal

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.

Continue »

Send mails using Excel VBA and Outlook

Published on Apr 23, 2012 in Automation, Excel Howtos, Learn Excel, VBA Macros
Send mails using Excel VBA and Outlook

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…

Continue »

Formula Forensic No 019. Converting uneven Text Strings to Time

Published on Apr 19, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensic No 019. Converting uneven Text Strings to Time

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

Continue »

Creating Customer Service Dashboard in Excel [Part 3 of 4]

Published on Apr 18, 2012 in Charts and Graphs, Learn Excel
Creating Customer Service Dashboard in Excel [Part 3 of 4]

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.

Continue »

Creating Cash Flow Statement by Indirect Method – II

Published on Apr 17, 2012 in Financial Modeling

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.

Continue »

Compare 2 Excel Files using View side by side Mode [Quick Tip]

Published on Apr 16, 2012 in Excel Howtos
Compare 2 Excel Files using View side by side Mode [Quick Tip]

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.

Continue »

Last chance to sign-up for my Australian Excel Masterclass [Reminder]

Published on Apr 13, 2012 in Excel Workshops

Hi readers,

Excel & Dashboards Masterclass in Australia - Sign-ups closing soon

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.

Continue »

Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

Published on Apr 12, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

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.

Continue »

Creating Cash Flow Statement by Indirect Method – I

Published on Apr 11, 2012 in Financial Modeling
Creating Cash Flow Statement by Indirect Method – I

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!

Continue »

Consolidate data from different excel files (VBA)

Published on Apr 9, 2012 in Automation, Excel Howtos, VBA Macros
Consolidate data from different excel files (VBA)

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.

Continue »

There are Easter eggs in this file!!!

There are Easter eggs in this file!!!

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 🙂

Continue »

Formula Forensics No. 017 – A Solution to Maljzm’s problem.

Published on Apr 5, 2012 in Formula Forensics, Posts by Faseeh
Formula Forensics No. 017 – A Solution to Maljzm’s problem.

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.

Continue »