A complete guide to the Microsoft Excel data analysis
About This Video
In Detail
In this four-course bundle, we look at a number of advanced Excel techniques all aimed at helping you make sense …
A complete guide to the Microsoft Excel data analysis
About This Video
In Detail
In this four-course bundle, we look at a number of advanced Excel techniques all aimed at helping you make sense of the numbers in your business.
In Excel for business analysts, you will learn to clean raw data and look at a number of tools and functions that can be used to conduct analysis. Finally, we move onto some more advanced techniques designed to aid forecasting and use existing data to predict future trends.
In Advanced Excel 2019, you will gain an in-depth understanding of more advanced Excel features that delve into high-level consolidation, analysis, and reporting of financial information.
Advanced PivotTables starts with a PivotTable refresher and then goes on to discuss advanced sorting, slicers, timelines, calculated fields, pivot charts, and conditional formatting.
In Advanced Formulas, learn the Excel formulas and tools that enable you to perform complex tasks and analyses.
Filtering a dataset, sorting using formulas, creating multi-dependent drop-down lists, 2-way look-ups, text extraction, dynamic chart titles, and XLOOKUP are a few of the exciting concepts covered in the course.
By the end of the course, you will become an expert in data analysis with Excel.
Chapter 1 : Excel for Business Analysts: Introduction
Introduction
Chapter 2 : Excel for Business Analysts: the Basics
A Recap of Basic Formulas
Absolute Versus Relative Referencing
Exercise 01
Chapter 3 : Excel for Business Analysts: Merging and Looking Up Data
Merging Data from Different Sources
Looking Up Information with VLOOKUP (Exact)
Looking Up Information with VLOOKUP (Approx.)
How to Use VLOOKUP with Data that Expands
Using HLOOKUP
Combining VLOOKUP and MATCH
Using INDEX, MATCH, and XLOOKUP
Data Validation Lists with Lookups
Exercise 02
Chapter 4 : Excel for Business Analysts: Making Better Decisions with IF
How to Use the IF Function
More Examples of the IF Function
Working with Nested Ifs
The IFS Function
Error Handling with IFERROR and IFNA
Using MAX and MIN Instead of the IF Function
Using SUMIFS and COUNTIFS
Exercise 03
Chapter 5 : Excel for Business Analysts: Preparing Data for Analysis
Splitting Data Using Text Functions
Using the Flash Fill Command
Concatenating Data
Standardizing Data
Formatting Data as a Table
Exercise 04
Chapter 6 : Excel for Business Analysts: PivotTables
An Introduction to PivotTables
Creating a PivotTable
Summarizing Data
Calculations in PivotTables
Using Slicers to Filter information
Exercise 05
Chapter 7 : Excel for Business Analysts: Visualizing Data with Charts
Creating a Pivot Chart
Formatting a Pivot Chart
Using Sparklines
A Basic Interactive Dashboard
Exercise 06
Chapter 8 : Excel for Business Analysts: Forecasting
Forecast Sheets
The Forecast Function
Exercise 07
Chapter 9 : Excel for Business Analysts: Additional Useful Functions in Excel
Conditional Formatting
The INDIRECT Function
The OFFSET Function
Using Histograms
Regression
Exercise 08
Chapter 10 : Excel for Business Analysts: WhatIf Analysis
Goal Seek
Scenario Manager
Data Tables
Solver
Exercise 09
Chapter 11 : Excel for Business Analysts: Conclusion
Summary
Chapter 12 : Advanced Excel 2019: Introduction
Introduction to the Advanced Excel 2019
Advanced Excel 2019 Learning Structure
Chapter 13 : Advanced Excel 2019: Functions
Functions - Part 1
Functions - Part 2
Autosum
Chapter 14 : Advanced Excel 2019: Date and Time Functions
How Date and Time Works
Basic Date and Time Functions
Complex Date and Time Functions
Chapter 15 : Advanced Excel 2019: Text Functions
Using Text Functions - Part 1
Using Text Functions - Part 2
Chapter 16 : Advanced Excel 2019: Logical Functions
Logical Functions
Exercise 01
Chapter 17 : Advanced Excel 2019: Lookup Functions
Lookup Functions - Part 1
Lookup Functions - Part 2
Exercise 02
Chapter 18 : Advanced Excel 2019: Financial Functions
Financial Functions and Terminology
Personal Financial Functions
Principal and Interest Payments
Depreciation
Exercise 03
Chapter 19 : Advanced Excel 2019: Statistical Functions
Statistical Functions for Description - Part 1
Statistical Functions for Description - Part 2
Statistical Functions for Forecasting - Part 1
Statistical Functions for Forecasting - Part 2
Exercise 04
One Click Forecasting
Statistical Functions for Inference
Chapter 20 : Advanced Excel 2019: Connecting to External Data
Connecting to Other Workbooks
Connecting to Access Databases
Connecting Web Data Sources
Get and Transform
Exercise 05
Chapter 21 : Advanced Excel 2019: Tables
Introduction to Tables
Working with Tables
Table References
Table Styles
Exercise 6
Chapter 22 : Advanced Excel 2019: Pivot Tables
Introduction to Pivot Tables
Working with Pivot Tables
Filters and Slicers
Pivot and Charts
Exercise 07
Chapter 23 : Advanced Excel 2019: Data Analysis
What If Analysis
Scenario Manager
Goal Seek
Solver
Exercise 08
Chapter 24 : Advanced Excel 2019: Graphs and Charts
Area Charts
Surface Charts
Radar Charts
Bubble Charts
Sparklines
Stock Charts
Exercise 9
Chapter 25 : Advanced Excel 2019: Web App
Web App - Part 1
Web App - Part 2
Chapter 26 : Advanced Excel 2019: Conclusion
Summary
Chapter 27 : Advanced PivotTables: Introduction
Introduction to Advanced PivotTables
PivotTables Recap
Chapter 28 : Advanced PivotTables: Importing Data
Importing Data from a Text File
Importing Data from Access
Exercise 01
Chapter 29 : Advanced PivotTables: Preparing Data for Analysis
Cleaning Data
Tabular Data
Exercise 02
Chapter 30 : Advanced PivotTables: Creating and Manipulating PivotTables
Creating and Manipulating a PivotTable
Combining Data from Multiple Worksheets
Grouping and Ungrouping
Report Layouts
Formatting Error Values and Empty Cells
Exercise 03
Chapter 31 : Advanced PivotTables: Formatting PivotTables
PivotTable Styles
Custom Number Formatting
Exercise 04
Chapter 32 : Advanced PivotTables: Value Field Settings
Summarizing Values
Show Values As
Exercise 05
Chapter 33 : Advanced PivotTables: Sorting and Filtering
Advanced Sorting
Advanced Filtering
Exercise 06
Chapter 34 : Advanced PivotTables: Interacting with PivotTables
Inserting and formatting Slicers
Inserting and formatting Timelines
Connecting Slicers to Multiple PivotTables
Using Slicers in Protected Workbooks
Exercise 07
Chapter 35 : Advanced PivotTables: Calculations
Creating a Calculated Field
Creating a Calculated Item
Solve Order and List Formulas
GETPIVOTDATA
Exercise 08
Chapter 36 : Advanced PivotTables: Pivot Charts
Creating a Pivot Chart
Formatting a Pivot Chart - Part 1
Formatting a Pivot Chart - Part 2
Creating a Map Chart using Pivot Data
Dynamic Chart Titles
Include a Sparkline with your PivotTable
Exercise 09
Chapter 37 : Advanced PivotTables: Conditional Formatting
Highlighting Cell Rules
Graphical Conditional Formats
Conditional Formatting and Slicers
Exercise 10
Chapter 38 : Advanced PivotTables: Dashboards
Creating an Interactive Dashboard - Part 1
Creating an Interactive Dashboard - Part 2
Updating Pivot Charts and PivotTables
Exercise 11
Chapter 39 : Advanced PivotTables: Conclusion
Summary
Chapter 40 : Advanced Formulas in Excel: Introduction
Introduction
Chapter 41 : Advanced Formulas
Filter a Dataset Using a Formula
Sort a Dataset Using a Formula and Defined Variables
Multiple Dependent Dynamic Drop-Down Lists
Perform a 2-way Lookup
Make Decisions with Complex Logical Calculations
Extracting Parts of a Text String
Creating a Dynamic Chart Title
Finding the Last Occurrence of a Value in a List
Looking Up Information with XLOOKUP
Find the st Match to a Value
Chapter 42 : Advanced Formulas in Excel: Conclusion
Summary