Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

All articles in 'Featured' Category

Are you a Solver Virgin? Watch this tutorial video …,

Published on Oct 15, 2010 in Featured, Learn Excel

Do you ever think about questions like this?
1) What is the maximum profit we can make?
2) What is the best way to schedule employees in shifts?
3) What the best combination of tasks we can finish in a given time?

You might have heard about Excel Solver tool while trying to find solutions to questions above. If you have never used Solver or have little idea about it, then this post and video are for you.

Continue »

Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]

Published on Sep 28, 2010 in Featured, Learn Excel
Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]

Here is a quick home work on excel conditional formatting. Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right). Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines. The data set is […]

Continue »

How to cook a delicious dynamic chart that will have your boss drool

Published on Aug 31, 2010 in Charts and Graphs, Featured
How to cook a delicious dynamic chart that will have your boss drool

Dynamic charts are like my favorite food, Mangoes. They tempt, tease and taste awesomely. In this post, we are going to learn how to create a dynamic chart using check boxes and formulas as shown in the animation aside. Are you ready for some excel chart cooking?

You can also download a FREE Dynamic chart template from the post. So go ahead and make your boss drool.

Continue »

How do you make charts when you have lots of small values but few extremely large values? [Debate]

Published on Aug 20, 2010 in Charts and Graphs, Featured, Learn Excel
How do you make charts when you have lots of small values but few extremely large values? [Debate]

Here is an interesting charting problem we come across once in a while. We have a lot of small numbers and a few very large numbers. How do we effectively plot all of them in a chart?

Now, how do you go about making a chart?

Continue »

What is the most unusual thing you have used Excel for? [Quick Poll]

Published on Aug 13, 2010 in Featured, Learn Excel
What is the most unusual thing you have used Excel for? [Quick Poll]

Ok. This is quick and short. What is the most weird, unusual, out of the world thing you have used excel for?

I have used excel to generate and clean a list of tags for this blog, to keep track of which movies I have seen in the IMDb Top 250 list, to simulate monopoly board game, just so I can play it better than my wife ;) etc.

What about you? Share using comments.

Continue »

How Francis Landed on Chandoo.org, Become Awesome and Made a Superb Dashboard, all in ONE Weekend

Published on Aug 4, 2010 in Charts and Graphs, Featured, Learn Excel

As part of our Reader Awesomeness Week, Francis shares with us a travel site dashboard he made for his company. Francis took just two days to prepare this awesome dashboard which uses concepts like bullet charts, sortable KPI grid, Date based filtering, dynamic charts and more. I was really moved by Francis’ gesture in sharing his work with us, so much that, I did a video review of his work. You can see it in the embedded youtube video to the left.

Read the rest of the article to know how this dashboard is made and get a copy of the file.

Continue »

Introduction to Financial Modeling using Excel [Part 1 of 6]

Published on Jul 21, 2010 in Featured, Financial Modeling, Learn Excel
Introduction to Financial Modeling using Excel [Part 1 of 6]

In this and next 5 posts, we are going to learn how to build a financial model to do project evaluation using Excel. The 6 parts of this tutorial are,

  1. Introduction to Financial Modeling
  2. Building a layout for Project Evaluation Model – Best practices
  3. Building Inputs and Assumptions Sheet
  4. Building Projections for Project Evaluation
  5. Modeling the Cash Flow Statement and Projections
  6. Putting it all together – Final Project Evaluation Model

What is financial modeling?

Financial modeling is creating a complete program/ structure, which helps you in coming to a decision regarding investment in a project/ company. Now this could be on a simple piece of paper or in excel. The advantage with excel is that, even if you have calculation speed and accuracy like me (this is one place where I am like Einstien!), then also you would be able to come to the right conclusion!

Continue »

Find-out Days Overlaped [Excel Formula Homework]

Published on Jul 7, 2010 in Featured, Learn Excel
Find-out Days Overlaped [Excel Formula Homework]

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like, =if(or(and(x>=a,x<=b),and(a>=x,a<=y)), “Overlap”,”Do not overlap”) […]

Continue »

Become a Comparison Ninja – Compare 2 Lists in Excel and Highlight Matches

Published on Jun 17, 2010 in Excel Howtos, Featured, Learn Excel
Become a Comparison Ninja – Compare 2 Lists in Excel and Highlight Matches

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel. We will learn how to compare 2 lists of data in 3 + 1 different ways. (click on links to jump to that section of […]

Continue »

Average of Top 5 Values [and some homework]

Published on Jun 4, 2010 in Featured, Learn Excel
Average of Top 5 Values [and some homework]

The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard. Now, if she wants average of all values, we can use AVERAGE() formula if she wants top 5 values […]

Continue »

What is new in Microsoft Excel 2010? [Office 2010 Week]

Published on May 17, 2010 in Featured, Learn Excel
What is new in Microsoft Excel 2010? [Office 2010 Week]

Office 2010, the latest and greatest version of Microsoft Office Productivity applications is going to be available worldwide in the next few weeks. I have been using Office 2010 beta since November last year and recently upgraded my installation to the RTM version. I was pleasantly surprised when I ran Microsoft Excel 2010 for first time. It felt smooth, fast, responsive and looked great on my comp.

This week, I want to celebrate the launch of Excel 2010 and write about the following new features in Excel 2010.

Also, you can win a copy of Office 2010 – home & student edition by leaving a comment on this or any of the other excel 2010 posts scheduled for this week

Continue »

Introduction to Panel Charts using Excel – Tutorial & Template

Published on May 12, 2010 in Charts and Graphs, Featured
Introduction to Panel Charts using Excel – Tutorial & Template

In this article we will learn what a Panel Chart is and how you can construct a panel chart in Excel.
What is a Panel Chart?

A panel chart is a set of similar charts neatly aligned in panels to help us understand some data which has multiple variables in it. Panel charts are also called by names “trellis displays” or “small multiples”. They are an effective way to display multi-variable data.

Here is an example panel chart showing the total defects per module over the last 4 weeks.

Continue »

Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide

Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide

If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!

How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost”

This post is going to detail the use of the Data Table function within Excel, which can help you answer that question and then so so much more.

Continue »

How to Visualize Survey Results using Incell Panel Charts [case study]

Published on Apr 1, 2010 in Charts and Graphs, Featured, Learn Excel
How to Visualize Survey Results using Incell Panel Charts [case study]

A panel chart (often called as trellis display or small-multiples) shows data for multiple variables in an easy to digest format. It lets users compare in any way and draw conclusions with ease.

Today, I want to discuss how the principles of panel chart can be applied to visualize a complex set of survey results. For this we will use the recent survey conducted by Gartner on how various customers use BI (Business Intelligence) tools.

Continue »

Making a Dynamic Dashboard in Excel [Part 1 of 4]

Published on Mar 16, 2010 in Charts and Graphs, Featured, Learn Excel
Making a Dynamic Dashboard in Excel [Part 1 of 4]

In this and next 3 posts, we will learn how to make a Dynamic Dashboard using Microsoft Excel.

At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using excel formulas and simple VBA macros.

Continue »