Description

In this course, we look at a number of advanced Excel techniques all aimed at helping you make sense of the numbers in your business. Each section in the course contains an exercise for practice and quiz. The course is created using Excel 2019/365 for Windows.

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. You can find all the resources for the course at https://github.com/PacktPublishing/The-Microsoft-Excel-Data-Analysis-Toolkit-Bundle

What You Will Learn

How to standardize and clean data ready for analysis in Excel
How to format a PivotTable, including adjusting styles
Create multi-dependent dynamic drop-down lists in Excel
Learn all about histograms and regression in Excel
Conduct a linear forecast and forecast smoothing in Excel
Automate repetitive tasks in Excel using macros

Audience

The course is designed for those who want to automate Excel tasks or procedures, for users with basic knowledge of Excel 2019/365, and those upgrading from previous software versions and a foundation of Microsoft Excel. Also, those who are seeking to advance their Excel 2019/365 knowledge can benefit from this course.

This course was recorded using Excel 2019 and Excel 365. It is also relevant to those using other, recent versions of Microsoft Excel, including Excel 2013 and 2016.

Approach

This is a complete practical course where we learn about data analysis in Excel 2019. In this course, each section contains an exercise for practice and a quiz to test your learning.

Key Features

Learn how to merge data from various sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUP * Learn how to create amazing looking dashboards using PivotTables * Learn how to use Goal Seek, Scenario Manager, and Solver to fill data gaps in Excel

Github Repo

https://github.com/PacktPublishing/The-Microsoft-Excel-Data-Analysis-Toolkit-Bundle

About the Author

Simon Sez IT

Simon Sez IT has offered technical courses for individuals, small businesses, and Fortune 500 companies since 2008, with thousands of employees who can benefit from the easy-to-learn and hands-on software training. It offers over 8,000 video tutorials on a range of software programs. Simon Sez IT ensures stress-free eLearning and enhanced employee productivity-whether you implement new software or a technological upgrade in your work environment. With over 600,000 students from 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses worldwide.

Course Outline

1. Excel for Business Analysts: Introduction

In this section, we will take a look at an introduction for business analysts. We start by looking at how to take raw data and clean it, so it is in a usable format. After that, we look at a number of tools and functions that can be used to conduct analysis before moving onto how to display data in the most meaningful way. Finally, we move onto some more advanced techniques designed to aid forecasting and use existing data to predict future trends.

1. Introduction

In this video, let us take a quick introduction.

2. Excel for Business Analysts: the Basics

In this section, we will take a look at the basics for business analysts.

1. A Recap of Basic Formulas

In this video, we will cover a recap of basic formulas.

2. Absolute Versus Relative Referencing

In this video, we will cover absolute versus relative referencing.

3. Exercise 01

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 01 file in the Excel for Business Analysts code bundle.

3. Excel for Business Analysts: Merging and Looking Up Data

In this section, we will take a look at merging and looking up data for business analysts.

1. Merging Data from Different Sources

In this video, we will be merging data from different sources.

2. Looking Up Information with VLOOKUP (Exact)

In this video, we will be looking up information with VLOOKUP (exact).

3. Looking Up Information with VLOOKUP (Approx.)

In this video, we will be looking up information with VLOOKUP (approx.).

4. How to Use VLOOKUP with Data that Expands

In this video, we will learn how to use VLOOKUP with data that expands.

5. Using HLOOKUP

In this video, we will be using HLOOKUP.

6. Combining VLOOKUP and MATCH

In this video, we will be combining VLOOKUP and MATCH.

7. Using INDEX, MATCH, and XLOOKUP

In this video, we will be using INDEX, MATCH, and XLOOKUP.

8. Data Validation Lists with Lookups

In this video, we will cover data validation lists with lookups.

9. Exercise 02

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 02 file in the Excel for Business Analysts code bundle.

4. Excel for Business Analysts: Making Better Decisions with IF

In this section, we will take a look at making better decisions with IF for business analysts.

1. How to Use the IF Function

In this video, we will learn how to use the IF function.

2. More Examples of the IF Function

In this video, we will cover more examples of the IF function.

3. Working with Nested Ifs

In this video, we will be working with nested Ifs.

4. The IFS Function

In this video, we will cover the IFS function.

5. Error Handling with IFERROR and IFNA

In this video, we will cover error handling with IFERROR and IFNA.

6. Using MAX and MIN Instead of the IF Function

In this video, we will be using MAX and MIN instead of the IF function.

7. Using SUMIFS and COUNTIFS

In this video, we will be using SUMIFS and COUNTIFS

8. Exercise 03

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 03 file in the Excel for Business Analysts code bundle.

5. Excel for Business Analysts: Preparing Data for Analysis

In this section, we will take a look at preparing data for analysis for business analysts.

1. Splitting Data Using Text Functions

In this video, we will be splitting data using text functions.

2. Using the Flash Fill Command

In this video, we will be using the flash fill command.

3. Concatenating Data

In this video, we will be concatenating data.

4. Standardizing Data

In this video, we will be standardizing data.

5. Formatting Data as a Table

In this video, we will be formatting data as a table.

6. Exercise 04

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 04 file in the Excel for Business Analysts code bundle.

6. Excel for Business Analysts: PivotTables

In this section, we will take a look at PivotTables for business analysts.

1. An Introduction to PivotTables

In this video, let us take a quick introduction to PivotTables.

2. Creating a PivotTable

In this video, we will be creating a PivotTable.

3. Summarizing Data

In this video, we will be summarizing data.

4. Calculations in PivotTables

In this video, we will cover calculations in PivotTables.

5. Using Slicers to Filter information

In this video, we will be using slicers to filter information.

6. Exercise 05

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 05 file in the Excel for Business Analysts code bundle.

7. Excel for Business Analysts: Visualizing Data with Charts

In this section, we will take a look at visualizing data with charts for business analysts.

1. Creating a Pivot Chart

In this video, we will be creating a pivot chart.

2. Formatting a Pivot Chart

In this video, we will be formatting a pivot chart.

3. Using Sparklines

In this video, we will be using Sparklines.

4. A Basic Interactive Dashboard

In this video, we will cover a basic interactive dashboard.

5. Exercise 06

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 06 file in the Excel for Business Analysts code bundle.

8. Excel for Business Analysts: Forecasting

In this section, we will take a look at forecasting for business analysts.

1. Forecast Sheets

In this video, we will cover forecast sheets.

2. The Forecast Function

In this video, we will cover the forecast function.

3. Exercise 07

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 07 file in the Excel for Business Analysts code bundle.

9. Excel for Business Analysts: Additional Useful Functions in Excel

In this section, we will take a look at additional useful functions in Excel.

1. Conditional Formatting

In this video, we will cover conditional formatting.

2. The INDIRECT Function

In this video, we will cover the INDIRECT function.

3. The OFFSET Function

In this video, we will cover the OFFSET function.

4. Using Histograms

In this video, we will be using histograms.

5. Regression

In this video, we will cover regression.

6. Exercise 08

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 08 file in the Excel for Business Analysts code bundle.

10. Excel for Business Analysts: WhatIf Analysis

In this section, we will take a look at WhatIf analysis for business analysts.

1. Goal Seek

In this video, we will cover goal seek.

2. Scenario Manager

In this video, we will cover scenario manager.

3. Data Tables

In this video, we will cover data tables.

4. Solver

In this video, we will cover Solver.

5. Exercise 09

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 09 file in the Excel for Business Analysts code bundle.

11. Excel for Business Analysts: Conclusion

In this section, we will take a look at conclusion for business analysts.

1. Summary

In this video, let us summarize what we have learned.

12. Advanced Excel 2019: Introduction

In this section, we will take a look at the introduction for Advanced Excel 2019. In this section, we will get beyond the basics and supercharge your current skill level in Excel. With this 9-hour, expert-led, video training section, you will gain an in-depth understanding of more advanced Excel features that delve into high-level consolidation, analysis, and reporting of financial information.

1. Introduction to the Advanced Excel 2019

In this video, let us take a quick introduction and see what we are going to cover in Advanced Excel 2019.

2. Advanced Excel 2019 Learning Structure

In this video, we will cover the learning structure.

13. Advanced Excel 2019: Functions

In this section, we will take a look at functions for Advanced Excel 2019.

1. Functions - Part 1

In this video, we will cover functions - part 1.

2. Functions - Part 2

In this video, we will cover functions - part 2.

3. Autosum

In this video, we will cover Autosum.

14. Advanced Excel 2019: Date and Time Functions

In this section, we will take a look at date and time functions for Advanced Excel 2019.

1. How Date and Time Works

In this video, we will learn how date and time works.

2. Basic Date and Time Functions

In this video, we will cover basic date and time functions.

3. Complex Date and Time Functions

In this video, we will cover complex date and time functions.

15. Advanced Excel 2019: Text Functions

In this section, we will take a look at text functions for Advanced Excel 2019.

1. Using Text Functions - Part 1

In this video, we will be using text functions - part 1.

2. Using Text Functions - Part 2

In this video, we will be using text functions - part 2.

16. Advanced Excel 2019: Logical Functions

In this section, we will take a look at logical functions for Advanced Excel 2019.

1. Logical Functions

In this video, we will cover logical functions.

2. Exercise 01

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 01 file in the Advanced Excel 2019 code bundle.

17. Advanced Excel 2019: Lookup Functions

In this section, we will take a look at lookup functions for Advanced Excel 2019.

1. Lookup Functions - Part 1

In this video, we will cover lookup functions - part 1.

2. Lookup Functions - Part 2

In this video, we will cover lookup functions - part 2.

3. Exercise 02

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 02 file in the Advanced Excel 2019 code bundle.

18. Advanced Excel 2019: Financial Functions

In this section, we will take a look at financial functions for Advanced Excel 2019.

1. Financial Functions and Terminology

In this video, we will cover financial functions and terminology.

2. Personal Financial Functions

In this video, we will cover personal financial functions.

3. Principal and Interest Payments

In this video, we will cover principal and interest payments.

4. Depreciation

In this video, we will cover depreciation.

5. Exercise 03

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 03 file in the Advanced Excel 2019 code bundle.

19. Advanced Excel 2019: Statistical Functions

In this section, we will take a look at statistical functions for Advanced Excel 2019.

1. Statistical Functions for Description - Part 1

In this video, we will cover statistical functions for description - part 1.

2. Statistical Functions for Description - Part 2

In this video, we will cover statistical functions for description - part 2.

3. Statistical Functions for Forecasting - Part 1

In this video, we will cover statistical functions for forecasting - part 1.

4. Statistical Functions for Forecasting - Part 2

In this video, we will cover statistical functions for forecasting - part 2.

5. Exercise 04

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 04 file in the Advanced Excel 2019 code bundle.

6. One Click Forecasting

In this video, we will cover one click forecasting.

7. Statistical Functions for Inference

In this video, we will cover statistical functions for inference.

20. Advanced Excel 2019: Connecting to External Data

In this section, we will take a look at connecting to external data for Advanced Excel 2019.

1. Connecting to Other Workbooks

In this video, we will be connecting to other workbooks.

2. Connecting to Access Databases

In this video, we will be connecting to access databases.

3. Connecting Web Data Sources

In this video, we will be connecting web data sources.

4. Get and Transform

In this video, we will cover Get and Transform.

5. Exercise 05

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 05 file in the Advanced Excel 2019 code bundle.

21. Advanced Excel 2019: Tables

In this section, we will take a look at tables for Advanced Excel 2019.

1. Introduction to Tables

In this video, let us take a quick Introduction to tables.

2. Working with Tables

In this video, we will be Working with tables.

3. Table References

In this video, we will cover table references.

4. Table Styles

In this video, we will cover table styles.

5. Exercise 6

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 06 file in the Advanced Excel 2019 code bundle.

22. Advanced Excel 2019: Pivot Tables

In this section, we will take a look at pivot tables for Advanced Excel 2019.

1. Introduction to Pivot Tables

In this video, let us take a quick Introduction to pivot tables.

2. Working with Pivot Tables

In this video, we will be working with pivot tables.

3. Filters and Slicers

In this video, we will cover filters and slicers.

4. Pivot and Charts

In this video, we will cover pivot and charts.

5. Exercise 07

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 07 file in the Advanced Excel 2019 code bundle.

23. Advanced Excel 2019: Data Analysis

In this section, we will take a look at data analysis for Advanced Excel 2019.

1. What If Analysis

In this video, we will learn and understand what if analysis.

2. Scenario Manager

In this video, we will cover scenario manager.

3. Goal Seek

In this video, we will cover Goal Seek.

4. Solver

In this video, we will cover Solver.

5. Exercise 08

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 08 file in the Advanced Excel 2019 code bundle.

24. Advanced Excel 2019: Graphs and Charts

In this section, we will take a look at graphs and charts for Advanced Excel 2019.

1. Area Charts

In this video, we will cover area charts.

2. Surface Charts

In this video, we will cover surface charts.

3. Radar Charts

In this video, we will cover radar charts.

4. Bubble Charts

In this video, we will cover bubble charts.

5. Sparklines

In this video, we will cover Sparklines.

6. Stock Charts

In this video, we will cover stock charts.

7. Exercise 9

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 09 file in the Advanced Excel 2019 code bundle.

25. Advanced Excel 2019: Web App

In this section, we will take a look at the web app for Advanced Excel 2019.

1. Web App - Part 1

In this video, we will cover web app - part 1.

2. Web App - Part 2

In this video, we will cover web app - part 2.

26. Advanced Excel 2019: Conclusion

In this section, we will take a look at conclusion for Advanced Excel 2019.

1. Summary

In this video, let us summarize what we have learned.

27. Advanced PivotTables: Introduction

In this section, we give a PivotTable refresher before moving on to some of the advanced features of this tool. Advanced sorting, slicers, timelines, calculated fields, pivot charts, and conditional formatting are just a few of the subjects included in this PivotTable deep-dive.

1. Introduction to Advanced PivotTables

In this video, let us take a quick introduction to advanced PivotTables.

2. PivotTables Recap

In this video, we will cover a recap on PivotTables.

28. Advanced PivotTables: Importing Data

In this section, we will take a look at importing data for PivotTables

1. Importing Data from a Text File

In this video, we will be importing data from a text file.

2. Importing Data from Access

In this video, we will be importing data from Access.

3. Exercise 01

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 01 file in the Advanced PivotTables code bundle.

29. Advanced PivotTables: Preparing Data for Analysis

In this section, we will take a look at preparing data for analysis for PivotTables.

1. Cleaning Data

In this video, we will be cleaning data.

2. Tabular Data

In this video, we will cover tabular data.

3. Exercise 02

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 02 file in the Advanced PivotTables code bundle.

30. Advanced PivotTables: Creating and Manipulating PivotTables

In this section, we will take a look at creating and manipulating PivotTables for PivotTables.

1. Creating and Manipulating a PivotTable

In this video, we will be creating and manipulating a PivotTable.

2. Combining Data from Multiple Worksheets

In this video, we will be combining data from multiple worksheets.

3. Grouping and Ungrouping

In this video, we will be grouping and ungrouping.

4. Report Layouts

In this video, we will cover report layouts.

5. Formatting Error Values and Empty Cells

In this video, we will be formatting error values and empty cells.

6. Exercise 03

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 03 file in the Advanced PivotTables code bundle.

31. Advanced PivotTables: Formatting PivotTables

In this section, we will take a look at formatting PivotTables for PivotTables.

1. PivotTable Styles

In this video, we will cover PivotTable styles.

2. Custom Number Formatting

In this video, we will cover custom number formatting.

3. Exercise 04

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 04 file in the Advanced PivotTables code bundle.

32. Advanced PivotTables: Value Field Settings

In this section, we will take a look at value field settings for PivotTables.

1. Summarizing Values

In this video, we will be summarizing values.

2. Show Values As

In this video, we will cover how to show values as.

3. Exercise 05

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 05 file in the Advanced PivotTables code bundle.

33. Advanced PivotTables: Sorting and Filtering

In this section, we will take a look at sorting and filtering for PivotTables.

1. Advanced Sorting

In this video, we will cover advanced sorting.

2. Advanced Filtering

In this video, we will cover advanced filtering.

3. Exercise 06

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 06 file in the code bundle.

34. Advanced PivotTables: Interacting with PivotTables

In this section, we will take a look at interacting with PivotTables for PivotTables.

1. Inserting and formatting Slicers

In this video, we will be inserting and formatting slicers.

2. Inserting and formatting Timelines

In this video, we will be inserting and formatting timelines.

3. Connecting Slicers to Multiple PivotTables

In this video, we will be connecting slicers to multiple PivotTables.

4. Using Slicers in Protected Workbooks

In this video, we will be using slicers in protected workbooks.

5. Exercise 07

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 07 file in the code bundle.

35. Advanced PivotTables: Calculations

In this section, we will take a look at calculations for PivotTables.

1. Creating a Calculated Field

In this video, we will be creating a calculated field.

2. Creating a Calculated Item

In this video, we will be creating a calculated item.

3. Solve Order and List Formulas

In this video, we will cover how to solve order and list formulas.

4. GETPIVOTDATA

In this video, we will cover GETPIVOTDATA.

5. Exercise 08

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 08 file in the code bundle.

36. Advanced PivotTables: Pivot Charts

In this section, we will take a look at pivot charts for PivotTables.

1. Creating a Pivot Chart

In this video, we will be creating a pivot chart.

2. Formatting a Pivot Chart - Part 1

In this video, we will be formatting a pivot chart - part 1.

3. Formatting a Pivot Chart - Part 2

In this video, we will be formatting a pivot chart - part 2.

4. Creating a Map Chart using Pivot Data

In this video, we will be creating a map chart using pivot data.

5. Dynamic Chart Titles

In this video, we will cover dynamic chart titles.

6. Include a Sparkline with your PivotTable

In this video, we will cover how to include a Sparkline with your PivotTable.

7. Exercise 09

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 09 file in the code bundle.

37. Advanced PivotTables: Conditional Formatting

In this section, we will take a look at conditional formatting for PivotTables.

1. Highlighting Cell Rules

In this video, we will cover highlighting cell rules.

2. Graphical Conditional Formats

In this video, we will cover graphical conditional formats.

3. Conditional Formatting and Slicers

In this video, we will cover conditional formatting and slicers.

4. Exercise 10

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 10 file in the code bundle.

38. Advanced PivotTables: Dashboards

In this section, we will take a look at dashboards for PivotTables.

1. Creating an Interactive Dashboard - Part 1

In this video, we will be creating an interactive dashboard - Part 1.

2. Creating an Interactive Dashboard - Part 2

In this video, we will be creating an interactive dashboard - part 2.

3. Updating Pivot Charts and PivotTables

In this video, we will be updating pivot charts and PivotTables.

4. Exercise 11

In this video, let us see an exercise to practice what we have learned from this section. Refer to the Exercise 11 file in the code bundle.

39. Advanced PivotTables: Conclusion

In this section, we will take a look at conclusion for PivotTables.

1. Summary

In this video, let us summarize what we have learned.

40. Advanced Formulas in Excel: Introduction

In this section, we will take a look at advanced formulas. If you want to master Excel, you need to know how formulas work together to accomplish complex tasks. In this Advanced Formulas in Excel section, we focus on a set of Excel formulas and tools that enable you to perform complex tasks and analyses.

1. Introduction

In this video, let us take a quick introduction.

41. Advanced Formulas

In this section, we will take a look at advanced formulas.

1. Filter a Dataset Using a Formula

In this video, we will cover how to filter a dataset using a formula.

2. Sort a Dataset Using a Formula and Defined Variables

In this video, we will cover how to sort a dataset using a formula and defined variables.

3. Multiple Dependent Dynamic Drop-Down Lists

In this video, we will cover multiple dependent dynamic drop-down lists.

4. Perform a 2-way Lookup

In this video, we will cover how to perform a 2-way Lookup.

5. Make Decisions with Complex Logical Calculations

In this video, we will cover how to make decisions with complex logical calculations.

6. Extracting Parts of a Text String

In this video, we will be extracting parts of a text string.

7. Creating a Dynamic Chart Title

In this video, we will be creating a dynamic chart title.

8. Finding the Last Occurrence of a Value in a List

In this video, we will be finding the last occurrence of a value in a list.

9. Looking Up Information with XLOOKUP

In this video, we will be looking up information with XLOOKUP.

10. Find the Closest Match to a Value

In this video, we will cover how to find the closest match to a value.

42. Advanced Formulas in Excel: Conclusion

In this section, we will take a look at the conclusion for advanced formulas.

1. Summary

In this video, let us summarize what we have learned.

Course Images

The Microsoft Excel Data Analysis Toolkit Bundle

By Packt

Booking options

Highlights

Description

Course Content

About The Provider

Tags

Reviews