Exercises to Test Your AI Excel Skills

What This Lesson Is About

You've learned about various AI tools that can help with Excel tasks-from generating formulas to cleaning data and creating reports. Now it's time to test your skills with real-world challenges that combine multiple techniques you've covered. This lesson gives you progressively challenging exercises that simulate actual workplace scenarios, showing you the difference between basic AI use and expert-level AI-assisted Excel work.

Each exercise in this lesson presents a complex problem that someone faces in their job or personal project. You'll see how beginners might struggle with their AI requests, and then learn how experienced users craft prompts and use AI tools to get exactly what they need. By working through these exercises, you'll develop the judgment to know when and how to use AI effectively in Excel.

Exercise 1: Building a Dynamic Sales Dashboard for a Small Retail Business

The Real Task

Maria runs a small electronics store and has been tracking sales in Excel for six months. She has a spreadsheet with columns for Date, Product Name, Category, Quantity Sold, Unit Price, and Salesperson. She needs to create a dashboard that automatically shows total revenue by category, top-performing products, and sales performance by employee. The dashboard should update whenever she adds new sales data.

The Weak Approach

Maria opens ChatGPT and types: "Help me make a sales dashboard in Excel."

The AI gives her generic advice about pivot tables and charts but doesn't address her specific data structure. She tries again: "Give me formulas for my sales data." The AI provides sample formulas using column names like A, B, C, which don't match her actual layout. Maria spends an hour trying to adapt these generic solutions, making mistakes with cell references and getting error messages she doesn't understand.

The Correct AI-Powered Approach

Maria uses a structured approach with her AI tool:

1. First prompt: "I have sales data in Excel with these columns in row 1: Date (A), Product Name (B), Category (C), Quantity Sold (D), Unit Price (E), Salesperson (F). Data starts in row 2 and goes to row 500. I need formulas to calculate: 1) Total revenue by category, 2) Top 5 products by revenue, 3) Total sales by each salesperson. Please give me the exact formulas with cell references."
2. The AI provides specific formulas using her actual column structure
3. Second prompt: "Now help me create a dashboard layout. I want the summary statistics in cells H2:J10. Give me the exact formulas for each cell, including what label to put in column G."
4. The AI creates a complete cell-by-cell layout
5. Third prompt: "I need a formula in cell H15 that shows the category with the highest sales. Use the data from my previous formulas."

Complete working solution the AI provided:

• Cell G2: "Total Revenue" | Cell H2: =SUM(D2:D500*E2:E500)
• Cell G4: "Electronics Revenue" | Cell H4: =SUMIF(C2:C500,"Electronics",D2:D500*E2:E500)
• Cell G5: "Accessories Revenue" | Cell H5: =SUMIF(C2:C500,"Accessories",D2:D500*E2:E500)
• Cell G7: "Top Product" | Cell H7: =INDEX(B2:B500,MATCH(MAX(D2:D500*E2:E500),D2:D500*E2:E500,0))
• Cell G8: "Top Product Revenue" | Cell H8: =MAX(D2:D500*E2:E500)

For the salesperson analysis, Maria then asked: "Create a formula that lists each unique salesperson and their total sales. I want this starting in cell G12." The AI provided a solution using UNIQUE and SUMIF functions that automatically updates when new salespeople are added.

What Made the Difference

• Specific data structure: Maria told the AI exactly where her data was and what each column contained
• Exact cell references: She specified where she wanted results to appear
• Breaking down the problem: Instead of asking for everything at once, she built the dashboard step by step
• Building on previous answers: Each prompt referenced the work already done
• Named her categories: Giving actual category names ("Electronics", "Accessories") meant formulas worked immediately

Exercise 2: Creating a Patient Appointment Scheduler with Conflict Detection

The Real Task

Dr. Patel runs a dental clinic with three dentists. She needs an Excel system where her receptionist can enter appointment details, and the spreadsheet automatically flags scheduling conflicts (when two patients are booked with the same dentist at overlapping times). The spreadsheet has columns for Patient Name, Dentist, Appointment Date, Start Time, Duration (in minutes), and she needs a "Conflict Alert" column that shows "OK" or "CONFLICT".

The Weak Approach

The receptionist asks an AI chatbot: "Excel formula to check for time conflicts."

The AI gives a simple formula that checks if two times are the same, but doesn't account for appointment duration or the fact that a 2:00 PM appointment lasting 30 minutes conflicts with a 2:15 PM appointment. The receptionist tries: "Formula to check if appointments overlap." The AI provides a complex array formula without explaining how to adapt it to multiple rows or specific dentists. After several failed attempts, the receptionist still has a formula that flags false conflicts between different dentists.

The Correct AI-Powered Approach

Dr. Patel uses a systematic prompting strategy:

1. First prompt: "I have appointment data in Excel. Column A: Patient Name, Column B: Dentist, Column C: Appointment Date, Column D: Start Time (formatted as time), Column E: Duration in minutes. Data starts in row 2. I need a formula in column F that checks if this appointment conflicts with any other appointment for the SAME dentist on the SAME date. A conflict means the time ranges overlap. The formula should show 'CONFLICT' if there's an overlap with another row, or 'OK' if there isn't. Give me the formula for cell F2."
2. The AI provides a formula but it's complex. Dr. Patel asks a clarifying question.
3. Second prompt: "That formula is hard to understand. Can you break it into helper columns first? Put the appointment end time in column G, then create the conflict check in column H."
4. The AI now provides simpler, step-by-step formulas
5. Third prompt: "The formula flags the appointment as conflicting with itself. How do I exclude the current row from the check?"

Final working solution:

• Cell G2 (End Time): =D2+TIME(0,E2,0)
• Cell F2 (Conflict Check): =IF(SUMPRODUCT(($B$2:$B$100=B2)*($C$2:$C$100=C2)*(ROW($B$2:$B$100)<>ROW(B2))*(($D$2:$D$100<G2)*($D$2:$D$100+TIME(0,$E$2:$E$100,0)>D2)))>0,"CONFLICT","OK")

Dr. Patel then asked: "Explain what each part of this formula does so my receptionist can understand it." The AI broke down the formula into understandable pieces:

• $B$2:$B$100=B2: Finds appointments with the same dentist
• $C$2:$C$100=C2: On the same date
• ROW($B$2:$B$100)<>ROW(B2): Excludes the current row
• Time overlap logic: Checks if any appointment starts before this one ends AND ends after this one starts
• SUMPRODUCT and IF: Counts conflicts and displays the appropriate message

What Made the Difference

• Described the business logic: Explained what constitutes a conflict in her specific situation
• Specified same dentist and same date requirements: Made the AI understand that appointments with different dentists don't conflict
• Asked for simplification: When the first solution was too complex, she requested helper columns
• Caught the self-comparison issue: Noticed the logical flaw and asked for a fix
• Requested an explanation: Ensured her staff could maintain and understand the solution

Exercise 3: Building a Student Grade Tracker with Custom Weighted Curves

The Real Task

Professor Johnson teaches a university course and needs an Excel grade book that handles complex grading rules. She has 40 students with scores for 5 assignments (each worth 10%), 2 midterms (each worth 15%), and 1 final exam (worth 30%). However, she wants to implement a conditional curve: if a student's final exam score is higher than their midterm average, the final should count for 40% and the midterms should each count for 12.5%. She also needs to assign letter grades based on the final percentage using a specific scale.

The Weak Approach

Professor Johnson tries: "Formula to calculate weighted grades in Excel."

The AI gives her a basic weighted average formula: =SUMPRODUCT(scores,weights)/SUM(weights). She tries to adapt this but can't figure out how to make the weights change conditionally based on the final exam performance. She asks: "How to make weights change?" The AI suggests IF statements, but doesn't show how to integrate them into a complete grading formula. After creating several broken formulas with mismatched parentheses and circular logic, she's stuck.

The Correct AI-Powered Approach

Professor Johnson breaks the problem into clear components:

1. First prompt: "I need to build a grade calculator in Excel. Here's my layout: Column B-F: Assignment scores (5 assignments), Column G-H: Midterm scores (2 midterms), Column I: Final exam score. All scores are out of 100. I need a formula that calculates the weighted average using these rules: Compare the final exam score (column I) to the average of the two midterms (columns G and H). If final exam score is HIGHER than midterm average: each assignment counts 10%, each midterm counts 12.5%, final counts 40%. If final exam score is LOWER or EQUAL to midterm average: each assignment counts 10%, each midterm counts 15%, final counts 30%. Put this formula in column J row 2."
2. The AI provides the complete conditional formula
3. Second prompt: "Now I need column K to show letter grades based on column J using this scale: 90-100 is A, 80-89 is B, 70-79 is C, 60-69 is D, below 60 is F. Give me the formula for K2."
4. Third prompt: "I want cell M1 to show the class average, M2 to show the highest grade, and M3 to show the lowest grade from column J. I have 40 students in rows 2-41."

Complete working solution:

Cell J2 (Weighted Grade):

=IF(I2>AVERAGE(G2:H2), (SUM(B2:F2)/5)*0.1*5 + G2*0.125 + H2*0.125 + I2*0.4, (SUM(B2:F2)/5)*0.1*5 + G2*0.15 + H2*0.15 + I2*0.3)

Cell K2 (Letter Grade):

=IF(J2>=90,"A",IF(J2>=80,"B",IF(J2>=70,"C",IF(J2>=60,"D","F"))))

Summary Statistics:

• Cell M1: =AVERAGE(J2:J41)
• Cell M2: =MAX(J2:J41)
• Cell M3: =MIN(J2:J41)

Professor Johnson then asked: "Can you add a column that shows how many percentage points each student is above or below the class average?" The AI provided: Cell L2: =J2-$M$1, with the $ signs ensuring the class average reference stays fixed when copying down.

What Made the Difference

• Specified exact column locations: Told the AI where each type of score was stored
• Clearly stated the conditional logic: Explained exactly when and how the weights should change
• Separated the grading components: Asked for weighted average calculation separate from letter grade assignment
• Provided the complete grading scale: Left no ambiguity about grade boundaries
• Built incrementally: Added features one at a time rather than requesting everything at once
• Used follow-up questions: Extended the solution with additional analyses after the core formula worked

Practice Tasks

Now it's your turn to apply what you've learned. For each task below, plan how you would use AI tools to solve the problem. Think about what information you need to provide, how to break down the problem, and what follow-up questions you might need.

Practice Task 1: Restaurant Inventory Alert System

You manage a small restaurant and keep inventory in Excel. You have columns for: Ingredient Name, Current Stock (in units), Minimum Required Stock, Unit Cost, and Supplier Name. You need to create a system that automatically highlights ingredients that are below the minimum required stock and calculates the total cost to reorder them back to the minimum level. Additionally, you want a summary section that shows total reorder cost by supplier and counts how many items need reordering from each supplier.

Plan your AI prompts to build this system. Consider: What specific cell locations will you use? How will you break this into steps? What helper columns might you need? How will you handle the conditional highlighting and calculations?

Practice Task 2: Freelancer Time Tracking and Invoice Generator

You're a freelance graphic designer working with multiple clients on different projects. Your Excel sheet tracks: Client Name, Project Name, Date, Hours Worked, Hourly Rate, and Expenses. You need to create an automated invoice section where you can select a client name and a date range, and Excel automatically calculates: total hours worked for that client in that period, total labor cost, total expenses, subtotal, tax (8%), and final amount due. The invoice section should also list all project names worked on during that period for that client.

Design your approach using AI tools. Think about: How will you allow date range selection? What formulas will filter data by client and date? How will you create a unique list of projects? What's the most logical order to build these components?

Practice Task 3: School Parent-Teacher Conference Scheduler

You're organizing parent-teacher conferences for a school with 8 teachers. Each conference slot is 15 minutes. Teachers are available from 3:00 PM to 7:00 PM on conference day. You have a list of students with their homeroom teacher, but parents need to schedule with multiple teachers (not just their homeroom teacher). You need an Excel system where: you can enter a parent's name, student name, desired teacher, and preferred time slot; the system checks if that teacher is available at that time; the system prevents double-booking teachers; and you can see each teacher's complete schedule for the day in a summary view.

Map out your AI-assisted solution strategy. Consider: How will you structure the time slots? What data validation might help prevent errors? How will you check for conflicts across all bookings? How will you create the teacher schedule summary view?