fbpx
Search
Close this search box.

All articles in 'Excel Challenges' Category

Who went to both USA & UK? [Excel Challenge]

Published on Nov 10, 2023 in Excel Challenges, Learn Excel
Who went to both USA & UK? [Excel Challenge]

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 […]

Continue »

Can you split “The Hangover” expenses? [Excel Homework]

Published on May 19, 2022 in Excel Challenges, Excel Howtos
Can you split “The Hangover” expenses? [Excel Homework]

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?

Continue »

Write a formula to get Department Budget for a Month [Homework]

Published on Mar 12, 2021 in Excel Challenges
Write a formula to get Department Budget for a Month [Homework]

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.

Continue »

Lookup last non blank value – Excel Challenge

Published on Jun 12, 2020 in Excel Challenges, Learn Excel
Lookup last non blank value – Excel Challenge

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 […]

Continue »

There are 20 Easter Eggs in this Workbook

Published on Apr 10, 2020 in Excel Challenges, Learn Excel
There are 20 Easter Eggs in this Workbook

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.

Continue »

Is there a secret code in this data? [Excel Homework]

Published on Mar 27, 2020 in Excel Challenges
Is there a secret code in this data? [Excel Homework]

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.

Continue »

Job Title Matching Problem [Excel Homework]

Published on Aug 30, 2019 in Excel Challenges, Power Query
Job Title Matching Problem [Excel Homework]

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.

Continue »

How many calls we got outside office hours? [Excel / Power Query homework]

Published on May 3, 2019 in Excel Challenges, Power Query
How many calls we got outside office hours? [Excel / Power Query homework]

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.

Continue »

There is an Easter Egg in this Power BI report

Published on Apr 18, 2019 in Excel Challenges, Power BI
There is an Easter Egg in this Power BI report

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?

Continue »

Elevator problem – Excel homework

Published on Dec 13, 2018 in Excel Challenges
Elevator problem – Excel homework

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. […]

Continue »

Leave entitlement vs. usage analysis with Power Query

Published on Sep 24, 2018 in Excel Challenges, Power Query
Leave entitlement vs. usage analysis with Power Query

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.

Continue »

How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Published on Sep 21, 2018 in Excel Challenges
How many people used their entire sick leave entitlement? [Power Query / Excel homework]

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.

sample data - sick leave entitlement vs. usage

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.

Continue »

When is the next Monday? [Homework]

Published on Aug 31, 2018 in Excel Challenges
When is the next Monday? [Homework]

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

Continue »

Which two teams qualify to next stage? [Excel Homework]

Published on Jun 15, 2018 in Excel Challenges
Which two teams qualify to next stage? [Excel Homework]

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
Continue »

How many Excel themed cryptic crossword clues can you solve? [Easter Eggs 2018]

Published on Mar 29, 2018 in Excel Challenges
How many Excel themed cryptic crossword clues can you solve? [Easter Eggs 2018]

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.

Continue »