Archive for March, 2011

Ok, Excel Hyperlinks may not be able to rescue you from an imploding planet, but they can add to the useability of your Excel Projects.

This post looks at Hyperlinks. What they are and how to use them in your workbooks

Excel can be a bland collection of cells with text and numbers or it can be a playground where you can have interactive engagement with your models.

The choice is yours!

Excel provides a small number of tools to allow you to interact with your models.
These tools are called Form Controls and can be added into and linked to your models data.

This post will look at the various types of Form Controls and discuss their application and use.

During last one week, we had a gala time with Dashboard Week on chandoo.org. To wrap-up the week, I am sharing a list of recommended resources, websites, tutorials & ideas for making dashboards.

Recommended Resources on Making Dashboards:
I have broken down this post in to various sections. Click on the links to quickly access the part you want to know or just keep scrolling to get the whole thing.

1. Books on Dashboards
2. Websites for Learning about Dashboards
3. Dashboard Training Programs
4. Add-ins & Software to Make Dashboards
5. Dashboard Tutorials & Downloads on Chandoo.org

In 2008, I received an email from Robert Mundigl, which was the start of a life-long friendship. Robert asked me if he can teach us how to make KPI dashboards using Excel. I gladly said yes because I am always looking for new ways to use Excel.

The original KPI dashboards using Excel article was so popular. They still help around 12,000 people around the globe every month. Many of our regular readers and members have once started their journey on Chandoo.org from these articles.

In this article, we will revisit the dashboard and give it a fresh new spin using Excel 2007.

Purpose of the dashboard:

This is a guest article written by John for our Excel Dashboard Week.

This Dashboard was constructed for a number of reasons, one of which was to reduce the number of reports produced with the same data ( up to 6 separate files ). As we all know, when it comes to senior management and reports / files the more information they can get on one report / file the better for them. So, with this in mind I created the Dashboard to show the data they need to see “quickly” each week.

Early in Jan, I got this mail from Mara, a student in Excel School first batch.

Hi Chandoo,

I took your first Excel batch class and loved it. I created a dynamic and interactive dashboard for my work. My boss thinks it’s an excellent tool and I have you to thank for and also Francis Chin who shared his travel dynamic dashboard. I integrated things you taught so thanks so much!

I felt very proud reading her email, so I asked her if she can share the dashboard with some dummy data so that we all can learn from her example.

Being a lovely person Mara is, she gladly emailed me the workbook and I am thrilled to include it in Dashboard Week.

As part of Dashboard Week, in this post, we will take a look at Health-care Dashboard prepared and shared by Alberto. He put together an excellent dashboard to visualize hospital performance and understand what is going on. Read this post to understand how this dashboard is made, watch a tutorial video & download example workbook.

Thank you Alberto for sharing the file & helping us learn.

Hello there,

I am glad to announce this week (21-25 March, 2011) as Dashboard Week on Chandoo.org
What happens in Dashboard Week?
As you can guess, during this week, I will be posting exclusively about Excel Dashboards. This is the tentative agenda … []

Here is a fun formula to write.

Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 58.

Now, how would you write a formula to find this sum automatically based on the number entered in the cell?

Go ahead and figure it out. If you can, come back and check your answer with mine below.

There are beautiful, powerful & awesome charting examples all around us. Today, I want to show you how we can harness the power of Excel to create Analytical Charts.

Analytical What?!?

To be frank, I do not know what to call these charts, so I choose the term Analytical Charts. But this is what I have in mind when I say Analytical charts:

A chart is analytical chart,
(1) If it is interactive
(2) It it can answer different questions by re-structuring same data differently

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.

If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

We have a new series on chandoo.org. – Excel Challenges. From now, every 1-2 months, I will post an interesting Excel Challenge. These are tricky problems for which elegant solutions should be found. To keep the challenges exciting, we will have a small prize for a winner (if more people answer correctly, we pick one randomly)

Excel Challenge #1 – Find Overlaps in Machine Schedule Dates

We have an Excel Table, with the following data (snapshot above). You need to write formulas to display Ok or Not Ok in the adjacent column based on the following criteria:

1. Display Ok if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
2. Display Not Ok otherwise.

I have a quick announcement for you.
As you may know, we have re-opened registrations for our 2nd batch of Financial Modeling School on Feb 23rd. We will be closing the doors for new students tonight at 11:59 pacific time. Thank you so much for supporting this program enthusiastically.

If you wish to join Financial Modeling School, click here.

Here is a quick tip on removing data validation rules. We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it? The normal approach is, Select the cells with […]