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.

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

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.

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

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

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.

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)