All articles in 'Excel Challenges' Category

How about a fun Excel challenge? I have data in below format in the table named trips I want to know which employees visited both USA & UK? How would you solve this problem? Post your solutions in the comments. Need sample data & my solution? Click here to download the file. Want more challenges […]

Can you split the atom? No, ok, how about splitting the expenses then?
In this homework challenge, can you take a look at the expenses of wolf-pack and tell me who owes who & how much?

Time for another homework. You got a spreadsheet of department budgets and need to write a formula to get budget for a given department, month combination.

I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom. How would you solve this? Refer to this workbook for 3 possible answers. Just move […]

Welcome to annual Easter Egg hunt at Chandoo.org. This year I have a feast for you. I hid 20 Easter Eggs in this workbook. Go ahead and find them. Share your solutions, clues or struggles in the comments box.

Happy hunting.

Are you ready for a fun Excel challenge? Read on then…

You are a nautical transport manifest analyst at New Donk city harbor. But you also have a secret identity. You are a spy for Global Intelligence Organization. As part of routine inspection of cargo details, you came across a list of shipping codes that look suspicious.

Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.

Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.

Its Easter time. At Chandoo.org, I have tradition of publishing Easter Egg hunts since 2009. This is the first time our Easter egg hunt is on Power BI. Changing times, eh?

The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. […]

Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.

This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.

Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

Your mission is to find out answers to below questions.

• How many employees used exactly 100% of their entitled sick leave?
• How many employees did not take any sick leaves?
• Listing of all employees who used 100% of their entitlement

Use either Power Query, Excel formulas or any other technique to answer the questions.

Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.

If so, take up this homework problem and post your answers in comments.

Assuming you have an input date in cell A1,

• What is the formula for finding next Monday?
• What is the formula for finding first Monday of next month?

Post your answers in comments

Time for some soccer fun in spreadsheets. Given a team points table like above,

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

• Pick the top two teams by points
• If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
• If there is a tie, use Goals For (GF) to break ties

It is Easter time. This is the first Easter we are celebrating in our new house. So it is bound to have so many special memories. For last 10 years (wow, it has been a decade of tradition), I have been running Excel based egg hunts every Easter. It all started in 2009, when I was living in Sweden (where Easter is a BIG thing). I had to share the enthusiasm with someone, so I made our first Easter egg hunt on the blog.

This year, join me on an Excel themed cryptic crossword clues – Easter egg hunt. Don’t forget to share your scores and explanations in the comments.