All articles with 'Learn Excel' Tag

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Pivot tables are a great way of summarising and consolidating data to produce summary reports.

One of the main limitations of Pivot tables is that they don’t natively return Text values.

This post looks at a method to work around this without the use of VBA.

Bhavik’s has presented us with a neat formula to determine how many working days are in a month.
This functionality has a wide range of uses in accounting, payroll, staff scheduling, accommodation planning & reporting and general scheduling activities.

Today in Formula Forensics we pull Bhavik’s Formula apart and see what is going on inside and the offer some extensions to increase the functionality of his formula.

Hello folks,

My flight to Sydney has been lengthy but fun. I have reached here on Sunday morning (8AM) and spent most of the day with Danielle’s family. (Danielle is the founder of Plum Solutions. She is the one who invited me to Australia and planned this whole experience for me).
On Monday (30th April), I went exploring the city on foot. I had coffee in the beautiful Queen Victoria Building, attended 1PM church service at the magnificent St. Mary’s cathedral, walked thru Hyde park, went to Sydney central station, took at sneak peek at the new Apple store in down town, got back to my hotel, walked to Opera house to meet up with our readers.

Reader meetup at Sydney
We had 6 people turn up for the meetup. It was fun talking about Excel & our journey with these wonderful folks. Here is a pic (you can see the harbor bridge in background & 8 awesome Excel users in foreground, Click on it to enlarge).

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.

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.