A Comprehensive Excel Masterclass

There are 5 modules in this course

The Comprehensive Excel Masterclass is an advanced course designed to empower students with the skills and knowledge needed to maximize the potential of Microsoft Excel in a business environment. This course goes beyond the basics and explores advanced functions, and features that will enable students to perform complex calculations, retrieve and manipulate data from multiple sources, and gain valuable insights from data. The course combines theoretical concepts with practical exercises and examples to reinforce learning.

A crucial aspect of business decision-making involves understanding the time value of money. In this course, students will be exposed to time value of money principles and learn how to utilize Excel's financial functions to analyze loans, create amortization schedules, and evaluate project valuations. By leveraging functions such as PV, FV, NPV, PMT, IRR and more, students will be able to make informed financial decisions. A significant portion of the course is dedicated to creating Excel dashboards, which are effective tools for visually presenting performance data (such as KPIs). Participants will learn how to leverage Pivot Tables to create dynamic and interactive dashboards utilizing slicers, timelines, and calculated fields to enhance data exploration and create compelling dashboards. Upon successful completion of this course, you will be able to: - Utilize complex Excel functions and techniques to extract and manipulate data. - Evaluate the financial viability of loans using Excel's financial functions. - Apply the principles of Capital Budgeting for project selection and investment decisions. - Create advanced Pivot Tables and Pivot Charts for data analysis and visualization. - Develop interactive dashboards with Form Controls to enhance user experience. - Design custom charts for effective data visualization. - Apply Excel's latest features and updates in Office 365/Google Sheets for increased productivity. - Showcase key performance indicators (KPIs) to support data-driven decision-making in business intelligence.

Welcome to Mastering Excel Essentials to Enhance Business Value! This module introduces Pivot Tables and Power Query as powerful tools for organizing, analyzing, and transforming data. You will learn to build dynamic reports, apply filters, group data, and create interactive dashboards, while also gaining an introduction to preparing and cleaning data with Power Query.

What's included

25 videos20 readings8 assignments1 discussion prompt

25 videosTotal 190 minutes

Course Overview Video3 minutes
Module 1 Introduction2 minutes
Overview of Pivot Tables5 minutes
Guidelines for Inserting Pivot Tables and Pivot Table Structure9 minutes
Understanding Variable Types and Pivot Table Structure12 minutes
Filtering, Organizing and Sorting Pivot Tables8 minutes
More on Filtering Pivot Tables5 minutes
Grouping Dates in Pivot Tables7 minutes
Pivot Tables Contextual Tabs: The Design and Analyze Tabs7 minutes
The “Show Value As” Display5 minutes
Creating Calculated Fields7 minutes
The Power of “Show Value As” Displays to Analyze Data – Part I10 minutes
The Power of “Show Value As” Displays to Analyze Data – Part II8 minutes
Pivot Table Options, Field Headers and Buttons7 minutes
Using Slicers to Filter Pivot Tables5 minutes
Using Slicers and Timelines to Filter Pivot Tables 8 minutes
Creating Pivot Charts I7 minutes
Creating Pivot Charts II7 minutes
Pivot Tables Formatting Options5 minutes
Pivot Tables with Different Levels of Granularity9 minutes
Pivot Tables in Google Sheets: Functional Differences (Interface, Slicers, Grouping Dates)14 minutes
Introduction to Power Query 3 minutes
Importing & Cleaning Data with Power Query10 minutes
Merging Data in Power Query12 minutes
Complex Data Prepping with Power Query14 minutes

20 readingsTotal 280 minutes

Syllabus30 minutes
Module 1 Lesson 1 Reading10 minutes
Module 1 Lesson 1 Video Instructions Pt.130 minutes
Module 1 Lesson 1 Video Instructions Pt.210 minutes
Pivot Table Exercise Completed Workbook10 minutes
Module 1 Lesson 2 Reading10 minutes
Module 1 Lesson 2 Video Instructions Pt.130 minutes
Module 1 Lesson 2 Video Instructions Pt.210 minutes
Pivot Tables II Exercise Completed Workbook10 minutes
Pivot Tables Exercise III Completed Worksheet10 minutes
(Optional) Google Sheets Exercises10 minutes
Module 1 Lesson 3 Reading 10 minutes
Module 1 Lesson 3 Video Instructions Pt.130 minutes
Sample Data Power Query Import File10 minutes
Module 1 Lesson 3 Video Instructions Pt.210 minutes
Power Query Exercise Data Files10 minutes
Power Query Exercise Part A Completed Workbook10 minutes
Power Query Exercise Part B Completed Workbook10 minutes
Module 1 Summative Assessment Answers10 minutes
Module 1 Summary10 minutes

8 assignmentsTotal 795 minutes

Pivot Table Exercise180 minutes
Pivot Tables Quiz180 minutes
Pivot Tables II Exercise180 minutes
Pivot Tables Exercise III15 minutes
Power Query Exercise Part A15 minutes
Power Query Exercise Part B15 minutes
Power Query Quiz30 minutes
Module 1 Summative Assessment180 minutes

1 discussion promptTotal 10 minutes

Meet and Greet Discussion10 minutes

Welcome to Module 2! This module explores the time value of money and its role in financial decision-making. You will use Excel’s financial functions to model investments, calculate loan payments, build amortization schedules, and analyze the impact of interest rates and repayment strategies on long-term outcomes.

What's included

25 videos16 readings9 assignments

25 videosTotal 169 minutes

Module 2 Introduction1 minute
Introduction to Time Value of Money and Cash Flow Diagrams7 minutes
Cash Flow Diagrams in Excel5 minutes
Introduction to Loans and Simple Interest8 minutes
Understanding Compound Interest8 minutes
Comparing Quotes Rates4 minutes
Calculating the Effective Interest Rate7 minutes
EFFECT and NOMINAL Functions5 minutes
Terminology: Interest Vs. Discount Rates2 minutes
The Power of Compounding7 minutes
Basic Excel Financial functions I (FV, PV,)8 minutes
Annuity and Perpetuities14 minutes
Basic Excel Financial functions II (RATE, NPER)8 minutes
Example I – Using APR, EAR, FV and PV Functions5 minutes
Example II – Calculating EAR and APR with Period Mismatch 7 minutes
Example III – Recursive and Nested PV Calculation6 minutes
Calculating Loan Payments (PMT)7 minutes
Understanding Amortization or Repayment Schedules5 minutes
Creating an Amortization in Excel: Applying IPMT, PPMT Functions9 minutes
Calculating Loan Balances with CUMPRINC Function10 minutes
CUMIPMT and CUMPRINC Functions – Understanding Total Interest and Principal Payment Calculations7 minutes
CUMIPMT and CUMPRINC Functions – Automating Start and End Periods by Year5 minutes
Impact of Paying Extra each Period on Loan Repayment Horizon (NPER)11 minutes
Impact of Adjustable Rates on Mortgages and Credit Card Loans – Part I8 minutes
Impact of Adjustable Rates on Mortgages and Credit Card Loans – Part II4 minutes

16 readingsTotal 200 minutes

Module 2 Lesson 1 Reading10 minutes
Module 2 Lesson 1 Video Instructions Pt.130 minutes
Module 2 Lesson 1 Video Instructions Pt.210 minutes
Module 2 Lesson 2 Reading10 minutes
Module 2 Lesson 2 Video Instructions Pt. 110 minutes
Module 2 Lesson 2 Video Instructions Pt. 210 minutes
Time Value of Money Exercise Completed Workbook10 minutes
Basic Financial Functions Exercise Completed Workbook10 minutes
More Complex Financial Functions Exercise Completed Workbook10 minutes
Module 2 Lesson 3 Reading10 minutes
Module 2 Lesson 3 Video Instructions Pt.130 minutes
Module 2 Lesson 3 Video Instructions Pt.210 minutes
Loan Analysis Exercise Completed Workbook10 minutes
Mortgage Loan & Refinancing Exercise Completed Workbook10 minutes
Module 2 Summative Assessment Answers10 minutes
Module 2 Summary10 minutes

9 assignmentsTotal 900 minutes

Time Value of Money Quiz180 minutes
Time Value of Money Exercise15 minutes
Basic Financial Functions Exercise 15 minutes
More Complex Financial Functions Exercise30 minutes
Excel Functions Quiz (Excel Needed) 180 minutes
Loan Analysis Exercise60 minutes
Mortgage Loan & Refinancing Exercise60 minutes
Excel Functions Quiz (No Excel Needed)180 minutes
Module 2 Summative Assessment180 minutes

Welcome to Module 3! This module focuses on building models to support data-driven decision-making and evaluating business projects. You will apply Excel tools like Goal Seek, data tables, and scenario analysis, while also mastering capital budgeting techniques such as NPV, IRR, and incremental analysis to select the most profitable investments.

What's included

25 videos13 readings8 assignments

25 videosTotal 149 minutes

Module 3 Introduction1 minute
Module 3 Lesson 3 Introduction1 minute
Inspired Endeavors Base Case – Using Cell References6 minutes
Inspired Endeavors Base Case -- Using Names8 minutes
One-Variable Data Table5 minutes
Text Label Format for One-Variable Data Table6 minutes
Two-Variable Data Table7 minutes
Scenario Manager15 minutes
Goal Seek & Loans4 minutes
One-Variable Data Tables & Loans5 minutes
Two-Variable Data Tables & Loans4 minutes
Introduction to Capital Budgeting3 minutes
Introduction to Net Present Value (NPV Function)8 minutes
NPV Pitfalls3 minutes
XNPV – NPV Function using Real Dates5 minutes
Evaluating One-Time Projects using Net Present Analysis9 minutes
Capital Budgeting for Ongoing Projects – Using a specified Time Horizon8 minutes
Equivalent Annual Worth Analysis Motivation6 minutes
Common Cycle Analysis in Excel5 minutes
Equivalent Annual Worth Analysis5 minutes
Internal Rate of Return (IRR) Introduction6 minutes
More on Internal Rate of Return (IRR)7 minutes
XIRR, RATE and Guesses for Internal Rate of Return5 minutes
Incremental Rate of Return Analysis -- Part I7 minutes
Incremental Rate of Return Analysis -- Part II8 minutes

13 readingsTotal 130 minutes

Module 3 Lesson 1 Videos Pt.110 minutes
Module 3 Lesson 1 Videos Pt.210 minutes
Base Case Analysis Exercise Completed Workbook10 minutes
Goal Seek Exercise Completed Workbook10 minutes
What-If Analysis Exercise Completed Workbook10 minutes
NPV Exercise Completed10 minutes
Module 3 Lesson 3 Reading10 minutes
Module 3 Lesson 3 Video Instructions Pt.110 minutes
Module 3 Lesson 3 Video Instructions Pt.210 minutes
Capital Budgeting Exercise Completed Workbook10 minutes
Project Evaluation and Selection Quiz - Solution Worksheet10 minutes
Module 3 Summative Assessment Answers10 minutes
Module 3 Summary10 minutes

8 assignmentsTotal 990 minutes

Practice Activity: Base Case Analysis Exercise180 minutes
Practice Activity: Goal Seek Exercise180 minutes
Practice Activity: What-If Analysis Exercise180 minutes
What-If Analysis Quiz30 minutes
NPV Exercise30 minutes
Capital Budgeting Exercise180 minutes
Project Evaluation and Selection Quiz30 minutes
Module 3 Summative Assessment180 minutes

Welcome to Module 4! In today's data-driven world, the ability to create dashboards that are both interactive and informative is absolutely crucial for effective data analysis and decision-making. In this module, we'll dive deep into the art of dashboard design, focusing on the practical application of our lessons. You'll learn how to leverage Excel's Form Controls to build dynamic and user-friendly dashboards without the need for VBA programming. But this module is about much more than Excel functions. It's about mastering the design principles that make dashboards meaningful and effective. You'll discover how to craft displays of quantitative data that facilitate managerial decision-making. We'll explore essential theories, concepts, and methodologies to visualize and analyze real-world data. From mastering data retrieval techniques with INDEX to exploring ways to create dynamic charts, this module is designed to transform you into an adept dashboard designer, capable of synthesizing and visualizing complex data in an easily digestible format.

What's included

38 videos17 readings5 assignments

38 videosTotal 318 minutes

Module 4 Introduction1 minute
Introduction to Mastering Excel Dashboards5 minutes
Dashboard Design Checklist6 minutes
Dashboard Design Basics – Getting Started4 minutes
Dashboard Workbook Structure3 minutes
Useful Shortcut Keys10 minutes
Dashboard Design and Creation Tips10 minutes
Static Dashboard Calculations I14 minutes
Static Dashboard Calculations II – GETPIVOTDATA Function6 minutes
Constructing Static Dashboard – Using Textboxes for Flexibility16 minutes
Design Elements of Dashboards12 minutes
Static Dashboard Formatting I -- Size, Fonts and Colors8 minutes
Static Dashboard Formatting II – Containers and Alignment16 minutes
Static Dashboard Formatting III – Object Properties & Final Touches7 minutes
MATCH Function Tutorial5 minutes
Using Slicers and Timelines in Pivot Tables14 minutes
INDEX Function – Efficient Lookup Function15 minutes
LARGE and SMALL – For sorting values5 minutes
CHOOSE – An alternative to IF Statements8 minutes
SUMIFS – A Review with Twists10 minutes
OFFSET Function - Basic Usage4 minutes
OFFSET with Arrays4 minutes
Introduction to the Developer Tab and Form Controls4 minutes
Combo Box – More Attractive Dropdown List8 minutes
Check Box – Check/Uncheck Option7 minutes
List Box – A Different Way to Selection from a List3 minutes
Spin Button – Increase or Decrease a Value/Position in a list2 minutes
Option Button – Choose Between Options3 minutes
Scroll Bar – Scroll Through Choices7 minutes
Calculations -- Form Controls and Slicer Cell Links11 minutes
Calculations -- Overall Metric Calculations14 minutes
Calculations -- Monthly Charts and Dynamic Titles16 minutes
Calculations -- Current vs. Previous Year Deltas16 minutes
Calculations – Top 3 and Bottom 3 Products by Profitability6 minutes
Construction and Design -- Shapes and Formatting14 minutes
Construction and Design – Charts Edits and Shape Properties9 minutes
Construction and Design – Linked Pictures10 minutes
Final Touches for Dynamic Dashboard5 minutes

17 readingsTotal 250 minutes

Module 4 Lesson 1 Reading10 minutes
Module 4 Lesson 1 Video Instructions Pt.130 minutes
Module 4 Lesson 1 Video Instructions Pt.210 minutes
Module 4 Lesson 2 Reading10 minutes
Module 4 Lesson 2 Video Instructions Pt.130 minutes
Module 4 Lesson 2 Video Instructions Pt.210 minutes
Data Retrieval and Reporting Exercise Completed 10 minutes
Module 4 Lesson 3 Reading10 minutes
Module 4 Lesson 3 - Section 1 Video Instructions Pt.130 minutes
Module 4 Lesson 3 - Section 1 Video Instructions Pt.210 minutes
Module 4 Lesson 3 - Section 2 Video Instructions Pt.130 minutes
Module 4 Lesson 3 - Section 2 Video Instructions Pt.210 minutes
Module 4 Lesson 3 - Section 3 Video Instructions Pt.110 minutes
Module 4 Lesson 3 - Section 3 Video Instructions Pt.210 minutes
Module 4 Lesson 3 - Section 4 Video Instructions Pt.110 minutes
Module 4 Lesson 3 - Section 4 Video Instructions Pt.210 minutes
Module 4 Summative Assessment Answers10 minutes

5 assignmentsTotal 750 minutes

Dashboard Design Quiz 180 minutes
Data Retrieval and Reporting Exercise180 minutes
Retrieval Functions Quiz 180 minutes
Form Controls Quiz30 minutes
Module 4 Summative Assessment180 minutes

This module contains the summative course assessment that has been designed to evaluate your understanding of the course material and assess your ability to apply the knowledge you have acquired throughout the course. Be sure to review the course material thoroughly before taking the assessment.

What's included

1 assignment

Build toward a degree

This course is part of the following degree program(s) offered by Illinois Tech. If you are admitted and enroll, your completed coursework may count toward your degree learning and your progress can transfer with you.¹

Instructor

Instructor ratings

(36 ratings)

Liz Durango-Cohen

Illinois Tech

2 Courses19,774 learners

Offered by

Illinois Tech

Why people choose Coursera for their career

Felipe M.

Learner since 2018

"To be able to take courses at my own pace and rhythm has been an amazing experience. I can learn whenever it fits my schedule and mood."

Jennifer J.

Learner since 2020

"I directly applied the concepts and skills I learned from my courses to an exciting new project at work."

Larry W.

Learner since 2021

"When I need courses on topics that my university doesn't offer, Coursera is one of the best places to go."

Chaitanya A.

"Learning isn't just about being better at your job: it's so much more than that. Coursera allows me to learn without limits."

Learner reviews

5 stars
81.42%
4 stars
15.71%
3 stars
0%
2 stars
1.42%
1 star
1.42%

Showing 3 of 70

GO

Reviewed on Aug 14, 2024

It was very comprehensive course on critical features and functions in Excel that are required for everyday use and data analysis and visualization.

RE

Reviewed on Jun 30, 2024

Great experience and I learned more than I projected.

HR

Reviewed on Aug 31, 2024

This Course will help an MBA students to get knowledge about Excel most important Functions like Pivot Table, Developer tool features and so on

View more reviews

Frequently asked questions

To access the course materials, assignments and to earn a Certificate, you will need to purchase the Certificate experience when you enroll in a course. You can try a Free Trial instead, or apply for Financial Aid. The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.

When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile.

Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.