This course prepares you for the Microsoft Office Specialist (MOS): Excel Expert certification exam (MO-201 or MO-211) . It is designed for professionals who need advanced Excel skills for business analysis, financial modelling, reporting, accounting, and data management .
Unlike a beginner or intermediate course, this training focuses on the expert-level features that set you apart. You will learn to create complex formulas, automate tasks with macros, manage large datasets with PivotTables, and build sophisticated charts . By the end, you will be fully prepared for the certification exam and equipped with skills that employers value.
🎯 Who this is for: Financial Analysts, Data Analysts, Accountants, Business Professionals, Project Coordinators, and anyone who wants to validate advanced Excel skills .
📌 Prerequisites: Intermediate Excel skills (VLOOKUP, PivotTables, basic formulas) or completion of a Level 1 course .
The Microsoft Excel Expert certification validates advanced proficiency in creating, managing, and distributing professional spreadsheets . The exam is performance-based – you complete real tasks in Excel rather than just answering multiple-choice questions .
This course covers all four skill domains tested on the Excel Expert exam. Each module includes video lessons, hands-on exercises, and practical projects .
Control, protect, and share workbooks professionally.
Master advanced data formatting and validation.
Build complex calculations with expert-level functions.
Find and fix errors in complex formulas.
Automate repetitive tasks with VBA macros.
Create professional, insightful charts.
Master Excel's most powerful analysis tool.
Most candidates benefit from 3–6 weeks of consistent study, depending on their existing Excel experience . Below is a suggested 4-week plan:
🎓 Certification Path: To earn MOS Expert certification, you must first pass MOS Associate exams (e.g., MO-200 for Excel) and then pass the Expert exam (MO-201 or MO-211) .
Welcome to Module One of your Certified Microsoft Excel Expert Level 2 course! In this module, we will learn about Workbook Options and Settings. This is the first step to becoming an Excel expert. You will learn how to protect your workbooks, share them, and manage them like a professional.
Think of a workbook like a notebook. You write important things in it. You want to keep it safe, maybe lock it so no one else changes it, and sometimes you want to share it with friends or colleagues. This module teaches you how to do all of that.
💡 What you will learn: Workbook protection, sharing, collaboration, version control, templates, and language settings.
By the end of this module, you will be able to:
Ada works in a small office in Lagos. She manages the sales reports for her company. Every month, she creates a workbook with all the sales numbers. She shares it with her boss and her team.
One day, someone accidentally deleted an important number in the workbook. Ada was very upset. She thought, "I need to protect my workbook so no one can change it by mistake."
She learned how to add a password to the workbook. She also learned how to track changes so she could see who changed what. Now, her workbook is safe and everyone can work together without fear. Ada became an Excel expert!
Definition: A workbook is an Excel file that contains one or more worksheets (spreadsheets).
Why it is important: Everything you do in Excel is inside a workbook. Understanding how to manage it is the first step to becoming an expert.
Simple explanation: Imagine a workbook is like a binder. Inside the binder are pages, and each page is a worksheet. You can write numbers, words, and formulas on each page.
📌 Mini summary: A workbook is an Excel file with one or more worksheets. It is your main tool for storing and organising data.
Definition: Protecting a workbook means locking it with a password so that only people with the password can open or change it.
Why it is important: It keeps your data safe from people who should not see it or change it.
Simple explanation: It is like putting a lock on your diary. Only you and the people you trust have the key.
How to protect a workbook:
📌 Mini summary: You can protect your workbook with a password to keep it safe. Only people with the password can open it.
Definition: Sharing a workbook means allowing other people to open and edit the same file.
Why it is important: It helps teams work together on the same data.
Simple explanation: It is like passing a notebook around the classroom so everyone can write in it.
How to share a workbook:
📌 Mini summary: You can share your workbook with others using OneDrive. They can view or edit it.
Definition: Tracking changes means recording who made what changes and when.
Why it is important: It helps you see what was changed and who changed it. You can also undo changes if needed.
Simple explanation: It is like having a video recording of everything that happens in your workbook.
How to track changes:
📌 Mini summary: Tracking changes records who made changes and when. It helps you keep track of edits.
Definition: Workbook versions are different saved copies of the same file.
Why it is important: If you make a mistake, you can go back to an older version.
Simple explanation: It is like keeping different drafts of your homework. You can go back to a previous draft if you need to.
How to manage versions:
📌 Mini summary: Version history lets you see and restore old versions of your workbook. It is a safety net for your work.
Definition: A template is a pre‑designed workbook that you can use to create new workbooks quickly.
Why it is important: Templates save time because you do not have to start from scratch every time.
Simple explanation: It is like using a cookie cutter to make cookies. You use the same shape every time.
How to use a template:
📌 Mini summary: Templates are ready‑made workbooks that save you time. You can use them for invoices, budgets, schedules, and more.
Definition: Language options let you change the language of Excel. Formula options control how formulas work.
Why it is important: You can work in your preferred language and control how formulas calculate.
Simple explanation: It is like changing the language on your phone. You choose what works best for you.
How to change language:
📌 Mini summary: You can change the language of Excel to suit your needs. You can also control formula settings.
Definition: Workbook inspection checks for hidden data or personal information that you might want to remove before sharing.
Why it is important: It protects your privacy and ensures you do not share sensitive information.
Simple explanation: It is like checking your backpack to make sure you are not carrying anything you should not have.
How to inspect a workbook:
📌 Mini summary: Workbook inspection checks for hidden data. It helps you keep your information private and safe.
Definition: Comments are notes you can add to cells to give feedback or ask questions.
Why it is important: They help people communicate without changing the data.
Simple explanation: It is like writing a note on a sticky note and putting it on a page.
How to add a comment:
📌 Mini summary: Comments are notes you add to cells. They help you communicate with others without changing the data.
Definition: Co‑authoring is when two or more people edit the same workbook at the same time.
Why it is important: It makes group work faster and easier.
Simple explanation: It is like two people drawing on the same piece of paper at the same time.
How to co‑author:
📌 Mini summary: Co‑authoring lets multiple people edit a workbook at the same time. It is great for group projects.
Definition: Workbook settings control how your workbook looks and behaves.
Why it is important: You can customise your workbook to suit your needs.
Simple explanation: It is like setting the preferences on your phone or computer.
How to change settings:
📌 Mini summary: Workbook settings let you customise how Excel works. You can change fonts, formulas, and more.
Definition: A reference to another workbook is when a formula in one workbook uses data from another workbook.
Why it is important: It lets you use data from multiple sources in one place.
Simple explanation: It is like using a library book to help you write your own book.
How to reference another workbook:
📌 Mini summary: You can link data from one workbook to another. This helps you combine information from different sources.
Did you know? You can add a digital signature to your workbook to prove its authenticity.
Did you know? You can password‑protect a workbook even if you share it with others.
Did you know? Excel automatically saves your work every 10 minutes by default.
| Feature | Password Protection | Sharing |
|---|---|---|
| Purpose | Keeps data safe | Allows others to view or edit |
| Requires password? | Yes | No |
| Can co‑author? | Yes (with password) | Yes |
| Best for | Sensitive data | Team projects |
| Feature | Template | New Workbook |
|---|---|---|
| Time | Faster | Slower |
| Design | Pre‑designed | Blank |
| Best for | Repeated tasks | New projects |
Congratulations! You have completed Module One. You now know:
You are now ready to move on to Module Two, where you will learn about managing and formatting data like an expert.
Match the term on the left with its description on the right.
| Term | Description |
|---|---|
| 1. Workbook | A. A note added to a cell |
| 2. Template | B. An Excel file |
| 3. Comment | C. A pre‑designed workbook |
| 4. Co‑authoring | D. Editing with others |
| 5. Version History | E. Shows old versions |
Answers: 1-B, 2-C, 3-A, 4-D, 5-E
In groups of 3-4, create a workbook about "Our Favourite Foods." Share it with your group using OneDrive. Each person should add a comment to another person's cell. Track the changes and review the version history.
Create a workbook about "My Weekly Schedule." Protect it with a password. Add a comment to the first cell. Save it and then open it again to test the password.
Project: "My Monthly Budget." Create a workbook with a monthly budget. Protect it with a password. Save it as a template. Share it with a friend.
Open Excel. Create a new workbook. Add 3 worksheets. Protect the workbook with a password. Save it as "My Workbook." Share it with a classmate.
Create a workbook with data from 2 other workbooks. Use references to link the data. Protect the workbook with a password. Share it with your teacher.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-F, 7-T, 8-T, 9-F, 10-T
In Module Two, you will learn about Managing and Formatting Data. You will learn about conditional formatting, data validation, custom number formats, and more. Keep practising what you learned in this module, and you will be ready for the next step.
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.
💡 What you will learn: Advanced conditional formatting, custom number formats, data validation, Flash Fill, grouping data, removing duplicates, and calculating subtotals.
By the end of this module, you will be able to:
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!"
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.
How to use conditional formatting:
📌 Mini summary: Conditional formatting changes cell colours based on rules. It helps you see important data quickly.
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.
How to create a custom number format:
📌 Mini summary: Custom number formats let you change how numbers look. You can add symbols or show percentages.
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.
How to set data validation:
📌 Mini summary: Data validation controls what can be entered into a cell. It prevents mistakes and keeps data clean.
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.
How to use Flash Fill:
📌 Mini summary: Flash Fill automatically fills in data based on patterns. It saves time and reduces errors.
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.
How to remove duplicates:
📌 Mini summary: Removing duplicates deletes repeated entries. It keeps your data clean and accurate.
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.
How to group data:
📌 Mini summary: Grouping data organises information so you can show or hide details. It helps manage large datasets.
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.
How to calculate subtotals:
📌 Mini summary: Subtotals calculate totals for each group in your data. They help you see the bigger picture.
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.
How to use advanced conditional formatting:
📌 Mini summary: Advanced conditional formatting uses colours and icons to make data easier to understand.
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.
How to custom sort:
📌 Mini summary: Custom sorting and filtering help you organise and focus on important data.
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.
How to use Find and Replace:
📌 Mini summary: Find and Replace lets you quickly update many cells. It saves time and reduces errors.
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.
How to use Text to Columns:
📌 Mini summary: Text to Columns splits data from one column into multiple columns. It helps organise data.
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.
How to use advanced filtering:
📌 Mini summary: Advanced filtering shows only the data that meets specific conditions. It helps you focus on important information.
Did you know? You can use formulas in conditional formatting.
Did you know? You can create a custom number format with your own symbols.
Did you know? Flash Fill can combine data from multiple columns.
| 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 |
| 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 |
Congratulations! You have completed Module Two. You now know:
You are now ready to move on to Module Three, where you will learn about advanced formulas and functions.
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
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.
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".
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.
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.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-T, 7-F, 8-F, 9-T, 10-T
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.
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.
💡 What you will learn: Logical functions (IF, AND, OR), lookup functions (XLOOKUP, VLOOKUP), date and time functions, financial functions (PMT), statistical functions (SUMIFS, COUNTIFS), and dynamic array formulas.
By the end of this module, you will be able to:
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.
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.
How to write a formula:
📌 Mini summary: A formula is an equation that does calculations. You start with = and then write your math.
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.
How to use IF:
Example: =IF(A1>50, "Pass", "Fail")
📌 Mini summary: The IF function makes decisions. It checks a condition and gives one answer for true and another for false.
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.
📌 Mini summary: AND checks if all conditions are true. OR checks if any condition is true.
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.
How to use XLOOKUP:
Example: =XLOOKUP("Bola", A2:A10, B2:B10)
📌 Mini summary: XLOOKUP finds data in a table and returns a value from the same row.
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.
How to use VLOOKUP:
Example: =VLOOKUP("Bola", A2:B10, 2, FALSE)
📌 Mini summary: VLOOKUP finds data in a table by searching the first column and returning a value from another column.
Definition: Date and time functions work with dates and times.
Why it is important: They help you track deadlines, schedules, and time.
📌 Mini summary: Date and time functions help you work with dates and times in Excel.
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.
How to use PMT:
📌 Mini summary: PMT calculates loan payments. It needs the interest rate, number of payments, and loan amount.
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.
How to use SUMIFS:
📌 Mini summary: SUMIFS adds numbers that meet multiple conditions. It is a powerful way to sum specific data.
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.
How to use COUNTIFS:
📌 Mini summary: COUNTIFS counts cells that meet multiple conditions. It is great for counting specific data.
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.
Common dynamic array functions:
📌 Mini summary: Dynamic array formulas return multiple results. They are powerful and easy to use.
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.
Example: =IF(AND(A1>50, B1<10), "OK", "Check")
📌 Mini summary: Nested functions are functions inside functions. They help you do complex tasks.
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.
How to trace precedents:
📌 Mini summary: Trace Precedents shows which cells are used in a formula. It helps you understand and fix errors.
Did you know? You can use IF with AND and OR to check multiple conditions.
Did you know? XLOOKUP can search from the bottom to the top.
Did you know? You can use named ranges in formulas to make them easier to understand.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search direction | Only left to right | Any direction |
| Default match | Approximate | Exact |
| Flexibility | Less | More |
| Best for | Older workbooks | Newer workbooks |
| 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 |
Congratulations! You have completed Module Three. You now know:
You are now ready to move on to Module Four, where you will learn about formula auditing and troubleshooting.
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
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.
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.
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.
Open Excel. Create a workbook with sales data for 10 products. Use IF, VLOOKUP, SUMIFS, and COUNTIFS. Save the workbook and submit it.
Create a workbook with data from your school or community. Use nested functions, dynamic arrays, and formula auditing. Present your findings to the class.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-F, 7-F, 8-T, 9-T, 10-T
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.
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.
💡 What you will learn: Trace Precedents and Dependents, Watch Window, Error Checking, Evaluate Formula, and handling circular references.
By the end of this module, you will be able to:
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.
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.
📌 Mini summary: Formula auditing helps you find and fix errors in your formulas.
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.
How to trace precedents:
📌 Mini summary: Trace Precedents shows which cells are used in a formula. It helps you understand where data comes from.
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.
How to trace dependents:
📌 Mini summary: Trace Dependents shows which cells are affected by the current cell. It helps you see the impact of changes.
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.
How to use the Watch Window:
📌 Mini summary: The Watch Window lets you monitor specific cells. It helps you see changes in real‑time.
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.
How to use error checking:
📌 Mini summary: Error Checking finds common formula errors and suggests fixes. It saves time and reduces mistakes.
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.
📌 Mini summary: Common errors tell you what is wrong with a formula. Learn to understand them.
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.
How to evaluate a formula:
📌 Mini summary: Evaluate Formula steps through a formula to show how it works. It helps you understand and fix problems.
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.
How to fix a circular reference:
📌 Mini summary: A circular reference is a formula that refers to itself. It causes errors and must be fixed.
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:
📌 Mini summary: Remove Arrows clears tracing lines. It helps keep your worksheet clean.
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.
How to check for errors:
📌 Mini summary: Checking for errors helps you find problems before they cause trouble. It ensures accuracy.
Definition: Best practices are guidelines for using formula auditing effectively.
📌 Mini summary: Best practices help you use formula auditing effectively. They keep your data accurate and your worksheet clean.
Did you know? You can trace precedents across multiple worksheets.
Did you know? The Watch Window can monitor cells in different worksheets.
Did you know? Excel has a tool called "Formula Evaluation" that shows each step of a calculation.
| 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 |
| 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 |
Congratulations! You have completed Module Four. You now know:
You are now ready to move on to Module Five, where you will learn about macros and automation.
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
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.
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.
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.
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.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-T, 3-F, 4-T, 5-T, 6-T, 7-F, 8-F, 9-T, 10-T
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.
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.
💡 What you will learn: Recording and running macros, saving macro-enabled workbooks, editing macros, enabling macros securely, and creating macros for common tasks.
By the end of this module, you will be able to:
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.
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.
📌 Mini summary: A macro automates tasks. It records your actions and plays them back to save time.
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.
How to record a macro:
📌 Mini summary: Recording a macro is easy. Just start recording, do your actions, and stop recording. Excel saves everything.
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.
How to run a macro:
📌 Mini summary: Running a macro plays back your recorded actions. It is quick and easy.
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.
How to save as macro‑enabled:
📌 Mini summary: Save as .xlsm to keep your macros. Normal .xlsx files cannot store macros.
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.
How to change macro security:
📌 Mini summary: Macro security protects your computer. Only enable macros from trusted sources.
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.
How to save a macro to the Personal Macro Workbook:
📌 Mini summary: The Personal Macro Workbook stores macros you can use in any Excel file. It is your personal toolkit.
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.
How to add a button:
📌 Mini summary: Buttons make macros easy to run. Add them from the Developer tab.
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.
How to edit a macro:
📌 Mini summary: You can edit macros in the VBA editor. It lets you customize them.
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.
Example macro idea:
📌 Mini summary: Automating common tasks saves the most time. Think about what you do often and record a macro.
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.
How to use relative references:
📌 Mini summary: Relative references make macros more flexible. They work from wherever you are.
Definition: Best practices are guidelines for creating effective macros.
📌 Mini summary: Best practices help you create effective, reliable macros.
Start Recording
|
V
Format Title (Bold, Centre)
|
V
Add Borders to Data
|
V
Stop Recording
|
V
Run Macro to Apply Formatting!
Did you know? You can assign a macro to a shape or picture, not just a button.
Did you know? You can record a macro that works on any worksheet.
Did you know? You can add comments to your VBA code to explain what it does.
| Feature | .xlsx | .xlsm |
|---|---|---|
| Can store macros? | No | Yes |
| Security | Safer | Potentially risky |
| File size | Smaller | Larger |
| Best for | Regular workbooks | Workbooks with macros |
| Feature | Absolute | Relative |
|---|---|---|
| Records from | Fixed cells | Active cell |
| Flexibility | Less | More |
| Best for | Specific ranges | Flexible positions |
Congratulations! You have completed Module Five. You now know:
You are now ready to move on to Module Six, where you will learn about advanced charts and visuals.
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
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.
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.
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.
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.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-T, 5-F, 6-T, 7-F, 8-F, 9-T, 10-F
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.
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.
💡 What you will learn: Dual‑axis charts, advanced chart types (Waterfall, Histogram, Funnel, Box & Whisker, Map, Sunburst), Sparklines, forecast charts, PivotCharts, and advanced chart formatting.
By the end of this module, you will be able to:
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.
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.
📌 Mini summary: Advanced charts show deeper insights. They help you tell a story with your data.
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.
How to create a dual‑axis chart:
📌 Mini summary: Dual‑axis charts combine two chart types. They show two types of data together.
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.
How to create a waterfall chart:
📌 Mini summary: Waterfall charts show step‑by‑step changes. They are great for financial data.
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.
How to create a histogram:
📌 Mini summary: Histograms show data distribution. They are great for seeing how data is spread out.
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.
How to create a funnel chart:
📌 Mini summary: Funnel charts show decreasing values through stages. They are great for sales and processes.
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.
How to create a Box and Whisker chart:
📌 Mini summary: Box and Whisker charts show data distribution and outliers. They are great for statistical analysis.
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.
How to create a map chart:
📌 Mini summary: Map charts show data on a map. They are great for geographical data.
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.
How to create a Sunburst chart:
📌 Mini summary: Sunburst charts show hierarchical data. They are great for nested categories.
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.
How to add Sparklines:
📌 Mini summary: Sparklines are tiny charts in cells. They show trends in a small space.
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.
How to create a forecast chart:
📌 Mini summary: Forecast charts predict future values. They help you plan ahead.
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.
How to create a PivotChart:
📌 Mini summary: PivotCharts are interactive charts based on PivotTables. They let you explore your data.
Definition: Advanced formatting lets you customize your charts to look professional.
📌 Mini summary: Advanced formatting makes charts look professional. It helps you communicate your data clearly.
Did you know? You can change the chart type after creating it.
Did you know? You can add a trendline to any chart.
Did you know? You can create a chart from a PivotTable.
| Feature | Waterfall | Histogram |
|---|---|---|
| Purpose | Show step‑by‑step changes | Show data distribution |
| Data type | Sequential | Numerical |
| Best for | Financial analysis | Statistical analysis |
| Feature | Funnel | Sunburst |
|---|---|---|
| Purpose | Show decreasing stages | Show hierarchical data |
| Data type | Sequential | Hierarchical |
| Best for | Sales pipelines | Nested categories |
Congratulations! You have completed Module Six. You now know:
You are now ready to move on to Module Seven, where you will learn about PivotTables and data analysis.
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
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.
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.
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.
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.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-F, 5-T, 6-T, 7-T, 8-F, 9-T, 10-T
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.
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.
💡 What you will learn: Creating and customising PivotTables, using slicers and timelines, grouping data, calculated fields, and PivotCharts.
By the end of this module, you will be able to:
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.
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.
📌 Mini summary: A PivotTable summarises data quickly. It helps you see patterns and insights.
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."
How to create a PivotTable:
📌 Mini summary: Creating a PivotTable is easy. Select your data, insert a PivotTable, and drag fields to organise it.
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.
+-----------------------------+
| Filters |
+-----------------------------+
| Rows | Columns |
| | |
| | Values |
| | |
+-----------------------------+
📌 Mini summary: The four areas are Rows, Columns, Values, and Filters. They control how your data is displayed.
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.
📌 Mini summary: Customising your PivotTable helps you present data clearly. You can change calculations, formatting, and layout.
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.
How to add a slicer:
📌 Mini summary: Slicers are visual filters. They make it easy to filter data with a single click.
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.
How to add a timeline:
📌 Mini summary: Timelines are date slicers. They let you filter by date ranges easily.
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.
How to group data:
📌 Mini summary: Grouping data combines items into groups. It helps you see data at a higher level.
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.
How to create a calculated field:
📌 Mini summary: Calculated fields let you create new data using formulas. They add new columns to your PivotTable.
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.
📌 Mini summary: Custom calculations let you show data in different ways, like percentages or running totals.
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.
How to create a PivotChart:
📌 Mini summary: PivotCharts are charts based on PivotTables. They help you visualise your data.
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:
📌 Mini summary: Refreshing updates your PivotTable with the latest data. Do it often when data changes.
Definition: Best practices are guidelines for creating effective PivotTables.
📌 Mini summary: Best practices help you create effective PivotTables. Use clean data, meaningful names, and refresh regularly.
Did you know? You can create a PivotTable from data in multiple worksheets.
Did you know? You can format a PivotTable with many different styles.
Did you know? You can use a PivotTable to create a dashboard.
| Feature | Slicer | Filter |
|---|---|---|
| Visual | Yes | No |
| Easy to use | Yes | Moderate |
| Multiple selections | Yes | Yes |
| Best for | Interactive filtering | Quick filtering |
| Feature | PivotTable | Normal Table |
|---|---|---|
| Summarises data | Yes | No |
| Interactive | Yes | No |
| Dynamic | Yes | No |
| Best for | Analysis | Data entry |
Congratulations! You have completed Module Seven. You now know:
You are now ready to move on to Module Eight, where you will learn about What‑If Analysis and data consolidation.
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
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.
Create a PivotTable from a data set of your choice. Add slicers and timelines. Create a calculated field. Create a PivotChart. Save the workbook.
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.
Open Excel. Create a PivotTable from a data set. Add slicers and timelines. Create a calculated field. Create a PivotChart. Save the workbook.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-F, 3-T, 4-T, 5-T, 6-T, 7-F, 8-T, 9-F, 10-T
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.
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.
💡 What you will learn: Goal Seek, Scenario Manager, data consolidation from multiple workbooks, forecasting with financial functions, and grouping and outlining data.
By the end of this module, you will be able to:
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.
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.
📌 Mini summary: What‑If Analysis shows you how changing a number affects your data. It helps you make better decisions.
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?"
How to use Goal Seek:
📌 Mini summary: Goal Seek finds the input value needed to achieve a desired result. It is like working backwards.
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.
How to use Scenario Manager:
📌 Mini summary: Scenario Manager lets you create and compare different sets of data. It helps you see different possibilities.
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.
How to consolidate data:
📌 Mini summary: Data consolidation combines data from multiple sources into one place. It helps you summarise information.
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.
How to consolidate from multiple workbooks:
📌 Mini summary: You can consolidate data from multiple Excel files. It brings all your data together.
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.
How to group data:
📌 Mini summary: Grouping and outlining let you show or hide details. It helps you manage large datasets.
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.
📌 Mini summary: Financial functions help you forecast the future. They are great for planning investments and loans.
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.
How to create a forecast sheet:
📌 Mini summary: A forecast sheet predicts future values based on your data. It helps you plan for the future.
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.
How to create a data table:
📌 Mini summary: Data tables show how changing variables affects your formula. They let you see many outcomes at once.
Definition: Best practices are guidelines for using What‑If Analysis effectively.
📌 Mini summary: Best practices help you use What‑If Analysis effectively. Plan, label, test, and save your work.
Did you know? You can have up to 32 different scenarios in Scenario Manager.
Did you know? You can consolidate data from up to 255 ranges.
Did you know? Goal Seek can be used with any formula, not just financial ones.
| 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 |
| 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 |
Congratulations! You have completed Module Eight. You now know:
You are now ready to move on to Module Nine, where you will learn about exam preparation and certification success.
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
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.
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.
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.
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.
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.
Fill-in-the-Blank Answers:
True or False Answers: 1-T, 2-T, 3-T, 4-T, 5-T, 6-F, 7-T, 8-T, 9-F, 10-T
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.