All articles in 'Excel Challenges' Category

Here is a quick visualization challenge for you.

There are 25 KPIs and each has a base value, current month, corresponding month values. The KPIs also have targets.

The data looks like this:

Download the data:

Click here to download the data for this contest.

Rules for this contest:

• Visualize this data using one Excel chart (one chart per KPI is ok too).
• You may omit any any data
• You can use interactive charts.
• Do not use add-ins or VBA to make your charts

Go ahead and make your chart(s).

Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.

So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.

Calculating Bill’s total working hours (video)

Here is a simple but tricky problem. Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy. As this is a one person production facility, Billy has the flexibility to choose his working hours. At the […]

I am in Sydney and yesterday we had a meetup of Sydney Excel users. Around 15 people turned up for the event and we talked about various Excel related topics over few drinks. One of the questions that came up was,

I learn and use Excel in better ways. But my boss doesn’t how to open the workbook and use simple stuff like filters, slicers, sorting and selecting. So I end up sending screenshots or PDFs instead of powerful Excel files. What to do?

Although we discussed various possible solutions to the problem, I thought it would be a good topic for us discuss here.

So how would you train your boss to use Excel?

Please share your thoughts, experiments, experiences and suggestions in the comments. Let’s make our bosses awesome.

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?

Here is a fun problem to think about.

Let’s say you are looking at some data like above.

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Imagine you run a cute little pastry in Rome (Italy). To boost the sales you have a 2 person sales team. Caterina & Antonio. Caterina is the manager & Antonio, her assistant. Apart from basic salary, they will also receive sales commission. This comes from a portion of net profit allocated to “incentive pool”. The […]

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Its homework time again. This time, lets tackle an interesting & everyday problem. Lets introduce our protagonist of the story – Jack. Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate. And oh yes, Jack is […]

Finally the wait is over. Here are the winners of our 2014 dashboard contest. Check them out.

Hello Friends…,

It is Easter time again. Every year, we at Chandoo.org celebrate Easter by hunting for eggs (not in the wild, just in the Excel workbooks). This year too I have hidden 30 Excel tips in a workbook. You have to solve 3 puzzles to find them. So what are you waiting for. Go ahead and hunt for them eggs.

Click here to download the Easter eggs file.

I am sure you will enjoy them 🙂

Time for another dashboard contest. Sit straight, set aside your appointments and get number-crunching.

Analyze state to state migration data between 2012 & 2010 and present your results in a dashboard (or charts) and you could walk away with glory, fame & and an iPad.

If all of this sounds tempting, read on.

The contest

1. Download dataset for the contest [mirror]
2. Analyze the data and create a dashboard or set of charts (see rules below)
3. Submit your workbook by 20th of April (Sunday), 2014.
4. If readers at chandoo.org and our panel of judges love your work, you get one of the cool prizes.

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

• How to delete blank cells & rows?
• Dealing with blanks – case study
• Quickly filling blank cells in a table
• Extracting non-blank data from a list

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Its Friday, that means time for another Excel challenge for you.

Calculate vacation days in a period:

Your mission, if you choose to accept it,

Step 1: Download the hom work problem file.

Step 2: Calculate number of vacations taken in a period. Specifically,

1) How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
2) How many vacations are taken such that at least one day of vacation is between start & end dates?
3) How many people took vacations? (if same person took multiple vacations, then count it as 1)