Module One: Advanced Formulas and Dynamic Arrays – Making Excel Think for You

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome to Level Three, young Excel champion! You have already learned so much. In Level Two, you learned how to clean data, analyse data, create charts, and even automate tasks with Copilot. Now it is time to go deeper.

In this module, we will learn about advanced formulas and dynamic arrays. These are special formulas that can do many things at once. They can filter data, sort data, find unique values, and even create custom functions that work like your own personal Excel tools.

Do not worry if these words sound big. We will explain everything in simple language. By the end of this module, you will be able to write powerful formulas that would impress any Excel expert. Copilot will be right beside you, helping you every step of the way.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Explain what dynamic arrays are.
• Use the FILTER function to show only what you need.
• Use the SORT function to arrange data automatically.
• Use the UNIQUE function to remove duplicates.
• Use the SEQUENCE function to create number lists.
• Master XLOOKUP for powerful lookups.
• Use INDEX and MATCH together.
• Handle errors with IFERROR and IFNA.
• Create custom functions with LAMBDA.
• Use Copilot to write advanced formulas for you.

Warm-up Story: Emeka’s Magic Formula

Emeka runs a small bookshop in Abuja. He sells textbooks, novels, and storybooks. Every day, he records his sales in Excel. His sheet has hundreds of rows with the date, book title, category, and price.

One day, Emeka’s father asked him, “Emeka, can you show me only the novels we sold this week?” Emeka tried to do it manually. He scrolled through the rows and copied the novels into a new sheet. It took him almost an hour, and he made mistakes.

His friend Ada said, “Emeka, use the FILTER function! It can show only the rows you want, automatically.” Emeka asked Copilot how to use FILTER. Copilot explained it in simple steps.

Emeka typed: =FILTER(A2:D100, C2:C100="Novel"). Instantly, Excel showed only the novels! Emeka was amazed. He then asked Copilot about SORT and UNIQUE. Copilot showed him how to sort his data and remove duplicates.

In less than an hour, Emeka had created a magical sheet that filtered, sorted, and cleaned his data automatically. His father was very proud. Emeka learned that advanced formulas can do the work of many hours in just seconds.

Moral of the story: Advanced formulas are like magic. They can filter, sort, and clean your data automatically. Copilot teaches you how to use them.

Main Lessons

Lesson 1: What Are Dynamic Arrays?

Definition: A dynamic array is a formula that can return many answers at once, and the answers spill into neighbouring cells automatically.

Why it is important: Dynamic arrays save time and make formulas simpler.

Simple explanation: Imagine planting one seed, and a whole row of flowers grows. That is what a dynamic array does.

Real-life example: A shop filters all sales above ₦10,000 with one formula.

School example: A teacher lists all students who scored above 80.

Home example: A family lists all items on a shopping list above ₦500.

Nigerian example: A trader lists all customers who owe more than ₦1,000.

Illustration:

  Normal formula:              Dynamic array:
  =A2                         =FILTER(A2:A10, A2:A10>5)
  (one answer)                (many answers, spill down)

  Result:
  A2 -> 10                    -> 10
                              -> 15
                              -> 20
                              (spills into cells below)
  

Mini summary: Dynamic arrays return many answers at once. They spill into nearby cells. Copilot helps you use them.

Lesson 2: The FILTER Function

Definition: FILTER shows only the rows that meet a condition.

Why it is important: FILTER lets you focus on what matters.

Simple explanation: Imagine a sieve that keeps only the big stones and lets the sand fall through.

Real-life example: A bank filters transactions above ₦100,000.

School example: A teacher filters students who scored above 70.

Home example: A family filters shopping items that cost more than ₦1,000.

Nigerian example: A trader filters sales made on Saturday.

Illustration:

  Data:
  +--------+--------+
  | Name   | Score  |
  +--------+--------+
  | Ada    | 85     |
  | Tunde  | 65     |
  | Ngozi  | 90     |
  | Emeka  | 72     |
  +--------+--------+

  Formula: =FILTER(A2:B5, B2:B5>70)

  Result:
  +--------+--------+
  | Ada    | 85     |
  | Ngozi  | 90     |
  | Emeka  | 72     |
  +--------+--------+
  

Step-by-step:

1. Select an empty cell.
2. Type =FILTER(.
3. Choose the range with data (e.g., A2:B5).
4. Type a comma.
5. Choose the condition (e.g., B2:B5>70).
6. Close the bracket and press Enter.

Mini summary: FILTER shows only the rows that meet a condition. Copilot writes it for you.

Lesson 3: The SORT Function

Definition: SORT arranges data in order, like A to Z or smallest to largest.

Why it is important: Sorted data is easier to read and understand.

Simple explanation: Imagine arranging books on a shelf from A to Z.

Real-life example: A bank sorts customers by account balance.

School example: A teacher sorts students by score.

Home example: A family sorts shopping items by price.

Nigerian example: A trader sorts goods by price.

Illustration:

  Before:                    After SORT:
  +--------+--------+        +--------+--------+
  | Ngozi  | 90     |        | Ada    | 85     |
  | Ada    | 85     |        | Emeka  | 72     |
  | Emeka  | 72     |        | Ngozi  | 90     |
  | Tunde  | 65     |        | Tunde  | 65     |
  +--------+--------+        +--------+--------+

  Formula: =SORT(A2:B5, 2, 1)
  

Step-by-step:

1. Select an empty cell.
2. Type =SORT(.
3. Choose the range (e.g., A2:B5).
4. Type the column number to sort by.
5. Type 1 for A to Z, or -1 for Z to A.
6. Close the bracket and press Enter.

Mini summary: SORT arranges data in order. Copilot writes it for you.

Lesson 4: The UNIQUE Function

Definition: UNIQUE shows only the values that appear once, removing duplicates.

Why it is important: UNIQUE helps you see a clean list of items.

Simple explanation: Imagine a list of names with many repeats. UNIQUE shows each name only once.

Real-life example: A bank lists all unique account types.

School example: A teacher lists all unique classes in a school.

Home example: A family lists all unique items on a shopping list.

Nigerian example: A trader lists all unique products sold.

Illustration:

  Before:                    After UNIQUE:
  +--------+                +--------+
  | Ada    |                | Ada    |
  | Tunde  |                | Tunde  |
  | Ada    |                | Ngozi  |
  | Ngozi  |                | Emeka  |
  | Tunde  |                +--------+
  | Emeka  |
  +--------+

  Formula: =UNIQUE(A2:A7)
  

Step-by-step:

1. Select an empty cell.
2. Type =UNIQUE(.
3. Choose the range (e.g., A2:A7).
4. Close the bracket and press Enter.

Mini summary: UNIQUE removes duplicates. Copilot writes it for you.

Lesson 5: The SEQUENCE Function

Definition: SEQUENCE creates a list of numbers in order.

Why it is important: SEQUENCE helps you create numbered lists quickly.

Simple explanation: Imagine writing 1, 2, 3, 4, 5 in one step.

Real-life example: A bank creates account numbers.

School example: A teacher creates student roll numbers.

Home example: A family creates a numbered shopping list.

Nigerian example: A trader creates invoice numbers.

Illustration:

  Formula: =SEQUENCE(5)

  Result:
  1
  2
  3
  4
  5
  

Step-by-step:

1. Select an empty cell.
2. Type =SEQUENCE(.
3. Type the number of rows you want.
4. Close the bracket and press Enter.

Mini summary: SEQUENCE creates numbered lists. Copilot writes it for you.

Lesson 6: XLOOKUP – The Super Lookup

Definition: XLOOKUP searches for a value and returns a matching value from another column.

Why it is important: XLOOKUP is more powerful and easier than VLOOKUP.

Simple explanation: Imagine looking up a word in a dictionary and finding its meaning.

Real-life example: A bank looks up a customer’s name by account number.

School example: A teacher looks up a student’s score by name.

Home example: A family looks up a price by item name.

Nigerian example: A trader looks up a product’s price by product code.

Illustration:

  Data:
  +--------+--------+
  | Name   | Score  |
  +--------+--------+
  | Ada    | 85     |
  | Tunde  | 65     |
  | Ngozi  | 90     |
  +--------+--------+

  Formula: =XLOOKUP("Ngozi", A2:A4, B2:B4)

  Result: 90
  

Step-by-step:

1. Select an empty cell.
2. Type =XLOOKUP(.
3. Type the value to look for.
4. Choose the range to search in.
5. Choose the range to return from.
6. Close the bracket and press Enter.

Mini summary: XLOOKUP finds matching values. Copilot writes it for you.

Lesson 7: INDEX and MATCH

Definition: INDEX returns a value from a position. MATCH finds the position of a value.

Why it is important: INDEX and MATCH work together as a powerful lookup.

Simple explanation: Imagine MATCH finds the row number, and INDEX grabs the value from that row.

Real-life example: A bank uses INDEX/MATCH to find customer details.

School example: A teacher finds a student’s score using INDEX/MATCH.

Home example: A family finds a price using INDEX/MATCH.

Nigerian example: A trader finds a product’s price using INDEX/MATCH.

Illustration:

  Data:
  +--------+--------+
  | Name   | Score  |
  +--------+--------+
  | Ada    | 85     |
  | Tunde  | 65     |
  | Ngozi  | 90     |
  +--------+--------+

  Formula: =INDEX(B2:B4, MATCH("Ngozi", A2:A4, 0))

  Result: 90
  

Step-by-step:

1. Use MATCH to find the row number.
2. Use INDEX to get the value from that row.
3. Combine them: =INDEX(range, MATCH(value, range, 0)).

Mini summary: INDEX and MATCH work together for lookups. Copilot writes them for you.

Lesson 8: Handling Errors with IFERROR and IFNA

Definition: IFERROR shows a friendly message when a formula gives an error. IFNA only handles #N/A errors.

Why it is important: Errors look messy. Friendly messages look professional.

Simple explanation: Imagine a spelling mistake. IFERROR fixes it and shows a nice word instead.

Real-life example: A bank shows “Not Found” instead of #N/A.

School example: A teacher shows “No Score” instead of #N/A.

Home example: A family shows “Unknown” instead of #N/A.

Nigerian example: A trader shows “Not Available” instead of #N/A.

Illustration:

  Without IFERROR:           With IFERROR:
  =XLOOKUP("Ada", ...)       =IFERROR(XLOOKUP("Ada", ...), "Not Found")
  Result: #N/A               Result: "Not Found"
  

Step-by-step:

1. Type your formula.
2. Wrap it with =IFERROR(.
3. Add a comma and your friendly message.
4. Close the bracket.

Mini summary: IFERROR and IFNA show friendly messages instead of errors. Copilot writes them for you.

Lesson 9: LAMBDA – Your Own Custom Function

Definition: LAMBDA lets you create your own custom function without writing code.

Why it is important: LAMBDA lets you reuse formulas easily.

Simple explanation: Imagine creating your own Excel function called “MULTIPLY” that multiplies two numbers.

Real-life example: A bank creates a custom function for interest calculation.

School example: A teacher creates a custom function for grade calculation.

Home example: A family creates a custom function for budget calculation.

Nigerian example: A trader creates a custom function for profit calculation.

Illustration:

  Formula: =LAMBDA(x, y, x * y)(5, 3)

  Result: 15

  In Name Manager, you can save it as MULTIPLY.
  Then use: =MULTIPLY(5, 3) → 15
  

Step-by-step:

1. Type =LAMBDA(.
2. Give names to your inputs (e.g., x, y).
3. Write the calculation (e.g., x * y).
4. Close the bracket.
5. Add inputs in another bracket: (5, 3).

Mini summary: LAMBDA creates custom functions. Copilot writes them for you.

Lesson 10: Combining Dynamic Arrays with Copilot

You can combine dynamic arrays for even more power.

Example 1: FILTER + SORT

=SORT(FILTER(A2:B10, B2:B10>70), 2, -1)

This shows only rows with score above 70, then sorts them from highest to lowest.

Example 2: UNIQUE + SORT

=SORT(UNIQUE(A2:A10))

This shows unique names sorted from A to Z.

Example 3: FILTER + UNIQUE

=UNIQUE(FILTER(A2:B10, B2:B10>70))

This shows unique rows where score is above 70.

Illustration:

  Combine: FILTER + SORT + UNIQUE
       |
       V
  Powerful result!
  

Mini summary: Combining dynamic arrays gives more power. Copilot writes the combinations for you.

Lesson 11: Common Mistakes with Advanced Formulas

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: A small mistake can give wrong results.

Simple explanation: Imagine typing a phone number with one wrong digit. It will not work.

Real-life example: A bank sends money to the wrong account because of a wrong formula.

School example: A teacher gives the wrong grade because of a formula error.

Home example: A family overspends because of a budget formula error.

Nigerian example: A trader loses money because of a pricing formula error.

Table of common mistakes:

Mini summary: Common mistakes include wrong ranges and missing quotes. Always check your formulas.

Lesson 12: Best Practices for Advanced Formulas

Definition: Best practices are good habits that make formulas work well.

Why it is important: Good habits save time and prevent mistakes.

Simple explanation: Like washing your hands before cooking.

Real-life example: A chef keeping the kitchen clean.

School example: A student keeping their notebook neat.

Home example: A family keeping the house tidy.

Nigerian example: A trader keeping the stall clean and organised.

List of best practices:

• Use clear column headings.
• Keep ranges the same size.
• Use XLOOKUP instead of VLOOKUP.
• Wrap formulas in IFERROR.
• Use dynamic arrays to simplify.
• Test formulas with sample data.
• Save your work often.
• Ask Copilot if unsure.
• Use LAMBDA for repeated calculations.
• Document your formulas.

Mini summary: Best practices: clear headings, same-size ranges, error handling, save often.

Lesson 13: Using Copilot to Write Advanced Formulas

Copilot can write advanced formulas for you.

Step 1: Open Copilot in Excel.

Step 2: Describe what you want in plain English.

Step 3: Copilot writes the formula.

Step 4: Review and adjust if needed.

Examples:

• “Show only rows where sales are above 1000.”
• “Sort this list from A to Z.”
• “Remove duplicates from this column.”
• “Create a list of numbers from 1 to 100.”
• “Look up the price for product code X.”

Illustration:

  You: "Show only rows where score is above 70"
       |
       V
  Copilot: =FILTER(A2:B10, B2:B10>70)
       |
       V
  You: Copy the formula into your sheet.
  

Mini summary: Copilot writes advanced formulas from plain English. Just ask.

Lesson 14: Building a Dynamic Sales Dashboard

Let’s use advanced formulas to build a dynamic dashboard.

Step 1: Open your sales data.

Step 2: Use =UNIQUE() to list all products.

Step 3: Use =SUMIF() or =SUMIFS() to find total sales per product.

Step 4: Use =SORT() to sort products by sales.

Step 5: Use =FILTER() to show only top products.

Step 6: Add charts based on the dynamic data.

Step 7: Save as “My_Dynamic_Dashboard”.

Illustration:

  UNIQUE products
       |
       V
  SUMIFS total sales
       |
       V
  SORT highest to lowest
       |
       V
  FILTER top products
       |
       V
  Add charts
       |
       V
  Dynamic Dashboard 🎉
  

Mini summary: Dynamic arrays make dashboards update automatically. Copilot helps at every step.

Lesson 15: Putting It All Together – Your Advanced Formula Toolkit

You now have a powerful toolkit. Let’s review.

FILTER: Show only rows that meet a condition.

SORT: Arrange data in order.

UNIQUE: Remove duplicates.

SEQUENCE: Create number lists.

XLOOKUP: Find matching values.

INDEX/MATCH: Another powerful lookup.

IFERROR/IFNA: Handle errors gracefully.

LAMBDA: Create custom functions.

Combinations: Combine functions for more power.

Illustration:

  Your Toolkit:
  +--------+  +--------+  +--------+
  | FILTER |  | SORT   |  | UNIQUE |
  +--------+  +--------+  +--------+
  +--------+  +--------+  +--------+
  |SEQUENCE|  |XLOOKUP |  | LAMBDA |
  +--------+  +--------+  +--------+
  +--------+  +--------+
  |IFERROR |  | INDEX  |
  +--------+  +--------+
  

Mini summary: You now have a toolkit of advanced formulas. Keep practising and combining them.

Key Vocabulary

Important Concepts

• Dynamic arrays return many answers: They spill into nearby cells.
• FILTER shows only what you need: Focus on important rows.
• SORT arranges data: Makes it easier to read.
• UNIQUE removes duplicates: Shows each value once.
• SEQUENCE creates number lists: Great for numbering.
• XLOOKUP is powerful: Better than VLOOKUP.
• INDEX and MATCH work together: Another way to look up values.
• IFERROR and IFNA handle errors: Show friendly messages.
• LAMBDA creates custom functions: Reuse your formulas.
• Copilot writes advanced formulas: Just ask in plain English.

Step-by-step Explanations

How to use FILTER step by step

1. Select an empty cell.
2. Type =FILTER(.
3. Choose your data range.
4. Type a comma.
5. Choose your condition.
6. Close the bracket and press Enter.

How to use SORT step by step

1. Select an empty cell.
2. Type =SORT(.
3. Choose your data range.
4. Type the column number to sort by.
5. Type 1 for A to Z or -1 for Z to A.
6. Close the bracket and press Enter.

How to use UNIQUE step by step

1. Select an empty cell.
2. Type =UNIQUE(.
3. Choose your data range.
4. Close the bracket and press Enter.

How to use XLOOKUP step by step

1. Select an empty cell.
2. Type =XLOOKUP(.
3. Type the value to look for.
4. Choose the range to search in.
5. Choose the range to return from.
6. Close the bracket and press Enter.

How to use IFERROR step by step

1. Type your formula.
2. Wrap it with =IFERROR(.
3. Add a comma and your friendly message.
4. Close the bracket and press Enter.

Real-life Examples

• Shops: Use FILTER to show top-selling products.
• Schools: Use SORT to rank students by score.
• Homes: Use UNIQUE to list unique shopping items.
• Offices: Use XLOOKUP to find employee details.
• Hospitals: Use IFERROR to handle missing patient data.

Nigerian Examples

• Market traders: Use FILTER to show sales on a specific day.
• POS operators: Use SUMIFS to total daily transactions.
• Schools in Lagos: Use SORT to rank students.
• Transporters: Use XLOOKUP to find fare by route.
• Church groups: Use UNIQUE to list unique donors.

Fun Examples Children Can Relate To

• Video game scores: FILTER to show high scores.
• Football league: SORT teams by points.
• Pocket money: UNIQUE to list spending categories.
• Chores: SEQUENCE to create a numbered list.
• Snack list: XLOOKUP to find snack prices.

Everyday Examples

• Shopping list: FILTER items under ₦500.
• School timetable: SORT subjects by time.
• Exercise log: UNIQUE to list exercise types.
• Reading list: SORT books by title.
• Grocery budget: XLOOKUP to find prices.

Parent Tips

• Encourage your child to try one new function each day.
• Let them practice with everyday data.
• Use examples from home and school.
• Set a small daily practice time.
• Celebrate small wins, like a working FILTER.
• Be patient. Advanced formulas take practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to check formulas carefully.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• Dynamic arrays were introduced in Excel 365.
• XLOOKUP is faster and easier than VLOOKUP.
• LAMBDA lets you create your own functions.
• FILTER can show thousands of rows instantly.
• UNIQUE removes duplicates in one step.
• SEQUENCE can create 1 million numbers.
• Copilot can write advanced formulas for you.
• Advanced formulas save hours of work.

Did You Know?

• Did you know that FILTER can show only rows that match a condition?
• Did you know that SORT can sort by more than one column?
• Did you know that UNIQUE removes duplicates automatically?
• Did you know that SEQUENCE can create 2D arrays?
• Did you know that XLOOKUP can search from bottom to top?
• Did you know that LAMBDA can be saved as a named function?
• Did you know that IFERROR can handle any error type?
• Did you know that Copilot can explain any formula step by step?

Remember This

• Dynamic arrays return many answers at once.
• FILTER shows only rows that meet a condition.
• SORT arranges data in order.
• UNIQUE removes duplicates.
• SEQUENCE creates number lists.
• XLOOKUP finds matching values.
• INDEX and MATCH work together.
• IFERROR and IFNA handle errors.
• LAMBDA creates custom functions.
• Copilot writes advanced formulas for you.

Common Mistakes

• Wrong range size.
• Wrong column number.
• Forgetting quotes.
• Forgetting brackets.
• Using old VLOOKUP instead of XLOOKUP.
• Not handling errors.
• Not testing formulas.
• Not saving work.

Best Practices

• Use clear column headings.
• Keep ranges the same size.
• Use XLOOKUP instead of VLOOKUP.
• Wrap formulas in IFERROR.
• Use dynamic arrays to simplify.
• Test formulas with sample data.
• Save your work often.
• Ask Copilot if unsure.
• Use LAMBDA for repeated calculations.
• Document your formulas.

Illustrations and Diagrams

Dynamic Array Spill

  Formula: =SEQUENCE(5)
       |
       V
  +-------+
  | 1     |
  | 2     |
  | 3     |
  | 4     |
  | 5     |
  +-------+
  (spills into cells below)
  

FILTER Illustration

  All Data:
  Ada 85
  Tunde 65
  Ngozi 90
  Emeka 72

  FILTER score > 70:
  Ada 85
  Ngozi 90
  Emeka 72
  

SORT Illustration

  Before:          After SORT:
  Ngozi 90         Tunde 65
  Ada 85           Emeka 72
  Emeka 72         Ada 85
  Tunde 65         Ngozi 90
  

XLOOKUP Illustration

  Look for "Ngozi"
       |
       V
  Find in column A
       |
       V
  Return value from column B
       |
       V
  Result: 90
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

VLOOKUP vs XLOOKUP

FILTER vs SORT vs UNIQUE

IFERROR vs IFNA

INDEX/MATCH vs XLOOKUP

Lesson Summaries

Lesson 1: Dynamic arrays return many answers at once.

Lesson 2: FILTER shows only rows that meet a condition.

Lesson 3: SORT arranges data in order.

Lesson 4: UNIQUE removes duplicates.

Lesson 5: SEQUENCE creates number lists.

Lesson 6: XLOOKUP finds matching values.

Lesson 7: INDEX and MATCH work together for lookups.

Lesson 8: IFERROR and IFNA handle errors gracefully.

Lesson 9: LAMBDA creates custom functions.

Lesson 10: Combining dynamic arrays gives more power.

Lesson 11: Common mistakes include wrong ranges and quotes.

Lesson 12: Best practices: clear headings, error handling, save often.

Lesson 13: Copilot writes advanced formulas from plain English.

Lesson 14: Build a dynamic sales dashboard with advanced formulas.

Lesson 15: You now have a toolkit of advanced formulas.

End-of-Module Summary

Congratulations! You have finished Module One of Copilot in Microsoft Excel – Level Three. You learned what dynamic arrays are. You learned how to use FILTER, SORT, UNIQUE, and SEQUENCE. You learned how to use XLOOKUP, INDEX and MATCH, and how to handle errors with IFERROR and IFNA. You learned how to create custom functions with LAMBDA. You learned how to combine dynamic arrays for more power. You learned common mistakes and best practices. Most importantly, you learned how Copilot can write advanced formulas for you. Keep practising, and you will become an Excel master!

Frequently Asked Questions

1. What is a dynamic array? A formula that returns many answers at once.
2. How do I use FILTER? Type =FILTER(range, condition).
3. How do I use SORT? Type =SORT(range, column, order).
4. How do I use UNIQUE? Type =UNIQUE(range).
5. How do I use SEQUENCE? Type =SEQUENCE(rows).
6. What is XLOOKUP? A powerful lookup function.
7. What is INDEX/MATCH? A combination of two functions for lookups.
8. What is IFERROR? A function that handles errors.
9. What is LAMBDA? A function that creates custom functions.
10. How does Copilot help? It writes advanced formulas from plain English.

Matching Exercises

Match the function to what it does.

Answers: 1-B, 2-C, 3-A, 4-E, 5-D

Scenario-based Exercises

1. Scenario: You want to show only sales above ₦1000. What do you use?
   Answer: FILTER.
2. Scenario: You want to arrange scores from highest to lowest. What do you use?
   Answer: SORT.
3. Scenario: You want to remove duplicate names. What do you use?
   Answer: UNIQUE.
4. Scenario: You want to create a numbered list from 1 to 100. What do you use?
   Answer: SEQUENCE.
5. Scenario: You want to find a customer’s name by account number. What do you use?
   Answer: XLOOKUP.

Group Activity

Title: “Build a Dynamic Sales Report Together”

Instructions: In groups of 3–4, create a sales sheet in Excel with at least 20 rows. Use FILTER, SORT, and UNIQUE to create a dynamic report. One person types, one person asks Copilot, one person checks, and one person presents. Share your report with the class.

Goal: Practice using dynamic arrays with Copilot.

Individual Activity

Task: Create a small Excel sheet with ten rows of data (e.g., names and scores). Use Copilot to:

• Filter rows with scores above 70.
• Sort the list from highest to lowest.
• Show unique names.
• Create a numbered list with SEQUENCE.

Hint: Start with a clean table with headings.

Mini Project

Project: “My Dynamic Sales Report”

Create an Excel sheet with at least 20 sales records. Use Copilot to:

• Filter sales above ₦500.
• Sort sales from highest to lowest.
• List unique products.
• Look up product prices with XLOOKUP.
• Handle errors with IFERROR.

Example output:

  +----------------------------------------+
  |         DYNAMIC SALES REPORT           |
  |  Filtered Sales > ₦500                 |
  |  Sorted Highest to Lowest              |
  |  Unique Products                       |
  |  XLOOKUP Prices                        |
  +----------------------------------------+
  

Practical Assignment

Assignment: Create a new Excel workbook called Advanced_Formulas. In the workbook, do the following:

1. Create a sheet called “Sales” with at least 20 rows of data.
2. Use FILTER to show sales above ₦500.
3. Use SORT to arrange data.
4. Use UNIQUE to list unique products.
5. Use XLOOKUP to find product prices.
6. Use IFERROR to handle errors.
7. Use LAMBDA to create a custom function.
8. Save the workbook.
9. Write a short explanation of each formula.
10. Submit your workbook and screenshots.

Submit: Your workbook file and screenshots of your formulas.

Key Takeaways

• Dynamic arrays return many answers at once.
• FILTER shows only rows that meet a condition.
• SORT arranges data in order.
• UNIQUE removes duplicates.
• SEQUENCE creates number lists.
• XLOOKUP finds matching values.
• INDEX and MATCH work together.
• IFERROR and IFNA handle errors.
• LAMBDA creates custom functions.
• Copilot writes advanced formulas for you.

Classroom Discussion Questions

1. What is a dynamic array?
2. When would you use FILTER?
3. Why is XLOOKUP better than VLOOKUP?
4. What is the difference between SORT and UNIQUE?
5. How can SEQUENCE help you?
6. Why should you handle errors?
7. What is LAMBDA and why is it useful?
8. How can combining functions be powerful?
9. Why should you test formulas?
10. What did Emeka learn from his magic formula?

Preparation for Module Two

In Module Two, we will learn about Power Query and data transformation. We will cover:

• What Power Query is.
• Connecting to external data sources.
• Automated data cleaning with Copilot.
• Merging and appending queries.
• Refreshing data automatically.

To prepare, make sure you have completed the practical assignment and have a clean data sheet ready. Review your notes on advanced formulas. Bring your curiosity!

See you in Module Two!

End of Module One – Copilot in Microsoft Excel – Level Three

Module Three: Power Pivot and Advanced Data Modelling – Connecting Your Data Like a Pro

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome back, young Excel champion! In Module One, you learned advanced formulas. In Module Two, you learned Power Query and how to clean data automatically. Now it is time to learn something even more powerful: Power Pivot and advanced data modelling.

Power Pivot is a tool inside Excel that lets you connect many tables together and create powerful calculations. Instead of putting all your data in one big table, you can keep separate tables and connect them with relationships. This is how professional data analysts work.

Think of Power Pivot as a bridge builder. You have different islands of data – customers, products, sales, and dates. Power Pivot builds bridges between them so you can see the whole picture. You can then create special calculations called DAX measures that give you powerful insights.

By the end of this module, you will be able to create relationships, write DAX measures, use time intelligence, and build KPIs. Copilot will be right beside you, helping you every step of the way.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Explain what Power Pivot is.
• Enable Power Pivot in Excel.
• Create table relationships.
• Understand the data model.
• Write basic DAX measures.
• Use CALCULATE to change context.
• Use time intelligence functions like TOTALYTD.
• Build KPIs with Copilot.
• Create a complete data model.
• Complete a mini project and practical assignment.

Warm-up Story: Ada’s Data Bridge

Ada runs a small supermarket in Lagos. She sells many products: rice, beans, oil, soap, and drinks. She has three separate Excel sheets: one for products, one for customers, and one for sales.

Ada wanted to know which customers bought the most rice, and which month had the highest sales. But her data was in three separate sheets. She tried to copy everything into one big sheet, but it became messy and slow.

Her cousin Emeka said, “Ada, use Power Pivot! It can connect your three sheets together with relationships. Then you can ask any question you want.”

Ada enabled Power Pivot in Excel. She created a relationship between the ProductID in the Sales sheet and the ProductID in the Products sheet. She created another relationship between the CustomerID in the Sales sheet and the Customers sheet.

Then she wrote a DAX measure: Total Sales = SUM(Sales[Amount]). She used this measure in a PivotTable and instantly saw her total sales. She created another measure: Sales YTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date]). Now she could see her year-to-date sales.

Ada was amazed. She had built a professional data model. Her father said, “You are a true data expert!” Ada smiled. She learned that Power Pivot connects data like building bridges.

Moral of the story: Power Pivot connects tables with relationships. DAX measures give powerful calculations. Copilot helps you build them.

Main Lessons

Lesson 1: What is Power Pivot?

Definition: Power Pivot is a tool in Excel that lets you connect many tables together and create powerful calculations.

Why it is important: Power Pivot lets you work with large amounts of data from different sources.

Simple explanation: Imagine building a bridge between two islands. Power Pivot builds bridges between your tables.

Real-life example: A bank connects customer data with transaction data.

School example: A teacher connects students with their scores.

Home example: A family connects shopping items with prices.

Nigerian example: A trader connects products with sales.

Illustration:

  Products Table      Sales Table      Customers Table
       |                  |                  |
       +------------------+------------------+
                          |
                          V
                    Power Pivot
                          |
                          V
                   Data Model 🎉
  

Mini summary: Power Pivot connects tables. It lets you create powerful calculations. Copilot helps you build it.

Lesson 2: Enabling Power Pivot in Excel

Definition: Enabling Power Pivot means turning it on in Excel so you can use it.

Why it is important: Power Pivot is not always visible by default. You need to enable it.

Simple explanation: Like turning on a light switch in a dark room.

Real-life example: A bank enables Power Pivot to work with large data.

School example: A teacher enables Power Pivot for student data.

Home example: A family enables Power Pivot for budget data.

Nigerian example: A trader enables Power Pivot for sales data.

Illustration:

  File → Options → Add-ins
       |
       V
  COM Add-ins → Go
       |
       V
  Check "Microsoft Power Pivot for Excel"
       |
       V
  Click OK
  

Step-by-step:

1. Click “File.”
2. Click “Options.”
3. Click “Add-ins.”
4. In the Manage box, choose “COM Add-ins.”
5. Click “Go.”
6. Check “Microsoft Power Pivot for Excel.”
7. Click “OK.”
8. The Power Pivot tab appears in Excel.

Mini summary: Enable Power Pivot from File > Options > Add-ins. Then the Power Pivot tab appears.

Lesson 3: Adding Tables to the Data Model

Definition: The data model is where Power Pivot stores all your connected tables.

Why it is important: Tables must be in the data model before you can create relationships.

Simple explanation: Like putting all your ingredients on the kitchen counter before cooking.

Real-life example: A bank adds customers, accounts, and transactions to the data model.

School example: A teacher adds students, subjects, and scores.

Home example: A family adds items, prices, and shops.

Nigerian example: A trader adds products, customers, and sales.

Illustration:

  Data Model:
  +----------------+
  | Products       |
  +----------------+
  | Customers      |
  +----------------+
  | Sales          |
  +----------------+
  | Dates          |
  +----------------+
  

Step-by-step:

1. Click the “Power Pivot” tab.
2. Click “Manage.”
3. Click “Add to Data Model” for each table.
4. Or use “Get Data” and check “Add to Data Model.”

Mini summary: Add your tables to the data model so Power Pivot can use them.

Lesson 4: Creating Table Relationships

Definition: A relationship is a connection between two tables based on a shared column.

Why it is important: Relationships let you combine data from different tables.

Simple explanation: Like a friendship between two people based on a shared interest.

Real-life example: A bank connects customers to their accounts with CustomerID.

School example: A teacher connects students to scores with StudentID.

Home example: A family connects items to prices with ItemID.

Nigerian example: A trader connects products to sales with ProductID.

Illustration:

  Products Table:          Sales Table:
  ProductID | Name         SaleID | ProductID | Amount
  1         | Rice         1      | 1         | 500
  2         | Beans        2      | 2         | 300
                           3      | 1         | 400

  Relationship: Products[ProductID] → Sales[ProductID]
  

Step-by-step:

1. Click “Power Pivot” tab.
2. Click “Manage.”
3. Click “Diagram View.”
4. Drag the shared column from one table to the other.
5. A relationship is created.

Mini summary: Relationships connect tables by a shared column. Copilot can show you how.

Lesson 5: Understanding the Data Model

Definition: The data model is the collection of all your connected tables.

Why it is important: The data model is the foundation of all your calculations.

Simple explanation: Like a map of all your connected towns.

Real-life example: A bank’s data model connects customers, accounts, and transactions.

School example: A teacher’s data model connects students, subjects, and scores.

Home example: A family’s data model connects items, prices, and shops.

Nigerian example: A trader’s data model connects products, customers, and sales.

Illustration:

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+
  

Mini summary: The data model is your connected tables. It powers all your analysis.

Lesson 6: Introduction to DAX Measures

Definition: DAX stands for Data Analysis Expressions. A DAX measure is a special calculation that works on your data model.

Why it is important: DAX measures give you powerful ways to calculate and analyse data.

Simple explanation: Like a magic formula that knows how to work across many tables.

Real-life example: A bank creates a measure for total deposits.

School example: A teacher creates a measure for average score.

Home example: A family creates a measure for total spending.

Nigerian example: A trader creates a measure for total sales.

Illustration:

  DAX Measure:
  Total Sales = SUM(Sales[Amount])

  Total Sales:
  500 + 300 + 400 = 1200
  

Step-by-step:

1. Click “Power Pivot” tab.
2. Click “Measures” > “New Measure.”
3. Type a name (e.g., Total Sales).
4. Type the DAX formula.
5. Click “OK.”

Mini summary: DAX measures are powerful calculations. Copilot writes them for you.

Lesson 7: Common DAX Functions

Here are some common DAX functions you will use.

SUM: Adds numbers.

Total Sales = SUM(Sales[Amount])

AVERAGE: Finds the average.

Average Sale = AVERAGE(Sales[Amount])

COUNT: Counts rows.

Number of Sales = COUNT(Sales[SaleID])

DISTINCTCOUNT: Counts unique values.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Illustration:

  Common DAX:
  +-----------------+---------------------------+
  | Function        | Example                   |
  +-----------------+---------------------------+
  | SUM             | SUM(Sales[Amount])        |
  | AVERAGE         | AVERAGE(Sales[Amount])    |
  | COUNT           | COUNT(Sales[SaleID])      |
  | DISTINCTCOUNT   | DISTINCTCOUNT(CustomerID) |
  +-----------------+---------------------------+
  

Mini summary: Common DAX functions include SUM, AVERAGE, COUNT, and DISTINCTCOUNT. Copilot writes them.

Lesson 8: Using CALCULATE to Change Context

Definition: CALCULATE changes the filter context of a measure.

Why it is important: CALCULATE lets you create measures for specific situations.

Simple explanation: Like asking a question in a specific way: “Sales for just this month” instead of “Sales overall.”

Real-life example: A bank calculates total deposits for a specific branch.

School example: A teacher calculates the average score for a specific class.

Home example: A family calculates spending for a specific month.

Nigerian example: A trader calculates sales for a specific product.

Illustration:

  Total Sales = SUM(Sales[Amount])

  Sales for Lagos = CALCULATE(
      [Total Sales],
      Customers[City] = "Lagos"
  )

  Result: Only sales where city is Lagos.
  

Step-by-step:

1. Create a base measure like Total Sales.
2. Create a new measure using CALCULATE.
3. Add a filter condition.
4. Test the result.

Mini summary: CALCULATE changes the context of a measure. Copilot writes it for you.

Lesson 9: Time Intelligence Functions

Definition: Time intelligence functions let you calculate things like year-to-date, month-to-date, or previous year.

Why it is important: Time intelligence helps you compare periods.

Simple explanation: Like asking, “How much have I earned so far this year?”

Real-life example: A bank calculates year-to-date profits.

School example: A teacher calculates term-to-date average scores.

Home example: A family calculates month-to-date spending.

Nigerian example: A trader calculates year-to-date sales.

Illustration:

  Total Sales = SUM(Sales[Amount])

  Sales YTD = TOTALYTD(
      [Total Sales],
      Dates[Date]
  )

  Sales MTD = TOTALMTD(
      [Total Sales],
      Dates[Date]
  )
  

Step-by-step:

1. Create a Dates table with all dates.
2. Link the Dates table to your Sales table.
3. Create a measure using TOTALYTD or TOTALMTD.
4. Use the measure in a PivotTable.

Mini summary: Time intelligence shows year-to-date, month-to-date, and more. Copilot writes them.

Lesson 10: Building KPIs

Definition: A KPI is a Key Performance Indicator. It is a number that shows how well you are doing.

Why it is important: KPIs help you track progress and make decisions.

Simple explanation: Like a score in a game. It tells you if you are winning or losing.

Real-life example: A bank tracks profit margin as a KPI.

School example: A teacher tracks average score as a KPI.

Home example: A family tracks monthly spending as a KPI.

Nigerian example: A trader tracks daily sales as a KPI.

Illustration:

  KPI: Total Sales
  Target: ₦500,000
  Actual: ₦450,000
  Status: Below Target (Red)
  

Step-by-step:

1. Create your DAX measure.
2. Ask Copilot: “Create a KPI for this measure.”
3. Set a target value.
4. Choose status colours (green, yellow, red).
5. Add the KPI to your dashboard.

Mini summary: KPIs track your progress. Copilot helps you build them.

Lesson 11: Using Copilot with Power Pivot

Copilot can help you build Power Pivot models and DAX measures.

Step 1: Open Copilot in Excel.

Step 2: Describe what you want in plain English.

Step 3: Copilot explains the steps.

Step 4: Follow the steps to build your model.

Examples:

• “How do I create a relationship between two tables?”
• “Write a DAX measure for total sales.”
• “How do I calculate year-to-date sales?”
• “Create a KPI for profit margin.”
• “How do I use CALCULATE?”

Illustration:

  You: "Write a DAX measure for total sales"
       |
       V
  Copilot: Total Sales = SUM(Sales[Amount])
       |
       V
  You: Copy the measure into Power Pivot.
  

Mini summary: Copilot explains Power Pivot and writes DAX measures. Just ask in plain English.

Lesson 12: Common Mistakes with Power Pivot

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: A small mistake can break your data model.

Simple explanation: Like building a bridge with weak parts. It might collapse.

Real-life example: A bank creates the wrong relationship and gets wrong totals.

School example: A teacher creates the wrong relationship and gets wrong averages.

Home example: A family creates the wrong relationship and gets wrong totals.

Nigerian example: A trader creates the wrong relationship and gets wrong sales.

Table of common mistakes:

Mini summary: Common mistakes include wrong relationships and formulas. Always check your work.

Lesson 13: Best Practices for Power Pivot

Definition: Best practices are good habits that make Power Pivot work well.

Why it is important: Good habits save time and prevent mistakes.

Simple explanation: Like keeping your room tidy. It is easier to find things.

Real-life example: A bank keeps a clean data model.

School example: A teacher uses clear table names.

Home example: A family uses clear column names.

Nigerian example: A trader uses consistent product codes.

List of best practices:

• Use clear table names.
• Use consistent column names.
• Create a Dates table for time intelligence.
• Test relationships with sample data.
• Write DAX measures with clear names.
• Use CALCULATE for specific filters.
• Build KPIs for key numbers.
• Save your work often.
• Ask Copilot if unsure.
• Document your model.

Mini summary: Best practices: clear names, Dates table, test, save often.

Lesson 14: Building a Complete Data Model

Let’s build a complete data model.

Step 1: Create tables: Products, Customers, Sales, Dates.

Step 2: Add them to the data model.

Step 3: Create relationships: Products→Sales, Customers→Sales, Dates→Sales.

Step 4: Create DAX measures: Total Sales, Average Sale, Unique Customers.

Step 5: Create time intelligence: Sales YTD, Sales MTD.

Step 6: Build KPIs: Total Sales vs Target.

Step 7: Create a PivotTable using your measures.

Step 8: Save as “My_Data_Model”.

Illustration:

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+

  DAX: Total Sales, Sales YTD, KPIs
  

Mini summary: A complete data model connects tables and creates powerful measures. Copilot helps.

Lesson 15: Putting It All Together – Your Data Model Toolkit

You now have a powerful toolkit. Let’s review.

Power Pivot: Connect tables with relationships.

Data Model: The collection of connected tables.

DAX Measures: Powerful calculations.

CALCULATE: Change the context of a measure.

Time Intelligence: Year-to-date, month-to-date, and more.

KPIs: Track performance against targets.

Copilot: Your helper for every step.

Illustration:

  Your Toolkit:
  +----------+  +----------+  +----------+
  | Power    |  | DAX      |  | CALCULATE|
  | Pivot    |  | Measures |  |          |
  +----------+  +----------+  +----------+
  +----------+  +----------+  +----------+
  | Time     |  | KPIs     |  | Copilot  |
  | Intel    |  |          |  |          |
  +----------+  +----------+  +----------+
  

Mini summary: You now have a toolkit of Power Pivot skills. Keep practising and combining them.

Key Vocabulary

Important Concepts

• Power Pivot connects tables: Relationships build bridges between data.
• Enable Power Pivot: Add-in must be turned on.
• The data model is the foundation: All tables live there.
• Relationships connect tables: Shared columns link them.
• DAX measures are powerful: They calculate across tables.
• CALCULATE changes context: Filter measures for specific situations.
• Time intelligence shows periods: YTD, MTD, and more.
• KPIs track performance: Compare actual vs target.
• Copilot writes DAX: Just ask in plain English.
• Test everything: Always verify your results.

Step-by-step Explanations

How to enable Power Pivot step by step

1. Click File > Options.
2. Click Add-ins.
3. Choose COM Add-ins.
4. Click Go.
5. Check Microsoft Power Pivot for Excel.
6. Click OK.

How to create a relationship step by step

1. Open Power Pivot.
2. Click Diagram View.
3. Drag the shared column from one table to the other.
4. The relationship is created.

How to create a DAX measure step by step

1. Click Power Pivot > Measures > New Measure.
2. Type a name.
3. Type the DAX formula.
4. Click OK.

How to use CALCULATE step by step

1. Create a base measure.
2. Create a new measure using CALCULATE.
3. Add a filter condition.
4. Test the result.

How to create a KPI step by step

1. Create a DAX measure.
2. Ask Copilot: “Create a KPI.”
3. Set a target.
4. Choose status colours.
5. Add the KPI to your dashboard.

Real-life Examples

• Shops: Connect products, customers, and sales.
• Schools: Connect students, subjects, and scores.
• Homes: Connect items, prices, and shops.
• Offices: Connect employees, departments, and salaries.
• Hospitals: Connect patients, doctors, and treatments.

Nigerian Examples

• Market traders: Connect products, customers, and sales.
• POS operators: Connect transactions, agents, and locations.
• Schools in Lagos: Connect students, classes, and scores.
• Transporters: Connect routes, vehicles, and fares.
• Church groups: Connect members, donations, and events.

Fun Examples Children Can Relate To

• Video game scores: Connect players, games, and scores.
• Football league: Connect teams, matches, and points.
• Pocket money: Connect days, items, and amounts.
• Chores: Connect people, chores, and time.
• Snack list: Connect snacks, prices, and shops.

Everyday Examples

• Shopping list: Connect items, prices, and shops.
• School timetable: Connect subjects, teachers, and times.
• Exercise log: Connect days, exercises, and minutes.
• Reading list: Connect books, authors, and pages.
• Grocery budget: Connect months, items, and spending.

Parent Tips

• Encourage your child to build simple data models.
• Let them practice with real data like shopping receipts.
• Use everyday examples to explain relationships.
• Set a small daily practice time.
• Celebrate small wins, like a working measure.
• Be patient. Power Pivot takes practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to test their measures.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• Power Pivot was first released in 2010.
• It can handle millions of rows.
• DAX stands for Data Analysis Expressions.
• CALCULATE is one of the most powerful DAX functions.
• Time intelligence is used in every industry.
• KPIs help businesses track performance.
• Copilot can write DAX measures for you.
• Power Pivot is used by data analysts worldwide.

Did You Know?

• Did you know that Power Pivot can connect many tables at once?
• Did you know that relationships work like VLOOKUP but faster?
• Did you know that DAX measures can reference other measures?
• Did you know that CALCULATE can add multiple filters?
• Did you know that time intelligence needs a Dates table?
• Did you know that KPIs can change colour based on performance?
• Did you know that Copilot can explain any DAX function?
• Did you know that Power Pivot is used in Power BI too?

Remember This

• Power Pivot connects tables with relationships.
• Enable Power Pivot from Add-ins.
• Add tables to the data model.
• Relationships connect tables by shared columns.
• DAX measures are powerful calculations.
• CALCULATE changes the context.
• Time intelligence shows YTD, MTD, and more.
• KPIs track performance.
• Copilot writes DAX for you.
• Always test your measures.

Common Mistakes

• Wrong column for relationship.
• Duplicate values in relationship column.
• Wrong DAX formula.
• Missing Dates table.
• Forgetting to save.
• Not testing measures.
• Not using clear names.
• Trusting Copilot blindly.

Best Practices

• Use clear table names.
• Use consistent column names.
• Create a Dates table for time intelligence.
• Test relationships with sample data.
• Write DAX measures with clear names.
• Use CALCULATE for specific filters.
• Build KPIs for key numbers.
• Save your work often.
• Ask Copilot if unsure.
• Document your model.

Illustrations and Diagrams

Power Pivot Data Model

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+
  

DAX Measure Illustration

  Total Sales = SUM(Sales[Amount])
       |
       V
  500 + 300 + 400 = 1200
  

CALCULATE Illustration

  Total Sales = SUM(Sales[Amount])
       |
       V
  Sales for Lagos = CALCULATE(
      [Total Sales],
      Customers[City] = "Lagos"
  )
       |
       V
  Only Lagos sales
  

Time Intelligence Illustration

  Sales YTD = TOTALYTD(
      [Total Sales],
      Dates[Date]
  )
       |
       V
  Sales from Jan 1 to today
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

Power Query vs Power Pivot

SUM vs CALCULATE

YTD vs MTD

Power Pivot vs Normal PivotTable

Lesson Summaries

Lesson 1: Power Pivot connects tables and creates powerful calculations.

Lesson 2: Enable Power Pivot from File > Options > Add-ins.

Lesson 3: Add tables to the data model.

Lesson 4: Relationships connect tables by shared columns.

Lesson 5: The data model is your collection of connected tables.

Lesson 6: DAX measures are powerful calculations.

Lesson 7: Common DAX functions include SUM, AVERAGE, COUNT, DISTINCTCOUNT.

Lesson 8: CALCULATE changes the context of a measure.

Lesson 9: Time intelligence shows YTD, MTD, and more.

Lesson 10: KPIs track performance against targets.

Lesson 11: Copilot explains Power Pivot and writes DAX.

Lesson 12: Common mistakes include wrong relationships.

Lesson 13: Best practices: clear names, Dates table, test.

Lesson 14: Build a complete data model with Copilot.

Lesson 15: You now have a toolkit of Power Pivot skills.

End-of-Module Summary

Congratulations! You have finished Module Three of Copilot in Microsoft Excel – Level Three. You learned what Power Pivot is. You learned how to enable it, add tables to the data model, create relationships, and understand the data model. You learned about DAX measures, common DAX functions, CALCULATE, and time intelligence. You learned how to build KPIs and how Copilot helps. You learned common mistakes and best practices. Most importantly, you can now connect tables and create powerful calculations. Keep practising, and you will become an Excel master!

Frequently Asked Questions

1. What is Power Pivot? A tool that connects tables and creates powerful calculations.
2. How do I enable Power Pivot? File > Options > Add-ins > COM Add-ins.
3. What is a relationship? A connection between two tables by a shared column.
4. What is a data model? The collection of all your connected tables.
5. What is DAX? Data Analysis Expressions – a formula language.
6. How do I create a measure? Power Pivot > Measures > New Measure.
7. What is CALCULATE? A DAX function that changes the filter context.
8. What is time intelligence? Functions for periods like YTD and MTD.
9. What is a KPI? Key Performance Indicator – a number showing performance.
10. How does Copilot help? It explains Power Pivot and writes DAX.

Matching Exercises

Match the term to its meaning.

Answers: 1-B, 2-C, 3-D, 4-A, 5-E

Scenario-based Exercises

1. Scenario: You have products and sales in separate tables. What do you do?
   Answer: Create a relationship.
2. Scenario: You want total sales. What do you create?
   Answer: A DAX measure.
3. Scenario: You want sales for Lagos only. What do you use?
   Answer: CALCULATE.
4. Scenario: You want year-to-date sales. What do you use?
   Answer: TOTALYTD.
5. Scenario: You want to track performance against a target. What do you build?
   Answer: A KPI.

Group Activity

Title: “Build a Data Model Together”

Instructions: In groups of 3–4, create three tables (Products, Customers, Sales). Use Power Pivot to create relationships and write DAX measures. One person types, one person asks Copilot, one person checks, and one person presents. Share your model with the class.

Goal: Practice building a data model with Power Pivot.

Individual Activity

Task: Create a small Excel workbook with two tables: Products and Sales. Use Power Pivot to:

• Add tables to the data model.
• Create a relationship.
• Create a DAX measure for Total Sales.
• Use CALCULATE to filter by product.

Hint: Start with clean tables with clear headers.

Mini Project

Project: “My Sales Data Model”

Create an Excel workbook with three tables: Products, Customers, and Sales. Use Power Pivot to:

• Add all tables to the data model.
• Create relationships.
• Create DAX measures: Total Sales, Average Sale, Unique Customers.
• Create time intelligence: Sales YTD.
• Build a KPI for Total Sales vs Target.

Example output:

  +----------------------------------------+
  |         SALES DATA MODEL               |
  |  Total Sales: ₦250,000                 |
  |  Average Sale: ₦2,500                  |
  |  Unique Customers: 150                 |
  |  Sales YTD: ₦250,000                   |
  |  KPI: On Target (Green)                |
  +----------------------------------------+
  

Practical Assignment

Assignment: Create a new Excel workbook called PowerPivot_Project. In the workbook, do the following:

1. Create three tables: Products, Customers, Sales.
2. Enable Power Pivot.
3. Add all tables to the data model.
4. Create relationships between the tables.
5. Create DAX measures: Total Sales, Average Sale, Unique Customers.
6. Create a time intelligence measure: Sales YTD.
7. Build a KPI for Total Sales.
8. Create a PivotTable using your measures.
9. Save the workbook.
10. Write a short explanation of your model.

Submit: Your workbook file and screenshots of your data model and measures.

Key Takeaways

• Power Pivot connects tables with relationships.
• Enable Power Pivot from Add-ins.
• Add tables to the data model.
• Relationships connect tables by shared columns.
• DAX measures are powerful calculations.
• CALCULATE changes the context.
• Time intelligence shows YTD, MTD, and more.
• KPIs track performance.
• Copilot writes DAX for you.
• Always test your measures.

Classroom Discussion Questions

1. What is Power Pivot?
2. Why is Power Pivot useful?
3. What is a relationship?
4. What is a data model?
5. What is a DAX measure?
6. What does CALCULATE do?
7. What is time intelligence?
8. What is a KPI?
9. How does Copilot help with Power Pivot?
10. What did Ada learn from her data bridge?

Preparation for Module Four

In Module Four, we will learn about AI insights and Power BI. We will cover:

• Using Copilot for advanced analysis and forecasting.
• Anomaly detection and smart narratives.
• Connecting Excel to Power BI.
• Building interactive dashboards.
• Completing the final certification project.

To prepare, make sure you have completed the practical assignment and have a clean data model ready. Review your notes on Power Pivot and DAX. Bring your creativity!

See you in Module Four!

End of Module Three – Copilot in Microsoft Excel – Level Three

Module Four: AI Insights, Power BI and Certification Project – Becoming a True Excel Master

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome to the final module, young Excel master! You have come a very long way. In Module One, you learned advanced formulas and dynamic arrays. In Module Two, you learned Power Query and how to clean data automatically. In Module Three, you learned Power Pivot and how to connect tables with relationships and DAX measures.

Now, in Module Four, we will learn how to use AI insights and Power BI to take your skills to the highest level. You will learn how Copilot can find hidden patterns, detect unusual values, forecast the future, and tell the story of your data in plain words. You will also learn how to connect Excel with Power BI to build interactive dashboards.

Finally, you will complete your certification project. This project will bring together everything you have learned in all three levels. By the end of this module, you will be a true Copilot in Excel master.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Use Copilot for advanced analysis and forecasting.
• Detect anomalies and unusual values with Copilot.
• Create smart narratives that explain your data.
• Understand what Power BI is.
• Connect Excel data to Power BI.
• Build interactive dashboards with Power BI and Copilot.
• Publish and share your dashboards.
• Prepare and complete your certification project.
• Review everything you learned in Level Three.
• Plan your next steps as an Excel expert.

Warm-up Story: Tunde’s Magic Crystal Ball

Tunde runs a small phone accessories shop in Ibadan. He sells chargers, earphones, phone cases, and screen protectors. Every month, he records his sales in Excel. He has been using Power Query and Power Pivot, so his data is very clean and well organised.

One day, Tunde’s father asked him, “Tunde, what will sales be like next month? Which product will sell the most?” Tunde did not know. He could see past data, but he could not see the future.

His friend Ada said, “Tunde, use Copilot for AI insights! It can analyse your data, find trends, and even forecast future sales. It is like a magic crystal ball.”

Tunde opened Copilot in Excel. He asked: “What is the trend in my sales?” Copilot replied: “Sales have been growing by 10% each month.” He asked: “Forecast my sales for next month.” Copilot replied: “Next month’s sales are forecast at ₦280,000.” He asked: “Are there any unusual values?” Copilot replied: “Yes, there was one sale on 15th March that was 5 times higher than usual.”

Tunde was amazed. He also connected his Excel data to Power BI. He built a dashboard with charts and KPIs. He published it online and shared the link with his father. His father could see the live dashboard on his phone.

His father said, “Tunde, you have become a true data scientist!” Tunde smiled. He learned that AI insights and Power BI make data come alive.

Moral of the story: AI insights find patterns and predict the future. Power BI turns data into interactive dashboards. Copilot helps at every step.

Main Lessons

Lesson 1: What Are AI Insights?

Definition: AI insights are useful discoveries that artificial intelligence finds in your data.

Why it is important: AI insights help you understand your data faster and see things you might miss.

Simple explanation: Imagine a detective who looks at all your clues and tells you what happened.

Real-life example: A bank uses AI insights to find unusual transactions.

School example: A teacher uses AI insights to find which students need help.

Home example: A family uses AI insights to find where their money goes.

Nigerian example: A trader uses AI insights to find which product sells best.

Illustration:

  Raw Data
      |
      V
  AI Insights (Copilot)
      |
      V
  Useful Discoveries
      |
      V
  Better Decisions 🎉
  

Mini summary: AI insights are useful discoveries in your data. Copilot finds them for you.

Lesson 2: Forecasting with Copilot

Definition: Forecasting means predicting what will happen in the future based on past data.

Why it is important: Forecasting helps you plan ahead.

Simple explanation: Imagine looking at a weather forecast to decide if you need an umbrella.

Real-life example: A bank forecasts profits for next year.

School example: A teacher forecasts student performance.

Home example: A family forecasts monthly spending.

Nigerian example: A trader forecasts sales for the next month.

Illustration:

  Past Sales:
  Jan: 100
  Feb: 150
  Mar: 200
  Apr: 250

  Forecast:
  May: 300 (predicted)

  Ask Copilot: "Forecast sales for next month"
  

Step-by-step:

1. Select your data table with dates and values.
2. Ask Copilot: “Forecast [column] for the next [period].”
3. Copilot shows the forecast.
4. Copilot can even create a forecast chart for you.

Mini summary: Forecasting predicts the future from past data. Copilot does it in seconds.

Lesson 3: Detecting Anomalies with Copilot

Definition: An anomaly is a value that is very different from the others – an unusual value.

Why it is important: Anomalies can be mistakes or important discoveries.

Simple explanation: Imagine a class where everyone scored 70-80, but one student scored 10. The 10 is an anomaly.

Real-life example: A bank finds an unusual transaction that might be fraud.

School example: A teacher finds a very low score in a class of high scores.

Home example: A family finds a very high electricity bill in a month when they were away.

Nigerian example: A trader finds a huge sale on a day when the shop was closed.

Illustration:

  Scores: 75, 78, 80, 82, 10, 79
                         ^
                     Anomaly

  Ask Copilot: "Find anomalies in this column"
  

Step-by-step:

1. Select your data column.
2. Ask Copilot: “Find anomalies in this column.”
3. Copilot highlights the unusual values.
4. Check if they are mistakes or important.

Mini summary: Anomalies are unusual values. Copilot finds them for you.

Lesson 4: Smart Narratives – Telling the Story of Your Data

Definition: A smart narrative is a short, plain-language summary of your data created by AI.

Why it is important: Smart narratives make your data easy to understand for everyone.

Simple explanation: Imagine asking a friend to explain a complicated chart in one paragraph. That is a smart narrative.

Real-life example: A bank uses smart narratives in its monthly reports.

School example: A teacher uses smart narratives for student progress reports.

Home example: A family uses smart narratives for budget summaries.

Nigerian example: A trader uses smart narratives for weekly sales reports.

Illustration:

  Data + Charts
       |
       V
  Copilot Smart Narrative
       |
       V
  "Sales grew 15% this month.
   Phone cases were the top product.
   Average sale was ₦2,500."
  

Step-by-step:

1. Create a PivotTable or chart.
2. Click “Insert” > “Smart Narrative.”
3. A text box appears with an AI-written summary.
4. You can edit the text if needed.

Mini summary: Smart narratives explain your data in words. Copilot writes them for you.

Lesson 5: What is Power BI?

Definition: Power BI is a Microsoft tool that turns your data into interactive dashboards and reports.

Why it is important: Power BI helps you share your insights with others in a beautiful way.

Simple explanation: Imagine turning your Excel charts into a live dashboard that anyone can explore.

Real-life example: A bank uses Power BI to monitor branch performance.

School example: A school uses Power BI to track student progress.

Home example: A family uses Power BI for a family budget dashboard.

Nigerian example: A trader uses Power BI to track sales across multiple shops.

Illustration:

  Excel Data
      |
      V
  Power BI
      |
      V
  Interactive Dashboard
      |
      V
  Share with others 🎉
  

Mini summary: Power BI turns data into interactive dashboards. It works well with Excel.

Lesson 6: Connecting Excel to Power BI

Definition: Connecting means linking your Excel data to Power BI so it can be used there.

Why it is important: This lets you reuse your Excel work in Power BI.

Simple explanation: Like carrying your ingredients from the kitchen to the dining table.

Real-life example: A bank connects Excel data to Power BI.

School example: A teacher connects student data to Power BI.

Home example: A family connects budget data to Power BI.

Nigerian example: A trader connects sales data to Power BI.

Illustration:

  Excel Workbook
      |
      V
  Power BI Desktop
      |
      V
  Get Data → Excel
      |
      V
  Choose your file
      |
      V
  Load data into Power BI
  

Step-by-step:

1. Open Power BI Desktop.
2. Click “Get Data.”
3. Choose “Excel Workbook.”
4. Select your Excel file.
5. Choose the tables you want.
6. Click “Load.”

Mini summary: Connecting Excel to Power BI is easy. Just use Get Data in Power BI.

Lesson 7: Building Interactive Dashboards

Definition: An interactive dashboard lets viewers click, filter, and explore your data.

Why it is important: Interactive dashboards are more useful than static reports.

Simple explanation: Imagine a dashboard where you can click a button to see only Lagos sales. That is interactive.

Real-life example: A bank builds an interactive dashboard for branch managers.

School example: A school builds an interactive dashboard for student performance.

Home example: A family builds an interactive dashboard for spending.

Nigerian example: A trader builds an interactive dashboard for daily sales.

Illustration:

  +----------------------------------------+
  |        INTERACTIVE DASHBOARD           |
  |  [Filter: Lagos ▼]                     |
  |  +--------+  +--------+  +--------+    |
  |  | Sales  |  | Profit |  |Customers|   |
  |  | ₦250k  |  | ₦80k   |  | 150     |   |
  |  +--------+  +--------+  +--------+    |
  |  +------------------+  +------------+  |
  |  | Bar Chart        |  | Pie Chart  |  |
  |  +------------------+  +------------+  |
  +----------------------------------------+
  

Step-by-step:

1. Open Power BI Desktop.
2. Load your Excel data.
3. Drag fields onto the canvas to create charts.
4. Add slicers (filters) for interactivity.
5. Ask Copilot: “Suggest improvements to my dashboard.”
6. Format and arrange the visuals.

Mini summary: Interactive dashboards let viewers explore data. Power BI makes them easily.

Lesson 8: Using Copilot in Power BI

Definition: Copilot in Power BI helps you create dashboards and insights with plain English.

Why it is important: Copilot makes Power BI easier and faster.

Simple explanation: Like having a helpful assistant who builds your dashboard for you.

Real-life example: A bank uses Copilot to create branch reports.

School example: A teacher uses Copilot to create student dashboards.

Home example: A family uses Copilot to create spending dashboards.

Nigerian example: A trader uses Copilot to create sales dashboards.

Illustration:

  You: "Create a dashboard showing sales by product"
       |
       V
  Copilot: Builds the dashboard for you
       |
       V
  You: Refine and share 🎉
  

Step-by-step:

1. Open Copilot in Power BI.
2. Type your request in plain English.
3. Copilot creates the visual or dashboard.
4. Review and adjust as needed.

Mini summary: Copilot helps you build Power BI dashboards from plain English.

Lesson 9: Publishing and Sharing Dashboards

Definition: Publishing means putting your dashboard online so others can see it.

Why it is important: Sharing lets others benefit from your analysis.

Simple explanation: Like posting a photo online so friends can see it.

Real-life example: A bank publishes dashboards for managers.

School example: A school publishes dashboards for parents.

Home example: A family publishes a budget dashboard for members.

Nigerian example: A trader publishes a sales dashboard for staff.

Illustration:

  Dashboard in Power BI Desktop
       |
       V
  Click "Publish"
       |
       V
  Choose workspace
       |
       V
  Share link with others 🎉
  

Step-by-step:

1. Finish your dashboard in Power BI Desktop.
2. Click “Publish.”
3. Choose a workspace (like My Workspace).
4. Click “Select.”
5. Open the Power BI service online and share the link.

Mini summary: Publish and share dashboards so others can see your insights.

Lesson 10: AI Insights for Forecasting and What-If Analysis

Definition: What-if analysis means asking, “What if this changed?” and seeing the effect.

Why it is important: It helps you plan for different situations.

Simple explanation: Imagine asking, “What if I sell 20% more? How much profit will I make?”

Real-life example: A bank asks, “What if interest rates go up?”

School example: A teacher asks, “What if every student studies one more hour?”

Home example: A family asks, “What if we save ₦5,000 more each month?”

Nigerian example: A trader asks, “What if I increase prices by 10%?”

Illustration:

  Current Sales: ₦100,000
       |
       V
  What if: Price increases by 10%?
       |
       V
  New Sales: ₦110,000 (predicted)
  

Step-by-step:

1. Create your base measure.
2. Ask Copilot: “What if I change [something]?”
3. Copilot shows the effect.
4. Use the answer to make decisions.

Mini summary: What-if analysis explores different situations. Copilot helps you try them.

Lesson 11: Common Mistakes with AI Insights and Power BI

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: Wrong insights can lead to wrong decisions.

Simple explanation: Like following a map with the wrong road.

Real-life example: A bank forecasts wrong sales and loses money.

School example: A teacher uses wrong insights to help students.

Home example: A family uses wrong insights for budgeting.

Nigerian example: A trader uses wrong forecasts for buying stock.

Table of common mistakes:

Mini summary: Common mistakes include messy data and trusting AI blindly. Always check.

Lesson 12: Best Practices for AI Insights and Power BI

Definition: Best practices are good habits that make AI insights reliable.

Why it is important: Good habits ensure correct decisions.

Simple explanation: Like washing your hands before cooking.

Real-life example: A bank checks AI insights carefully.

School example: A teacher verifies Copilot results.

Home example: A family double-checks forecasts.

Nigerian example: A trader verifies sales forecasts.

List of best practices:

• Clean your data before analysis.
• Use enough data for forecasting.
• Consider the business context.
• Always verify Copilot’s insights.
• Review dashboards before sharing.
• Refresh data regularly.
• Use clear titles and labels.
• Save your work often.
• Share findings clearly.
• Keep learning new features.

Mini summary: Best practices: clean data, verify insights, review before sharing.

Lesson 13: Preparing for the Certification Project

Your certification project brings together everything you have learned.

Project idea: Build a complete Business Intelligence solution.

Steps:

1. Create or collect a real dataset.
2. Clean the data with Power Query.
3. Build a data model with Power Pivot.
4. Write DAX measures.
5. Create charts and KPIs.
6. Use Copilot for insights and forecasting.
7. Build a Power BI dashboard.
8. Publish and share it.

Illustration:

  Raw Data
      |
      V
  Power Query (Clean)
      |
      V
  Power Pivot (Model + DAX)
      |
      V
  Charts + KPIs
      |
      V
  AI Insights (Forecast, Anomalies)
      |
      V
  Power BI Dashboard
      |
      V
  Publish + Share 🎉
  

Mini summary: The certification project combines all skills. Plan carefully and start early.

Lesson 14: Reviewing Everything You Learned in Level Three

You have learned so much in Level Three. Let’s review.

Module One: Advanced formulas (FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, LAMBDA).

Module Two: Power Query (clean, transform, refresh).

Module Three: Power Pivot (relationships, DAX, time intelligence, KPIs).

Module Four: AI insights (forecasting, anomalies, smart narratives) and Power BI.

Illustration:

  Level Three Skills:
  +-----------------+
  | Advanced        |
  | Formulas        |
  +-----------------+
  +-----------------+
  | Power Query     |
  +-----------------+
  +-----------------+
  | Power Pivot     |
  +-----------------+
  +-----------------+
  | AI Insights     |
  +-----------------+
  +-----------------+
  | Power BI        |
  +-----------------+
  

Mini summary: You have mastered advanced formulas, Power Query, Power Pivot, AI insights, and Power BI.

Lesson 15: Your Certification and Beyond

Your certification is proof that you are a Copilot in Excel master.

Next steps:

• Complete your certification project.
• Share your portfolio online.
• Help others learn Excel and Copilot.
• Apply your skills in school, home, or business.
• Keep learning – Excel keeps improving.

Illustration:

  Certification
       |
       V
  Portfolio
       |
       V
  Share with others
       |
       V
  Help others learn
       |
       V
  Apply skills
       |
       V
  Excel Master 🎉
  

Mini summary: Your certification opens doors. Keep growing and helping others.

Key Vocabulary

Important Concepts

• AI insights find hidden patterns: Copilot discovers them for you.
• Forecasting predicts the future: Based on past data.
• Anomalies are unusual values: They may be mistakes or discoveries.
• Smart narratives explain data: In plain language.
• Power BI creates interactive dashboards: Share with anyone.
• Excel connects to Power BI: Reuse your work easily.
• Copilot works in Power BI too: Plain English requests.
• Publishing shares your work: Anyone can see it online.
• What-if analysis explores options: Ask “what if” questions.
• Always verify AI insights: Trust but check.

Step-by-step Explanations

How to forecast with Copilot step by step

1. Select data with dates and values.
2. Ask Copilot: “Forecast [column] for next [period].”
3. Copilot shows the forecast.
4. Review and use it for planning.

How to find anomalies step by step

1. Select your data column.
2. Ask Copilot: “Find anomalies in this column.”
3. Copilot highlights the unusual values.
4. Investigate each anomaly.

How to create a smart narrative step by step

1. Create a PivotTable or chart.
2. Click “Insert” > “Smart Narrative.”
3. An AI-written summary appears.
4. Edit if needed.

How to connect Excel to Power BI step by step

1. Open Power BI Desktop.
2. Click “Get Data” > “Excel Workbook.”
3. Select your Excel file.
4. Choose tables and click “Load.”

How to publish a dashboard step by step

1. Finish your dashboard.
2. Click “Publish.”
3. Choose a workspace.
4. Click “Select.”
5. Share the link online.

Real-life Examples

• Shops: Forecast sales and find top products.
• Schools: Predict student performance and find anomalies.
• Homes: Forecast spending and detect unusual bills.
• Offices: Build dashboards for teams.
• Hospitals: Track patient trends with Power BI.

Nigerian Examples

• Market traders: Forecast sales for market days.
• POS operators: Detect unusual transactions.
• Schools in Lagos: Track student progress with dashboards.
• Transporters: Forecast fuel costs.
• Church groups: Track donations with dashboards.

Fun Examples Children Can Relate To

• Video game scores: Forecast your next high score.
• Football league: Predict league champions.
• Pocket money: Forecast savings.
• Chores: Detect unusually slow chores.
• Snack list: Find which snacks you buy most.

Everyday Examples

• Shopping list: Forecast next week’s spending.
• School timetable: Track study time trends.
• Exercise log: Predict weekly exercise minutes.
• Reading list: Forecast books read per month.
• Grocery budget: Detect unusual spending.

Parent Tips

• Encourage your child to use AI insights on real data.
• Let them practice with family budget or shopping data.
• Use everyday examples to explain forecasts.
• Set a small daily practice time.
• Celebrate small wins, like a correct forecast.
• Be patient. AI insights take practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to verify AI insights.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• AI can forecast future values with high accuracy.
• Anomaly detection is used in banks to catch fraud.
• Smart narratives save hours of report writing.
• Power BI is used by over 250,000 companies.
• You can view Power BI dashboards on your phone.
• Copilot in Power BI works in many languages.
• What-if analysis is used by presidents and CEOs.
• Excel and Power BI are a powerful team.

Did You Know?

• Did you know that Copilot can forecast up to 6 months ahead?
• Did you know that anomalies can be mistakes or important discoveries?
• Did you know that smart narratives update when data changes?
• Did you know that Power BI has a free version?
• Did you know that you can share Power BI dashboards with anyone?
• Did you know that Copilot can suggest the best chart for your data?
• Did you know that what-if analysis can use sliders?
• Did you know that Excel and Power BI use the same data model?

Remember This

• AI insights find hidden patterns.
• Forecasting predicts the future.
• Anomalies are unusual values.
• Smart narratives explain data in words.
• Power BI creates interactive dashboards.
• Excel connects easily to Power BI.
• Copilot works in Power BI too.
• Publishing shares your work.
• What-if analysis explores options.
• Always verify AI insights.

Common Mistakes

• Using messy data.
• Too little data for forecasting.
• Ignoring business context.
• Trusting AI blindly.
• Sharing without checking.
• Not refreshing data.
• Not testing what-if analysis.
• Not saving work.

Best Practices

• Clean your data before analysis.
• Use enough data for forecasting.
• Consider the business context.
• Always verify Copilot’s insights.
• Review dashboards before sharing.
• Refresh data regularly.
• Use clear titles and labels.
• Save your work often.
• Share findings clearly.
• Keep learning new features.

Illustrations and Diagrams

AI Insights Workflow

  Clean Data
      |
      V
  Copilot Analysis
      |
      V
  Insights (Forecast, Anomalies, Narratives)
      |
      V
  Better Decisions 🎉
  

Forecast Illustration

  300 |           * (forecast)
  250 |        *
  200 |     *
  150 |  *
  100 |
      +----------------
       Jan Feb Mar Apr May
  

Anomaly Detection Illustration

  Sales: 100, 110, 105, 500, 115
                        ^
                    Anomaly (500)
  

Power BI Dashboard Illustration

  +----------------------------------------+
  |        INTERACTIVE DASHBOARD           |
  |  [Filter: Lagos ▼]                     |
  |  +--------+  +--------+  +--------+    |
  |  | Sales  |  | Profit |  |Customers|   |
  |  +--------+  +--------+  +--------+    |
  |  +------------------+  +------------+  |
  |  | Bar Chart        |  | Pie Chart  |  |
  |  +------------------+  +------------+  |
  +----------------------------------------+
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

Excel Dashboard vs Power BI Dashboard

Forecast vs What-If

Anomaly vs Outlier

Copilot in Excel vs Copilot in Power BI

Lesson Summaries

Lesson 1: AI insights are useful discoveries found by AI.

Lesson 2: Forecasting predicts the future from past data.

Lesson 3: Anomalies are unusual values.

Lesson 4: Smart narratives explain data in words.

Lesson 5: Power BI creates interactive dashboards.

Lesson 6: Excel connects easily to Power BI.

Lesson 7: Interactive dashboards let viewers explore data.

Lesson 8: Copilot helps build Power BI dashboards.

Lesson 9: Publish and share dashboards online.

Lesson 10: What-if analysis explores different situations.

Lesson 11: Common mistakes include messy data and trusting AI blindly.

Lesson 12: Best practices: clean data, verify insights, review before sharing.

Lesson 13: The certification project combines all skills.

Lesson 14: Review everything you learned in Level Three.

Lesson 15: Your certification opens doors. Keep growing.

End-of-Module Summary

Congratulations! You have finished Module Four of Copilot in Microsoft Excel – Level Three. You learned what AI insights are, how to forecast with Copilot, how to detect anomalies, and how to create smart narratives. You learned what Power BI is, how to connect Excel to Power BI, how to build interactive dashboards, how to use Copilot in Power BI, and how to publish and share dashboards. You learned common mistakes and best practices. You reviewed everything you learned in Level Three. Most importantly, you completed the course and are now a Copilot in Excel master. Keep practising, and keep learning!

Frequently Asked Questions

1. What are AI insights? Useful discoveries found by AI.
2. How do I forecast with Copilot? Ask Copilot: “Forecast [column].”
3. What is an anomaly? An unusual value.
4. What is a smart narrative? A plain-language summary of data.
5. What is Power BI? A tool for interactive dashboards.
6. How do I connect Excel to Power BI? Use Get Data > Excel Workbook.
7. How do I build a dashboard? Drag fields onto the Power BI canvas.
8. How do I publish a dashboard? Click Publish and choose a workspace.
9. What is what-if analysis? Asking “what if” questions.
10. How does Copilot help? It finds insights and builds dashboards from plain English.

Matching Exercises

Match the term to its meaning.

Answers: 1-B, 2-A, 3-D, 4-C, 5-E

Scenario-based Exercises

1. Scenario: You want to predict next month’s sales. What do you use?
   Answer: Forecast with Copilot.
2. Scenario: You want to find unusual transactions. What do you use?
   Answer: Anomaly detection.
3. Scenario: You want a written summary of your report. What do you use?
   Answer: Smart narrative.
4. Scenario: You want to share an interactive dashboard online. What do you use?
   Answer: Power BI.
5. Scenario: You want to see what happens if prices increase. What do you use?
   Answer: What-if analysis.

Group Activity

Title: “Build an AI Insights Dashboard Together”

Instructions: In groups of 3–4, create a sales sheet in Excel. Use Copilot to forecast future sales, find anomalies, and write a smart narrative. Then connect the data to Power BI and build a dashboard. One person types, one person asks Copilot, one person builds the dashboard, and one person presents. Share your dashboard with the class.

Goal: Practice AI insights and Power BI together.

Individual Activity

Task: Create a small Excel sheet with ten rows of data (e.g., monthly sales). Use Copilot to:

• Forecast the next month’s value.
• Find any anomalies.
• Write a smart narrative.
• Create a simple what-if analysis.

Hint: Start with a clean table with dates and values.

Mini Project

Project: “My AI Insights Report”

Create an Excel sheet with at least 20 rows of sales data. Use Copilot to:

• Forecast next month’s sales.
• Find anomalies.
• Write a smart narrative.
• Create a what-if analysis.
• Connect to Power BI.
• Build a dashboard.
• Publish the dashboard.

Example output:

  +----------------------------------------+
  |         AI INSIGHTS REPORT             |
  |  Forecast Next Month: ₦280,000         |
  |  Anomalies Found: 2                    |
  |  Smart Narrative: "Sales grew 15%..."  |
  |  Power BI Dashboard: [Link]            |
  +----------------------------------------+
  

Practical Assignment

Assignment: Complete your certification project. Build a complete Business Intelligence solution using everything you learned in Level Three:

1. Create or collect a real dataset (at least 50 rows).
2. Clean the data with Power Query.
3. Build a data model with Power Pivot.
4. Write DAX measures.
5. Create charts and KPIs.
6. Use Copilot for insights, forecasting, and anomalies.
7. Build a Power BI dashboard.
8. Publish and share your dashboard.
9. Write a report explaining your project.
10. Submit your workbook, dashboard link, and report.

Submit: Your workbook file, Power BI dashboard link, and report.

Key Takeaways

• AI insights find hidden patterns.
• Forecasting predicts the future.
• Anomalies are unusual values.
• Smart narratives explain data in words.
• Power BI creates interactive dashboards.
• Excel connects easily to Power BI.
• Copilot works in Power BI too.
• Publishing shares your work.
• What-if analysis explores options.
• Always verify AI insights.

Classroom Discussion Questions

1. What are AI insights and why are they useful?
2. How can forecasting help a business?
3. Why is anomaly detection important?
4. What is a smart narrative?
5. What is Power BI and how does it help?
6. How do you connect Excel to Power BI?
7. Why are interactive dashboards useful?
8. What is what-if analysis?
9. Why should you verify AI insights?
10. What did Tunde learn from his magic crystal ball?

Next Steps After the Course

Congratulations! You have completed the entire Copilot in Microsoft Excel – Level Three course. Here are some next steps you can take:

• Practice: Keep using Excel, Copilot, Power Query, Power Pivot, and Power BI.
• Build: Create more real-world projects and dashboards.
• Share: Publish your dashboards and teach others.
• Explore: Learn more advanced Power BI and DAX features.
• Connect: Join Excel and Power BI communities online.
• Apply: Use your skills in school, home, or business.
• Keep learning: Microsoft keeps adding new Copilot features.

Remember, this is just the beginning. You are now a Copilot in Excel master. Keep practising and keep learning!

End of Module Four – Copilot in Microsoft Excel – Level Three

🎉 Congratulations! You have completed the entire Level Three course! 🎉