The Microsoft Excel Data Analysis Toolkit Bundle

The Microsoft Excel Data Analysis Toolkit Bundle

by Simon Sez IT
Released July 2021
Publisher(s): Packt Publishing
ISBN: 9781803239262

Watch it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

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.

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.

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.

Table of contents

  1. Chapter 1 : Excel for Business Analysts: Introduction
    1. Introduction
  2. Chapter 2 : Excel for Business Analysts: the Basics
    1. A Recap of Basic Formulas
    2. Absolute Versus Relative Referencing
    3. Exercise 01
  3. Chapter 3 : Excel for Business Analysts: Merging and Looking Up Data
    1. Merging Data from Different Sources
    2. Looking Up Information with VLOOKUP (Exact)
    3. Looking Up Information with VLOOKUP (Approx.)
    4. How to Use VLOOKUP with Data that Expands
    5. Using HLOOKUP
    6. Combining VLOOKUP and MATCH
    7. Using INDEX, MATCH, and XLOOKUP
    8. Data Validation Lists with Lookups
    9. Exercise 02
  4. Chapter 4 : Excel for Business Analysts: Making Better Decisions with IF
    1. How to Use the IF Function
    2. More Examples of the IF Function
    3. Working with Nested Ifs
    4. The IFS Function
    5. Error Handling with IFERROR and IFNA
    6. Using MAX and MIN Instead of the IF Function
    7. Using SUMIFS and COUNTIFS
    8. Exercise 03
  5. Chapter 5 : Excel for Business Analysts: Preparing Data for Analysis
    1. Splitting Data Using Text Functions
    2. Using the Flash Fill Command
    3. Concatenating Data
    4. Standardizing Data
    5. Formatting Data as a Table
    6. Exercise 04
  6. Chapter 6 : Excel for Business Analysts: PivotTables
    1. An Introduction to PivotTables
    2. Creating a PivotTable
    3. Summarizing Data
    4. Calculations in PivotTables
    5. Using Slicers to Filter information
    6. Exercise 05
  7. Chapter 7 : Excel for Business Analysts: Visualizing Data with Charts
    1. Creating a Pivot Chart
    2. Formatting a Pivot Chart
    3. Using Sparklines
    4. A Basic Interactive Dashboard
    5. Exercise 06
  8. Chapter 8 : Excel for Business Analysts: Forecasting
    1. Forecast Sheets
    2. The Forecast Function
    3. Exercise 07
  9. Chapter 9 : Excel for Business Analysts: Additional Useful Functions in Excel
    1. Conditional Formatting
    2. The INDIRECT Function
    3. The OFFSET Function
    4. Using Histograms
    5. Regression
    6. Exercise 08
  10. Chapter 10 : Excel for Business Analysts: WhatIf Analysis
    1. Goal Seek
    2. Scenario Manager
    3. Data Tables
    4. Solver
    5. Exercise 09
  11. Chapter 11 : Excel for Business Analysts: Conclusion
    1. Summary
  12. Chapter 12 : Advanced Excel 2019: Introduction
    1. Introduction to the Advanced Excel 2019
    2. Advanced Excel 2019 Learning Structure
  13. Chapter 13 : Advanced Excel 2019: Functions
    1. Functions - Part 1
    2. Functions - Part 2
    3. Autosum
  14. Chapter 14 : Advanced Excel 2019: Date and Time Functions
    1. How Date and Time Works
    2. Basic Date and Time Functions
    3. Complex Date and Time Functions
  15. Chapter 15 : Advanced Excel 2019: Text Functions
    1. Using Text Functions - Part 1
    2. Using Text Functions - Part 2
  16. Chapter 16 : Advanced Excel 2019: Logical Functions
    1. Logical Functions
    2. Exercise 01
  17. Chapter 17 : Advanced Excel 2019: Lookup Functions
    1. Lookup Functions - Part 1
    2. Lookup Functions - Part 2
    3. Exercise 02
  18. Chapter 18 : Advanced Excel 2019: Financial Functions
    1. Financial Functions and Terminology
    2. Personal Financial Functions
    3. Principal and Interest Payments
    4. Depreciation
    5. Exercise 03
  19. Chapter 19 : Advanced Excel 2019: Statistical Functions
    1. Statistical Functions for Description - Part 1
    2. Statistical Functions for Description - Part 2
    3. Statistical Functions for Forecasting - Part 1
    4. Statistical Functions for Forecasting - Part 2
    5. Exercise 04
    6. One Click Forecasting
    7. Statistical Functions for Inference
  20. Chapter 20 : Advanced Excel 2019: Connecting to External Data
    1. Connecting to Other Workbooks
    2. Connecting to Access Databases
    3. Connecting Web Data Sources
    4. Get and Transform
    5. Exercise 05
  21. Chapter 21 : Advanced Excel 2019: Tables
    1. Introduction to Tables
    2. Working with Tables
    3. Table References
    4. Table Styles
    5. Exercise 6
  22. Chapter 22 : Advanced Excel 2019: Pivot Tables
    1. Introduction to Pivot Tables
    2. Working with Pivot Tables
    3. Filters and Slicers
    4. Pivot and Charts
    5. Exercise 07
  23. Chapter 23 : Advanced Excel 2019: Data Analysis
    1. What If Analysis
    2. Scenario Manager
    3. Goal Seek
    4. Solver
    5. Exercise 08
  24. Chapter 24 : Advanced Excel 2019: Graphs and Charts
    1. Area Charts
    2. Surface Charts
    3. Radar Charts
    4. Bubble Charts
    5. Sparklines
    6. Stock Charts
    7. Exercise 9
  25. Chapter 25 : Advanced Excel 2019: Web App
    1. Web App - Part 1
    2. Web App - Part 2
  26. Chapter 26 : Advanced Excel 2019: Conclusion
    1. Summary
  27. Chapter 27 : Advanced PivotTables: Introduction
    1. Introduction to Advanced PivotTables
    2. PivotTables Recap
  28. Chapter 28 : Advanced PivotTables: Importing Data
    1. Importing Data from a Text File
    2. Importing Data from Access
    3. Exercise 01
  29. Chapter 29 : Advanced PivotTables: Preparing Data for Analysis
    1. Cleaning Data
    2. Tabular Data
    3. Exercise 02
  30. Chapter 30 : Advanced PivotTables: Creating and Manipulating PivotTables
    1. Creating and Manipulating a PivotTable
    2. Combining Data from Multiple Worksheets
    3. Grouping and Ungrouping
    4. Report Layouts
    5. Formatting Error Values and Empty Cells
    6. Exercise 03
  31. Chapter 31 : Advanced PivotTables: Formatting PivotTables
    1. PivotTable Styles
    2. Custom Number Formatting
    3. Exercise 04
  32. Chapter 32 : Advanced PivotTables: Value Field Settings
    1. Summarizing Values
    2. Show Values As
    3. Exercise 05
  33. Chapter 33 : Advanced PivotTables: Sorting and Filtering
    1. Advanced Sorting
    2. Advanced Filtering
    3. Exercise 06
  34. Chapter 34 : Advanced PivotTables: Interacting with PivotTables
    1. Inserting and formatting Slicers
    2. Inserting and formatting Timelines
    3. Connecting Slicers to Multiple PivotTables
    4. Using Slicers in Protected Workbooks
    5. Exercise 07
  35. Chapter 35 : Advanced PivotTables: Calculations
    1. Creating a Calculated Field
    2. Creating a Calculated Item
    3. Solve Order and List Formulas
    4. GETPIVOTDATA
    5. Exercise 08
  36. Chapter 36 : Advanced PivotTables: Pivot Charts
    1. Creating a Pivot Chart
    2. Formatting a Pivot Chart - Part 1
    3. Formatting a Pivot Chart - Part 2
    4. Creating a Map Chart using Pivot Data
    5. Dynamic Chart Titles
    6. Include a Sparkline with your PivotTable
    7. Exercise 09
  37. Chapter 37 : Advanced PivotTables: Conditional Formatting
    1. Highlighting Cell Rules
    2. Graphical Conditional Formats
    3. Conditional Formatting and Slicers
    4. Exercise 10
  38. Chapter 38 : Advanced PivotTables: Dashboards
    1. Creating an Interactive Dashboard - Part 1
    2. Creating an Interactive Dashboard - Part 2
    3. Updating Pivot Charts and PivotTables
    4. Exercise 11
  39. Chapter 39 : Advanced PivotTables: Conclusion
    1. Summary
  40. Chapter 40 : Advanced Formulas in Excel: Introduction
    1. Introduction
  41. Chapter 41 : Advanced Formulas
    1. Filter a Dataset Using a Formula
    2. Sort a Dataset Using a Formula and Defined Variables
    3. Multiple Dependent Dynamic Drop-Down Lists
    4. Perform a 2-way Lookup
    5. Make Decisions with Complex Logical Calculations
    6. Extracting Parts of a Text String
    7. Creating a Dynamic Chart Title
    8. Finding the Last Occurrence of a Value in a List
    9. Looking Up Information with XLOOKUP
    10. Find the st Match to a Value
  42. Chapter 42 : Advanced Formulas in Excel: Conclusion
    1. Summary

Product information

  • Title: The Microsoft Excel Data Analysis Toolkit Bundle
  • Author(s): Simon Sez IT
  • Release date: July 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781803239262