.jpg){kind=logo}
XLS-DA-BM.AB1.E0T
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
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.
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