Archive for October, 2012

Journey of Hurricane Sandy – Animated Excel Chart

Published on Oct 31, 2012 in Charts and Graphs, VBA Macros
Journey of Hurricane Sandy – Animated Excel Chart

Hurricane Sandy has taken front seat in all major news channels, papers, websites even in far off places like India. I hope & pray that our readers in US East coast are safe.

Today, lets understand the journey of Hurricane Sandy in this animated chart, prepared by Chris from Excel365.

Continue »

Why am I killing Vitamin XL?

Published on Oct 29, 2012 in products
Why am I killing Vitamin XL?

About 10 days ago, I have introduced Vitamin XL – our membership program and asked for your feedback. I got several enthusiastic responses (both for and against it).

I have decided to go ahead and kill this product idea. Please read to understand why.

Continue »

Designing a Project Portfolio Dashboard [Part 1 of 2]

Published on Oct 25, 2012 in Project Management
Designing a Project Portfolio Dashboard [Part 1 of 2]

In this 2 part tutorial, we will learn how to design a project portfolio dashboard. Part 1 will focus on user needs & design. Part 2 on Excel implementation.

Background
As you may know, we sell a set of Excel Project Management templates. These templates help plan, track, manage & report a project right from Excel.

While these templates good, they have one limitation. They work for one project at a time. Many customers have asked me if I come up with a project portfolio dashboard that can tell what is going on in a set of projects in one view.

And that is where we begin.

Continue »

Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros
Even faster ways to Extract file name from path [quick tip]

The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.

1. Using find replace
2. Using text to columns
3. Using UDFs

Continue »

Extract file name from full path using formulas

Published on Oct 23, 2012 in Excel Howtos, Formula Forensics
Extract file name from full path using formulas

Today lets tackle a very familiar problem. You have a bunch of very long, complicated file names & paths. Your boss wants a list of files extracted from these paths, like below:

Of course nothing is impossible. You just need correct ingredients. I cannot help you with a strong cup of coffee, so go and get it. I will wait…

Back already? well, lets start the formula magic then.

Continue »

Please help me design our new product: Vitamin XL

Published on Oct 19, 2012 in Learn Excel, products
Please help me design our new product: Vitamin XL

Hello friends, fans & well wishers of Chandoo.org,

I am happy to announce about our new product – Vitamin XL, a membership program for you. I want to make sure that Vitamin XL offers you the best possible features & value. I need your help in designing this product. Please read this short article and give me your feedback.
What is Vitamin XL?
Just like vitamins you give you strength and health, Vitamin XL ups your Excel mojo, gives you new ideas & powers. Here is what I have in mind:

Vitamin XL is a membership program with 3 distinct benefits

  1. Excel Training
  2. Excel Resources
  3. Excel user community
Continue »

Check Cells for Equality – Follow Up [Quick Tip]

Published on Oct 16, 2012 in Huis, Posts by Hui, Quick Tip
Check Cells for Equality – Follow Up [Quick Tip]

Excel contains an often overlooked function
The Excel = Function
Today we quickly review what it can do for us.

Continue »

Last day to join Excel School + Excel Hero Academy

Published on Oct 15, 2012 in products

Become

As you may know, I have partnered with Daniel Ferry to offer an irresistible bundle of Excel goodness: Excel School + Excel Hero Academy.

Today is the last day to enroll in this combined program. More than hundred eager & enthusiastic bunch of participants have already joined us. As you read this, there are dozens of people becoming awesome in Excel.

If you have been waiting to enroll, now is the time.

Continue »

Write a formula to check few cells have same value [homework]

Published on Oct 12, 2012 in Excel Howtos

Lets test your Excel skills. Can you write a formula to check few cells are equal?

Your homework:

  • Let us say you have four values in cells A1, A2, A3, A4
  • Write a formula to check if all 4 cells have same value (ie A1=A2=A3=A4)
  • Your output can be TRUE/FALSE or 1/0 to indicate a match (or mis-match)
Continue »

Formula Forensics No. 031 – Production Scheduling using Excel

Published on Oct 11, 2012 in Excel Howtos, Formula Forensics, Posts by Sajan
Formula Forensics No. 031 – Production Scheduling using Excel

Your the production manager and have a need to schedule uneven resource across uneven requirements, how can you do that?
Well today we’ll look at Resource Allocation and Scheduling using Excel to do the hard work

Continue »

Use Indexed charts when understanding change [Charting Techniques]

Published on Oct 9, 2012 in Charts and Graphs, Excel Howtos
Use Indexed charts when understanding change [Charting Techniques]

Today, lets talk about indexing, a technique used to compare changes in values over time.

What is indexing?
Lets say you want to compare prices of Gold & Coffee over last few years. Gold price in 2011 (oct) is $1,655 per ounce. And now (sept 2012) it is $1,744. Like wise, Silver price in 2011 is $32.06 and in 2012 it is $33.61. How do we compare such diverse numbers?

Enter indexing.

First we need to calculate price of Gold and Silver in 2012 assuming their starting price is 100. This can be done with simple arithmetic.

Now, we can easily compare the prices. Looking at the indexed prices, we can conclude that both Gold & Silver prices have gone up by similar percentage (~5%).

Continue »

Excel Formatting Tips – Gangnam Style [open thread]

Published on Oct 5, 2012 in Learn Excel
Excel Formatting Tips – Gangnam Style [open thread]

Ever seen a glaring, over the top, wow-I-am-sooo-cool type of spreadsheet? Lets call them Gangnam spreadsheets!

Gangnam what?!?

If you have never heard about Gangnam style, do not worry. Just like you I too was living under a rock for about a week ago. Then I watched the awesome Gangnam style song. And now I am hooked. You can see it here.

What has all this got to do with Excel?
Oh I am coming to the point. One of the key ingredients of being awesome in Excel is,

To make our Excel workbooks communicate best by avoiding over the top formatting, unnecessary bells & whistles and focusing on what our users want.

But Excel being a feature rich software, it does have various so called Gangnam styles – superfluous 3d effects, formatting options, charting choices and as such.

Today, lets talk Excel formatting – Gangnam style

Continue »

Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence

Published on Oct 4, 2012 in Formula Forensics, Posts by Sajan
Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence

Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.

Continue »

Using pivot tables to find out non performing customers

Published on Oct 3, 2012 in Excel Howtos, Pivot Tables & Charts, VBA Macros
Using pivot tables to find out non performing customers

Moosa, one of our readers emailed this interesting question:

I have huge list of customers (around 1500).
Table includes following information
Customer # , Customer Name, Sales 2002, sales 2003, … sales 2012

My requirements are
1. list of customer who did not have sales during all these years
2. List of customer who have not business from 2003
3. List of customer who have not business from 2004

Today, lets learn how to identify all the non-performing customers.

Continue »