How to Solve Excel Challenges
Imagine yourself as an intrepid explorer setting out on a grand adventure to uncover hidden treasures. The terrain is vast, dotted with enigmatic clues and guarded secrets.
Each Excel challenge you encounter is like a map leading you to a precious trove, waiting to be discovered.
Here are the stages to follow in solving these challenges;
1. The Mysterious Map:
Every Excel challenge begins with a mysterious map – a complex dataset or a tricky problem. As a seasoned explorer, you know that the map holds the key to unimaginable riches, but only if you can decipher its secrets. With each cell, formula, and function, you unveil a part of the path, bringing you closer to the treasure.
2. The Journey:
There are winding paths and puzzling obstacles. Yet, with every step, you grow more adept, your skills sharpening like a finely honed sword. You learn to navigate the labyrinth of data with precision, using your knowledge of Excel as your trusty compass.
3. The Euphoria of Discovery:
As you solve each challenge, the thrill of discovery courses through your veins. When you crack a complex formula or create a flawless solution, it’s as if you’ve unearthed a glittering gem. Each solution brings a sense of accomplishment and a deeper understanding of the vast landscape of Excel.
4. The Treasure Chest:
At the end of your quest lies the treasure chest, brimming with newfound knowledge and enhanced skills. This treasure is more than just gold and jewels; the empowerment comes from mastering Excel. It’s the ability to turn raw data into insightful information, simplify the complex, and confidently solve problems.
5. Join Explorers:
Fellow explorers share the same passion and determination. Participating in Excel challenges allows you to join a community of like-minded adventurers, each bringing unique perspectives and techniques. Together, you map uncharted territories, celebrate discoveries, and support one another on the journey.
Read More journey advice from an explorer named Owen Price
Sample Our Solutions to Various Challenges👇
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 3 steps:
=SORT(
     WRAPROWS(
         TOCOL(tbl,1) —–1.Convert to Rows
                 ,2) —-2. Wrap Rows in Columns
              ) —3. Sort Array
Step-by-step Video â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 4 steps:
1. Check the last row where 8 appears
2. Check which rows appear after the last 8
3. Check if numbers in these rows are greater than 8
4. Finally, Filter these numbers
Step-by-step Video â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 5 steps:
***Extracting Alphas
1. tbl+0 â–ºconvert the array into numbers. Alphas return an error 2.IF(ISERR(tbl+0),tbl,1/0) â–ºCheck if they are Errors and replace them with arrays
3. TOCOL(IF(ISERR(tbl+0),tbl,1/0),3) â–ºConvert them to a single column and ignore the errors.
****Extract Numbers
1. tbl+0 â–ºconvert the array into numbers. Alphas return an error
2. TOCOL(tbl+0,3) â–ºConvert them to a single column and ignore the errors
Step-by-step Video â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 4 steps:
***Filter the Rows with the word “SELECT.”Â
FILTER(ROW(tbl[Birds])-1,tbl[Birds]=”SELECT”)
****Get the Prior & Next Rows
FILTER(ROW(tbl[Birds])-1,tbl[Birds]=”SELECT”)+{-1,1}
****Lookup the Items
INDEX(tbl[Birds],
FILTER(ROW(tbl[Birds])-1,tbl[Birds]=”SELECT”)+{-1,1})
****Convert the array into a Column
=TOCOL(INDEX(tbl[Birds],
FILTER(ROW(tbl[Birds])-1,tbl[Birds]=”SELECT”)+{-1,1}),2)
Step-by-step Video â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 3 steps:
Step 1:
Check if they are greater than the previous:
(tbl[Temperature]>E1:E19)
Step 2:
Check if they are greater than the Next (Use asterisk to represent AND logic)
*(tbl[Temperature]>E3:E21)
Step 3:
Filter the data
=FILTER(tbl[Date], (tbl[Temperature]>E1:E19)*(tbl[Temperature]>E3:E21))
Step-by-step Video â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 3 steps:
Step 1:
Check the number of spaces in the words:
LEN(Names)-LEN(SUBSTITUTE(Names,” “,””))
Step 2:
Check if there is only 1 space: This will represent a string having 2 words
LEN(Names)-LEN(SUBSTITUTE(Names,” “,””))=1
Step 3:
Filter the data
=FILTER(Names,
LEN(Names)-LEN(SUBSTITUTE(Names,” “,””))=1)
Step-by-step Video & Workbook â–º Watch Here
Challenge Owner:Â Excel (Vijay A. Verma) BI
Solution: This can be solved in 4 steps:
Step 1: Â Split words
TEXTSPLIT(B2,” “)
Step 2: Check which word has all uppercase
EXACT(TEXTSPLIT(B2,” “),UPPER(TEXTSPLIT(B2,” “)))
Step 3: Check if TRUE and return the word(s)
IF(
EXACT(TEXTSPLIT(B2,” “),UPPER(TEXTSPLIT(B2,” “))),
TEXTSPLIT(B2,” “)
Step 4: Finally, Join the word(s)
=TEXTJOIN(” “,,
IF(
EXACT(TEXTSPLIT(B2,” “),UPPER(TEXTSPLIT(B2,” “))),
TEXTSPLIT(B2,” “),””))
Step-by-step Video & Workbook â–º Watch Here
Client Testimonials
I am blessed to have met such wonderful Excel Experts
Highly recommend CrispExcel for their exceptional skills as a versatile Data analyst, Trainers, and PowerApps Maker.
With robust analytical acumen, effective training methodologies, and proficiency in PowerApps development. They bring a unique blend of expertise to drive data-driven solutions and empower teams through insightful training sessions.
After working on a solution with CrispExcel, my colleagues think that I’m clever and wonder how I know these things about Excel.
I learned a lot in the process too.
Thank you so much for your help, I now have a tool that I can share with my teams to find the data they are looking for.
When it comes to data and report automation, you won’t go wrong with Crispo holding your hand.
He’s a reliable professional and vastly knowledgeable in data analytics and Business Intelligence.
Looking for a trainer/coach in Advanced Excel or BI in Microsoft technologies? I’ll not hesitate to recommend Crispo for your projects!
I have worked with CrispExcel for over 8 years in Learning and Development…they are amazing at their job!
Crispo, as the lead, is a people person, he is patient with teams, he has the qualities of a trainer, and he does whatever it takes to help you and others in the team understand his requirements pertaining to his learning development.
My collaboration with CrispExcel has always been very smooth and his assistance definitely made a huge impact on our success to our company as a learning provider for technology…without a doubt at a service or consultancy level because they know their stuff!
Crispo, a seasoned Excel MVP, brings his wealth of knowledge and experience to the table, transforming what could be a daunting topic into a delightful and engaging learning experience.
His writing & teaching style is clear, concise, and friendly, making complex concepts accessible and enjoyable.
It’s evident that he’s not just an expert but also a passionate educator.
Before my training with CrispExcel, I was a jackleg in data management and analytics.
Within a few weeks of dedicated coaching with Crispo, my Excel skills tremendously improved. Crispo’s willingness to share knowledge made a whole difference in my work.
I did not become an IT pundit, However, I must say I got my current role courtesy of what I learned from him.
