.jpg){kind=logo}
XLS-DA-BM.AB2
Microsoft Excel Data Analysis and Business Modeling
Intermediate Self-paced · 1 year access
60 Hands-On LiveLabs
Practice real IT tasks in guided environments.
- Real environments
- Auto-graded
- No installation
01 / Lessons & labs
See exactly what you will learn and practice
Lessons
111 Interactive Lessons · 223 topics01 Introduction 3 topics +
- What you should know before reading this course?
- How to use this course?
- What’s New in this Edition?
02 Basic Formulas and Functions 6 topics · 2 LiveLab +
- Basic worksheet modeling
- Range names
- How can I create named ranges?
- Remarks
- Lookup functions
- Syntax of the lookup functions
2 LiveLab in this lesson — see the labs panel →
03 The INDEX function 5 topics · 1 LiveLab +
- Syntax of the INDEX function
- Answers to this lesson’s questions
- Answers to the INDEX function questions
- Problems
- Problems 4
1 LiveLab in this lesson — see the labs panel →
04 The MATCH function 5 topics · 1 LiveLab +
- Syntax of the MATCH function
- Answers to this lesson’s questions
- Answers to the MATCH function questions
- Problems
- Problems 5
1 LiveLab in this lesson — see the labs panel →
05 Text functions and Flash Fill 5 topics · 1 LiveLab +
- Text function syntax
- Answers to this lesson’s questions
- Answers to Text functions and Flash Fill questions
- Problems
- Problems 6
1 LiveLab in this lesson — see the labs panel →
06 Date, Time, IF, and Financial Functions +
07 Dates and date functions 4 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Answers to Dates and Date Functions questions
- Problems
- Problems 1
1 LiveLab in this lesson — see the labs panel →
08 IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions 4 topics · 1 LiveLab +
- Answers to IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions questions
- Answers to this lesson’s questions
- Problems 2
- Problems
1 LiveLab in this lesson — see the labs panel →
09 Time and time functions 4 topics · 1 LiveLab +
- Answers to time and time functions questions
- Answers to this lesson’s questions
- Problems 3
- Problems
1 LiveLab in this lesson — see the labs panel →
10 The net present value functions: NPV and XNPV 4 topics · 1 LiveLab +
- Answers to The net present value functions: NPV and XNPV questions
- Answers to this lesson’s questions
- Problems 4
- Problems
1 LiveLab in this lesson — see the labs panel →
11 The internal rate of return: IRR, XIRR, and MIRR functions 4 topics +
- Answers to The Internal Rate of Return: IRR, XIRR, and MIRR functions questions
- Answers to this lesson’s questions
- Problems 5
- Problems
12 More Excel financial functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
13 Tools for Modeling +
14 Circular references 4 topics +
- Answers to this lesson’s questions
- Answers to Circular references questions
- Problems
- Problems 1
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 Summarizing Data +
22 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 →
23 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 →
24 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 →
25 Summarizing data with descriptive statistics 2 topics +
- Answers to this lesson’s questions
- Problems
26 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 →
27 Consolidating data 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
28 Creating subtotals 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
29 Advanced Excel Functions and Form Controls +
30 The OFFSET function 3 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Remarks
- Problems
1 LiveLab in this lesson — see the labs panel →
31 The INDIRECT function 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
32 Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes 2 topics +
- Answers to this lesson’s questions
- Problems
33 Excel Tables and Charting +
34 Conditional formatting 2 topics +
- Answers to this lesson’s questions
- Problems
35 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 →
36 Basic charting 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
37 Advanced charting 2 topics +
- Answers to this lesson’s questions
- Problems
38 Filled and 3D Maps 2 topics +
- Questions answered in this lesson
- Problems
39 Sparklines 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
40 Importing and Transforming Data +
41 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 →
42 The Power Query Editor 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
43 Excel’s new data types 2 topics +
- Answers to this lesson’s questions
- Problems
44 Sorting in Excel 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
45 Filtering data and removing duplicates 2 topics +
- Answers to this lesson’s questions
- Problems
46 Array formulas and functions 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
47 Excel’s new dynamic array functions 2 topics +
- Answers to this lesson’s questions
- Problems
48 Validating data 3 topics +
- Answers to this lesson’s questions
- Remarks
- Problems
49 Importing past stock prices, exchange rates, and...tocurrency prices with the STOCKHISTORY function 2 topics +
- Answers to this lesson’s questions
- Problems
50 PivotTables, the Data Model and Power Pivot +
51 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 →
52 The Data Model 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
53 Power Pivot 2 topics +
- Answers to this lesson’s questions
- Problems
54 Use Analyze Data to find patterns in your data 2 topics +
- Answers to this lesson’s questions
- Problems
55 Linear Optimization +
56 An introduction to optimization with Excel Solver 2 topics +
- Answers to this lesson’s questions
- Problems
57 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 →
58 Using Solver to schedule your workforce 2 topics +
- Answers to this lesson’s question
- Problems
59 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 →
60 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 →
61 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 →
62 Nonlinear Optimization +
63 Using Solver to rate sports teams 2 topics +
- Answer to this lesson’s question
- Problems
64 Warehouse location and the GRG Multistart and Evolutionary Solver engines 2 topics +
- Answers to this lesson’s questions
- Problems
65 Penalties and the Evolutionary Solver 2 topics +
- Answers to this lesson’s questions
- Problems
66 The traveling salesperson problem 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
67 Understanding relationships between two variables +
68 Estimating straight-line relationships 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
69 Modeling exponential growth 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
70 The power curve 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
71 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 →
72 Multiple Regression and ANOVA +
73 Introduction to multiple regression 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
74 Incorporating qualitative factors into multiple regression 2 topics +
- Answers to this lesson’s questions
- Problems
75 Modeling nonlinearities and interactions 2 topics +
- Answers to this lesson’s questions
- Problems for Lessons 51–53
76 Analysis of variance: One-way ANOVA 2 topics +
- Answers to this lesson’s questions
- Problems
77 Randomized blocks and two-way ANOVA 2 topics +
- Answers to this lesson’s questions
- Problems
78 Introduction to Probability and Random Variables +
79 An introduction to probability 2 topics +
- Answers to this lesson’s questions
- Problems
80 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 →
81 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 →
82 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 →
83 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 →
84 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 →
85 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 →
86 Forecasting +
87 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 →
88 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 →
89 Making probability statements from forecasts 2 topics +
- Answers to this lesson’s questions
- Problems
90 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 →
91 Forecasting in the presence of special events 2 topics +
- Answers to this lesson’s questions
- Problems
92 Monte Carlo Simulation +
93 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 →
94 Calculating an optimal bid 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
95 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 →
96 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 →
97 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 →
98 Advanced sensitivity analysis 2 topics · 1 LiveLab +
- Answer to this lesson’s question
- Problems
1 LiveLab in this lesson — see the labs panel →
99 Applications to Finance, Operations, and Marketing +
100 Pricing stock options 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
101 Determining customer value 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
102 The economic order quantity inventory model 2 topics +
- Answers to this lesson’s questions
- Problems
103 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 →
104 Queuing theory: The mathematics of waiting in line 2 topics +
- Answers to this lesson’s questions
- Problems
105 Estimating a demand curve 2 topics · 1 LiveLab +
- Answers to this lesson’s questions
- Problems
1 LiveLab in this lesson — see the labs panel →
106 Pricing products by using tie-ins 2 topics +
- Answer to this lesson’s question
- Problems
107 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 →
108 Nonlinear pricing 2 topics +
- Answers to this lesson’s questions
- Problems
109 Macros and Custom Functions +
110 Recording macros 2 topics +
- Answers to this lesson’s questions
- Problems
111 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.
02 / FAQs
Questions before you start
Keep going
You might also like
Prepare for Microsoft Excel Data Analysis and Business Modeling
One-time payment. Full access for 1 year. Start with a free trial if you want to look around first.
- 1 year of full access
- 60 LiveLab included
- Certificate of completion
No credit card required