XLS-DA-BM.AB1
Microsoft Excel Data Analysis and Business Modeling
Learn the core concepts of MS data analysis and business modeling to develop the right skills for transforming worksheet data into actionable insights.
- Practice in 60 Hands-On Labs — nothing to install
- 97 Interactive Lessons and 207 topics mapped to the official exam objectives
- 368 Practice Test Questions
Intermediate Self-paced · 1 year access 4.7/5 (313 Reviews)
60 Hands-On LiveLabs
Practice real IT tasks in guided environments.
- Real environments
- Auto-graded
- No installation
97Interactive Lessons
207Topics
60LiveLab
368Practice Test Questions
111Flashcards
111Glossary of terms
01 / Skills you'll get
What you will be able to do
Try Free →
No credit card required
In today’s data-driven world, every business (big or small) thrives on its ability to transform data into action items. This Excel Business Modeling and Data Analysis course teaches you how to use MS Excel for analyzing data and building financial models that help businesses make informed decisions. Other than learning basic excel formulas, functions, data manipulation and chart creation, you’ll also learn how to use data analysis tools like PivotTables, data tables, scenario analysis (what-if situations), and data consolidation. Use of analytical techniques for building financial models like budgets and forecasts is also covered in the Excel data analysis training course. To make it a fun learning experience for you, we have also included interactive activities like hand-on labs, flashcards, and quizzes along with the course content.
- Translating Excel basics to relatable analytics
- Using Power Query for cleaning and connecting data
- Create custom functions with LAMBDA (without VBA)
- Utilizing new charts and data types for visualizing data like a pro
- Utilizing data manipulation techniques like advanced XLOOKUP function
- Using 3D Maps for highlighting geographical trends
- Confidently building powerful business data models
Target Career Roles
- Data Journalist
- Retail Manager
- Project Manager
- Business Analyst
- Financial Analyst
- Information Clerk
- Administrative Assistant
02 / Lessons & labs
See exactly what you will learn and practice
Lessons
97 Interactive Lessons · 207 topics01 Introduction 2 topics +
- What you should know before reading this course?
- How to use this course?
02 Basic worksheet modeling 2 topics · 1 LiveLab +
- Answers to this lesson's questions
- Problems
1 LiveLab in this lesson — see the labs panel →
03 Range names 4 topics +
- How can I create named ranges?
- Answers to this lesson’s questions
- Remarks
- Problems
04 Lookup functions 3 topics · 1 LiveLab +
- Syntax of the lookup functions
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
05 The INDEX function 3 topics · 1 LiveLab +
- Syntax of the INDEX function
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
06 The MATCH function 3 topics · 1 LiveLab +
- Syntax of the MATCH function
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
07 Text functions and Flash Fill 3 topics · 1 LiveLab +
- Text function syntax
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
08 Dates and date functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
09 IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
10 Time and time functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
11 The net present value functions: NPV and XNPV 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
12 The internal rate of return: IRR, XIRR, and MIRR functions 2 topics +
- Answers to this lesson’s questions
- Problems
13 More Excel financial functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
14 Circular references 2 topics +
- Answers to this lesson’s questions
- Problems
15 The Paste Special command 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
16 Three-dimensional formulas and hyperlinks 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
17 The auditing tool and the Inquire add-in 3 topics +
- Excel auditing options
- Answers to this lesson’s questions
- Problems
18 Sensitivity analysis with data tables 2 topics +
- Answers to this lesson’s questions
- Problems
19 The Goal Seek command 2 topics +
- Answers to this lesson’s questions
- Problems
20 Using the Scenario Manager for sensitivity analysis 3 topics +
- Answer to this lesson’s question
- Remarks
- Problems
21 The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions 3 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Remarks
- Problems
1 LiveLab in this lesson — see the labs panel →
22 The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
23 Summarizing data with histograms and Pareto charts 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
24 Summarizing data with descriptive statistics 2 topics +
- Answers to this lesson’s questions
- Problems
25 Summarizing data with database statistical functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
26 Consolidating data 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
27 Creating subtotals 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
28 The OFFSET function 3 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Remarks
- Problems
1 LiveLab in this lesson — see the labs panel →
29 The INDIRECT function 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
30 Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes 2 topics +
- Answers to this lesson’s questions
- Problems
31 Conditional formatting 2 topics +
- Answers to this lesson’s questions
- Problems
32 Excel tables and table slicers 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
33 Basic charting 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
34 Advanced charting 2 topics +
- Answers to this lesson’s questions
- Problems
35 Filled and 3D Maps 2 topics +
- Questions answered in this lesson
- Problems
36 Sparklines 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
37 Importing data from a text file or document 2 topics · 1 LiveLab +
- Answers to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
38 The Power Query Editor 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
39 Excel’s new data types 2 topics +
- Answers to this lesson’s questions
- Problems
40 Sorting in Excel 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
41 Filtering data and removing duplicates 2 topics +
- Answers to this lesson’s questions
- Problems
42 Array formulas and functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
43 Excel’s new dynamic array functions 2 topics +
- Answers to this lesson’s questions
- Problems
44 Validating data 3 topics +
- Answers to this lesson’s questions
- Remarks
- Problems
45 Importing past stock prices, exchange rates, and...tocurrency prices with the STOCKHISTORY function 2 topics +
- Answers to this lesson’s questions
- Problems
46 Using PivotTables and slicers to describe data 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
47 The Data Model 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
48 Power Pivot 2 topics +
- Answers to this lesson’s questions
- Problems
49 Use Analyze Data to find patterns in your data 2 topics +
- Answers to this lesson’s questions
- Problems
50 An introduction to optimization with Excel Solver 2 topics +
- Answers to this lesson’s questions
- Problems
51 Using Solver to determine the optimal product mix 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
52 Using Solver to schedule your workforce 2 topics +
- Answers to this lesson’s question
- Problems
53 Using Solver to solve transportation or distribution problems 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
54 Using Solver for capital budgeting 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
55 Using Solver for financial planning 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
56 Using Solver to rate sports teams 2 topics +
- Answer to this lesson’s question
- Problems
57 Warehouse location and the GRG Multistart and Evolutionary Solver engines 2 topics +
- Answers to this lesson’s questions
- Problems
58 Penalties and the Evolutionary Solver 2 topics +
- Answers to this lesson’s questions
- Problems
59 The traveling salesperson problem 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
60 Estimating straight-line relationships 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
61 Modeling exponential growth 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
62 The power curve 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
63 Using correlations to summarize relationships 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
64 Introduction to multiple regression 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
65 Incorporating qualitative factors into multiple regression 2 topics +
- Answers to this lesson’s questions
- Problems
66 Modeling nonlinearities and interactions 2 topics +
- Answers to this lesson’s questions
- Problems for Lessons 51–53
67 Analysis of variance: One-way ANOVA 2 topics +
- Answers to this lesson’s questions
- Problems
68 Randomized blocks and two-way ANOVA 2 topics +
- Answers to this lesson’s questions
- Problems
69 An introduction to probability 2 topics +
- Answers to this lesson’s questions
- Problems
70 An introduction to random variables 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
71 The binomial, hypergeometric, and negative binomial random variables 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
72 The Poisson and exponential random variable 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
73 The normal random variable and Z-scores 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
74 Using the lognormal random variable to model stock prices 3 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Remarks
- Problems
1 LiveLab in this lesson — see the labs panel →
75 Weibull and beta distributions: Modeling machine life and duration of a project 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
76 Using moving averages to understand time series 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problem
1 LiveLab in this lesson — see the labs panel →
77 Ratio-to-moving-average forecast method 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problem
1 LiveLab in this lesson — see the labs panel →
78 Making probability statements from forecasts 2 topics +
- Answers to this lesson’s questions
- Problems
79 The Winters method and the Forecast Sheet tool 3 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Remarks
- Problems
1 LiveLab in this lesson — see the labs panel →
80 Forecasting in the presence of special events 2 topics +
- Answers to this lesson’s questions
- Problems
81 Introduction to Monte Carlo simulation 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
82 Calculating an optimal bid 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
83 Simulating stock prices and asset-allocation modeling 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
84 Fun and games: Simulating gambling and sporting-event probabilities 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
85 Using resampling to analyze data 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
86 Advanced sensitivity analysis 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
87 Pricing stock options 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
88 Determining customer value 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
89 The economic order quantity inventory model 2 topics +
- Answers to this lesson’s questions
- Problems
90 Inventory modeling with uncertain demand 2 topics · 2 LiveLab +
- Answers to this lesson’s questions
- Problems
2 LiveLab in this lesson — see the labs panel →
91 Queuing theory: The mathematics of waiting in line 2 topics +
- Answers to this lesson’s questions
- Problems
92 Estimating a demand curve 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
93 Pricing products by using tie-ins 2 topics +
- Answer to this lesson’s question
- Problems
94 Pricing products by using subjectively determined demand 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
95 Nonlinear pricing 2 topics +
- Answers to this lesson’s questions
- Problems
96 Recording macros 2 topics +
- Answers to this lesson’s questions
- Problems
97 The LET and LAMBDA functions and the LAMBDA helper functions 2 topics +
- Answers to this lesson’s questions
- Problems
Hands-On Labs Our edge
60 LiveLabs- Performing Mathematical Calculations using Formulas
- Accumulating Data Using the VLOOKUP Function
- Extracting Data Using the INDEX Function
- Finding the Required Data Using the MATCH Function
- Creating Email Addresses Using the Excel Text Functions
- Calculating the Number of Workdays Using a Date Function
- Computing Annual Sales Using the IF Function
- Calculating Race Timings Using the Time Functions
- Calculating Net Present Value Using the NPV Function
- Determining Depreciation Using Excel Financial Functions
- Using the Paste Special Command to Convert Data
- Summarizing Data Using Three-Dimensional Formulas
- Counting Cells with Criteria Using COUNTIF and COUNTIFS Functions
- Calculating with Criteria Using the COUNTIF and SUMIF Functions
- Creating Bin Ranges Using Histograms
- Summarizing Data
- Consolidating Data
- Creating a Subtotal using the SUBTOTAL Function
- Using the OFFSET Function to Create Lagged Values
- Using the INDIRECT Function to Tabulate Data
- Using Excel Tables to Perform Calculations
- Creating a Scatter Chart
- Creating Sparklines
- Importing Data from a Text File
- Using the Power Query Editor to Transform Data
- Sorting Data
- Performing Calculations Using Array Functions and Formulas
- Creating a PivotTable and PivotChart
- Using the Distinct Count Option for Calculation
- Determining the Profit-Maximizing Product Mix Using Solver
- Finding an Optimal Solution Using Solver
- Obtaining Maximum NPV using Solver
- Determining the Monthly Payment Using Solver
- Solving the Traveling Salesperson Problem
- Creating a Scatter Chart and Adding a Trendline
- Creating an Exponential Trend Curve
- Creating a Power Curve
- Using Correlations to Find the Relationship Between Variables
- Using Multiple Regression to Find the Optimal Forecasting Equation
- Using Variance and Standard Deviation to Measure the Spread of Data
- Computing Binomial Probabilities
- Computing Poisson Distribution
- Calculating Z-Scores
- Calculating the Future Price of a Stock Using a Lognormal Variable
- Determining Probability Using the Beta Random Variable
- Creating a Moving Average Graph
- Using the Ratio-to-Moving-Average Forecasting Method
- Estimating Smoothing Constants
- Simulating the Values of a Normal Random Variable
- Determining the Optimal Bid using Simulation
- Determining Asset Allocation
- Simulating the Outcome of a Sporting Event
- Implementing Resampling
- Creating a Spider Plot
- Using Formula Protection in a Worksheet
- Determining Customer Value
- Determining the Economic Order Quantity (EOQ)
- Determining the Reorder Point
- Plotting a Linear Demand Curve
- Finding the Optimal Price Using Subjectively Determined Demand
Labs run in your browser — nothing to install.
03 / FAQs
Questions before you start
What advanced techniques will I learn with the Microsoft Excel Data Analysis and Business Modeling course?+
Besides working on the latest excel features, you’ll also learn to use advanced tools like XLOOKUP and LAMBDA functions.
How will I benefit from this course?+
Most businesses (big or small) need meaningful data insights for making informed decisions, which is why there is a growing need of skilled Data Analysts across all industry verticals. With uCetify’s Microsoft Excel Data Analysis and Business Modeling course you can develop the right skill set needed for this job profile.
Where can I find technical support for this Learning Excel: Data Analysis course?+
At uCertify, we provide 24/7 tech support to the users for all of our courses.
Is this course suitable for beginner’s wanting to upskill?+
Definitely Yes. All those wanting to upskill themselves and learn advanced excel tools for data manipulation and analysis will benefit greatly from this course.
What opportunities can I pursue after completing this Excel data analysis course?+
Completing this course will open doors to some exciting job opportunities like data analyst, financial analyst, management consultant, business, market research analyst, supply chain analyst, human resource analyst, and data journalist.
Get Ready to Become a Data Analysis Pro
Learn how to utilize the power of MS excel to analyze data and drive your career towards success.
- 1 year of full access
- 60 LiveLab included
- Certificate of completion
No credit card required