All articles with 'Learn Excel' Tag

Today, while answering a reader’s email, I wrote this VBA code, If Target.Value = “yes” Then ‘do something End If But I realized that my code would run only the Target cell has “yes” in it. It wont run if the target cell has “YES”, or “Yes” or “YeS”. This is because by default, all […]

.
.
.
.
.
.
.
This is the Forth post in Chandoo’s, Formula Forensics series.

Last week Luke showed us how to extract a sorted list according to a criteria from a larger list
and he analysed a formula to solve this problem

This week we look at Fred’s Problem…

How do I simplify a very long formula?

Last year, Steven shared a beautiful Christmas Gift List template with all of us. It is packed with lots of Excel goodness. Just a few days ago, he emailed me another copy of his file with some improvements. So if you are planning for Christmas shopping and want a handy tracker, you don’t want to miss this.

So how did your weekend go?

I did a bit of gardening, painted our car shed, played badminton (I am learning), attended 60th birthday of a close friend’s dad. Pretty hectic, but fun as usual.

To start this week, let me share a simple but fun way to add data to charts.

Lets say you have a chart that depicts Annual sales for last few years. And you want to add the data of Profits (or Expenses) to this chart. Here is a dead-simple way to do it.

1. Copy the profit data by selecting it and pressing CTRL+C
2. Select the chart
3. Paste by pressing CTRL+V
4. That is all!

Today we publish what is hopefully the First of many posts by guest author Luke M. it is also the Third post in the Formula Forensics series, which has now moved to a regular and new Thursday slot ?

Luke has taken up Hui’s and 3G’s challenge of explaining how the technique he regularly uses on the Chandoo.org Forums for extracting individual records out of a group.

Today Luke tells us how to extract a list of items from a larger list according to some criteria.

Today, lets learn how to create small-multiples (or panel charts) to visualize trend of 2 product lines over years in various regions. The inspiration for this chart and article came from my friend, Paresh‘s recent article on his blog.

See how you can create a combination of area & line chart to create the small-multiple chart and clone it. You can also download the workbook and play with it to understand this technique better.

My dear friends & readers of Chandoo.org,

We are so happy to announce Excel for Project Managers program for your consideration. Please read this short page to understand the benefits of this course & to sign-up. Or visit our sales page for details.

Who is this course for?

Well, I am sure you know the answer. This course is aimed at Project Managers, Business Analysts, Project Leads and people aiming for various project management roles. This course also helps project sponsors, people working in reporting & financial analysis of projects.

To enjoy this course, you should be familiar with various aspects of Excel (preferably Excel 2007 or 2010). This course is not aimed at absolute newbies of Excel.

While I was away, Hui did a splendid job of starting a new series called Formula Forensics. The idea is to break down formulas for difficult real-world problems so that we can understand them better. In that spirit, I am giving you an interesting and tough formula homework. Situation: Imagine you work for Large Fries […]

Last week, we have announced about an upcoming online training program – Excel for Project Managers. Quite a few of expressed interest and shared ideas on how we should do this course. Finally, I am happy to announce the dates for this program and share details. Read on… Who is this program for? Well, there […]

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

• Pull apart an Array Formula and see what’s under the hood;
• Have a look at a technique for quickly developing Custom Number Formats &
• Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

Few days ago, we announced about our upcoming Excel for PMs course. Quite a few of emailed me and told that you are looking forward for this.

Today I want to share our preliminary agenda for this course and take your help in fine-tuning it.

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps …,

This post is authored by Martin, one of our readers.

Situation:

Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:

This can be annoying when it comes to interpreting the data and when sorting columns.

Solution:

To know how I solve this, just read rest of the article.

In 2005, fresh out of MBA School, I was put on managing an e-business transformation strategy project. It was then that realized that project management was a full time job (I always wondered before that why PMs were even paid a dime!). We created huge Project Management reports for the client, where were tracked and […]