MODULE 2

Managing and Formatting Data Like a Pro

Module Introduction

Welcome to Module Two of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn how to manage and format data like a professional.

Think of your data like a garden. You need to plant it (enter it), water it (format it), and remove weeds (remove duplicates). This module will teach you how to make your data look beautiful, organised, and easy to understand.

Learning Objectives

By the end of this module, you will be able to:

• Use conditional formatting to highlight important data.
• Create custom number formats (like showing ₦ for Naira).
• Set rules for what data can be entered (data validation).
• Use Flash Fill to quickly fill in data.
• Remove duplicate entries from your data.
• Group data and calculate subtotals.
• Use advanced formatting to make your data stand out.

---

Warm‑up Story

Bola's Beautiful Sales Report

Bola works in a shop in Ibadan. She keeps a record of all the items sold every day. She had a long list of sales, but it was hard to read. Some items were duplicated, and the numbers didn't look neat.

She thought, "How can I make this data look more professional?" She learned about conditional formatting. She used it to highlight items that sold more than 50 pieces. She used Flash Fill to quickly fix the names of items. She removed duplicate entries.

Now, Bola's sales report looks beautiful and is easy to understand. Her boss was impressed and said, "This is what a professional report looks like!"

Main Lessons

Lesson 1: Conditional Formatting

Definition: Conditional formatting is a tool that changes the appearance of cells based on certain rules.

Why it is important: It helps you quickly see important data, like high numbers or low numbers.

Simple explanation: It is like using a highlighter to mark important parts of a book.

• Real‑life example: A teacher uses conditional formatting to highlight students with scores above 90%.
• School example: A student uses it to highlight homework that is overdue.
• Home example: A parent uses it to highlight expenses that are over budget.
• Nigerian example: A shop owner uses it to highlight products that are selling well.

How to use conditional formatting:

1. Select the cells you want to format.
2. Go to the Home tab.
3. Click Conditional Formatting.
4. Choose a rule (e.g., "Highlight Cells Rules" > "Greater Than").
5. Set the rule and choose a format.
6. Click OK.

Lesson 2: Custom Number Formats

Definition: Custom number formats let you change how numbers look in a cell.

Why it is important: You can show numbers as currency, percentages, or with specific symbols.

Simple explanation: It is like choosing how you want to write a number – as a dollar, a percentage, or just a plain number.

• Real‑life example: A business owner uses a custom format to show ₦ for Naira.
• School example: A student uses a custom format to show percentages.
• Home example: A parent uses a custom format to show currency.
• Nigerian example: A shop owner uses a custom format to show prices in Naira.

How to create a custom number format:

1. Select the cells you want to format.
2. Right-click and choose Format Cells.
3. Click the Number tab.
4. Choose Custom.
5. Type your custom format (e.g., "₦#,##0.00").
6. Click OK.

Lesson 3: Data Validation

Definition: Data validation is a tool that controls what data can be entered into a cell.

Why it is important: It prevents mistakes by only allowing certain types of data.

Simple explanation: It is like a bouncer at a club – only the right people (data) get in.

• Real‑life example: A teacher sets data validation so students can only enter numbers between 0 and 100.
• School example: A student sets data validation to only allow dates.
• Home example: A parent sets data validation to only allow positive numbers.
• Nigerian example: A shop owner sets data validation so only items from a list can be entered.

How to set data validation:

1. Select the cells.
2. Go to the Data tab.
3. Click Data Validation.
4. Choose a rule (e.g., "Whole number" > "between 0 and 100").
5. Click OK.

Lesson 4: Flash Fill

Definition: Flash Fill is a tool that automatically fills in data based on a pattern.

Why it is important: It saves time when you are entering repetitive data.

Simple explanation: It is like magic – Excel guesses what you want to type and does it for you.

• Real‑life example: A secretary uses Flash Fill to split full names into first and last names.
• School example: A student uses Flash Fill to fix data entry errors.
• Home example: A parent uses Flash Fill to organise a list of names.
• Nigerian example: A shop owner uses Flash Fill to clean up product names.

How to use Flash Fill:

1. Type an example in a new column.
2. Press Enter.
3. Start typing the next example.
4. Excel will show a suggestion. Press Enter to accept.

Lesson 5: Removing Duplicates

Definition: Removing duplicates means deleting repeated entries from your data.

Why it is important: It keeps your data clean and prevents double counting.

Simple explanation: It is like weeding your garden – you remove unwanted duplicates.

• Real‑life example: A manager removes duplicate customer names from a list.
• School example: A student removes duplicate answers from a survey.
• Home example: A parent removes duplicate items from a shopping list.
• Nigerian example: A shop owner removes duplicate product entries.

How to remove duplicates:

1. Select the data.
2. Go to the Data tab.
3. Click Remove Duplicates.
4. Choose the columns to check.
5. Click OK.

Lesson 6: Grouping Data

Definition: Grouping data means organising data into groups so you can show or hide them.

Why it is important: It helps you manage large amounts of data by hiding details.

Simple explanation: It is like putting books on a shelf – you can see the whole shelf or look at one section.

• Real‑life example: A finance team groups data by month.
• School example: A student groups data by subject.
• Home example: A parent groups expenses by category.
• Nigerian example: A shop owner groups sales by product type.

How to group data:

1. Select the rows or columns you want to group.
2. Go to the Data tab.
3. Click Group.
4. Choose Rows or Columns.
5. Click OK.

Lesson 7: Calculating Subtotals

Definition: Subtotals are totals that are calculated for each group in your data.

Why it is important: They let you see totals for each group, like total sales per month.

Simple explanation: It is like adding up all the numbers in each section of a book.

• Real‑life example: A manager calculates subtotals for sales by region.
• School example: A student calculates subtotals for scores by subject.
• Home example: A parent calculates subtotals for expenses by category.
• Nigerian example: A shop owner calculates subtotals for sales by day.

How to calculate subtotals:

1. Sort your data by the group column.
2. Go to the Data tab.
3. Click Subtotal.
4. Choose the column to group by.
5. Choose the function (e.g., Sum).
6. Choose the column to total.
7. Click OK.

Lesson 8: Advanced Conditional Formatting

Definition: Advanced conditional formatting uses more complex rules, like colour scales or icon sets.

Why it is important: It makes your data even more visual and easier to understand.

Simple explanation: It is like using a rainbow of colours to show different values.

• Real‑life example: A manager uses colour scales to show performance from low to high.
• School example: A student uses icon sets to show grades.
• Home example: A parent uses colour scales to show spending habits.
• Nigerian example: A shop owner uses icon sets to show product popularity.

How to use advanced conditional formatting:

1. Select the cells.
2. Go to Home > Conditional Formatting.
3. Choose Color Scales or Icon Sets.
4. Choose a style.

Lesson 9: Custom Sorting and Filtering

Definition: Custom sorting lets you sort data by your own rules. Filtering lets you show only the data you want.

Why it is important: It helps you focus on the most important data.

Simple explanation: It is like organising your bookshelf – you can sort by title, author, or colour.

• Real‑life example: A manager sorts data by sales amount.
• School example: A student filters data to see only high scores.
• Home example: A parent sorts a shopping list by category.
• Nigerian example: A shop owner sorts products by price.

How to custom sort:

1. Select the data.
2. Go to the Data tab.
3. Click Sort.
4. Choose the column and order.
5. Click OK.

Lesson 10: Advanced Find and Replace

Definition: Find and Replace lets you search for specific data and replace it with something else.

Why it is important: It saves time when you need to update many cells at once.

Simple explanation: It is like using Ctrl+F to find a word in a book and replace it.

• Real‑life example: A manager replaces old product codes with new ones.
• School example: A student replaces a typo in many cells.
• Home example: A parent replaces "Milk" with "Milk (2%)" in a list.
• Nigerian example: A shop owner replaces "Naira" with "₦".

How to use Find and Replace:

1. Press Ctrl + H.
2. Type what you want to find.
3. Type what you want to replace it with.
4. Click Replace All.

Lesson 11: Text to Columns

Definition: Text to Columns splits data from one column into multiple columns.

Why it is important: It helps you break down data that is combined, like full names.

Simple explanation: It is like separating a mixed bag of beans into different piles.

• Real‑life example: A manager splits full names into first and last names.
• School example: A student splits a list of dates into day, month, and year.
• Home example: A parent splits addresses into street, city, and state.
• Nigerian example: A shop owner splits product codes into category and number.

How to use Text to Columns:

1. Select the column.
2. Go to the Data tab.
3. Click Text to Columns.
4. Choose Delimited or Fixed width.
5. Follow the steps and click Finish.

Lesson 12: Advanced Filtering

Definition: Advanced filtering lets you filter data using complex criteria.

Why it is important: It allows you to show only the data that meets specific conditions.

Simple explanation: It is like using a sieve to get only the fine flour.

• Real‑life example: A manager filters data to show only sales above ₦10,000.
• School example: A student filters data to show only scores above 80.
• Home example: A parent filters expenses to show only food items.
• Nigerian example: A shop owner filters products to show only items in stock.

How to use advanced filtering:

1. Set up a criteria range.
2. Go to the Data tab.
3. Click Advanced.
4. Choose Filter the list, in‑place.
5. Set the criteria range.
6. Click OK.

Key Vocabulary

Important Concepts

• Conditional formatting helps you see important data quickly.
• Custom number formats let you display numbers the way you want.
• Data validation prevents mistakes in your data.
• Flash Fill saves time by predicting what you want to type.
• Removing duplicates keeps your data clean.
• Grouping and subtotals help you manage and summarise data.

Step-by-Step Explanations

How to Use Conditional Formatting

1. Select the cells.
2. Go to Home → Conditional Formatting.
3. Choose a rule (e.g., "Greater Than").
4. Set the rule and choose a format.
5. Click OK.

How to Remove Duplicates

1. Select the data.
2. Go to Data → Remove Duplicates.
3. Choose the columns to check.
4. Click OK.

Real-Life Examples

• In a business: A manager uses conditional formatting to highlight low sales.
• In a school: A teacher uses data validation to ensure students enter valid grades.
• At home: A parent uses Flash Fill to organise a family contact list.
• In Nigeria: A shop owner uses custom number formats to show prices in Naira.

Nigerian Examples

• A shop owner in Lagos uses conditional formatting to highlight products that are selling fast.
• A teacher in Abuja uses data validation to ensure students enter valid scores.
• A parent in Kano uses Flash Fill to organise a list of family members.
• A business owner in Enugu uses custom number formats to show prices in Naira.

Fun Examples Children Can Relate To

• Using conditional formatting to highlight your favourite colours.
• Using data validation to make sure you only enter numbers in a game.
• Using Flash Fill to quickly type your friends' names.
• Removing duplicates from a list of your favourite toys.

Everyday Examples

• Using conditional formatting to highlight expenses that are over budget.
• Using custom number formats to show currency in your budget.
• Using data validation to ensure you only enter valid dates.
• Using Flash Fill to quickly fill in a list of names.

Teacher Notes

• Emphasise the importance of clean data.
• Show students how to use conditional formatting to highlight important data.
• Teach students to use data validation to prevent errors.
• Encourage students to use Flash Fill to save time.

Parent Tips

• Help your child use conditional formatting to highlight important data.
• Show them how to use data validation to prevent mistakes.
• Encourage them to use Flash Fill to save time.
• Teach them to remove duplicates to keep data clean.

Interesting Facts

• Excel has over 60 different conditional formatting rules.
• Flash Fill was introduced in Excel 2013.
• Data validation can create drop‑down lists.
• You can use icons to show data trends.

Did You Know?

Remember This

• Conditional formatting highlights important data.
• Custom number formats let you display numbers your way.
• Data validation prevents mistakes.
• Flash Fill saves time.
• Remove duplicates to keep data clean.

Common Mistakes

• Not using conditional formatting: It helps you see important data.
• Ignoring data validation: It prevents mistakes.
• Not removing duplicates: They can cause errors.
• Using the wrong number format: It can confuse readers.
• Not using Flash Fill: It saves time.

Best Practices

• Use conditional formatting to highlight key data.
• Use data validation to prevent errors.
• Remove duplicates regularly.
• Use custom number formats for consistency.
• Use Flash Fill to save time.

Comparison: Conditional Formatting vs Data Validation

Feature	Conditional Formatting	Data Validation
Purpose	Highlight data	Control data entry
Changes cell appearance?	Yes	No
Controls what is entered?	No	Yes
Best for	Visualising data	Preventing errors

Comparison: Flash Fill vs Find and Replace

Feature	Flash Fill	Find and Replace
Purpose	Fill data based on patterns	Find and replace data
Requires pattern	Yes	No
Can replace data?	No	Yes
Best for	Cleaning data	Updating data

End-of-Module Summary

Congratulations! You have completed Module Two. You now know:

• How to use conditional formatting to highlight important data.
• How to create custom number formats.
• How to use data validation to prevent errors.
• How to use Flash Fill to save time.
• How to remove duplicates and group data.
• How to calculate subtotals and use advanced filtering.

You are now ready to move on to Module Three, where you will learn about advanced formulas and functions.

Frequently Asked Questions

1. What is conditional formatting? It changes cell appearance based on rules.
2. How do I create a custom number format? Use Format Cells → Custom.
3. What is data validation? It controls what data can be entered.
4. What is Flash Fill? It automatically fills data based on patterns.
5. How do I remove duplicates? Use Data → Remove Duplicates.
6. What is grouping data? Organising data into sections.
7. What is a subtotal? A total for a group of data.
8. How do I use advanced filtering? Use Data → Advanced.
9. What is Text to Columns? Splitting data into multiple columns.
10. What is Find and Replace? Finding and replacing data.

Review Questions

1. What is conditional formatting?
2. How do you create a custom number format?
3. What is data validation?
4. How do you use Flash Fill?
5. How do you remove duplicates?
6. What is grouping data?
7. What is a subtotal?
8. How do you use advanced filtering?
9. What is Text to Columns?
10. What is Find and Replace?
11. Why is conditional formatting useful?
12. Why is data validation important?
13. How can Flash Fill save time?
14. Why should you remove duplicates?
15. How can subtotals help you?

Fill-in-the-Blank Exercises

1. __________ formatting changes cell appearance based on rules.
2. Custom number __________ let you display numbers your way.
3. Data __________ controls what data can be entered.
4. __________ Fill automatically fills data based on patterns.
5. Remove __________ deletes repeated entries.
6. __________ data organises information into sections.
7. A __________ is a total for a group of data.
8. __________ filtering shows only data that meets conditions.
9. Text to __________ splits data into multiple columns.
10. Find and __________ lets you quickly update data.

True or False

1. Conditional formatting changes cell appearance. (True)
2. Custom number formats are not useful. (False)
3. Data validation prevents mistakes. (True)
4. Flash Fill requires you to type everything manually. (False)
5. Removing duplicates deletes repeated entries. (True)
6. Grouping data hides details. (True)
7. Subtotals are totals for individual cells. (False)
8. Advanced filtering is the same as simple filtering. (False)
9. Text to Columns splits data into multiple columns. (True)
10. Find and Replace saves time. (True)

Multiple Choice Questions

1. What is conditional formatting?
   a) A tool that changes cell appearance b) A tool that sorts data c) A tool that creates charts d) A tool that prints data
   Answer: a
2. How do you create a custom number format?
   a) Format Cells → Custom b) Home → Custom c) Data → Custom d) Insert → Custom
   Answer: a
3. What is data validation?
   a) A tool that controls data entry b) A tool that sorts data c) A tool that creates charts d) A tool that prints data
   Answer: a
4. What is Flash Fill?
   a) A tool that automatically fills data b) A tool that sorts data c) A tool that creates charts d) A tool that prints data
   Answer: a
5. How do you remove duplicates?
   a) Data → Remove Duplicates b) Home → Remove Duplicates c) Insert → Remove Duplicates d) View → Remove Duplicates
   Answer: a
6. What is grouping data?
   a) Organising data into sections b) Deleting data c) Printing data d) Sorting data
   Answer: a
7. What is a subtotal?
   a) A total for a group b) A total for all data c) A chart d) A macro
   Answer: a
8. What is advanced filtering?
   a) Filtering with complex rules b) Sorting data c) Printing data d) Creating charts
   Answer: a
9. What is Text to Columns?
   a) Splitting data into multiple columns b) Combining columns c) Printing data d) Sorting data
   Answer: a
10. What is Find and Replace?
    a) Finding and replacing data b) Sorting data c) Printing data d) Creating charts
    Answer: a
11. Why is conditional formatting useful?
    a) It highlights important data b) It deletes data c) It prints data d) It sorts data
    Answer: a
12. Why is data validation important?
    a) It prevents mistakes b) It deletes data c) It prints data d) It sorts data
    Answer: a
13. How can Flash Fill save time?
    a) It fills data based on patterns b) It deletes data c) It prints data d) It sorts data
    Answer: a
14. Why should you remove duplicates?
    a) To keep data clean b) To delete all data c) To print data d) To sort data
    Answer: a
15. How can subtotals help you?
    a) They show totals for groups b) They delete data c) They print data d) They sort data
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Conditional Formatting	A. Controls data entry
2. Data Validation	B. Changes cell appearance
3. Flash Fill	C. Automatically fills data
4. Remove Duplicates	D. Deletes repeated entries
5. Subtotal	E. A total for a group

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

Short Answer Questions

1. What is conditional formatting and how do you use it?
2. Why is data validation important?
3. How does Flash Fill save time?
4. What is the purpose of removing duplicates?
5. How do subtotals help in data analysis?

Scenario-Based Exercises

1. Scenario: You have a list of sales with many duplicate entries. What should you do?
2. Scenario: You want to highlight all sales above ₦50,000. What tool would you use?
3. Scenario: You want to ensure that only valid dates are entered. What should you set up?

Group Activity

In groups of 3-4, create a list of 20 items with some duplicates. Use the Remove Duplicates tool to clean the data. Then, use conditional formatting to highlight items that are over a certain price.

Individual Activity

Create a workbook with a list of 20 names. Use Flash Fill to split them into first and last names. Use conditional formatting to highlight names that start with the letter "A".

Classroom Discussion Questions

1. Why is it important to keep data clean?
2. How can conditional formatting help you in your daily work?
3. What would happen if you did not remove duplicates?
4. How can data validation prevent errors?
5. What is your favourite formatting tool and why?

Mini Project

Project: "My Monthly Expenses." Create a workbook with a list of your expenses for one month. Use conditional formatting to highlight expenses above ₦5,000. Remove any duplicates. Use a custom number format to show currency.

Practical Assignment

Open Excel. Create a workbook with sales data for 10 products. Use conditional formatting to highlight top sellers. Remove any duplicates. Use a custom number format for prices. Save the workbook.

Challenge Exercise

Create a workbook with data from your school or community. Use conditional formatting, data validation, and Flash Fill. Remove duplicates and calculate subtotals. Present your findings to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. Conditional
2. formats
3. validation
4. Flash
5. duplicates
6. Grouping
7. subtotal
8. Advanced
9. Columns
10. Replace

True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-T, 7-F, 8-F, 9-T, 10-T

Key Takeaways

• Conditional formatting highlights important data.
• Custom number formats display numbers your way.
• Data validation prevents entry errors.
• Flash Fill saves time by predicting patterns.
• Removing duplicates keeps data clean.
• Grouping and subtotals help summarise data.

Preparation for the Next Module

In Module Three, you will learn about Advanced Formulas and Functions. You will learn about logical functions, lookup functions, and financial functions. Keep practising what you learned in this module, and you will be ready for the next step.

---

MODULE 3

Advanced Formulas and Functions

Module Introduction

Welcome to Module Three of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about advanced formulas and functions. This is where Excel becomes really powerful.

Think of formulas like recipes. You put ingredients (numbers) together in a certain way to get a result. Functions are like pre‑made recipes that Excel gives you. You just add your ingredients and Excel does the rest.

Learning Objectives

By the end of this module, you will be able to:

• Use logical functions like IF, AND, and OR to make decisions.
• Use lookup functions like XLOOKUP and VLOOKUP to find data.
• Use date and time functions like TODAY and WEEKDAY.
• Use financial functions like PMT to calculate loan payments.
• Use statistical functions like SUMIFS and COUNTIFS.
• Use dynamic array formulas to work with multiple results.

---

Warm‑up Story

Chidi's Big Formula Adventure

Chidi works in a bank in Abuja. He has to calculate loan payments for customers every day. He was doing it manually, and it took a long time. He thought, "There must be an easier way."

He learned about the PMT function in Excel. Now, he just enters the loan amount, interest rate, and number of payments, and Excel calculates everything for him. He also learned about XLOOKUP to find customer details quickly.

Chidi became the fastest worker in his office. His boss was so impressed that he gave Chidi a promotion. Chidi learned that formulas and functions are like superpowers in Excel.

Main Lessons

Lesson 1: What is a Formula?

Definition: A formula is an equation that performs calculations on values in your workbook.

Why it is important: Formulas are the heart of Excel. They let you do math, make decisions, and analyse data.

Simple explanation: A formula is like a math problem. You type =, then the numbers and symbols. Excel solves it.

• Real‑life example: A teacher uses a formula to calculate total marks.
• School example: A student uses a formula to add up scores.
• Home example: A parent uses a formula to calculate monthly expenses.
• Nigerian example: A shop owner uses a formula to calculate total sales.

How to write a formula:

1. Click on a cell.
2. Type = (equals sign).
3. Type the numbers and symbols (e.g., =5+3).
4. Press Enter.

Lesson 2: Logical Functions – IF

Definition: The IF function checks a condition and returns one value if true and another if false.

Why it is important: It lets you make decisions based on your data.

Simple explanation: It is like a traffic light – if the light is green, you go; if it is red, you stop.

• Real‑life example: A manager uses IF to check if sales are above target.
• School example: A student uses IF to check if they passed a test.
• Home example: A parent uses IF to check if expenses are under budget.
• Nigerian example: A shop owner uses IF to check if stock is low.

How to use IF:

Example: =IF(A1>50, "Pass", "Fail")

Lesson 3: Logical Functions – AND and OR

Definition: AND checks if all conditions are true. OR checks if any condition is true.

Why it is important: They let you check multiple conditions at once.

Simple explanation: AND is like needing both your hands to clap. OR is like being able to use either hand to wave.

• Real‑life example: A manager uses AND to check if both sales and profit are high.
• School example: A student uses OR to check if they passed math or science.
• Home example: A parent uses AND to check if it is both weekend and sunny.
• Nigerian example: A shop owner uses OR to check if an item is on sale or discounted.

Lesson 4: Lookup Functions – XLOOKUP

Definition: XLOOKUP finds a value in a range and returns a corresponding value.

Why it is important: It helps you find data quickly without searching manually.

Simple explanation: It is like a librarian who can find any book you ask for.

• Real‑life example: A manager uses XLOOKUP to find a customer's phone number.
• School example: A student uses XLOOKUP to find a student's grade.
• Home example: A parent uses XLOOKUP to find a contact's address.
• Nigerian example: A shop owner uses XLOOKUP to find a product's price.

How to use XLOOKUP:

Example: =XLOOKUP("Bola", A2:A10, B2:B10)

Lesson 5: Lookup Functions – VLOOKUP

Definition: VLOOKUP finds a value in the first column of a table and returns a value from another column.

Why it is important: It is a classic lookup function that many people use.

Simple explanation: It is like looking up a word in a dictionary.

• Real‑life example: A manager uses VLOOKUP to find an employee's salary.
• School example: A student uses VLOOKUP to find a teacher's contact.
• Home example: A parent uses VLOOKUP to find a product's price.
• Nigerian example: A shop owner uses VLOOKUP to find a supplier's phone number.

How to use VLOOKUP:

Example: =VLOOKUP("Bola", A2:B10, 2, FALSE)

Lesson 6: Date and Time Functions

Definition: Date and time functions work with dates and times.

Why it is important: They help you track deadlines, schedules, and time.

• TODAY: Returns today's date. =TODAY()
• NOW: Returns the current date and time. =NOW()
• WEEKDAY: Returns the day of the week. =WEEKDAY(A1)
• WORKDAY: Returns a date after a certain number of workdays. =WORKDAY(A1, 5)

Lesson 7: Financial Functions – PMT

Definition: PMT calculates the payment for a loan based on constant payments and a constant interest rate.

Why it is important: It helps you calculate loan payments quickly.

Simple explanation: It is like a calculator for loan payments.

• Real‑life example: A bank uses PMT to calculate mortgage payments.
• School example: A student uses PMT to calculate a loan for school fees.
• Home example: A parent uses PMT to calculate a car loan payment.
• Nigerian example: A shop owner uses PMT to calculate a business loan.

How to use PMT:

Lesson 8: Statistical Functions – SUMIFS

Definition: SUMIFS adds cells that meet multiple conditions.

Why it is important: It lets you add data that meets specific criteria.

Simple explanation: It is like adding only the numbers that you want.

• Real‑life example: A manager uses SUMIFS to add sales for a specific product.
• School example: A student uses SUMIFS to add marks for a specific subject.
• Home example: A parent uses SUMIFS to add expenses for a specific category.
• Nigerian example: A shop owner uses SUMIFS to add sales for a specific region.

How to use SUMIFS:

Lesson 9: Statistical Functions – COUNTIFS

Definition: COUNTIFS counts cells that meet multiple conditions.

Why it is important: It lets you count data that meets specific criteria.

Simple explanation: It is like counting only the items you want.

• Real‑life example: A manager uses COUNTIFS to count sales for a specific product.
• School example: A student uses COUNTIFS to count scores above 80.
• Home example: A parent uses COUNTIFS to count expenses over ₦5,000.
• Nigerian example: A shop owner uses COUNTIFS to count products in stock.

How to use COUNTIFS:

Lesson 10: Dynamic Array Formulas

Definition: Dynamic array formulas return multiple results that "spill" into adjacent cells.

Why it is important: They make it easy to work with multiple results at once.

Simple explanation: It is like magic – one formula gives you many answers.

• Real‑life example: A manager uses SORT to automatically sort a list.
• School example: A student uses UNIQUE to get a list of unique values.
• Home example: A parent uses FILTER to see only specific data.
• Nigerian example: A shop owner uses SORT to sort products by price.

Common dynamic array functions:

• SORT: Sorts a range. =SORT(A2:A10)
• UNIQUE: Returns unique values. =UNIQUE(A2:A10)
• FILTER: Filters a range. =FILTER(A2:B10, B2:B10>50)

Lesson 11: Nested Functions

Definition: A nested function is a function inside another function.

Why it is important: It lets you combine functions to do more complex tasks.

Simple explanation: It is like a matryoshka doll – one function inside another.

• Real‑life example: A manager uses IF with AND to check multiple conditions.
• School example: A student uses IF with VLOOKUP to look up and check.
• Home example: A parent uses IF with SUMIFS to sum conditionally.
• Nigerian example: A shop owner uses IF with XLOOKUP to find and check.

Example: =IF(AND(A1>50, B1<10), "OK", "Check")

Lesson 12: Formula Auditing – Trace Precedents

Definition: Trace Precedents shows which cells affect the current cell.

Why it is important: It helps you understand and debug formulas.

Simple explanation: It is like following a trail of clues.

• Real‑life example: A manager traces precedents to find errors.
• School example: A student traces precedents to understand a formula.
• Home example: A parent traces precedents to check a budget.
• Nigerian example: A shop owner traces precedents to fix a sales formula.

How to trace precedents:

1. Click the cell with the formula.
2. Go to the Formulas tab.
3. Click Trace Precedents.
4. Arrows appear showing which cells affect the formula.

Key Vocabulary

Important Concepts

• Formulas are equations that do calculations.
• Functions are pre‑made formulas that save time.
• Logical functions help you make decisions.
• Lookup functions help you find data.
• Financial functions help with money calculations.
• Statistical functions help you analyse data.

Step-by-Step Explanations

How to Use IF to Check a Condition

1. Click on a cell.
2. Type =IF(.
3. Type the condition (e.g., A1>50).
4. Type , then the value if true.
5. Type , then the value if false.
6. Type ) and press Enter.

How to Use XLOOKUP

1. Click on a cell.
2. Type =XLOOKUP(.
3. Type the value to find.
4. Type , then the range to search.
5. Type , then the range to return.
6. Type ) and press Enter.

Real-Life Examples

• In a business: A manager uses IF to check if sales are above target.
• In a school: A teacher uses VLOOKUP to find student grades.
• At home: A parent uses PMT to calculate a loan payment.
• In Nigeria: A shop owner uses SUMIFS to calculate total sales by region.

Nigerian Examples

• A bank in Lagos uses PMT to calculate loan payments.
• A school in Abuja uses VLOOKUP to find student records.
• A shop in Kano uses SUMIFS to calculate sales by product.
• A business in Enugu uses XLOOKUP to find supplier details.

Fun Examples Children Can Relate To

• Using IF to check if you have enough pocket money.
• Using VLOOKUP to find a friend's phone number.
• Using SUMIFS to add up all the money you earned from chores.
• Using COUNTIFS to count how many times you played your favourite game.

Everyday Examples

• Using IF to check if your expenses are under budget.
• Using VLOOKUP to find a contact's address.
• Using PMT to calculate a car loan payment.
• Using SUMIFS to add up all your shopping expenses.

Teacher Notes

• Emphasise the importance of understanding each function.
• Show students how to use the Insert Function dialog.
• Teach students to use the Formula Auditing tools.
• Encourage students to practise with real data.

Parent Tips

• Help your child practise using formulas with simple data.
• Show them how to use VLOOKUP to find information.
• Encourage them to use PMT to calculate savings.
• Teach them to use SUMIFS to add up their expenses.

Interesting Facts

• Excel has over 475 functions.
• VLOOKUP was introduced in 1985.
• XLOOKUP is newer and more powerful than VLOOKUP.
• Dynamic arrays were introduced in Excel 365.

Did You Know?

Remember This

• Formulas start with =.
• Functions are pre‑made formulas.
• IF helps you make decisions.
• XLOOKUP and VLOOKUP help you find data.
• PMT calculates loan payments.
• SUMIFS and COUNTIFS add and count with conditions.

Common Mistakes

• Forgetting the =: Formulas must start with =.
• Using the wrong range: Make sure your ranges are correct.
• Mixing up IF, AND, and OR: Use them correctly.
• Not using absolute references: Use $ to lock cells.
• Using VLOOKUP incorrectly: Make sure the lookup value is in the first column.

Best Practices

• Always start formulas with =.
• Use named ranges to make formulas easier to read.
• Use absolute references ($) when needed.
• Test your formulas with sample data.
• Use the Formula Auditing tools to check your work.

Comparison: VLOOKUP vs XLOOKUP

Feature	VLOOKUP	XLOOKUP
Search direction	Only left to right	Any direction
Default match	Approximate	Exact
Flexibility	Less	More
Best for	Older workbooks	Newer workbooks

Comparison: SUMIFS vs COUNTIFS

Feature	SUMIFS	COUNTIFS
What it does	Adds numbers	Counts cells
Requires sum range	Yes	No
Conditions	Multiple	Multiple
Best for	Adding specific data	Counting specific data

End-of-Module Summary

Congratulations! You have completed Module Three. You now know:

• How to use logical functions like IF, AND, and OR.
• How to use lookup functions like XLOOKUP and VLOOKUP.
• How to use date and time functions.
• How to use financial functions like PMT.
• How to use statistical functions like SUMIFS and COUNTIFS.
• How to use dynamic array formulas and nested functions.

You are now ready to move on to Module Four, where you will learn about formula auditing and troubleshooting.

Frequently Asked Questions

1. What is a formula? An equation that does calculations.
2. What is a function? A pre‑made formula.
3. What does IF do? It checks a condition and returns a value.
4. What is the difference between XLOOKUP and VLOOKUP? XLOOKUP is newer and more flexible.
5. What does PMT do? It calculates loan payments.
6. What does SUMIFS do? It adds numbers that meet conditions.
7. What does COUNTIFS do? It counts cells that meet conditions.
8. What is a dynamic array formula? It returns multiple results.
9. What is a nested function? A function inside another function.
10. What is Trace Precedents? It shows which cells affect a formula.

Review Questions

1. What is a formula?
2. What does IF do?
3. What is the difference between AND and OR?
4. What does XLOOKUP do?
5. What does VLOOKUP do?
6. What does PMT do?
7. What does SUMIFS do?
8. What does COUNTIFS do?
9. What is a dynamic array formula?
10. What is a nested function?
11. What is Trace Precedents?
12. Why are lookup functions useful?
13. How can financial functions help you?
14. How can statistical functions help you?
15. What is the best practice for writing formulas?

Fill-in-the-Blank Exercises

1. A __________ is an equation that does calculations.
2. The __________ function checks a condition and returns a value.
3. __________ checks if all conditions are true.
4. __________ finds a value in a range and returns a corresponding value.
5. __________ calculates loan payments.
6. __________ adds numbers that meet conditions.
7. __________ counts cells that meet conditions.
8. A __________ array formula returns multiple results.
9. A __________ function is a function inside another function.
10. __________ Precedents shows which cells affect a formula.

True or False

1. Formulas start with =. (True)
2. IF cannot check multiple conditions. (False)
3. AND checks if all conditions are true. (True)
4. VLOOKUP is newer than XLOOKUP. (False)
5. PMT calculates loan payments. (True)
6. SUMIFS counts cells. (False)
7. COUNTIFS adds numbers. (False)
8. Dynamic array formulas return multiple results. (True)
9. A nested function is a function inside a function. (True)
10. Trace Precedents shows which cells affect a formula. (True)

Multiple Choice Questions

1. What is a formula?
   a) An equation b) A chart c) A table d) A macro
   Answer: a
2. What does IF do?
   a) Checks a condition b) Adds numbers c) Counts cells d) Finds data
   Answer: a
3. What is the difference between AND and OR?
   a) AND checks all; OR checks any b) AND checks any; OR checks all c) They are the same d) None
   Answer: a
4. What does XLOOKUP do?
   a) Finds data b) Adds numbers c) Counts cells d) Makes decisions
   Answer: a
5. What does PMT do?
   a) Calculates loan payments b) Adds numbers c) Counts cells d) Finds data
   Answer: a
6. What does SUMIFS do?
   a) Adds numbers with conditions b) Counts cells with conditions c) Finds data d) Makes decisions
   Answer: a
7. What does COUNTIFS do?
   a) Counts cells with conditions b) Adds numbers with conditions c) Finds data d) Makes decisions
   Answer: a
8. What is a dynamic array formula?
   a) Returns multiple results b) Returns one result c) Adds numbers d) Counts cells
   Answer: a
9. What is a nested function?
   a) A function inside a function b) A function that adds numbers c) A function that counts cells d) A function that finds data
   Answer: a
10. What is Trace Precedents?
    a) Shows which cells affect a formula b) Adds numbers c) Counts cells d) Finds data
    Answer: a
11. Why are lookup functions useful?
    a) They find data quickly b) They add numbers c) They count cells d) They make decisions
    Answer: a
12. How can financial functions help you?
    a) They calculate money b) They add numbers c) They count cells d) They find data
    Answer: a
13. How can statistical functions help you?
    a) They analyse data b) They find data c) They make decisions d) They calculate money
    Answer: a
14. What is the best practice for writing formulas?
    a) Start with = b) Use many functions c) Avoid testing d) Ignore errors
    Answer: a
15. What is the difference between VLOOKUP and XLOOKUP?
    a) XLOOKUP is newer b) VLOOKUP is newer c) They are the same d) None
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Formula	A. Checks a condition
2. IF	B. An equation that does calculations
3. XLOOKUP	C. Finds data in a range
4. PMT	D. Calculates loan payments
5. SUMIFS	E. Adds numbers with conditions

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

Short Answer Questions

1. What is the difference between a formula and a function?
2. How does IF work?
3. What is the difference between XLOOKUP and VLOOKUP?
4. What does PMT do and what are its arguments?
5. What is the purpose of SUMIFS and COUNTIFS?

Scenario-Based Exercises

1. Scenario: You have a list of sales. You want to check if each sale is above ₦50,000. What function would you use?
2. Scenario: You need to find a product's price from a list. What function would you use?
3. Scenario: You want to calculate a loan payment. What function would you use?

Group Activity

In groups of 3-4, create a workbook with sales data for 10 products. Use IF to check if sales are above target. Use VLOOKUP to find product details. Use SUMIFS to calculate total sales by region.

Individual Activity

Create a workbook with data about your favourite things. Use IF to make decisions. Use XLOOKUP to find information. Use SUMIFS to add up numbers.

Classroom Discussion Questions

1. Why are formulas important in Excel?
2. How can lookup functions help you in your daily life?
3. What is your favourite function and why?
4. How can financial functions help you plan for the future?
5. What is the most challenging part of using formulas?

Mini Project

Project: "My Dream Business." Create a workbook with sales data for your business. Use IF to check sales targets. Use VLOOKUP to find product details. Use PMT to calculate a loan for your business.

Practical Assignment

Open Excel. Create a workbook with sales data for 10 products. Use IF, VLOOKUP, SUMIFS, and COUNTIFS. Save the workbook and submit it.

Challenge Exercise

Create a workbook with data from your school or community. Use nested functions, dynamic arrays, and formula auditing. Present your findings to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. formula
2. IF
3. AND
4. XLOOKUP
5. PMT
6. SUMIFS
7. COUNTIFS
8. dynamic
9. nested
10. Trace

True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-F, 7-F, 8-T, 9-T, 10-T

Key Takeaways

• Formulas are equations that do calculations.
• Functions are pre‑made formulas.
• IF helps you make decisions.
• Lookup functions help you find data.
• Financial functions help with money.
• Statistical functions help analyse data.

Preparation for the Next Module

In Module Four, you will learn about Formula Auditing and Troubleshooting. You will learn how to find and fix errors in your formulas. Keep practising what you learned in this module, and you will be ready for the next step.

---

MODULE 4

Formula Auditing and Troubleshooting

Module Introduction

Welcome to Module Four of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about formula auditing and troubleshooting. Sometimes formulas do not work the way we expect. This module will teach you how to find and fix those problems.

Think of formula auditing like being a detective. You look for clues, follow the trail, and find the problem. Once you find the problem, you can fix it easily.

Learning Objectives

By the end of this module, you will be able to:

• Use Trace Precedents to see which cells affect a formula.
• Use Trace Dependents to see which cells are affected by a formula.
• Use the Watch Window to monitor cell changes.
• Use Error Checking to find and fix common errors.
• Use Evaluate Formula to step through a formula.
• Find and fix circular references.

---

Warm‑up Story

Funke's Formula Mystery

Funke works in an office in Abuja. She created a workbook with many formulas. But some of the numbers did not add up correctly. She was confused and frustrated.

Her colleague said, "Use the formula auditing tools. They will help you find the problem." Funke used Trace Precedents to see which cells were used in the formula. She found that one cell had the wrong number. She fixed it and everything worked perfectly.

Funke learned that formula auditing tools are like a detective's kit. They help you find and fix errors quickly.

Main Lessons

Lesson 1: What is Formula Auditing?

Definition: Formula auditing is a set of tools that help you understand and fix formulas.

Why it is important: It helps you find errors and understand how formulas work.

Simple explanation: It is like having a magnifying glass to examine your formulas.

• Real‑life example: A manager uses formula auditing to find errors in a budget.
• School example: A student uses formula auditing to check their homework.
• Home example: A parent uses formula auditing to check their budget.
• Nigerian example: A shop owner uses formula auditing to check sales totals.

Lesson 2: Trace Precedents

Definition: Trace Precedents shows which cells affect the current cell.

Why it is important: It helps you understand where a formula gets its data.

Simple explanation: It is like following a trail of clues to find the source.

• Real‑life example: A manager traces precedents to find errors in a formula.
• School example: A student traces precedents to understand a formula.
• Home example: A parent traces precedents to check a budget.
• Nigerian example: A shop owner traces precedents to check sales formulas.

How to trace precedents:

1. Click the cell with the formula.
2. Go to the Formulas tab.
3. Click Trace Precedents.
4. Arrows appear showing which cells affect the formula.

Lesson 3: Trace Dependents

Definition: Trace Dependents shows which cells are affected by the current cell.

Why it is important: It helps you see the impact of a cell on other formulas.

Simple explanation: It is like seeing who is affected by your actions.

• Real‑life example: A manager traces dependents to see which formulas use a cell.
• School example: A student traces dependents to see which formulas use their data.
• Home example: A parent traces dependents to see which formulas use their budget.
• Nigerian example: A shop owner traces dependents to see which formulas use a price.

How to trace dependents:

1. Click the cell.
2. Go to the Formulas tab.
3. Click Trace Dependents.
4. Arrows appear showing which cells use the current cell.

Lesson 4: Watch Window

Definition: The Watch Window lets you monitor specific cells as you make changes.

Why it is important: It helps you see how changes affect important cells.

Simple explanation: It is like watching your favourite shows on TV.

• Real‑life example: A manager watches key cells while updating a budget.
• School example: A student watches cells while working on a project.
• Home example: A parent watches cells while managing expenses.
• Nigerian example: A shop owner watches cells while updating prices.

How to use the Watch Window:

1. Go to the Formulas tab.
2. Click Watch Window.
3. Click Add Watch.
4. Select the cell you want to watch.
5. Click Add.

Lesson 5: Error Checking

Definition: Error Checking finds common errors in your formulas and suggests fixes.

Why it is important: It saves time by automatically finding errors.

Simple explanation: It is like a spell checker for your formulas.

• Real‑life example: A manager uses error checking to find formula errors.
• School example: A student uses error checking to find mistakes.
• Home example: A parent uses error checking to find budget errors.
• Nigerian example: A shop owner uses error checking to find sales errors.

How to use error checking:

1. Go to the Formulas tab.
2. Click Error Checking.
3. Excel will show any errors it finds.
4. Follow the suggestions to fix the errors.

Lesson 6: Common Excel Errors

Definition: Common errors are messages that appear when something is wrong with a formula.

Why it is important: Understanding errors helps you fix them quickly.

• #DIV/0! – Dividing by zero. Check your divisor.
• #N/A – Value not available. Check your lookup value.
• #NAME? – Excel does not recognise the name. Check spelling.
• #REF! – Cell reference is invalid. Check your ranges.
• #VALUE! – Wrong type of value. Check your data.
• #NUM! – Number problem. Check your numbers.
• #NULL! – Space between ranges. Check your ranges.

Lesson 7: Evaluate Formula

Definition: Evaluate Formula steps through a formula one part at a time.

Why it is important: It helps you see how a formula calculates its result.

Simple explanation: It is like watching a math problem being solved step by step.

• Real‑life example: A manager evaluates a formula to understand it.
• School example: A student evaluates a formula to learn how it works.
• Home example: A parent evaluates a formula to check a budget.
• Nigerian example: A shop owner evaluates a formula to check sales.

How to evaluate a formula:

1. Click the cell with the formula.
2. Go to the Formulas tab.
3. Click Evaluate Formula.
4. Click Evaluate to step through the formula.

Lesson 8: Circular References

Definition: A circular reference occurs when a formula refers to itself.

Why it is important: It can cause incorrect calculations or endless loops.

Simple explanation: It is like saying, "I am lying" – it creates a loop that never ends.

• Real‑life example: A formula in A1 = A1 + 1 creates a circular reference.
• School example: A student creates a formula that refers to itself by mistake.
• Home example: A parent creates a formula that refers to itself.
• Nigerian example: A shop owner creates a formula that refers to itself.

How to fix a circular reference:

1. Excel will show a warning.
2. Use the Circular References menu in the Formulas tab to find the cell.
3. Change the formula so it does not refer to itself.

Lesson 9: Removing Arrows

Definition: Removing arrows clears the tracing lines from your worksheet.

Why it is important: It cleans up your worksheet after using trace tools.

Simple explanation: It is like erasing pencil lines after you have finished drawing.

How to remove arrows:

1. Go to the Formulas tab.
2. Click Remove Arrows.

Lesson 10: Checking for Errors

Definition: Checking for errors is a way to find problems before they cause trouble.

Why it is important: It helps ensure your data is accurate.

Simple explanation: It is like proofreading your work before handing it in.

• Real‑life example: A manager checks for errors before sharing a report.
• School example: A student checks for errors before submitting homework.
• Home example: A parent checks for errors before paying bills.
• Nigerian example: A shop owner checks for errors before sending invoices.

How to check for errors:

1. Go to the Formulas tab.
2. Click Error Checking.
3. Review any errors that are found.

Lesson 11: Formula Auditing Best Practices

Definition: Best practices are guidelines for using formula auditing effectively.

• Trace Precedents regularly: Check your formulas often.
• Use the Watch Window: Monitor important cells.
• Evaluate complex formulas: Step through them to understand them.
• Check for circular references: Fix them immediately.
• Remove arrows when done: Keep your worksheet clean.

Key Vocabulary

Important Concepts

• Trace Precedents shows you where data comes from.
• Trace Dependents shows you where data goes.
• The Watch Window helps you monitor key cells.
• Error Checking finds and fixes common errors.
• Evaluate Formula helps you understand complex formulas.
• Circular references must be fixed immediately.

Step-by-Step Explanations

How to Trace Precedents

1. Click the cell with the formula.
2. Go to the Formulas tab.
3. Click Trace Precedents.
4. Arrows appear showing which cells affect the formula.
5. Click Remove Arrows to clear them.

How to Use the Watch Window

1. Go to the Formulas tab.
2. Click Watch Window.
3. Click Add Watch.
4. Select the cell you want to watch.
5. Click Add.

Real-Life Examples

• In a business: A manager traces precedents to find errors in a budget.
• In a school: A teacher uses error checking to find mistakes in student work.
• At home: A parent uses the Watch Window to monitor expenses.
• In Nigeria: A shop owner uses formula auditing to check sales totals.

Nigerian Examples

• A business in Lagos uses Trace Precedents to check sales formulas.
• A school in Abuja uses Error Checking to find mistakes in student grades.
• A shop in Kano uses the Watch Window to monitor inventory.
• A bank in Enugu uses Evaluate Formula to check loan calculations.

Fun Examples Children Can Relate To

• Using Trace Precedents to see where your allowance comes from.
• Using Error Checking to find mistakes in a game score.
• Using the Watch Window to monitor your favourite toy's price.
• Using Evaluate Formula to understand a math problem.

Everyday Examples

• Using Trace Precedents to check a budget formula.
• Using Error Checking to find mistakes in a grocery list.
• Using the Watch Window to monitor your bank balance.
• Using Evaluate Formula to understand a complex calculation.

Teacher Notes

• Emphasise the importance of checking formulas for errors.
• Show students how to use each auditing tool.
• Teach students to understand common error messages.
• Encourage students to use the Watch Window for monitoring.

Parent Tips

• Help your child check their formulas for errors.
• Show them how to use Trace Precedents and Dependents.
• Encourage them to use the Watch Window to monitor important cells.
• Teach them to fix circular references.

Interesting Facts

• Error Checking was introduced in Excel 2002.
• The Watch Window was introduced in Excel 2003.
• Trace Precedents was introduced in Excel 97.
• Excel can have multiple circular references in one workbook.

Did You Know?

Remember This

• Trace Precedents shows where data comes from.
• Trace Dependents shows where data goes.
• The Watch Window monitors important cells.
• Error Checking finds common errors.
• Evaluate Formula steps through a calculation.
• Circular references must be fixed.

Common Mistakes

• Ignoring error messages: Always fix errors when they appear.
• Not removing arrows: Remove arrows after tracing to keep your worksheet clean.
• Not using the Watch Window: It is a powerful tool for monitoring.
• Not evaluating formulas: Evaluating helps you understand complex formulas.
• Leaving circular references: Always fix them immediately.

Best Practices

• Trace Precedents regularly to check your formulas.
• Use the Watch Window to monitor important cells.
• Evaluate complex formulas to understand them.
• Check for circular references and fix them.
• Remove arrows after using trace tools.

Comparison: Trace Precedents vs Trace Dependents

Feature	Trace Precedents	Trace Dependents
Shows	Which cells affect the formula	Which cells are affected by the formula
Direction	Into the formula	Out of the formula
Best for	Finding where data comes from	Finding what data affects

Comparison: Error Checking vs Evaluate Formula

Feature	Error Checking	Evaluate Formula
Purpose	Find errors	Understand the formula
Shows	Error messages	Step-by-step calculation
Best for	Finding mistakes	Learning how a formula works

End-of-Module Summary

Congratulations! You have completed Module Four. You now know:

• How to use Trace Precedents to see where data comes from.
• How to use Trace Dependents to see where data goes.
• How to use the Watch Window to monitor important cells.
• How to use Error Checking to find and fix common errors.
• How to use Evaluate Formula to understand formulas.
• How to find and fix circular references.

You are now ready to move on to Module Five, where you will learn about macros and automation.

Frequently Asked Questions

1. What is Trace Precedents? It shows which cells affect a formula.
2. What is Trace Dependents? It shows which cells are affected by a formula.
3. What is the Watch Window? It monitors specific cells.
4. What is Error Checking? It finds common formula errors.
5. What is Evaluate Formula? It steps through a formula.
6. What is a circular reference? A formula that refers to itself.
7. How do I remove arrows? Click Remove Arrows in the Formulas tab.
8. What does #DIV/0! mean? Dividing by zero.
9. What does #N/A mean? Value not available.
10. What does #REF! mean? Invalid cell reference.

Review Questions

1. What is formula auditing?
2. What does Trace Precedents do?
3. What does Trace Dependents do?
4. What is the Watch Window used for?
5. What is Error Checking?
6. What is Evaluate Formula?
7. What is a circular reference?
8. How do you remove arrows?
9. What does #DIV/0! mean?
10. What does #N/A mean?
11. What does #REF! mean?
12. Why is it important to check for errors?
13. How can the Watch Window help you?
14. What is the best practice for formula auditing?
15. How do you fix a circular reference?

Fill-in-the-Blank Exercises

1. __________ shows which cells affect a formula.
2. __________ shows which cells are affected by a formula.
3. The __________ Window monitors specific cells.
4. __________ Checking finds common formula errors.
5. __________ Formula steps through a formula.
6. A __________ reference is a formula that refers to itself.
7. __________ Arrows clears tracing lines.
8. #DIV/0! means dividing by __________.
9. #N/A means value not __________.
10. #REF! means invalid __________ reference.

True or False

1. Trace Precedents shows which cells affect a formula. (True)
2. Trace Dependents shows which cells are affected by a formula. (True)
3. The Watch Window is used to add pictures. (False)
4. Error Checking finds common formula errors. (True)
5. Evaluate Formula steps through a formula. (True)
6. A circular reference is a formula that refers to itself. (True)
7. Remove Arrows adds arrows. (False)
8. #DIV/0! means dividing by one. (False)
9. #N/A means value not available. (True)
10. #REF! means invalid cell reference. (True)

Multiple Choice Questions

1. What does Trace Precedents show?
   a) Which cells affect a formula b) Which cells are affected c) Errors d) Charts
   Answer: a
2. What does Trace Dependents show?
   a) Which cells affect a formula b) Which cells are affected c) Errors d) Charts
   Answer: b
3. What is the Watch Window used for?
   a) Monitoring cells b) Adding pictures c) Creating charts d) Printing
   Answer: a
4. What does Error Checking do?
   a) Finds errors b) Adds pictures c) Creates charts d) Prints
   Answer: a
5. What does Evaluate Formula do?
   a) Steps through a formula b) Adds pictures c) Creates charts d) Prints
   Answer: a
6. What is a circular reference?
   a) A formula that refers to itself b) A formula that adds numbers c) A formula that creates charts d) A formula that prints
   Answer: a
7. How do you remove arrows?
   a) Remove Arrows b) Add Arrows c) Clear All d) Delete
   Answer: a
8. What does #DIV/0! mean?
   a) Dividing by zero b) Dividing by one c) Adding zero d) Subtracting zero
   Answer: a
9. What does #N/A mean?
   a) Value not available b) Value available c) Number available d) Number not available
   Answer: a
10. What does #REF! mean?
    a) Invalid cell reference b) Valid cell reference c) Cell reference d) Number reference
    Answer: a
11. Why is it important to check for errors?
    a) To ensure accuracy b) To add pictures c) To create charts d) To print
    Answer: a
12. How can the Watch Window help you?
    a) By monitoring important cells b) By adding pictures c) By creating charts d) By printing
    Answer: a
13. What is the best practice for formula auditing?
    a) Trace Precedents regularly b) Ignore errors c) Never use tools d) Only use one tool
    Answer: a
14. How do you fix a circular reference?
    a) Change the formula b) Add a picture c) Create a chart d) Print
    Answer: a
15. What is the purpose of the Evaluate Formula tool?
    a) To understand how a formula works b) To add pictures c) To create charts d) To print
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Trace Precedents	A. Monitors specific cells
2. Trace Dependents	B. Shows which cells affect a formula
3. Watch Window	C. Shows which cells are affected
4. Error Checking	D. Steps through a formula
5. Evaluate Formula	E. Finds common errors

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

Short Answer Questions

1. What is the difference between Trace Precedents and Trace Dependents?
2. How does the Watch Window help you?
3. What is a circular reference and why is it a problem?
4. What does Error Checking do?
5. How does Evaluate Formula help you understand formulas?

Scenario-Based Exercises

1. Scenario: You have a formula that is not giving the correct result. What tool would you use to see which cells affect the formula?
2. Scenario: You want to see how changing one cell affects other cells. What tool would you use?
3. Scenario: You have a circular reference warning. What should you do?

Group Activity

In groups of 3-4, create a workbook with several formulas. Use Trace Precedents and Trace Dependents to understand how they work. Add a circular reference intentionally and fix it. Use the Watch Window to monitor important cells.

Individual Activity

Create a workbook with 5 formulas. Use Trace Precedents to check them. Use Error Checking to find any errors. Use Evaluate Formula to understand one of the formulas.

Classroom Discussion Questions

1. Why is it important to check formulas for errors?
2. How can formula auditing tools help you in your daily work?
3. What is the most common error you have encountered in Excel?
4. How does the Watch Window help you monitor data?
5. What is the best way to fix a circular reference?

Mini Project

Project: "My Budget Analysis." Create a workbook with a budget and formulas. Use Trace Precedents and Trace Dependents to check the formulas. Use the Watch Window to monitor key cells. Fix any errors you find.

Practical Assignment

Open Excel. Create a workbook with sales data and formulas. Use Trace Precedents and Trace Dependents to check the formulas. Use Error Checking to find and fix any errors. Save the workbook.

Challenge Exercise

Create a workbook with complex formulas. Introduce intentional errors. Use formula auditing tools to find and fix all the errors. Present your findings to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. Trace Precedents
2. Trace Dependents
3. Watch
4. Error
5. Evaluate
6. circular
7. Remove
8. zero
9. available
10. cell

True or False Answers: 1-T, 2-T, 3-F, 4-T, 5-T, 6-T, 7-F, 8-F, 9-T, 10-T

Key Takeaways

• Trace Precedents shows where data comes from.
• Trace Dependents shows where data goes.
• The Watch Window monitors important cells.
• Error Checking finds and fixes common errors.
• Evaluate Formula helps you understand formulas.
• Circular references must be fixed immediately.

Preparation for the Next Module

In Module Five, you will learn about Macros and Automation. You will learn how to record and run macros to automate repetitive tasks. Keep practising what you learned in this module, and you will be ready for the next step.

---

MODULE 5

Macros and Automation

Module Introduction

Welcome to Module Five of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about macros and automation. Macros are like magic spells that make Excel do work for you.

Think about all the tasks you do over and over again in Excel. What if Excel could do them for you with just one click? That is exactly what macros do. They record your actions and play them back whenever you need.

Learning Objectives

By the end of this module, you will be able to:

• Record a macro that automates repetitive tasks.
• Run a macro with a button or shortcut key.
• Save a workbook as a macro‑enabled file (.xlsm).
• Understand macro security and how to enable macros safely.
• Edit a macro in the Visual Basic for Applications (VBA) editor.
• Create a macro for a common task like formatting or reporting.
• Assign a macro to a button or shape for easy access.

---

Warm‑up Story

Kemi's Time‑Saving Magic

Kemi works in an accounting office in Lagos. Every month, she has to create a report with the same format: a title, a table, and a chart. It took her 20 minutes every time.

One day, her colleague said, "Why don't you record a macro? It will do all that work for you with one click!" Kemi did not know what a macro was. Her colleague showed her: she clicked View → Macros → Record Macro, then did all the steps she normally did. When she finished, she clicked Stop Recording.

Now, whenever Kemi needs to create a report, she just runs the macro. It automatically creates the title, the table, and the chart in seconds. She saves hours every month. Kemi learned that macros are like having a personal assistant in Excel.

Main Lessons

Lesson 1: What is a Macro?

Definition: A macro is a set of instructions that automates repetitive tasks in Excel.

Why it is important: Macros save time and reduce errors by doing the work for you.

Simple explanation: It is like recording a video of yourself doing a task, then playing it back whenever you want.

• Real‑life example: A manager records a macro to format a monthly report.
• School example: A student records a macro to add a header to every page.
• Home example: A parent records a macro to format a budget.
• Nigerian example: A shop owner records a macro to create a sales report.

Lesson 2: Recording a Macro

Definition: Recording a macro means telling Excel to watch what you do and save it as a macro.

Why it is important: It is the easiest way to create a macro without writing code.

Simple explanation: It is like pressing "record" on a video camera.

• Real‑life example: A manager records a macro to create a weekly report.
• School example: A student records a macro to format a project.
• Home example: A parent records a macro to organize a budget.
• Nigerian example: A shop owner records a macro to print receipts.

How to record a macro:

1. Go to the View tab.
2. Click the arrow under Macros.
3. Choose Record Macro.
4. Give the macro a name (no spaces).
5. Choose where to store it (This Workbook or Personal Macro Workbook).
6. Optionally, assign a shortcut key (like Ctrl+Shift+M).
7. Click OK.
8. Perform the actions you want to record.
9. Click View → Macros → Stop Recording.

Lesson 3: Running a Macro

Definition: Running a macro means playing back the recorded actions.

Why it is important: It is how you use the macro to do your work.

Simple explanation: It is like pressing "play" on a video.

• Real‑life example: A manager runs a macro to create a monthly report.
• School example: A student runs a macro to format a project.
• Home example: A parent runs a macro to update a budget.
• Nigerian example: A shop owner runs a macro to generate sales data.

How to run a macro:

1. Go to the View tab.
2. Click Macros.
3. Select the macro from the list.
4. Click Run.

Lesson 4: Macro-Enabled Workbooks

Definition: A macro‑enabled workbook is an Excel file that can contain macros. It has the extension .xlsm.

Why it is important: Normal workbooks (.xlsx) cannot store macros. You need to save as .xlsm.

Simple explanation: It is like a special container that can hold macros.

• Real‑life example: A manager saves a report as .xlsm to keep the macros.
• School example: A student saves a project as .xlsm.
• Home example: A parent saves a budget as .xlsm.
• Nigerian example: A shop owner saves a sales workbook as .xlsm.

How to save as macro‑enabled:

1. Click File.
2. Click Save As.
3. Choose a location.
4. In Save as type, choose Excel Macro-Enabled Workbook (*.xlsm).
5. Click Save.

Lesson 5: Macro Security

Definition: Macro security is a setting that controls whether macros can run on your computer.

Why it is important: Macros can sometimes contain harmful code. Security protects your computer.

Simple explanation: It is like a lock on your door. It keeps out things that might be dangerous.

• Real‑life example: A company enables macros only from trusted sources.
• School example: A student enables macros only for their own workbooks.
• Home example: A parent enables macros only from trusted files.
• Nigerian example: A shop owner enables macros only for their own files.

How to change macro security:

1. Click File.
2. Click Options.
3. Click Trust Center.
4. Click Trust Center Settings.
5. Click Macro Settings.
6. Choose Disable all macros with notification (safe) or Enable all macros (if you trust the source).
7. Click OK.

Lesson 6: The Personal Macro Workbook

Definition: The Personal Macro Workbook is a hidden workbook that stores macros you want to use in any Excel file.

Why it is important: It lets you use your macros in any workbook, not just the one where you recorded them.

Simple explanation: It is like a toolbox that you can carry to any job.

• Real‑life example: A manager stores formatting macros in the Personal Macro Workbook.
• School example: A student stores common macros for projects.
• Home example: A parent stores budget macros.
• Nigerian example: A shop owner stores sales macros.

How to save a macro to the Personal Macro Workbook:

1. When recording a macro, choose Personal Macro Workbook in the Store macro in dropdown.
2. The workbook is hidden. To unhide it, go to View → Unhide.

Lesson 7: Assigning a Macro to a Button

Definition: Assigning a macro to a button means you can run the macro by clicking a button on your worksheet.

Why it is important: It makes running macros even easier and more user‑friendly.

Simple explanation: It is like having a remote control for your macro.

• Real‑life example: A manager adds a "Run Report" button to a dashboard.
• School example: A student adds a "Format" button to a project.
• Home example: A parent adds a "Update Budget" button.
• Nigerian example: A shop owner adds a "Print Sales" button.

How to add a button:

1. Go to the Developer tab. (If you do not see it, go to File → Options → Customize Ribbon and check Developer.)
2. Click Insert and choose a button (Form Control).
3. Draw the button on your worksheet.
4. In the dialog box, select the macro you want to assign.
5. Click OK.

Lesson 8: Editing a Macro in VBA

Definition: VBA (Visual Basic for Applications) is the programming language behind macros. You can edit macros in the VBA editor.

Why it is important: Editing macros lets you change them or make them more powerful.

Simple explanation: It is like editing a recipe to make it better.

• Real‑life example: A manager edits a macro to add new features.
• School example: A student edits a macro to change formatting.
• Home example: A parent edits a macro to fix a bug.
• Nigerian example: A shop owner edits a macro to change calculations.

How to edit a macro:

1. Go to the Developer tab.
2. Click Macros.
3. Select the macro and click Edit.
4. The VBA editor opens. Make your changes.
5. Close the editor and save your workbook.

Lesson 9: Creating a Macro for a Common Task

Definition: A common task is something you do often, like formatting a report or creating a chart.

Why it is important: Automating common tasks saves the most time.

Simple explanation: It is like having a robot that does your chores.

• Real‑life example: A manager creates a macro to format a monthly sales report.
• School example: A student creates a macro to add a header to all pages.
• Home example: A parent creates a macro to update a budget.
• Nigerian example: A shop owner creates a macro to calculate daily sales.

Example macro idea:

• Format a title: bold, 16pt, centre.
• Add a table with borders.
• Create a chart from selected data.

Lesson 10: Using Relative References in Macros

Definition: Relative references in macros mean the macro records actions relative to the active cell.

Why it is important: It makes macros more flexible because they work from anywhere.

Simple explanation: It is like giving directions that start from where you are, not from a fixed point.

• Real‑life example: A macro that formats the row below the active cell.
• School example: A macro that adds a number to the cell to the right.
• Home example: A macro that enters today's date in the next cell.
• Nigerian example: A macro that calculates a discount on the row below.

How to use relative references:

1. Go to the Developer tab.
2. Click Use Relative References before recording.
3. Now, when you record, the macro will use relative references.

Lesson 11: Macro Best Practices

Definition: Best practices are guidelines for creating effective macros.

• Plan before recording: Know what steps you will take.
• Use descriptive names: Name your macros clearly.
• Test your macros: Run them to make sure they work.
• Comment your code: Add notes to explain what the macro does.
• Keep macros simple: Break complex tasks into smaller macros.

Key Vocabulary

Important Concepts

• Macros automate repetitive tasks and save time.
• Recording is the easiest way to create a macro.
• Save as .xlsm to keep your macros.
• Macro security protects your computer from harmful code.
• Buttons make macros easy to run.
• Relative references make macros flexible.

Step-by-Step Explanations

How to Record and Run a Simple Macro

1. Go to View → Macros → Record Macro.
2. Name it FormatReport.
3. Click OK.
4. Select the title cell and make it bold and centre.
5. Select the data and add borders.
6. Click View → Macros → Stop Recording.
7. To run it, go to View → Macros → select FormatReport → Run.

Start Recording
      |
      V
Format Title (Bold, Centre)
      |
      V
Add Borders to Data
      |
      V
Stop Recording
      |
      V
Run Macro to Apply Formatting!
    

How to Add a Button to Run a Macro

1. Enable the Developer tab (File → Options → Customize Ribbon).
2. Go to Developer → Insert → Button (Form Control).
3. Draw the button on your worksheet.
4. Select the macro you want to assign.
5. Click OK.
6. Click the button to run the macro.

Real-Life Examples

• In a business: A manager records a macro to format a monthly sales report.
• In a school: A teacher records a macro to grade student work.
• At home: A parent records a macro to update a budget.
• In Nigeria: A shop owner records a macro to generate daily sales reports.

Nigerian Examples

• A bank in Lagos uses macros to format loan reports.
• A school in Abuja uses macros to grade student assignments.
• A shop in Kano uses macros to calculate daily sales.
• A business in Enugu uses macros to generate invoices.

Fun Examples Children Can Relate To

• Recording a macro to format a homework sheet.
• Creating a macro to add your name to every page.
• Using a button to run a "clean up" macro for your desk.
• Making a macro that creates a to‑do list.

Everyday Examples

• Recording a macro to format a weekly shopping list.
• Creating a macro to add a header to all pages.
• Using a macro to update a budget.
• Making a macro that creates a chart.

Teacher Notes

• Emphasise that macros are safe if used correctly.
• Show students how to record and run macros.
• Teach students to save as .xlsm.
• Encourage students to create macros for their own tasks.

Parent Tips

• Help your child record a macro for a common task.
• Show them how to save as .xlsm.
• Encourage them to use macros to save time.
• Teach them about macro security.

Interesting Facts

• Macros were introduced in Excel 5.0 in 1993.
• VBA was introduced in Excel 5.0 in 1993.
• The Personal Macro Workbook is stored in a folder called XLSTART.
• You can have more than 100 macros in a workbook.

Did You Know?

Remember This

• Macros automate repetitive tasks.
• Recording is the easiest way to create macros.
• Save as .xlsm to keep macros.
• Enable macros only from trusted sources.
• Buttons make macros easy to run.

Common Mistakes

• Forgetting to save as .xlsm: You will lose your macros.
• Not enabling macros: They will not run.
• Recording too many actions: Keep macros focused.
• Not testing macros: Always test them.
• Ignoring macro security: Only enable macros from trusted sources.

Best Practices

• Plan your macro before recording.
• Use descriptive names.
• Test your macros.
• Use relative references for flexibility.
• Store reusable macros in the Personal Macro Workbook.

Comparison: .xlsx vs .xlsm

Feature	.xlsx	.xlsm
Can store macros?	No	Yes
Security	Safer	Potentially risky
File size	Smaller	Larger
Best for	Regular workbooks	Workbooks with macros

Comparison: Absolute vs Relative References

Feature	Absolute	Relative
Records from	Fixed cells	Active cell
Flexibility	Less	More
Best for	Specific ranges	Flexible positions

End-of-Module Summary

Congratulations! You have completed Module Five. You now know:

• What macros are and how they automate tasks.
• How to record and run macros.
• How to save workbooks as macro‑enabled (.xlsm).
• How to enable macros securely.
• How to use the Personal Macro Workbook.
• How to assign macros to buttons.
• How to edit macros in VBA.

You are now ready to move on to Module Six, where you will learn about advanced charts and visuals.

Frequently Asked Questions

1. What is a macro? A set of instructions that automates tasks.
2. How do I record a macro? View → Macros → Record Macro.
3. How do I run a macro? View → Macros → select macro → Run.
4. What is .xlsm? A macro‑enabled workbook.
5. What is VBA? Visual Basic for Applications – the macro language.
6. What is the Personal Macro Workbook? A hidden workbook for personal macros.
7. How do I add a button? Developer → Insert → Button.
8. What are relative references? Actions recorded relative to the active cell.
9. Why is macro security important? It protects your computer.
10. Can I edit a macro? Yes, in the VBA editor.

Review Questions

1. What is a macro?
2. How do you record a macro?
3. How do you run a macro?
4. What is the file extension for macro‑enabled workbooks?
5. What is VBA?
6. What is the Personal Macro Workbook?
7. How do you add a button to run a macro?
8. What are relative references in macros?
9. Why is macro security important?
10. How do you edit a macro?
11. What is the difference between .xlsx and .xlsm?
12. What is the Developer tab?
13. How can you make a macro available in all workbooks?
14. What is a common use for macros?
15. What is the best practice for naming macros?

Fill-in-the-Blank Exercises

1. A __________ is a set of instructions that automates tasks.
2. To record a macro, go to __________ → Macros → Record Macro.
3. Macro‑enabled workbooks have the extension __________.
4. VBA stands for __________ for Applications.
5. The __________ Macro Workbook stores macros for use in any file.
6. A __________ is a control that runs a macro when clicked.
7. __________ references record actions relative to the active cell.
8. __________ security controls whether macros can run.
9. You edit macros in the __________ editor.
10. Macros help you __________ repetitive tasks.

True or False

1. Macros automate repetitive tasks. (True)
2. You cannot edit a macro after recording it. (False)
3. .xlsm files can store macros. (True)
4. VBA is the language used for macros. (True)
5. The Personal Macro Workbook is visible by default. (False)
6. You can assign a macro to a button. (True)
7. Relative references are not useful in macros. (False)
8. Macro security is not important. (False)
9. You can record a macro with a shortcut key. (True)
10. Macros can only be used in one workbook. (False)

Multiple Choice Questions

1. What is a macro?
   a) A set of instructions b) A chart c) A table d) A picture
   Answer: a
2. How do you record a macro?
   a) View → Macros → Record Macro b) Insert → Macro c) Home → Macro d) Data → Macro
   Answer: a
3. What is the file extension for macro‑enabled workbooks?
   a) .xlsx b) .xlsm c) .xls d) .csv
   Answer: b
4. What is VBA?
   a) Visual Basic for Applications b) Very Basic Access c) Visual Basic Analysis d) View Basic Application
   Answer: a
5. What is the Personal Macro Workbook?
   a) A workbook for personal macros b) A hidden workbook c) Both a and b d) None
   Answer: c
6. How do you add a button?
   a) Developer → Insert → Button b) Home → Button c) Insert → Button d) View → Button
   Answer: a
7. What are relative references?
   a) Actions relative to active cell b) Fixed actions c) Chart references d) Table references
   Answer: a
8. Why is macro security important?
   a) Protects your computer b) Makes macros faster c) Both a and b d) None
   Answer: a
9. How do you edit a macro?
   a) Developer → Macros → Edit b) Home → Edit c) Insert → Edit d) View → Edit
   Answer: a
10. What is the difference between .xlsx and .xlsm?
    a) .xlsm stores macros b) .xlsx stores macros c) No difference d) .xlsm is older
    Answer: a
11. What is the Developer tab?
    a) A tab for macro tools b) A tab for charts c) A tab for tables d) A tab for pictures
    Answer: a
12. How can you make a macro available in all workbooks?
    a) Store it in Personal Macro Workbook b) Save as .xlsx c) Use absolute references d) Add a button
    Answer: a
13. What is a common use for macros?
    a) Formatting reports b) Creating charts c) Updating data d) All of the above
    Answer: d
14. What is the best practice for naming macros?
    a) Use descriptive names b) Use short names c) Use numbers d) Use special characters
    Answer: a
15. What is the purpose of a button in Excel?
    a) To run a macro b) To add a picture c) To insert a chart d) To create a table
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Macro	A. File extension for macro workbooks
2. .xlsm	B. The language used for macros
3. VBA	C. A set of instructions that automates tasks
4. Personal Macro Workbook	D. A control that runs a macro
5. Button	E. Stores macros for use in any file

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

Short Answer Questions

1. What is a macro and why is it useful?
2. How do you record a macro?
3. What is the difference between .xlsx and .xlsm?
4. What is the Personal Macro Workbook and how do you use it?
5. How do you add a button to run a macro?

Scenario-Based Exercises

1. Scenario: You create the same monthly report every month. What can you do to save time?
2. Scenario: You have recorded a macro but it only works on one worksheet. How can you make it work on any worksheet?
3. Scenario: You want to make a macro available in all your workbooks. What should you do?

Group Activity

In groups of 3-4, create a macro that formats a sales report. Include formatting a title, adding borders, and creating a chart. Add a button to run the macro. Present your macro to the class.

Individual Activity

Create a macro that formats a weekly budget. Include a title, a table with borders, and a chart. Save the workbook as .xlsm. Add a button to run the macro.

Classroom Discussion Questions

1. How can macros save you time in your daily work?
2. What are some tasks you would automate with a macro?
3. Why is it important to understand macro security?
4. How does the Personal Macro Workbook help you?
5. What is the most useful macro you can think of?

Mini Project

Project: "My Monthly Report Automation." Create a macro that generates a monthly sales report. Include a title, a table, and a chart. Save it as .xlsm. Add a button to run the macro. Present your project to the class.

Practical Assignment

Open Excel. Create a macro that formats a list of names. Include bold formatting, borders, and a header. Save the workbook as .xlsm. Add a button to run the macro.

Challenge Exercise

Create a macro that imports data from another workbook, formats it, and creates a chart. Use relative references so the macro works anywhere. Add a button to run the macro. Save it as .xlsm.

Quiz Answers

Fill-in-the-Blank Answers:

1. macro
2. View
3. .xlsm
4. Visual Basic
5. Personal
6. button
7. Relative
8. Macro
9. VBA
10. automate

True or False Answers: 1-T, 2-F, 3-T, 4-T, 5-F, 6-T, 7-F, 8-F, 9-T, 10-F

Key Takeaways

• Macros automate repetitive tasks and save time.
• Recording is the easiest way to create a macro.
• Save as .xlsm to keep your macros.
• Macro security protects your computer.
• Buttons make macros easy to run.
• Relative references make macros flexible.

Preparation for the Next Module

In Module Six, you will learn about Advanced Charts and Visuals. You will learn how to create professional charts like dual‑axis, waterfall, and histogram charts. Keep practising what you learned in this module, and you will be ready for the next step.

---

MODULE 6

Advanced Charts and Visuals

Module Introduction

Welcome to Module Six of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about advanced charts and visuals. Charts are like pictures of your data. They make numbers easy to understand.

You already know about bar charts and pie charts. Now you will learn about dual‑axis charts, waterfall charts, histograms, funnel charts, and more. These charts help you tell a story with your data.

Learning Objectives

By the end of this module, you will be able to:

• Create dual‑axis (combo) charts.
• Create advanced charts like Waterfall, Histogram, Funnel, Box & Whisker, Map, and Sunburst.
• Use Sparklines to show trends in a single cell.
• Create forecast charts to predict future values.
• Create PivotCharts for interactive analysis.
• Apply advanced chart formatting.

---

Warm‑up Story

Chidi's Chart Adventure

Chidi works in a marketing office in Abuja. He had a lot of sales data, but it was hard to see the story. He created a simple bar chart, but it did not show everything.

His manager said, "Can you create a chart that shows both sales and profit together?" Chidi learned about dual‑axis charts. He created a chart that showed sales as bars and profit as a line. It was perfect.

He also learned about waterfall charts to show how costs added up. He used histogram charts to see how many sales were in each price range. Chidi became the chart expert in his office.

Main Lessons

Lesson 1: What are Advanced Charts?

Definition: Advanced charts are special chart types that help you tell a deeper story with your data.

Why it is important: They show patterns and insights that simple charts cannot.

Simple explanation: It is like using a special camera to see things you cannot see with a normal camera.

• Real‑life example: A manager uses a waterfall chart to show how costs add up.
• School example: A student uses a histogram to show test scores.
• Home example: A parent uses a funnel chart to show sales stages.
• Nigerian example: A shop owner uses a map chart to show sales by state.

Lesson 2: Dual‑Axis Charts (Combo Charts)

Definition: A dual‑axis chart combines two chart types (like bars and lines) with two different axes.

Why it is important: It shows two types of data on one chart, like sales and profit.

Simple explanation: It is like having two rulers on the same page – one for sales and one for profit.

• Real‑life example: A manager shows sales as bars and profit as a line.
• School example: A student shows temperature and rainfall on one chart.
• Home example: A parent shows income and expenses on one chart.
• Nigerian example: A shop owner shows price and quantity on one chart.

How to create a dual‑axis chart:

1. Select your data.
2. Go to Insert → Combo Chart.
3. Choose Create Custom Combo Chart.
4. For one data series, check Secondary Axis.
5. Click OK.

Lesson 3: Waterfall Charts

Definition: A waterfall chart shows how an initial value increases or decreases step by step.

Why it is important: It is perfect for showing financial data, like how profit is built.

Simple explanation: It is like a waterfall – you see each step that adds or subtracts.

• Real‑life example: A finance manager shows how revenue turns into profit.
• School example: A student shows how marks add up to a total.
• Home example: A parent shows how income turns into savings.
• Nigerian example: A shop owner shows how sales lead to profit.

How to create a waterfall chart:

1. Select your data.
2. Go to Insert → Waterfall.
3. Excel creates the chart automatically.
4. You can format it as needed.

Lesson 4: Histogram Charts

Definition: A histogram chart shows the frequency of data in different ranges (bins).

Why it is important: It helps you see how data is distributed.

Simple explanation: It is like sorting data into boxes and counting how many are in each box.

• Real‑life example: A teacher uses a histogram to show test score distribution.
• School example: A student uses a histogram to show grades.
• Home example: A parent uses a histogram to show spending categories.
• Nigerian example: A shop owner uses a histogram to show sales distribution.

How to create a histogram:

1. Select your data.
2. Go to Insert → Histogram.
3. Excel creates the chart. You can adjust the bins.

Lesson 5: Funnel Charts

Definition: A funnel chart shows how values decrease as they move through stages.

Why it is important: It is perfect for showing sales pipelines or conversion funnels.

Simple explanation: It is like a funnel – wide at the top and narrow at the bottom.

• Real‑life example: A sales manager shows leads through the sales process.
• School example: A student shows applicants through admission stages.
• Home example: A parent shows a process from start to finish.
• Nigerian example: A shop owner shows customers through a sales process.

How to create a funnel chart:

1. Select your data.
2. Go to Insert → Funnel.
3. Excel creates the chart automatically.

Lesson 6: Box and Whisker Charts

Definition: A Box and Whisker chart shows the distribution of data using quartiles.

Why it is important: It shows the spread and outliers in your data.

Simple explanation: It is like a box that shows the middle 50% of your data, with lines (whiskers) that show the rest.

• Real‑life example: A statistician uses a Box and Whisker chart to show test scores.
• School example: A student uses it to show grades distribution.
• Home example: A parent uses it to show spending spread.
• Nigerian example: A shop owner uses it to show sales variation.

How to create a Box and Whisker chart:

1. Select your data.
2. Go to Insert → Box and Whisker.
3. Excel creates the chart automatically.

Lesson 7: Map Charts

Definition: A map chart shows data on a geographical map.

Why it is important: It helps you see geographical patterns in your data.

Simple explanation: It is like a colour‑coded map that shows data by region.

• Real‑life example: A manager shows sales by state.
• School example: A student shows population by country.
• Home example: A parent shows travel destinations.
• Nigerian example: A shop owner shows sales by Nigerian state.

How to create a map chart:

1. Select your data.
2. Go to Insert → Map.
3. Excel creates the map chart automatically.

Lesson 8: Sunburst Charts

Definition: A Sunburst chart shows hierarchical data using concentric circles.

Why it is important: It helps you see the structure of nested data.

Simple explanation: It is like a pie chart with multiple levels.

• Real‑life example: A manager shows sales by product category and sub‑category.
• School example: A student shows grades by subject and topic.
• Home example: A parent shows expenses by category and sub‑category.
• Nigerian example: A shop owner shows sales by region and city.

How to create a Sunburst chart:

1. Select your data.
2. Go to Insert → Sunburst.
3. Excel creates the chart automatically.

Lesson 9: Sparklines

Definition: A Sparkline is a tiny chart that fits inside a single cell.

Why it is important: It shows trends in a small space.

Simple explanation: It is like a mini‑chart that lives in a cell.

• Real‑life example: A manager adds Sparklines to a dashboard.
• School example: A student adds Sparklines to a report.
• Home example: A parent adds Sparklines to a budget.
• Nigerian example: A shop owner adds Sparklines to a sales report.

How to add Sparklines:

1. Select the cell where you want the Sparkline.
2. Go to Insert → Sparklines.
3. Choose Line, Column, or Win/Loss.
4. Select the data range and click OK.

Lesson 10: Forecast Charts

Definition: A forecast chart predicts future values based on your data.

Why it is important: It helps you plan for the future.

Simple explanation: It is like looking into a crystal ball to see what might happen next.

• Real‑life example: A manager forecasts sales for the next year.
• School example: A student forecasts future grades.
• Home example: A parent forecasts expenses.
• Nigerian example: A shop owner forecasts sales.

How to create a forecast chart:

1. Select your data.
2. Go to Data → Forecast Sheet.
3. Choose the end date and click Create.

Lesson 11: PivotCharts

Definition: A PivotChart is a chart based on a PivotTable.

Why it is important: It lets you interactively explore your data.

Simple explanation: It is like a chart that you can filter and change easily.

• Real‑life example: A manager creates a PivotChart for sales analysis.
• School example: A student creates a PivotChart for a project.
• Home example: A parent creates a PivotChart for budgeting.
• Nigerian example: A shop owner creates a PivotChart for product sales.

How to create a PivotChart:

1. Create a PivotTable.
2. Select the PivotTable.
3. Go to Insert → PivotChart.
4. Choose the chart type and click OK.

Lesson 12: Advanced Chart Formatting

Definition: Advanced formatting lets you customize your charts to look professional.

• Chart Titles: Add and format titles.
• Data Labels: Show values on the chart.
• Axis Formatting: Change scale and format numbers.
• Colours: Customize colour schemes.
• Trendlines: Add trendlines to show patterns.

Key Vocabulary

Important Concepts

• Advanced charts show deeper insights than simple charts.
• Dual‑axis charts combine two chart types.
• Waterfall charts show step‑by‑step changes.
• Histograms show data distribution.
• Funnel charts show decreasing stages.
• Sparklines are tiny charts in cells.
• Forecast charts predict future values.

Step-by-Step Explanations

How to Create a Dual‑Axis Chart

1. Select your data.
2. Go to Insert → Combo Chart.
3. Choose Create Custom Combo Chart.
4. For one data series, check Secondary Axis.
5. Click OK.

How to Add Sparklines

1. Select the cell where you want the Sparkline.
2. Go to Insert → Sparklines.
3. Choose Line, Column, or Win/Loss.
4. Select the data range and click OK.

Real-Life Examples

• In a business: A manager uses a waterfall chart to show profit analysis.
• In a school: A teacher uses a histogram to show test scores.
• At home: A parent uses Sparklines to show spending trends.
• In Nigeria: A shop owner uses a map chart to show sales by state.

Nigerian Examples

• A business in Lagos uses a dual‑axis chart to show sales and profit.
• A school in Abuja uses a histogram to show student grades.
• A shop in Kano uses a funnel chart to show sales stages.
• A bank in Enugu uses a forecast chart to predict loan applications.

Fun Examples Children Can Relate To

• Using a histogram to show how many friends like different colours.
• Using a funnel chart to show how many people enter a competition.
• Using Sparklines to show daily temperature changes.
• Using a map chart to show where your friends live.

Everyday Examples

• Using a waterfall chart to show how your allowance is spent.
• Using a histogram to show how much time you spend on different activities.
• Using Sparklines to show daily mood changes.
• Using a forecast chart to predict future savings.

Teacher Notes

• Emphasise that different charts tell different stories.
• Show students how to choose the right chart for their data.
• Teach students to format charts for clarity.
• Encourage students to experiment with different chart types.

Parent Tips

• Help your child understand which chart to use for different data.
• Show them how to create charts for school projects.
• Encourage them to use Sparklines for small data sets.
• Teach them to forecast future values.

Interesting Facts

• The waterfall chart was invented in the 1980s.
• Sparklines were introduced in Excel 2010.
• Map charts were introduced in Excel 2016.
• You can create custom chart templates.

Did You Know?

Remember This

• Dual‑axis charts show two types of data together.
• Waterfall charts show step‑by‑step changes.
• Histograms show data distribution.
• Funnel charts show decreasing stages.
• Sparklines are tiny charts in cells.
• Forecast charts predict future values.

Common Mistakes

• Using the wrong chart type: Choose the chart that fits your data.
• Not adding labels: Always add titles and labels.
• Overcomplicating charts: Keep charts simple and clear.
• Ignoring formatting: Format charts to look professional.
• Not using Sparklines: They are great for small spaces.

Best Practices

• Choose the right chart type for your data.
• Add titles and labels for clarity.
• Use colours consistently.
• Keep charts simple and uncluttered.
• Use Sparklines for small data sets.

Comparison: Waterfall vs Histogram

Feature	Waterfall	Histogram
Purpose	Show step‑by‑step changes	Show data distribution
Data type	Sequential	Numerical
Best for	Financial analysis	Statistical analysis

Comparison: Funnel vs Sunburst

Feature	Funnel	Sunburst
Purpose	Show decreasing stages	Show hierarchical data
Data type	Sequential	Hierarchical
Best for	Sales pipelines	Nested categories

End-of-Module Summary

Congratulations! You have completed Module Six. You now know:

• How to create dual‑axis charts.
• How to create advanced charts like Waterfall, Histogram, Funnel, Box & Whisker, Map, and Sunburst.
• How to use Sparklines to show trends.
• How to create forecast charts.
• How to create PivotCharts.
• How to apply advanced formatting.

You are now ready to move on to Module Seven, where you will learn about PivotTables and data analysis.

Frequently Asked Questions

1. What is a dual‑axis chart? A chart with two axes, often combining bars and lines.
2. What is a waterfall chart? A chart showing step‑by‑step changes.
3. What is a histogram? A chart showing data distribution in bins.
4. What is a funnel chart? A chart showing decreasing values through stages.
5. What is a Box and Whisker chart? A chart showing data distribution and outliers.
6. What is a map chart? A chart showing data on a geographical map.
7. What is a Sunburst chart? A hierarchical chart with concentric circles.
8. What is a Sparkline? A tiny chart inside a cell.
9. What is a forecast chart? A chart predicting future values.
10. What is a PivotChart? A chart based on a PivotTable.

Review Questions

1. What is a dual‑axis chart?
2. What is a waterfall chart?
3. What is a histogram?
4. What is a funnel chart?
5. What is a Box and Whisker chart?
6. What is a map chart?
7. What is a Sunburst chart?
8. What is a Sparkline?
9. What is a forecast chart?
10. What is a PivotChart?
11. How do you create a dual‑axis chart?
12. How do you add Sparklines?
13. What is the purpose of a forecast chart?
14. How do you format a chart?
15. Why are advanced charts useful?

Fill-in-the-Blank Exercises

1. A __________ chart combines two chart types with two axes.
2. A __________ chart shows step‑by‑step changes.
3. A __________ shows data distribution in bins.
4. A __________ chart shows decreasing values through stages.
5. A __________ chart shows data on a map.
6. A __________ chart shows hierarchical data with concentric circles.
7. A __________ is a tiny chart inside a cell.
8. A __________ chart predicts future values.
9. A __________ is a chart based on a PivotTable.
10. Advanced chart __________ makes charts look professional.

True or False

1. A dual‑axis chart has two axes. (True)
2. A waterfall chart shows data distribution. (False)
3. A histogram shows data in bins. (True)
4. A funnel chart shows increasing values. (False)
5. A Box and Whisker chart shows outliers. (True)
6. A map chart shows data on a map. (True)
7. A Sunburst chart is a hierarchical chart. (True)
8. A Sparkline is a large chart. (False)
9. A forecast chart predicts future values. (True)
10. A PivotChart is based on a PivotTable. (True)

Multiple Choice Questions

1. What is a dual‑axis chart?
   a) A chart with two axes b) A chart with one axis c) A chart with no axes d) A chart with three axes
   Answer: a
2. What is a waterfall chart?
   a) Shows step‑by‑step changes b) Shows data distribution c) Shows decreasing stages d) Shows hierarchical data
   Answer: a
3. What is a histogram?
   a) Shows data distribution b) Shows step‑by‑step changes c) Shows decreasing stages d) Shows hierarchical data
   Answer: a
4. What is a funnel chart?
   a) Shows decreasing stages b) Shows data distribution c) Shows step‑by‑step changes d) Shows hierarchical data
   Answer: a
5. What is a Box and Whisker chart?
   a) Shows data distribution and outliers b) Shows step‑by‑step changes c) Shows decreasing stages d) Shows hierarchical data
   Answer: a
6. What is a map chart?
   a) Shows data on a map b) Shows data distribution c) Shows step‑by‑step changes d) Shows hierarchical data
   Answer: a
7. What is a Sunburst chart?
   a) Shows hierarchical data b) Shows data distribution c) Shows step‑by‑step changes d) Shows decreasing stages
   Answer: a
8. What is a Sparkline?
   a) A tiny chart in a cell b) A large chart c) A map chart d) A funnel chart
   Answer: a
9. What is a forecast chart?
   a) Predicts future values b) Shows data distribution c) Shows step‑by‑step changes d) Shows hierarchical data
   Answer: a
10. What is a PivotChart?
    a) A chart based on a PivotTable b) A chart based on data c) A map chart d) A funnel chart
    Answer: a
11. How do you create a dual‑axis chart?
    a) Insert → Combo Chart b) Insert → Column Chart c) Insert → Line Chart d) Insert → Pie Chart
    Answer: a
12. How do you add Sparklines?
    a) Insert → Sparklines b) Insert → Chart c) Insert → Table d) Insert → Picture
    Answer: a
13. What is the purpose of a forecast chart?
    a) To predict future values b) To show data distribution c) To show step‑by‑step changes d) To show hierarchical data
    Answer: a
14. How do you format a chart?
    a) Right‑click and choose Format b) Insert → Format c) View → Format d) Data → Format
    Answer: a
15. Why are advanced charts useful?
    a) They show deeper insights b) They are colourful c) They are easy to create d) They are fun
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Dual‑Axis	A. Shows step‑by‑step changes
2. Waterfall	B. Shows data distribution
3. Histogram	C. Combines two chart types
4. Funnel	D. Shows decreasing stages
5. Sparkline	E. Tiny chart in a cell

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

Short Answer Questions

1. What is the difference between a waterfall chart and a histogram?
2. How do you add Sparklines to a worksheet?
3. What is the purpose of a forecast chart?
4. What is a PivotChart and how is it useful?
5. How do you choose the right chart for your data?

Scenario-Based Exercises

1. Scenario: You want to show how sales and profit relate. What chart would you use?
2. Scenario: You want to show how many students scored in each grade range. What chart would you use?
3. Scenario: You want to show a sales pipeline. What chart would you use?

Group Activity

In groups of 3-4, create a presentation with different chart types. Use a dual‑axis chart, a waterfall chart, and a histogram. Explain what each chart shows. Present your findings to the class.

Individual Activity

Create a workbook with three different chart types: a dual‑axis chart, a waterfall chart, and a histogram. Add titles and labels. Save the workbook.

Classroom Discussion Questions

1. Which chart type do you find most useful and why?
2. How can charts help you tell a story with data?
3. What is the most challenging part of creating charts?
4. How do you choose the right chart for your data?
5. What is your favourite chart type and why?

Mini Project

Project: "My Sales Dashboard." Create a dashboard with at least four different chart types. Include a dual‑axis chart, a waterfall chart, a histogram, and a map chart. Use Sparklines for small trends. Present your dashboard to the class.

Practical Assignment

Open Excel. Create a workbook with sales data. Create a dual‑axis chart showing sales and profit. Create a waterfall chart showing how profit is built. Create a histogram showing sales distribution. Save the workbook.

Challenge Exercise

Create a workbook with data from a real‑world source. Use at least five different chart types. Include Sparklines and a forecast chart. Add advanced formatting to make the charts look professional. Present your work to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. dual‑axis
2. waterfall
3. histogram
4. funnel
5. map
6. Sunburst
7. Sparkline
8. forecast
9. PivotChart
10. formatting

True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-T, 7-T, 8-F, 9-T, 10-T

Key Takeaways

• Advanced charts show deeper insights.
• Dual‑axis charts combine two chart types.
• Waterfall charts show step‑by‑step changes.
• Histograms show data distribution.
• Sparklines are tiny charts in cells.
• Forecast charts predict future values.
• PivotCharts are interactive charts based on PivotTables.

Preparation for the Next Module

In Module Seven, you will learn about PivotTables and Data Analysis. You will learn how to create and customize PivotTables, use slicers, and create calculated fields.

---

MODULE 7

PivotTables and Data Analysis

Module Introduction

Welcome to Module Seven of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about PivotTables and data analysis. PivotTables are one of the most powerful tools in Excel.

Think of a PivotTable as a magic table that can rearrange your data instantly. You can look at your data from different angles without changing the original data. It is like having a super‑powerful magnifying glass for your numbers.

Learning Objectives

By the end of this module, you will be able to:

• Create a PivotTable from a data source.
• Customise a PivotTable layout.
• Use slicers and timelines to filter data.
• Group data in a PivotTable.
• Create calculated fields and custom calculations.
• Create PivotCharts for visual analysis.
• Refresh and update PivotTables.

---

Warm‑up Story

Ngozi's Sales Analysis

Ngozi works in a large shop in Lagos. She has thousands of sales records. She wanted to know which products sold best in which months. She was overwhelmed by all the data.

Her manager said, "Use a PivotTable. It will organise your data for you." Ngozi created a PivotTable. She put products in rows, months in columns, and sales in the values. Instantly, she could see which products sold best in each month.

She used slicers to filter by product category. She used a timeline to filter by date. She even created a PivotChart to see the trends visually. Ngozi learned that PivotTables are the best way to analyse large amounts of data.

Main Lessons

Lesson 1: What is a PivotTable?

Definition: A PivotTable is a tool that lets you quickly summarise and analyse large amounts of data.

Why it is important: It helps you see patterns and insights that are hidden in your data.

Simple explanation: It is like a magic table that reorganises your data so you can see it from different angles.

• Real‑life example: A manager uses a PivotTable to summarise sales by product and month.
• School example: A student uses a PivotTable to summarise test scores by subject.
• Home example: A parent uses a PivotTable to summarise expenses by category.
• Nigerian example: A shop owner uses a PivotTable to summarise sales by region.

Lesson 2: Creating a PivotTable

Definition: Creating a PivotTable means selecting your data and telling Excel how to summarise it.

Why it is important: It is the first step to analysing your data.

Simple explanation: It is like telling Excel, "Please organise this data for me."

• Real‑life example: A manager creates a PivotTable from sales data.
• School example: A student creates a PivotTable from grade data.
• Home example: A parent creates a PivotTable from expense data.
• Nigerian example: A shop owner creates a PivotTable from inventory data.

How to create a PivotTable:

1. Select your data.
2. Go to Insert → PivotTable.
3. Choose where to place the PivotTable (new worksheet or existing).
4. Click OK.
5. Drag fields into the four areas: Rows, Columns, Values, and Filters.

Lesson 3: The Four Areas of a PivotTable

Definition: A PivotTable has four areas: Rows, Columns, Values, and Filters.

Why it is important: Each area controls how your data is displayed.

Simple explanation: It is like arranging furniture in a room – you decide where each piece goes.

• Rows: Data that goes down the left side (e.g., products).
• Columns: Data that goes across the top (e.g., months).
• Values: The numbers you want to summarise (e.g., sales).
• Filters: Data that you can use to filter the entire PivotTable (e.g., region).

+-----------------------------+
|        Filters               |
+-----------------------------+
| Rows  |  Columns            |
|       |                     |
|       |  Values             |
|       |                     |
+-----------------------------+
    

Lesson 4: Customising a PivotTable

Definition: Customising means changing the layout, appearance, and calculations of your PivotTable.

Why it is important: It helps you present your data clearly.

Simple explanation: It is like decorating your room to make it look nice.

• How to customise:
  • Change the value field settings (e.g., Sum, Count, Average).
  • Change number formatting.
  • Change the layout (e.g., Show in Tabular Form).
  • Add or remove fields.

Lesson 5: Slicers

Definition: A slicer is a visual filter that lets you filter a PivotTable with a single click.

Why it is important: It makes filtering easy and interactive.

Simple explanation: It is like a button that turns on and off to show you different data.

• Real‑life example: A manager uses a slicer to filter by product category.
• School example: A student uses a slicer to filter by subject.
• Home example: A parent uses a slicer to filter by expense category.
• Nigerian example: A shop owner uses a slicer to filter by region.

How to add a slicer:

1. Click on the PivotTable.
2. Go to PivotTable Analyze → Insert Slicer.
3. Choose the field(s) you want to filter.
4. Click OK.

Lesson 6: Timelines

Definition: A timeline is a special slicer for date fields.

Why it is important: It lets you filter by date ranges easily.

Simple explanation: It is like a slider that lets you choose a date range.

• Real‑life example: A manager uses a timeline to filter sales by month.
• School example: A student uses a timeline to filter grades by semester.
• Home example: A parent uses a timeline to filter expenses by year.
• Nigerian example: A shop owner uses a timeline to filter sales by quarter.

How to add a timeline:

1. Click on the PivotTable.
2. Go to PivotTable Analyze → Insert Timeline.
3. Choose the date field.
4. Click OK.

Lesson 7: Grouping Data in a PivotTable

Definition: Grouping data means combining items into groups in a PivotTable.

Why it is important: It helps you see data at a higher level.

Simple explanation: It is like putting similar things together in a box.

• Real‑life example: A manager groups sales by month.
• School example: A student groups grades by letter (A, B, C).
• Home example: A parent groups expenses by category.
• Nigerian example: A shop owner groups sales by region.

How to group data:

1. Select the items you want to group in the PivotTable.
2. Right‑click and choose Group.
3. For dates, you can group by days, months, quarters, or years.

Lesson 8: Calculated Fields

Definition: A calculated field is a new field that you create using a formula.

Why it is important: It lets you perform calculations that are not in your original data.

Simple explanation: It is like adding a new column to your PivotTable.

• Real‑life example: A manager creates a calculated field for profit (sales - cost).
• School example: A student creates a calculated field for average.
• Home example: A parent creates a calculated field for savings.
• Nigerian example: A shop owner creates a calculated field for margin.

How to create a calculated field:

1. Click on the PivotTable.
2. Go to PivotTable Analyze → Fields, Items, & Sets → Calculated Field.
3. Enter a name and a formula.
4. Click OK.

Lesson 9: Custom Calculations

Definition: Custom calculations let you show data as percentages, differences, or running totals.

Why it is important: It gives you different ways to look at your data.

Simple explanation: It is like changing the lens on a camera to see different things.

• Show Values As: Right‑click a value field → Value Field Settings → Show Values As.
• Options: % of Grand Total, % of Column Total, % of Row Total, Difference From, Running Total, etc.

Lesson 10: PivotCharts

Definition: A PivotChart is a chart based on a PivotTable.

Why it is important: It lets you see your PivotTable data visually.

Simple explanation: It is like a picture of your PivotTable.

• Real‑life example: A manager creates a PivotChart to show sales trends.
• School example: A student creates a PivotChart to show grade distribution.
• Home example: A parent creates a PivotChart to show expenses.
• Nigerian example: A shop owner creates a PivotChart to show sales by product.

How to create a PivotChart:

1. Click on the PivotTable.
2. Go to Insert → PivotChart.
3. Choose the chart type and click OK.

Lesson 11: Refreshing a PivotTable

Definition: Refreshing means updating the PivotTable when the source data changes.

Why it is important: It keeps your analysis up to date.

Simple explanation: It is like refreshing a web page to see the latest news.

How to refresh:

• Right‑click the PivotTable and choose Refresh.
• Or go to PivotTable Analyze → Refresh.

Lesson 12: PivotTable Best Practices

Definition: Best practices are guidelines for creating effective PivotTables.

• Use clean data: Make sure your data has no blank rows or columns.
• Use meaningful names: Name your fields clearly.
• Refresh regularly: Keep your PivotTable up to date.
• Use slicers for interactivity: Make your PivotTable interactive.
• Format your PivotTable: Make it look professional.

Key Vocabulary

Important Concepts

• PivotTables summarise large amounts of data.
• Slicers make filtering easy and interactive.
• Timelines filter by date ranges.
• Grouping helps you see data at a higher level.
• Calculated fields let you create new data.
• PivotCharts visualise your PivotTable data.

Step-by-Step Explanations

How to Create a PivotTable

1. Select your data.
2. Go to Insert → PivotTable.
3. Choose where to place it.
4. Click OK.
5. Drag fields to Rows, Columns, and Values.

How to Add a Slicer

1. Click on the PivotTable.
2. Go to PivotTable Analyze → Insert Slicer.
3. Choose the field(s).
4. Click OK.

Real-Life Examples

• In a business: A manager uses a PivotTable to summarise sales by product and region.
• In a school: A teacher uses a PivotTable to summarise student grades.
• At home: A parent uses a PivotTable to summarise monthly expenses.
• In Nigeria: A shop owner uses a PivotTable to summarise sales by day.

Nigerian Examples

• A bank in Lagos uses PivotTables to summarise loan data.
• A school in Abuja uses PivotTables to summarise exam results.
• A shop in Kano uses PivotTables to summarise product sales.
• A business in Enugu uses PivotTables to summarise employee data.

Fun Examples Children Can Relate To

• Using a PivotTable to summarise your favourite games.
• Using a slicer to filter by type of game.
• Using a timeline to see how many games you played each month.
• Creating a PivotChart to show your game scores.

Everyday Examples

• Using a PivotTable to summarise weekly expenses.
• Using a slicer to filter by category.
• Using a timeline to see expenses by month.
• Creating a PivotChart to show spending trends.

Teacher Notes

• Emphasise that PivotTables are for summarising data.
• Show students how to use slicers and timelines.
• Teach students to create calculated fields.
• Encourage students to create PivotCharts.

Parent Tips

• Help your child create a PivotTable for a school project.
• Show them how to use slicers to filter data.
• Encourage them to use PivotCharts for visual analysis.
• Teach them to refresh PivotTables when data changes.

Interesting Facts

• PivotTables were introduced in Excel 5.0 in 1993.
• Slicers were introduced in Excel 2010.
• Timelines were introduced in Excel 2013.
• You can create PivotTables from external data sources.

Did You Know?

Remember This

• PivotTables summarise large amounts of data.
• Slicers filter data easily.
• Timelines filter by date ranges.
• Grouping helps you see data at a higher level.
• Calculated fields create new data.
• PivotCharts visualise your data.

Common Mistakes

• Data with blank rows: Ensure your data is clean.
• Not refreshing: Always refresh after changing source data.
• Using the wrong field in Values: Choose Sum, Count, or Average correctly.
• Not using slicers: Slicers make filtering easy.
• Not formatting: A formatted PivotTable looks professional.

Best Practices

• Use clean data with no blank rows.
• Use meaningful field names.
• Refresh PivotTables regularly.
• Use slicers and timelines for interactivity.
• Format your PivotTable for clarity.

Comparison: Slicer vs Filter

Feature	Slicer	Filter
Visual	Yes	No
Easy to use	Yes	Moderate
Multiple selections	Yes	Yes
Best for	Interactive filtering	Quick filtering

Comparison: PivotTable vs Normal Table

Feature	PivotTable	Normal Table
Summarises data	Yes	No
Interactive	Yes	No
Dynamic	Yes	No
Best for	Analysis	Data entry

End-of-Module Summary

Congratulations! You have completed Module Seven. You now know:

• How to create and customise PivotTables.
• How to use slicers and timelines to filter data.
• How to group data in a PivotTable.
• How to create calculated fields and custom calculations.
• How to create PivotCharts.
• How to refresh and update PivotTables.

You are now ready to move on to Module Eight, where you will learn about What‑If Analysis and data consolidation.

Frequently Asked Questions

1. What is a PivotTable? A tool for summarising and analysing data.
2. How do you create a PivotTable? Insert → PivotTable.
3. What is a slicer? A visual filter for a PivotTable.
4. What is a timeline? A date slicer for a PivotTable.
5. What is grouping? Combining items into groups.
6. What is a calculated field? A new field created with a formula.
7. What is a PivotChart? A chart based on a PivotTable.
8. How do you refresh a PivotTable? Right‑click → Refresh.
9. What are the four areas of a PivotTable? Rows, Columns, Values, Filters.
10. Why are PivotTables useful? They summarise large amounts of data quickly.

Review Questions

1. What is a PivotTable?
2. How do you create a PivotTable?
3. What are the four areas of a PivotTable?
4. What is a slicer?
5. What is a timeline?
6. What is grouping in a PivotTable?
7. What is a calculated field?
8. What is a PivotChart?
9. How do you refresh a PivotTable?
10. Why are PivotTables useful?
11. How do you add a slicer?
12. How do you add a timeline?
13. What is the difference between a slicer and a filter?
14. What is the difference between a PivotTable and a normal table?
15. What are the best practices for PivotTables?

Fill-in-the-Blank Exercises

1. A __________ summarises large amounts of data.
2. A __________ is a visual filter for a PivotTable.
3. A __________ is a date slicer for a PivotTable.
4. __________ combines items into groups.
5. A __________ is a new field created with a formula.
6. A __________ is a chart based on a PivotTable.
7. You __________ a PivotTable to update it with new data.
8. The four areas of a PivotTable are Rows, Columns, __________, and Filters.
9. __________ make filtering easy and interactive.
10. __________ help you see data at a higher level.

True or False

1. A PivotTable summarises large amounts of data. (True)
2. A slicer is a date filter. (False)
3. A timeline is a date slicer. (True)
4. Grouping combines items into groups. (True)
5. A calculated field is created with a formula. (True)
6. A PivotChart is based on a PivotTable. (True)
7. You do not need to refresh a PivotTable. (False)
8. The four areas are Rows, Columns, Values, and Filters. (True)
9. Slicers are not interactive. (False)
10. Grouping helps you see data at a higher level. (True)

Multiple Choice Questions

1. What is a PivotTable?
   a) A tool for summarising data b) A chart c) A table d) A picture
   Answer: a
2. What is a slicer?
   a) A visual filter b) A date filter c) A chart d) A table
   Answer: a
3. What is a timeline?
   a) A date slicer b) A visual filter c) A chart d) A table
   Answer: a
4. What is grouping?
   a) Combining items into groups b) Filtering data c) Creating a chart d) Refreshing data
   Answer: a
5. What is a calculated field?
   a) A new field with a formula b) A filter c) A chart d) A table
   Answer: a
6. What is a PivotChart?
   a) A chart based on a PivotTable b) A filter c) A table d) A picture
   Answer: a
7. How do you refresh a PivotTable?
   a) Right‑click → Refresh b) Insert → Refresh c) View → Refresh d) File → Refresh
   Answer: a
8. What are the four areas of a PivotTable?
   a) Rows, Columns, Values, Filters b) Rows, Columns, Charts, Filters c) Rows, Columns, Tables, Filters d) Rows, Columns, Data, Filters
   Answer: a
9. Why are PivotTables useful?
   a) They summarise data quickly b) They create charts c) They add pictures d) They print data
   Answer: a
10. How do you add a slicer?
    a) PivotTable Analyze → Insert Slicer b) Insert → Slicer c) View → Slicer d) Data → Slicer
    Answer: a
11. How do you add a timeline?
    a) PivotTable Analyze → Insert Timeline b) Insert → Timeline c) View → Timeline d) Data → Timeline
    Answer: a
12. What is the difference between a slicer and a filter?
    a) Slicer is visual b) Filter is visual c) No difference d) Both are the same
    Answer: a
13. What is the difference between a PivotTable and a normal table?
    a) PivotTable summarises data b) Normal table summarises data c) No difference d) Both are the same
    Answer: a
14. What is a best practice for PivotTables?
    a) Use clean data b) Use messy data c) Never refresh d) Ignore formatting
    Answer: a
15. What does grouping help you do?
    a) See data at a higher level b) Filter data c) Create charts d) Refresh data
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. PivotTable	A. A visual filter
2. Slicer	B. A date filter
3. Timeline	C. Summarises data
4. Calculated Field	D. A new field with a formula
5. PivotChart	E. A chart based on a PivotTable

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

Short Answer Questions

1. What is a PivotTable and why is it useful?
2. What are the four areas of a PivotTable?
3. What is a slicer and how does it help?
4. What is a calculated field?
5. What are the best practices for PivotTables?

Scenario-Based Exercises

1. Scenario: You have sales data for different products and months. You want to see total sales by product and month. What tool would you use?
2. Scenario: You want to filter your PivotTable by product category with a single click. What tool would you use?
3. Scenario: You want to filter your PivotTable by date range. What tool would you use?

Group Activity

In groups of 3-4, create a PivotTable from a large data set. Use slicers and timelines to filter the data. Create a calculated field. Create a PivotChart. Present your findings to the class.

Individual Activity

Create a PivotTable from a data set of your choice. Add slicers and timelines. Create a calculated field. Create a PivotChart. Save the workbook.

Classroom Discussion Questions

1. Why are PivotTables useful for data analysis?
2. How do slicers and timelines make analysis easier?
3. What is your favourite feature of PivotTables?
4. How can PivotTables help you in your daily life?
5. What is the most challenging part of using PivotTables?

Mini Project

Project: "My Data Dashboard." Create a dashboard with a PivotTable, slicers, a timeline, a calculated field, and a PivotChart. Use a data set of your choice. Present your dashboard to the class.

Practical Assignment

Open Excel. Create a PivotTable from a data set. Add slicers and timelines. Create a calculated field. Create a PivotChart. Save the workbook.

Challenge Exercise

Create a PivotTable from a data set with multiple sheets. Use slicers and timelines. Create multiple calculated fields. Create multiple PivotCharts. Present your dashboard to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. PivotTable
2. slicer
3. timeline
4. Grouping
5. calculated field
6. PivotChart
7. refresh
8. Values
9. Slicers
10. Grouping

True or False Answers: 1-T, 2-F, 3-T, 4-T, 5-T, 6-T, 7-F, 8-T, 9-F, 10-T

Key Takeaways

• PivotTables summarise large amounts of data.
• Slicers and timelines make filtering easy.
• Grouping helps you see data at a higher level.
• Calculated fields let you create new data.
• PivotCharts visualise your data.
• Refresh PivotTables when data changes.

Preparation for the Next Module

In Module Eight, you will learn about What-If Analysis and Data Consolidation. You will learn about Goal Seek, Scenario Manager, and how to consolidate data from multiple sources.

---

MODULE 8

What‑If Analysis and Data Consolidation

Module Introduction

Welcome to Module Eight of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about What‑If Analysis and Data Consolidation. These tools help you make better decisions by exploring different possibilities.

Think of What‑If Analysis like a magic crystal ball. You can ask Excel, "What if I change this number?" and it shows you the answer. Data consolidation is like collecting information from many different places and putting it all together in one place.

Learning Objectives

By the end of this module, you will be able to:

• Use Goal Seek to find the input needed to achieve a desired result.
• Use Scenario Manager to compare different sets of data.
• Consolidate data from multiple workbooks.
• Use financial functions for forecasting.
• Group and outline data for better organisation.
• Create a forecast sheet to predict future values.

---

Warm‑up Story

Olu's Business Decision

Olu runs a small business in Lagos. He wanted to know how many products he needed to sell to break even. He had all his data, but he was not sure how to find the answer.

His accountant said, "Use Goal Seek. It will tell you what number you need." Olu opened Excel, set up his formula, and used Goal Seek. Instantly, Excel told him he needed to sell 500 units to break even.

He also used Scenario Manager to compare different pricing strategies. He used data consolidation to combine sales data from three different stores. Olu learned that What‑If Analysis tools help you make better business decisions.

Main Lessons

Lesson 1: What is What‑If Analysis?

Definition: What‑If Analysis is a tool that lets you see how changing one number affects other numbers.

Why it is important: It helps you make decisions by showing you different possibilities.

Simple explanation: It is like asking, "What if I change this?" and Excel shows you the answer.

• Real‑life example: A manager asks, "What if we increase the price by 10%?"
• School example: A student asks, "What if I study one more hour?"
• Home example: A parent asks, "What if we save more money each month?"
• Nigerian example: A shop owner asks, "What if we buy more stock?"

Lesson 2: Goal Seek

Definition: Goal Seek finds the input value needed to achieve a desired result.

Why it is important: It is like working backwards – you know the answer you want, and Goal Seek tells you what you need to do to get it.

Simple explanation: It is like saying, "I want to get a score of 90. How many marks do I need on the next test?"

• Real‑life example: A manager uses Goal Seek to find the sales needed to break even.
• School example: A student uses Goal Seek to find the marks needed to pass.
• Home example: A parent uses Goal Seek to find the savings needed for a goal.
• Nigerian example: A shop owner uses Goal Seek to find the price needed to make a profit.

How to use Goal Seek:

1. Go to Data → What-If Analysis → Goal Seek.
2. Set the target cell (the formula you want to change).
3. Set the target value (the result you want).
4. Set the changing cell (the input you want to find).
5. Click OK.

Lesson 3: Scenario Manager

Definition: Scenario Manager lets you create and compare different sets of input values.

Why it is important: It helps you compare different possibilities, like best case and worst case scenarios.

Simple explanation: It is like writing different versions of a story and seeing which one ends best.

• Real‑life example: A manager compares three scenarios: best case, worst case, and most likely.
• School example: A student compares different study plans.
• Home example: A parent compares different budget plans.
• Nigerian example: A shop owner compares different pricing strategies.

How to use Scenario Manager:

1. Go to Data → What-If Analysis → Scenario Manager.
2. Click Add to create a new scenario.
3. Name the scenario and select the changing cells.
4. Enter the values for the scenario.
5. Click OK.
6. To view a scenario, select it and click Show.

Lesson 4: Data Consolidation

Definition: Data consolidation combines data from multiple ranges or workbooks into one location.

Why it is important: It helps you summarise data from different sources.

Simple explanation: It is like collecting all your notes from different classes into one notebook.

• Real‑life example: A manager consolidates sales data from different regions.
• School example: A student consolidates grades from different subjects.
• Home example: A parent consolidates expenses from different categories.
• Nigerian example: A shop owner consolidates sales from different stores.

How to consolidate data:

1. Go to Data → Consolidate.
2. Choose the function (e.g., Sum, Average).
3. Add each range you want to consolidate.
4. Choose whether to use top row or left column labels.
5. Click OK.

Lesson 5: Consolidating from Multiple Workbooks

Definition: Consolidating from multiple workbooks means combining data from different Excel files.

Why it is important: It lets you bring together data from different departments or time periods.

Simple explanation: It is like taking chapters from different books and putting them into one book.

• Real‑life example: A manager consolidates data from different branch offices.
• School example: A student consolidates data from different group members.
• Home example: A parent consolidates data from different bank accounts.
• Nigerian example: A shop owner consolidates data from different suppliers.

How to consolidate from multiple workbooks:

1. Open all the workbooks you want to consolidate.
2. Go to Data → Consolidate.
3. Choose the function (e.g., Sum).
4. Add each range from each workbook.
5. Click OK.

Lesson 6: Grouping and Outlining Data

Definition: Grouping and outlining data lets you show or hide detailed data to focus on the summary.

Why it is important: It helps you manage large amounts of data by hiding details.

Simple explanation: It is like folding a big map – you can see the whole map or look at one part.

• Real‑life example: A manager groups data by region and hides details.
• School example: A student groups data by subject.
• Home example: A parent groups expenses by category.
• Nigerian example: A shop owner groups sales by product type.

How to group data:

1. Select the rows or columns you want to group.
2. Go to Data → Group.
3. Choose Rows or Columns.
4. Click OK.
5. Use the + and - buttons to show or hide details.

Lesson 7: Financial Functions for Forecasting

Definition: Financial functions help you calculate loan payments, interest, and future values.

Why it is important: They help you plan for the future and understand your finances.

• FV: Calculates the future value of an investment. =FV(rate, nper, pmt)
• PV: Calculates the present value of an investment. =PV(rate, nper, pmt)
• NPER: Calculates the number of payment periods. =NPER(rate, pmt, pv)
• RATE: Calculates the interest rate. =RATE(nper, pmt, pv)

Lesson 8: Creating a Forecast Sheet

Definition: A forecast sheet is a chart that predicts future values based on your data.

Why it is important: It helps you predict trends and plan for the future.

Simple explanation: It is like looking into a crystal ball to see what might happen next.

• Real‑life example: A manager forecasts sales for the next year.
• School example: A student forecasts future grades.
• Home example: A parent forecasts expenses.
• Nigerian example: A shop owner forecasts demand.

How to create a forecast sheet:

1. Select your data (with dates and values).
2. Go to Data → Forecast Sheet.
3. Choose the end date for the forecast.
4. Click Create.

Lesson 9: Using Data Tables for What‑If Analysis

Definition: A data table shows how changing one or two variables affects your formula.

Why it is important: It lets you see many possibilities at once.

Simple explanation: It is like a table that shows all the possible outcomes.

• Real‑life example: A manager uses a data table to see how different prices affect profit.
• School example: A student uses a data table to see how different study hours affect grades.
• Home example: A parent uses a data table to see how different savings affect goals.
• Nigerian example: A shop owner uses a data table to see how different discounts affect sales.

How to create a data table:

1. Set up your formula.
2. Create a row or column of input values.
3. Select the data table range.
4. Go to Data → What-If Analysis → Data Table.
5. Choose the row input cell or column input cell.
6. Click OK.

Lesson 10: Best Practices for What‑If Analysis

Definition: Best practices are guidelines for using What‑If Analysis effectively.

• Plan your analysis: Know what you want to find out.
• Use clear labels: Label your scenarios and tables clearly.
• Test your formulas: Make sure your formulas are correct.
• Document your scenarios: Write down what each scenario represents.
• Save your work: Save your workbook with different scenarios.

Key Vocabulary

Important Concepts

• What‑If Analysis helps you explore different possibilities.
• Goal Seek finds the input needed to get a desired result.
• Scenario Manager compares different sets of data.
• Data Consolidation combines data from multiple sources.
• Forecasting predicts future values.
• Grouping helps you manage large datasets.

Step-by-Step Explanations

How to Use Goal Seek

1. Go to Data → What-If Analysis → Goal Seek.
2. Set the target cell (the formula).
3. Set the target value (the result you want).
4. Set the changing cell (the input you want to find).
5. Click OK.

How to Consolidate Data from Multiple Workbooks

1. Open all the workbooks.
2. Go to Data → Consolidate.
3. Choose the function (e.g., Sum).
4. Add each range from each workbook.
5. Click OK.

Real-Life Examples

• In a business: A manager uses Goal Seek to find the sales needed to break even.
• In a school: A student uses Scenario Manager to compare different study plans.
• At home: A parent uses data consolidation to combine expenses from different bank accounts.
• In Nigeria: A shop owner uses a forecast sheet to predict future sales.

Nigerian Examples

• A business in Lagos uses Goal Seek to find the price needed to make a profit.
• A school in Abuja uses Scenario Manager to compare different exam scenarios.
• A shop in Kano uses data consolidation to combine sales from different stores.
• A bank in Enugu uses a forecast sheet to predict loan applications.

Fun Examples Children Can Relate To

• Using Goal Seek to find how many chores you need to do to earn pocket money.
• Using Scenario Manager to compare different ways to spend your allowance.
• Using data consolidation to combine scores from different games.
• Using a forecast sheet to predict how many games you will win next month.

Everyday Examples

• Using Goal Seek to find how much you need to save each month for a holiday.
• Using Scenario Manager to compare different budgets.
• Using data consolidation to combine expenses from different categories.
• Using a forecast sheet to predict your savings for the next year.

Teacher Notes

• Emphasise that What‑If Analysis is for decision making.
• Show students how to use Goal Seek and Scenario Manager.
• Teach students to consolidate data from multiple sources.
• Encourage students to use forecasting for planning.

Parent Tips

• Help your child use Goal Seek to find answers to questions.
• Show them how to use Scenario Manager for planning.
• Encourage them to consolidate data from different sources.
• Teach them to forecast future values.

Interesting Facts

• Goal Seek was introduced in Excel 2.0 in 1987.
• Scenario Manager was introduced in Excel 4.0 in 1992.
• Data consolidation was introduced in Excel 5.0 in 1993.
• Forecast sheets were introduced in Excel 2016.

Did You Know?

Remember This

• What‑If Analysis helps you explore different possibilities.
• Goal Seek finds the input needed to achieve a desired result.
• Scenario Manager compares different scenarios.
• Data consolidation combines data from multiple sources.
• Forecast sheets predict future values.
• Grouping helps you manage large datasets.

Common Mistakes

• Not setting up formulas correctly: Make sure your formulas are correct.
• Using Goal Seek on the wrong cell: Make sure you are changing the right cell.
• Not saving scenarios: Save your scenarios so you can use them later.
• Not consolidating correctly: Make sure you are including all the data you need.
• Ignoring forecasting: Forecasting can help you plan for the future.

Best Practices

• Plan your What‑If Analysis before you start.
• Use clear labels for scenarios.
• Test your formulas before using Goal Seek.
• Save your scenarios for future use.
• Use forecasting to plan for the future.

Comparison: Goal Seek vs Scenario Manager

Feature	Goal Seek	Scenario Manager
Purpose	Find a single input	Compare multiple inputs
Number of variables	One	Multiple
Best for	Finding a specific value	Comparing different possibilities

Comparison: Consolidate vs Group

Feature	Consolidate	Group
Purpose	Combine data from multiple sources	Organise data into sections
Data source	Multiple ranges or workbooks	Same worksheet
Best for	Summarising data	Managing large datasets

End-of-Module Summary

Congratulations! You have completed Module Eight. You now know:

• How to use Goal Seek to find the input needed to achieve a result.
• How to use Scenario Manager to compare different possibilities.
• How to consolidate data from multiple sources.
• How to use financial functions for forecasting.
• How to group and outline data.
• How to create a forecast sheet.

You are now ready to move on to Module Nine, where you will learn about exam preparation and certification success.

Frequently Asked Questions

1. What is What‑If Analysis? A tool that shows how changing a number affects your data.
2. What is Goal Seek? Finds the input needed to achieve a desired result.
3. What is Scenario Manager? Creates and compares different sets of data.
4. What is data consolidation? Combines data from multiple sources.
5. What is a forecast sheet? Predicts future values.
6. What is grouping? Organising data into sections.
7. What is a data table? Shows how variables affect a formula.
8. What does FV do? Calculates the future value of an investment.
9. What does PV do? Calculates the present value of an investment.
10. What is the best practice for What‑If Analysis? Plan, label, test, and save your work.

Review Questions

1. What is What‑If Analysis?
2. What is Goal Seek?
3. What is Scenario Manager?
4. What is data consolidation?
5. What is a forecast sheet?
6. What is grouping?
7. What is a data table?
8. What does FV do?
9. What does PV do?
10. What is the best practice for What‑If Analysis?
11. How do you use Goal Seek?
12. How do you create a forecast sheet?
13. How do you consolidate data?
14. How do you group data?
15. Why is forecasting useful?

Fill-in-the-Blank Exercises

1. __________ Analysis shows how changing a number affects your data.
2. __________ finds the input needed to achieve a desired result.
3. __________ creates and compares different sets of data.
4. __________ combines data from multiple sources.
5. A __________ sheet predicts future values.
6. __________ organises data into sections.
7. A __________ shows how variables affect a formula.
8. __________ calculates the future value of an investment.
9. __________ calculates the present value of an investment.
10. __________ is the number of payment periods.

True or False

1. What‑If Analysis shows how changing a number affects your data. (True)
2. Goal Seek finds the input needed to achieve a desired result. (True)
3. Scenario Manager compares different sets of data. (True)
4. Data consolidation combines data from multiple sources. (True)
5. A forecast sheet predicts future values. (True)
6. Grouping combines data from multiple sources. (False)
7. FV calculates the future value of an investment. (True)
8. PV calculates the present value of an investment. (True)
9. You cannot consolidate data from multiple workbooks. (False)
10. Forecasting helps you plan for the future. (True)

Multiple Choice Questions

1. What is What‑If Analysis?
   a) Shows how changing a number affects your data b) Creates charts c) Prints data d) Sorts data
   Answer: a
2. What is Goal Seek?
   a) Finds the input needed to achieve a result b) Creates charts c) Prints data d) Sorts data
   Answer: a
3. What is Scenario Manager?
   a) Compares different sets of data b) Creates charts c) Prints data d) Sorts data
   Answer: a
4. What is data consolidation?
   a) Combines data from multiple sources b) Creates charts c) Prints data d) Sorts data
   Answer: a
5. What is a forecast sheet?
   a) Predicts future values b) Creates charts c) Prints data d) Sorts data
   Answer: a
6. What is grouping?
   a) Organising data into sections b) Combining data c) Creating charts d) Printing data
   Answer: a
7. What is a data table?
   a) Shows how variables affect a formula b) Creates charts c) Prints data d) Sorts data
   Answer: a
8. What does FV do?
   a) Calculates future value b) Calculates present value c) Prints data d) Sorts data
   Answer: a
9. What does PV do?
   a) Calculates present value b) Calculates future value c) Prints data d) Sorts data
   Answer: a
10. What is the best practice for What‑If Analysis?
    a) Plan, label, test, and save b) Ignore errors c) Never save d) Use random numbers
    Answer: a
11. How do you use Goal Seek?
    a) Data → What-If Analysis → Goal Seek b) Insert → Goal Seek c) Home → Goal Seek d) View → Goal Seek
    Answer: a
12. How do you create a forecast sheet?
    a) Data → Forecast Sheet b) Insert → Forecast c) Home → Forecast d) View → Forecast
    Answer: a
13. How do you consolidate data?
    a) Data → Consolidate b) Insert → Consolidate c) Home → Consolidate d) View → Consolidate
    Answer: a
14. How do you group data?
    a) Data → Group b) Insert → Group c) Home → Group d) View → Group
    Answer: a
15. Why is forecasting useful?
    a) Helps you plan for the future b) Creates charts c) Prints data d) Sorts data
    Answer: a

Matching Exercises

Match the term on the left with its description on the right.

Term	Description
1. Goal Seek	A. Combines data from multiple sources
2. Scenario Manager	B. Finds the input needed for a desired result
3. Consolidate	C. Compares different sets of data
4. Forecast Sheet	D. Organises data into sections
5. Group	E. Predicts future values

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

Short Answer Questions

1. What is the difference between Goal Seek and Scenario Manager?
2. How does data consolidation help you?
3. What is a forecast sheet and why is it useful?
4. How do you group data in Excel?
5. What are the best practices for What‑If Analysis?

Scenario-Based Exercises

1. Scenario: You want to know how many products you need to sell to break even. What tool would you use?
2. Scenario: You want to compare three different budget plans. What tool would you use?
3. Scenario: You have sales data from three different stores. You want to combine them into one report. What tool would you use?

Group Activity

In groups of 3-4, create a workbook with sales data from three different regions. Use data consolidation to combine the data. Use Goal Seek to find the price needed to achieve a profit target. Use Scenario Manager to compare different pricing strategies. Present your findings to the class.

Individual Activity

Create a workbook with a budget. Use Goal Seek to find how much you need to save to reach a goal. Use Scenario Manager to compare different savings plans. Create a forecast sheet to predict your savings for the next year.

Classroom Discussion Questions

1. How can What‑If Analysis help you make better decisions?
2. When would you use Goal Seek instead of Scenario Manager?
3. What are the benefits of consolidating data?
4. How can forecasting help you plan for the future?
5. What is your favourite What‑If Analysis tool and why?

Mini Project

Project: "My Business Plan." Create a workbook for a business plan. Use What‑If Analysis to explore different scenarios. Use data consolidation to combine data from different departments. Create a forecast sheet to predict future sales. Present your business plan to the class.

Practical Assignment

Open Excel. Create a workbook with sales data. Use Goal Seek to find the sales needed to achieve a profit target. Use Scenario Manager to compare different pricing strategies. Create a forecast sheet to predict future sales. Save the workbook.

Challenge Exercise

Create a workbook with data from multiple sources. Use data consolidation to combine the data. Use Goal Seek and Scenario Manager to explore different possibilities. Create a forecast sheet to predict future trends. Present your findings to the class.

Quiz Answers

Fill-in-the-Blank Answers:

1. What‑If
2. Goal Seek
3. Scenario Manager
4. Data consolidation
5. forecast
6. Grouping
7. data table
8. FV
9. PV
10. NPER

True or False Answers: 1-T, 2-T, 3-T, 4-T, 5-T, 6-F, 7-T, 8-T, 9-F, 10-T

Key Takeaways

• What‑If Analysis helps you explore different possibilities.
• Goal Seek finds the input needed to achieve a desired result.
• Scenario Manager compares different sets of data.
• Data consolidation combines data from multiple sources.
• Forecast sheets predict future values.
• Grouping helps you manage large datasets.

Preparation for the Next Module

In Module Nine, you will learn about Exam Preparation and Certification Success. You will review all the skills you have learned and prepare for the MOS Excel Expert exam.

---