The Ultimate Microsoft Excel Mastery Bundle - 8 Courses

Master Microsoft Excel with this huge-value beginner to advanced eight-course bundle and become an Excel power user!

What you'll learn

Become familiar with what’s new in Excel 2021 and navigate the Excel 2021 interface

Create your first Excel spreadsheet and use basic and intermediate Excel formulas and functions

Utilize useful keyboard shortcuts to increase productivity

Linking to other worksheets & workbooks and protecting & sharing workbooks

How to use logical functions to make better business decisions

Creating an interactive dashboard to present high-level metrics

Using the NEW dynamic array functions to perform tasks

Recording and running macros to automate repetitive tasks

Predicting future values using forecast functions and forecast sheets

Using statistical functions to rank data and to calculate the MEDIAN and MODE

Understanding and making minor edits to VBA code

How to merge data from different sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUP

How to standardize and clean data ready for analysis in Excel

Conducting a Linear Forecast and Forecast Smoothing in Excel

All about Histograms and Regression in Excel

How to use Goal Seek, Scenario Manager, and Solver to fill data gaps in Excel

Learn to unlock advanced Excel tools Power Query and Power Pivot

Analyze huge buckets of data to make informed business decisions

How to create PivotTables

Grouping and ungrouping PivotTable data and dealing with errors

Creating PivotCharts and adding sparklines and slicers

Adding slicers and timelines and applying them to multiple tables

Combining data from multiple worksheets for a PivotTable

All about the GETPIVOTDATA function

How to use 3D maps from a PivotTable

Updating your data in a PivotTable and PivotChart

About Dashboard architecture and inspiration

How to prepare data for analysis (cleaning data)

Useful formulas for creating dashboards in Excel

How to create and edit Pivot Tables in Excel

How to create Pivot Charts from Pivot Tables

Advanced chart techniques in Excel

How to add interactive elements (form controls) into your dashboards

How to create a Sales Dashboard from scratch

How to create an HR Dashboard from scratch

Requirements

Microsoft Excel 2021/365 desktop version for Windows for the Excel 2021 courses

You'll need a copy of Microsoft Excel that is compatible with the Power Pivot tool for the Power Pivot, Power Query & DAX course

The PivotTable courses were created using Excel 2019/365 for Windows. All of the code used in this course except for one property is compatible for Excel 2007, Excel 2010, Excel 2013, Excel 2016 and Excel 2019.

Description

**This bundle includes practice exercises, downloadable files, and LIFETIME access**Let us take you on a journey from being an Excel novice to an Excel expert with this amazing value 8-course training bundle. By the end of this training, you will be able to clean, summarize, and analyze data easily, as well as create PivotTables, charts, macros, and so much more!We'll take you on a no-nonsense journey to learn specific functions, formulas, and tools that Excel has to help conduct business or data analysis. We'll also look at three advanced Excel features: Power Pivot, Power Query, and DAX. This suite of Excel functions allows you to manipulate, analyze, and evaluate millions of rows of data from Excel or other databases.This ultimate Excel course bundle is designed for students of all levels. If you are brand new to Microsoft Excel, this course can get you started on your journey. If you already have a good understanding of Excel, you can further your skills with the more advanced courses in this bundle. This is the only Excel training you are ever going to need!All courses include practice exercises and follow-along instructor files so you can immediately apply what you learn.What's included?Excel 2021 for BeginnersBecome familiar with what’s new in Excel 2021Navigate the Excel 2021 interfaceUtilize useful keyboard shortcuts to increase productivityCreate your first Excel spreadsheetUse basic and intermediate Excel formulas and functionsEffectively apply formatting to cells and use conditional formattingUse Excel lists and master sorting and filteringWork efficiently by using the cut, copy, and paste optionsLink to other worksheets and workbooksAnalyze data using chartsInsert pictures in a spreadsheetWork with views, zooms, and freezing panesSet page layout and print optionsProtect and share workbooksSave your workbook in different file formatsExcel 2021 IntermediateDesigning better spreadsheets and controlling user inputHow to use logical functions to make better business decisionsConstructing functional and flexible lookup formulasHow to use Excel tables to structure data and make it easy to updateExtracting unique values from a listSorting and filtering data using advanced features and new Excel formulasWorking with date and time functionsExtracting data using text functionsImporting data and cleaning it up before analysisAnalyzing data using PivotTablesRepresenting data visually with PivotChartsAdding interactions to PivotTables and PivotChartsCreating an interactive dashboard to present high-level metricsAuditing formulas and troubleshooting common Excel errorsHow to control user input with data validationUsing WhatIf analysis tools to see how changing inputs affect outcomes.Excel 2021 AdvancedUsing the NEW dynamic array functions to perform tasksCreating advanced and flexible lookup formulasUsing statistical functions to rank data and to calculate the MEDIAN and MODEProducing accurate results when working with financial data using math functionsCreating variables and functions with LET and LAMBDAAnalyzing data with advanced PivotTable and PivotChart hacksCreating interactive reports and dashboards by incorporating form controlsImporting and cleaning data using Power QueryPredicting future values using forecast functions and forecast sheetsRecording and running macros to automate repetitive tasksUnderstanding and making minor edits to VBA codeCombining functions to create practical formulas to complete specific tasks.Excel for Business AnalystsHow to merge data from different sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUPHow to use IF, IFS, IFERROR, SUMIF, and COUNTIF to apply logic to your analysisHow to split data using text functions SEARCH, LEFT, RIGHT, MIDHow to standardize and clean data ready for analysisAbout using the PivotTable function to perform data analysisHow to use slicers to draw out informationHow to display your analysis using Pivot ChartsAll about forecasting and using the Forecast SheetsConducting a Linear Forecast and Forecast SmoothingHow to use Conditional Formatting to highlight areas of your dataAll about Histograms and RegressionHow to use Goal Seek, Scenario Manager, and Solver to fill data gapsPower Pivot, Power Query & DAXHow to get started with Power QueryHow to connect Excel to multiple workbooksHow to get data from the web and other sourcesHow to merge and append queries using Power QueryHow the Power Pivot window worksHow to set up and manage relationships in a data modelHow to create a PivotTable to display your data from the Power Pivot data modelHow to add calculated columns using DAXHow to use functions such as CALCULATE, DIVIDE, DATESYTD in DAXAll about creating Pivot Charts and PivotTables and using your data modelHow to use slicers to adjust the data you displayPivotTables for BeginnersHow to clean and prepare your dataCreating a basic PivotTableUsing the PivotTable fields paneAdding fields and pivoting the fieldsFormatting numbers in PivotTableDifferent ways to summarize dataGrouping PivotTable dataUsing multiple fields and dimensionThe methods of aggregationHow to choose and lock the report layoutApplying PivotTable stylesSorting data and using filtersCreate pivot charts based on PivotTable dataSelecting the right chart for your dataApply conditional formattingAdd slicers and timelines to your dashboardsAdding new data to the original source datasetUpdating PivotTables and chartsAdvanced PivotTablesHow to do a PivotTable (a quick refresher)How to combine data from multiple worksheets for a PivotTableGrouping, ungrouping, and dealing with errorsHow to format a PivotTable, including adjusting stylesHow to use the Value Field SettingsAdvanced Sorting and Filtering in PivotTablesHow to use Slicers, Timelines on multiple tablesHow to create a Calculated FieldAll about GETPIVOTDATAHow to create a Pivot Chart and add sparklines and slicersHow to use 3D Maps from a PivotTableHow to update your data in a PivotTable and Pivot ChartAll about Conditional Formatting in a PivotTableHow to create amazing-looking dashboardsInteractive Excel DashboardsAbout Dashboard architecture and inspirationHow to prepare data for analysis (cleaning data)Useful formulas for creating dashboards in ExcelHow to create and edit Pivot Tables in ExcelHow to create Pivot Charts from Pivot TablesAdvanced chart techniques in ExcelHow to add interactive elements (form controls) into your dashboardsHow to create a Sales Dashboard from scratchHow to create an HR Dashboard from scratchThis bundle includes:60+ hours of video tutorials550+ individual video lecturesCourse and exercise files to follow alongCertificate of completion

Overview

Section 1: Microsoft Excel 2021 for Beginners: Introduction

Lecture 1 Course Introduction

Lecture 2 WATCH ME: Essential Information for a Successful Training Experience

Lecture 3 Downloadable Course Transcript

Lecture 4 DOWNLOAD ME: Course Files

Lecture 5 DOWNLOAD ME: Exercise Files

Lecture 6 Free Keyboard Shortcut Guides

Lecture 7 Excel 2021 vs Excel for Microsoft 365

Section 2: Microsoft Excel 2021 for Beginners: Getting Started in Excel 2021

Lecture 8 Launching Excel

Lecture 9 The Start Screen

Lecture 10 Exploring the Interface

Lecture 11 Understanding Ribbons, Tabs and Menus

Lecture 12 The Backstage Area

Lecture 13 Customizing the Quick Access Toolbar

Lecture 14 Useful Keyboard Shortcuts

Lecture 15 Getting Help

Lecture 16 Exercise 01

Section 3: Microsoft Excel 2021 for Beginners: Creating You First Excel Spreadsheet

Lecture 17 Working with Excel Templates

Lecture 18 Working with Workbooks and Worksheets

Lecture 19 Saving Workbooks and Worksheets

Lecture 20 Entering and Editing Data

Lecture 21 Navigating and Selecting Cells, Rows and Columns

Lecture 22 Exercise 02

Section 4: Microsoft Excel 2021 for Beginners: Introduction to Excel Formulas and Functions

Lecture 23 Formulas and Functions Explained

Lecture 24 Performing Calculations with the SUM Function

Lecture 25 Counting Values and Blanks

Lecture 26 Finding the Average with the AVERAGE Function

Lecture 27 Working with the MIN and MAX Functions

Lecture 28 Handling Errors in Formulas

Lecture 29 Absolute vs Relative Referencing

Lecture 30 Autosum and AutoFill

Lecture 31 Flash Fill

Lecture 32 Exercise 03

Section 5: Microsoft Excel 2021 for Beginners: Using Named Ranges

Lecture 33 What are Named Ranges?

Lecture 34 Creating Named Ranges

Lecture 35 Managing Named Ranges

Lecture 36 Using Named Ranges in Calculations

Lecture 37 Exercise 04

Section 6: Microsoft Excel 2021 for Beginners: Formatting Numbers and Cells

Lecture 38 Applying Number Formats

Lecture 39 Applying Date and Time Formats

Lecture 40 Formatting Cells, Rows and Columns

Lecture 41 Using Format Painter

Lecture 42 Exercise 05

Section 7: Microsoft Excel 2021 for Beginners: Formatting Worksheets

Lecture 43 Working with Rows and Columns

Lecture 44 Deleting and Clearing Cells

Lecture 45 Aligning Text and Numbers

Lecture 46 Applying Themes and Styles

Lecture 47 Exercise 06

Section 8: Microsoft Excel 2021 for Beginners: Working with Excel Lists

Lecture 48 How to Structure a List

Lecture 49 Sorting a List (Single-Level Sort)

Lecture 50 Sorting a List (Multi-Level Sort)

Lecture 51 Sorting Using a Custom List (Custom Sort)

Lecture 52 Using Autofilter to Filter a List

Lecture 53 Format as a Table

Lecture 54 Creating Subtotals in a List

Lecture 55 Exercise 07

Section 9: Microsoft Excel 2021 for Beginners: Moving and Linking to Data

Lecture 56 Using Cut and Copy

Lecture 57 Paste Options

Lecture 58 Pasting from the Clipboard

Lecture 59 Linking to Other Worksheets and Workbooks

Lecture 60 3D Referencing

Lecture 61 Inserting Hyperlinks to Worksheets

Lecture 62 Exercise 08

Section 10: Microsoft Excel 2021 for Beginners: An Introduction to Intermediate Formulas

Lecture 63 Looking up Information with VLOOKUP

Lecture 64 VLOOKUP Approximate Match

Lecture 65 Error Handling Functions

Lecture 66 Basic Logical Functions (IF, AND, OR)

Lecture 67 Making Decisions with IF Statements

Lecture 68 Cleaning Data Using Text Functions

Lecture 69 Working with Time and Date Functions

Lecture 70 Exercise 09

Section 11: Microsoft Excel 2021 for Beginners: Analyzing Data with Charts

Lecture 71 Choosing the Correct Chart Type

Lecture 72 Presenting Data with Charts

Lecture 73 Formatting Charts

Lecture 74 Exercise 10

Section 12: Microsoft Excel 2021 for Beginners: Conditional Formatting

Lecture 75 Highlighting Cell Values

Lecture 76 Data Bars

Lecture 77 Color Scales

Lecture 78 Icon Sets

Lecture 79 Exercise 11

Section 13: Microsoft Excel 2021 for Beginners: Inserting Items into a Spreadsheet

Lecture 80 Inserting Pictures

Lecture 81 Inserting Shapes and Text Boxes

Lecture 82 Inserting Icons and 3D Models

Lecture 83 Creating Diagrams Using SmartArt

Lecture 84 Inserting Screenshots

Lecture 85 Inserting Comments

Lecture 86 Exercise 12

Section 14: Microsoft Excel 2021 for Beginners: Working with Views and Zoom

Lecture 87 Workbook Views

Lecture 88 Using Zoom in a Worksheet

Lecture 89 Arranging Workbooks and Worksheets

Lecture 90 Freezing Panes

Lecture 91 Exercise 13

Section 15: Microsoft Excel 2021 for Beginners: Page Layout and Print Options

Lecture 92 Setting Margins and Orientation

Lecture 93 Setting and Clearing the Print Area

Lecture 94 Inserting Page Breaks

Lecture 95 Setting Print Titles and a Background

Lecture 96 Inserting Headers and Footers

Lecture 97 Printing a Workbook

Lecture 98 Exercise 14

Section 16: Microsoft Excel 2021 for Beginners: Finalizing the Spreadsheet

Lecture 99 Protecting Workbooks and Worksheets

Lecture 100 Spell Checking

Lecture 101 Inspecting the Workbook

Lecture 102 Saving the Workbook in Different Formats

Lecture 103 Sharing a Workbook

Lecture 104 Exercise 15

Section 17: Microsoft Excel 2021 for Beginners: Course Close

Lecture 105 Course Close

Section 18: Microsoft Excel 2021 Intermediate: Introduction

Lecture 106 Course Introduction

Lecture 107 WATCH ME: Essential Information for a Successful Training Experience

Lecture 108 Downloadable Course Transcript

Lecture 109 DOWNLOAD ME: Course Exercise Files

Lecture 110 DOWNLOAD ME: Course Instructor Files

Section 19: Microsoft Excel 2021 Intermediate: Designing Better Spreadsheets

Lecture 111 The Golden Rules of Spreadsheet Design

Lecture 112 Improving Readability with Cell Styles

Lecture 113 Controlling Data Input

Lecture 114 Adding Navigation Buttons

Section 20: Microsoft Excel 2021 Intermediate: Making Decisions with Logical Functions

Lecture 115 Logical Functions (AND, OR, IF)

Lecture 116 The IF Function

Lecture 117 Nested IFs

Lecture 118 The IFS Function

Lecture 119 Conditional IFs (SUMIF, COUNTIF, AVERAGEIF)

Lecture 120 Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS)

Lecture 121 Error Handling with IFERROR and IFNA

Lecture 122 Exercise 01

Section 21: Microsoft Excel 2021 Intermediate: Looking Up Information

Lecture 123 Looking Up Information using VLOOKUP (Exact Match)

Lecture 124 Looking Up Information using VLOOKUP (Approx Match)

Lecture 125 Looking Up Information Horizontally using HLOOKUP

Lecture 126 Performing Flexible Lookups with INDEX and MATCH

Lecture 127 Using XLOOKUP and XMATCH

Lecture 128 The OFFSET Function

Lecture 129 The INDIRECT Function

Lecture 130 Exercise 02

Section 22: Microsoft Excel 2021 Intermediate: Advanced Sorting and Filtering

Lecture 131 Performing Sorts on Multiple Columns

Lecture 132 Sorting Using a Custom List

Lecture 133 The SORT and SORTBY Functions

Lecture 134 Using the Advanced Filter

Lecture 135 Extracting Unique Values - The UNIQUE Function

Lecture 136 The FILTER Function

Lecture 137 Exercise 03

Section 23: Microsoft Excel 2021 Intermediate: Working with Date and Time

Lecture 138 Understanding How Dates are Stored in Excel

Lecture 139 Applying Custom Date Formats

Lecture 140 Using Date and Time Functions

Lecture 141 Using the WORKDAY and WORKDAY.INT Functions

Lecture 142 Using the NETWORKDAYS and NETWORKDAYS.INT Function

Lecture 143 Tabulate Date Differences with the DATEDIF Function

Lecture 144 Calculate Dates with EDATE and EOMONTH

Lecture 145 Exercise 04

Section 24: Microsoft Excel 2021 Intermediate: Preparing Data for Analysis

Lecture 146 Importing Data into Excel

Lecture 147 Removing Blank Rows, Cells and Duplicates

Lecture 148 Changing Case and Removing Spaces

Lecture 149 Splitting Data using Text to Columns

Lecture 150 Splitting Data using Text Functions

Lecture 151 Splitting or Combining Cell Data Using Flashfill

Lecture 152 Joining Data using CONCAT

Lecture 153 Formatting Data as a Table

Lecture 154 Exercise 05

Section 25: Microsoft Excel 2021 Intermediate: PivotTables

Lecture 155 PivotTables Explained

Lecture 156 Creating a PivotTable from Scratch

Lecture 157 Pivoting the PivotTable Fields

Lecture 158 Applying Subtotals and Grand Totals

Lecture 159 Applying Number Formatting to PivotTable Data

Lecture 160 Show Values As and Summarize Values By

Lecture 161 Grouping PivotTable Data

Lecture 162 Formatting Error Values and Empty Cells

Lecture 163 Choosing a Report Layout

Lecture 164 Applying PivotTable Styles

Lecture 165 Exercise 06

Section 26: Microsoft Excel 2021 Intermediate: Pivot Charts

Lecture 166 Creating a Pivot Chart

Lecture 167 Formatting a Pivot Chart - Part 1

Lecture 168 Formatting a Pivot Chart - Part 2

Lecture 169 Using Map Charts

Lecture 170 Exercise 07

Section 27: Microsoft Excel 2021 Intermediate: Adding Interaction to PivotTables and Charts

Lecture 171 Inserting and Formatting Slicers

Lecture 172 Inserting Timeline Slicers

Lecture 173 Connecting Slicers to Pivot Charts

Lecture 174 Updating PivotTable Data

Lecture 175 Exercise 08

Section 28: Microsoft Excel 2021 Intermediate: Interactive Dashboards

Lecture 176 What is a Dashboard?

Lecture 177 Assembling a Dashboard - Part 1

Lecture 178 Assembling a Dashboard - Part 2

Lecture 179 Assembling a Dashboard - Part 3

Lecture 180 Exercise 09

Section 29: Microsoft Excel 2021 Intermediate: Formula Auditing

Lecture 181 Troubleshooting Common Errors

Lecture 182 Tracing Precedents and Formula Auditing

Lecture 183 Exercise 10

Section 30: Microsoft Excel 2021 Intermediate: Data Validation

Lecture 184 Creating Dynamic Drop-down Lists

Lecture 185 Other Types of Data Validation

Lecture 186 Custom Data Validation

Lecture 187 Exercise 11

Section 31: Microsoft Excel 2021 Intermediate: WhatIf Analysis Tools

Lecture 188 Goal Seek and the PMT Function

Lecture 189 Using Scenario Manager

Lecture 190 Data Tables: One Variable

Lecture 191 Data Tables: Two Variables

Lecture 192 Exercise 12

Section 32: Microsoft Excel 2021 Intermediate: Course Close

Lecture 193 Course Close

Section 33: Microsoft Excel 2021 Advanced: Introduction

Lecture 194 Course Introduction

Lecture 195 WATCH ME: Essential Information for a Successful Training Experience

Lecture 196 Downloadable Course Transcript

Lecture 197 DOWNLOAD ME: Course Exercise Files

Lecture 198 DOWNLOAD ME: Course Instructor Files

Lecture 199 Exercise 01

Section 34: Microsoft Excel 2021 Advanced: Dynamic Arrays

Lecture 200 What are Dynamic Arrays?

Lecture 201 Introduction to Spills and Arrays

Lecture 202 Extract and Count Unique Entries

Lecture 203 Unique vs Distinct

Lecture 204 Extract Unique Values with Multiple Criteria

Lecture 205 Extract Unique Values by Column

Lecture 206 The SORT Function

Lecture 207 The SORT Function - Horizontal SORT

Lecture 208 The SORTBY Function

Lecture 209 The SORTBY Function - Horizontal SORT

Lecture 210 Simple SEQUENCE and Unstacking Records

Lecture 211 The FILTER Function

Lecture 212 FILTER with Logic: + Operator (OR)

Lecture 213 FILTER with Logic: * Operator (AND)

Lecture 214 FILTER with Logic: = Operator (Both or Neither)

Lecture 215 FILTER with Logic: - Operator (One or the Other)

Lecture 216 Using RANDARRAY and RANDBETWEEN to Randomize Data

Lecture 217 Using XLOOKUP to Perform Complex Lookups

Lecture 218 Using XMATCH

Lecture 219 Exercise 02

Section 35: Microsoft Excel 2021 Advanced: Advanced Lookup and Reference Functions

Lecture 220 Performing 2-Way Lookups

Lecture 221 Using the CHOOSE Function

Lecture 222 Using the SWITCH Function

Lecture 223 Exercise 03

Section 36: Microsoft Excel 2021 Advanced: Statistical Functions

Lecture 224 Using MEDIAN, MODE.SNGL and MODE.MULT

Lecture 225 Using LARGE and SMALL

Lecture 226 Ranking Data with Sorting using RANK.EQ and RANK.AVG

Lecture 227 The COUNTBLANK Function

Lecture 228 Exercise 04

Section 37: Microsoft Excel 2021 Advanced: Math Functions

Lecture 229 Rounding Values using ROUND, ROUNDUP and ROUNDDOWN

Lecture 230 Specialized Rounding (MROUND, CEILING.MATH and FLOOR.MATH)

Lecture 231 Using the AGGREGATE Function

Lecture 232 Exercise 05

Section 38: Microsoft Excel 2021 Advanced: New Functions - LET and LAMBDA

Lecture 233 The LET Function

Lecture 234 The LAMBDA Function

Lecture 235 Exercise 06

Section 39: Microsoft Excel 2021 Advanced: Advanced PivotTables and Pivot Charts

Lecture 236 Creating a Custom PivotTable Style

Lecture 237 Applying Custom Number Formatting

Lecture 238 Sorting Using Custom Lists

Lecture 239 Applying Multiple Filters to Data

Lecture 240 Adjusting Slicer Settings

Lecture 241 Locking Workbooks but not Slicers

Lecture 242 Creating a Calculated Field

Lecture 243 Creating a Calculated Item

Lecture 244 Solve Order

Lecture 245 GETPIVOTDATA

Lecture 246 Using GETPIVOTDATA - Dependent Data Validation Lists

Lecture 247 Creating a Dynamic Pivot Chart Title

Lecture 248 Adding a Dynamic Series to a Pivot Chart

Lecture 249 Adding Totals to a Stacked Column Chart

Lecture 250 Exercise 07

Section 40: Microsoft Excel 2021 Advanced: Form Controls

Lecture 251 Introduction to Form Controls

Lecture 252 Combo Box - Select from a List

Lecture 253 Check Box - Check/Uncheck Option

Lecture 254 Option Button - Choose an Option

Lecture 255 Spin Button - Move Up and Down a List

Lecture 256 List Box - Select from a List of Items

Lecture 257 Scroll Bar - Scroll Through a List

Lecture 258 Exercise 08

Section 41: Microsoft Excel 2021 Advanced: Working with Power Query

Lecture 259 Power Query Overview

Lecture 260 Importing Data Using Get and Transform

Lecture 261 The Power Query Editor

Lecture 262 Combining Files

Lecture 263 Transforming Files

Lecture 264 Loading Files into Excel

Lecture 265 Exercise 09

Section 42: Microsoft Excel 2021 Advanced: Forecasting

Lecture 266 Getting Forecast Data with FRED

Lecture 267 Creating a Linear Sales Forecast

Lecture 268 Creating a Sales Forecast with Seasonality

Lecture 269 Adding Confidence Levels

Lecture 270 Using Forecast Sheets

Lecture 271 Exercise 10

Section 43: Microsoft Excel 2021 Advanced: Macros and VBA

Lecture 272 Using Macros to Automate Tasks

Lecture 273 Recording, Saving and Running Macros

Lecture 274 Recording a Macro with Relative Referencing

Lecture 275 Multi-step Macros

Lecture 276 The VBA Editor

Lecture 277 Assigning Macros to Buttons

Lecture 278 Creating a Custom Macro Ribbon

Lecture 279 Exercise 11

Section 44: Microsoft Excel 2021 Advanced: Practical Excel - Advanced Formulas

Lecture 280 Finding the Last Occurrence of a Value

Lecture 281 Finding the Closest Value

Lecture 282 Extract Data from the Middle of a String

Lecture 283 Exercise 12

Section 45: Microsoft Excel 2021 Advanced: Course Close

Lecture 284 Course Close

Section 46: Excel for Business Analysts: Introduction

Lecture 285 Introduction to Business Analysis

Lecture 286 DOWNLOAD ME: Course Exercise Files

Lecture 287 DOWNLOAD ME: Course Instructor Files

Section 47: Excel for Business Analysts: The Basics

Lecture 288 A Recap of Basic Formulas

Lecture 289 Absolute vs Relative Referencing

Lecture 290 Exercise 01

Section 48: Excel for Business Analysts: Merging and Looking Up Data

Lecture 291 Merging Data from Different Sources

Lecture 292 Looking Up Information with VLOOKUP (Exact)

Lecture 293 Looking Up information with VLOOKUP (Approx)

Lecture 294 How to Use VLOOKUP with Data that Expands

Lecture 295 Using HLOOKUP

Lecture 296 Combining VLOOKUP and MATCH

Lecture 297 Using INDEX, MATCH and XLOOKUP

Lecture 298 Data Validation Lists with Lookups

Lecture 299 Exercise 02

Section 49: Excel for Business Analysts: Making Better Decisions with IF

Lecture 300 How to Use the IF function

Lecture 301 More Examples of the IF Function

Lecture 302 Working with Nested Ifs

Lecture 303 The IFS function

Lecture 304 Error handling with IFERROR and IFNA

Lecture 305 Using MAX and MIN instead of the IF function

Lecture 306 Using SUMIFS and COUNTIFS

Lecture 307 Exercise 03

Section 50: Excel for Business Analysts: Preparing Data for Analysis

Lecture 308 Splitting Data Using Text Functions

Lecture 309 Using the Flash Fill Command

Lecture 310 Concatenating Data

Lecture 311 Standardizing Data

Lecture 312 Formatting Data as a Table

Lecture 313 Exercise 04

Section 51: Excel for Business Analysts: PivotTables

Lecture 314 An Introduction to PivotTables

Lecture 315 Creating a PivotTable

Lecture 316 Summarizing Data

Lecture 317 Calculations in PivotTables

Lecture 318 Using Slicers to filter information

Lecture 319 Exercise 05

Section 52: Excel for Business Analysts: Visualizing Data with Charts

Lecture 320 Creating a Pivot Chart

Lecture 321 Formatting a Pivot Chart

Lecture 322 Using Sparklines

Lecture 323 A Basic Interactive Dashboard

Lecture 324 Exercise 06

Section 53: Excel for Business Analysts: Forecasting

Lecture 325 Forecast Sheets

Lecture 326 The Forecast Function

Lecture 327 Exercise 07

Section 54: Excel for Business Analysts: Additional Useful Tools in Excel

Lecture 328 Conditional Formatting

Lecture 329 The INDIRECT Function

Lecture 330 The OFFSET Function

Lecture 331 Using Histograms

Lecture 332 Regression

Lecture 333 Exercise 08

Section 55: Excel for Business Analysts: WhatIf Analysis

Lecture 334 Goal Seek

Lecture 335 Scenario Manager

Lecture 336 Data Tables

Lecture 337 Solver

Lecture 338 Exercise 09

Section 56: Excel for Business Analysts: Course Close

Lecture 339 Course Close

Section 57: PowerPivot, PowerQuery, DAX: Introduction

Lecture 340 Welcome and Overview

Lecture 341 DOWNLOAD ME: Course Exercise Files

Lecture 342 DOWNLOAD ME: Course Instructor Files

Lecture 343 What is Power Query?

Lecture 344 What is Power Pivot?

Section 58: PowerPivot, PowerQuery, DAX: Getting Started with Power Query

Lecture 345 Exploring the Power Query Editor

Lecture 346 Common Power Query Transformations

Lecture 347 Editing an Existing Query

Lecture 348 Import Multiple Files from a Folder

Lecture 349 Connect to Data in Another Excel Workbook

Lecture 350 Troubleshooting Data Errors When Using the Sample Files

Lecture 351 IMPORTANT: Checking the Location of your Query's Source

Lecture 352 Get Data From the Web

Lecture 353 Practise Exercise

Section 59: PowerPivot, PowerQuery, DAX: Useful Power Query Features

Lecture 354 Unpivoting Columns

Lecture 355 Combine Data from Multiple Tables with Merge Queries

Lecture 356 Use Merge Queries to Compare Two Tables

Lecture 357 Stack Data into One Table with Append Queries

Lecture 358 Duplicating and Referencing Queries

Lecture 359 Grouping and Aggregating Data

Lecture 360 Conditional Columns in Power Query

Lecture 361 Practise Exercise

Section 60: PowerPivot, PowerQuery, DAX: Creating the Data Model

Lecture 362 Enable the Power Pivot Add-In

Lecture 363 Understanding the Power Pivot Window

Lecture 364 Creating Relationships Between tables

Lecture 365 Managing the Relationships of the Model

Lecture 366 Creating a PivotTable from the Data Model

Lecture 367 Hide Fields from Client Tools

Lecture 368 Grouping Queries

Lecture 369 Practise Exercise

Section 61: PowerPivot, PowerQuery, DAX: Introduction to DAX

Lecture 370 Why use DAX?

Lecture 371 Creating Calculated Columns with DAX

Lecture 372 Creating your First DAX Measure

Lecture 373 The COUNTROWS Function

Lecture 374 SUMX and RELATED Functions

Lecture 375 Practise Exercise

Section 62: PowerPivot, PowerQuery, DAX: More DAX Measures

Lecture 376 Create a Date Table in Power Pivot

Lecture 377 The CALCULATE Function

Lecture 378 The DIVIDE Function

Lecture 379 Using the DATESYTD Function

Lecture 380 Calculate the Percentage of a Total

Lecture 381 Practise Exercise

Section 63: PowerPivot, PowerQuery, DAX: Using PivotTables and Slicers

Lecture 382 Create PivotTables and PivotCharts

Lecture 383 Using Slicers with your PivotTables

Lecture 384 Create a Top 10 PivotTable

Lecture 385 Practise Exercise

Section 64: PowerPivot, PowerQuery, DAX: Closing

Lecture 386 Wrap Up

Section 65: Pivot Tables for Beginners: Introduction

Lecture 387 Course Introduction

Lecture 388 WATCH ME: Essential Information for a Successful Training Experience

Lecture 389 DOWNLOAD ME: Course Exercise Files

Lecture 390 DOWNLOAD ME: Course Instructor Files

Lecture 391 What are Pivot Tables and Why are They Useful?

Section 66: Pivot Tables for Beginners: Preparing Data for Analysis

Lecture 392 The Importance of Cleaning Data

Lecture 393 Removing Blank Rows

Lecture 394 Removing Duplicate Rows

Lecture 395 Clearing Formatting

Lecture 396 Applying Number Formatting

Lecture 397 Changing the Case

Lecture 398 Removing Spaces and Non-printing Characters

Lecture 399 Merging and Splitting Columns

Lecture 400 Convert Numbers Stored as Text

Lecture 401 Finding and Replacing Text

Lecture 402 Spell Check

Lecture 403 Exercise 01

Section 67: Pivot Tables for Beginners: The Importance of Excel Tables

Lecture 404 Excel Tables vs Pivot Tables

Lecture 405 Format Data as a Table

Lecture 406 Exercise 02

Section 68: Pivot Tables for Beginners: Creating a Basic Pivot Table Report

Lecture 407 Using Recommended Pivot Tables

Lecture 408 Creating a Pivot Table from Scratch

Lecture 409 Pivot Table Ribbons and Fields

Lecture 410 Pivoting the Fields

Lecture 411 Adding Multiple Dimensions

Lecture 412 Deleting Fields and Locking the Report Layout

Lecture 413 Exercise 03

Section 69: Pivot Tables for Beginners: Aggregation and Grouping

Lecture 414 Methods of Aggregation

Lecture 415 Combining Methods of Aggregation

Lecture 416 Grouping and Ungrouping Data

Lecture 417 Exercise 04

Section 70: Pivot Tables for Beginners: Formatting Values

Lecture 418 Applying Number Formatting

Lecture 419 Handling Cells with No Data

Lecture 420 Exercise 05

Section 71: Pivot Tables for Beginners: Layout Options

Lecture 421 Subtotals and Grand Totals

Lecture 422 Choosing a Report Layout

Lecture 423 Inserting Blank Rows

Lecture 424 Exercise 06

Section 72: Pivot Tables for Beginners: Formatting Pivot Tables

Lecture 425 Applying Pivot Table Styles

Lecture 426 Modifying Pivot Table Styles

Lecture 427 Creating a Custom Pivot Table Style

Lecture 428 Exercise 07

Section 73: Pivot Tables for Beginners: Sorting and Filtering

Lecture 429 Sorting Pivot Table Data

Lecture 430 Filtering Pivot Table Data

Lecture 431 Top 10 Filter

Lecture 432 Exercise 08

Section 74: Pivot Tables for Beginners: Creating Pivot Charts

Lecture 433 Selecting the Right Chart Type

Lecture 434 Clustered Column and Bar Charts

Lecture 435 Line Charts

Lecture 436 Pie and Donut Charts

Lecture 437 Map Charts

Lecture 438 Applying Chart Layouts

Lecture 439 Exercise 09

Section 75: Pivot Tables for Beginners: Formatting Pivot Charts

Lecture 440 Adding a Slicer

Lecture 441 Formatting a Slicer

Lecture 442 Adding a Timeline

Lecture 443 Connecting Slicers and Timelines

Lecture 444 Exercise 10

Section 76: Pivot Tables for Beginners: Applying Conditional Formatting

Lecture 445 Highlight Cell Rules

Lecture 446 Data Bars

Lecture 447 Color Scales

Lecture 448 Icon Sets

Lecture 449 Exercise 11

Section 77: Pivot Tables for Beginners: Updating Pivot Tables and Pivot Charts

Lecture 450 Adding Data to the Dataset

Lecture 451 Updating Pivot Tables and Pivot Charts

Lecture 452 Exercise 12

Section 78: Pivot Tables for Beginners: Course Close and Next Steps

Lecture 453 Course Close and Next Steps

Section 79: Advanced PivotTables in Excel: Introduction

Lecture 454 Introduction to Advanced PivotTables

Lecture 455 WATCH ME: Essential Information for a Successful Training Experience

Lecture 456 DOWNLOAD ME: Course Exercise Files

Lecture 457 DOWNLOAD ME: Course Instructor Files

Lecture 458 PivotTables Recap

Section 80: Advanced PivotTables in Excel: Importing Data

Lecture 459 Importing Data From a Text File

Lecture 460 Importing Data From Access

Lecture 461 Exercise 01

Section 81: Advanced PivotTables in Excel: Preparing Data for Analysis

Lecture 462 Cleaning Data

Lecture 463 Tabular Data

Lecture 464 Exercise 02

Section 82: Advanced PivotTables in Excel: Creating and Manipulating PivotTables

Lecture 465 Creating and Manipulating a PivotTable

Lecture 466 Combining Data from Multiple Worksheets

Lecture 467 Grouping and Ungrouping

Lecture 468 Report Layouts

Lecture 469 Formatting Error Values and Empty Cells

Lecture 470 Exercise 03

Section 83: Advanced PivotTables in Excel: Formatting PivotTables

Lecture 471 PivotTable Styles

Lecture 472 Custom Number Formatting

Lecture 473 Exercise 04

Section 84: Advanced PivotTables in Excel: Value Field Settings

Lecture 474 Summarizing Values

Lecture 475 Show Values As

Lecture 476 Exercise 05

Section 85: Advanced PivotTables in Excel: Sorting and Filtering

Lecture 477 Advanced Sorting

Lecture 478 Advanced Filtering

Lecture 479 Exercise 06

Section 86: Advanced PivotTables in Excel: Interacting with PivotTables

Lecture 480 Inserting and Formatting Slicers

Lecture 481 Inserting and Formatting Timelines

Lecture 482 Connecting Slicers to Multiple PivotTables

Lecture 483 Using Slicers in Protected Workbooks

Lecture 484 Exercise 07

Section 87: Advanced PivotTables in Excel: Calculations

Lecture 485 Creating a Calculated Field

Lecture 486 Creating a Calculated Item

Lecture 487 Solve Order and List Formulas

Lecture 488 GETPIVOTDATA

Lecture 489 Exercise 08

Section 88: Advanced PivotTables in Excel: Pivot Charts

Lecture 490 Creating a Pivot Chart

Lecture 491 Formatting a Pivot Chart - Part 1

Lecture 492 Formatting a Pivot Chart - Part 2

Lecture 493 Creating a Map Chart using Pivot Data

Lecture 494 Dynamic Chart Titles

Lecture 495 Include a Sparkline with your PivotTable

Lecture 496 Exercise 09

Section 89: Advanced PivotTables in Excel: Conditional Formatting

Lecture 497 Highlighting Cell Rules

Lecture 498 Graphical Conditional Formats

Lecture 499 Conditional Formatting and Slicers

Lecture 500 Exercise 10

Section 90: Advanced PivotTables in Excel: Dashboards

Lecture 501 Creating an Interactive Dashboard - Part 1

Lecture 502 Creating an Interactive Dashboard - Part 2

Lecture 503 Updating Pivot Charts and PivotTables

Lecture 504 Exercise 11

Section 91: Advanced PivotTables in Excel: Course Close

Lecture 505 Course Close

Section 92: Interactive Dashboard in Excel: Introduction

Lecture 506 Introduction to Excel Dashboards

Lecture 507 WATCH ME: Essential Information for a Successful Training Experience

Lecture 508 Course Exercise Files

Lecture 509 DOWNLOAD ME: Course Exercise Files

Lecture 510 DOWNLOAD ME: Course Demo Files

Section 93: Interactive Dashboard in Excel: Dashboard Setup

Lecture 511 Spreadsheet Architecture

Section 94: Interactive Dashboard in Excel: Design Inspiration

Lecture 512 Dashboard Design Inspiration and Resources

Section 95: Interactive Dashboard in Excel: Preparing Data for Analysis

Lecture 513 Why Clean Data?

Lecture 514 Remove Blanks and Duplicates

Lecture 515 Use Text Functions to Clean Data

Lecture 516 Find and Replace

Lecture 517 Convert Numbers Stored as Text to Numbers

Lecture 518 Spell Check

Lecture 519 Format Data

Lecture 520 Exercise 01

Section 96: Interactive Dashboard in Excel: Useful Excel Formulas

Lecture 521 Basic INDEX and MATCH

Lecture 522 XLOOKUP (Microsoft 365 Users)

Lecture 523 Dependent, Dynamic Drop-down Lists

Lecture 524 SUMIFS and COUNTIFS

Lecture 525 LARGE and SMALL

Lecture 526 CHOOSE function

Lecture 527 Use CHOOSE with Form Controls and Charts

Lecture 528 The INDIRECT function

Lecture 529 Extracting Unique Values: UNIQUE and the Advanced Filter

Lecture 530 Exercise 02

Section 97: Interactive Dashboard in Excel: PivotTables and Pivot Charts

Lecture 531 Create a PivotTable

Lecture 532 Manipulating PivotTable Data

Lecture 533 Number Formatting

Lecture 534 Value Field Settings

Lecture 535 Show Values As

Lecture 536 Create a Pivot Chart

Lecture 537 Formatting Pivot Charts - Part 1

Lecture 538 Formatting Pivot Charts - Part 2

Lecture 539 Slicers and Timelines

Lecture 540 GETPIVOTDATA()

Lecture 541 Exercise 03

Section 98: Interactive Dashboard in Excel: Advanced Chart Techniques

Lecture 542 Add Total Values to Stacked Column Charts

Lecture 543 Dynamic Chart Titles and Slicer Macros

Lecture 544 Dynamic Map Charts

Lecture 545 Exercise 04

Section 99: Interactive Dashboard in Excel: Interactive Elements - Form Controls

Lecture 546 Introduction to Form Controls

Lecture 547 Combo Box

Lecture 548 Check Box

Lecture 549 Option Button

Lecture 550 Spin Button

Lecture 551 List Box

Lecture 552 Scroll Bar

Lecture 553 Exercise 05

Section 100: Interactive Dashboard in Excel: Sales Analysis Dashboard

Lecture 554 Dashboard Overview

Lecture 555 Data Setup

Lecture 556 Wireframe Design

Lecture 557 Form Controls: Combo Box Setup

Lecture 558 Headline Stats - SUMIFS, MAX, LARGE and INDEX and MATCH

Lecture 559 Sales Tables Calculations - IF, SUMIFS and COUNTIFS

Lecture 560 Map Chart - Dynamic Filled Map

Lecture 561 Discount Charts - Setup

Lecture 562 Stacked Column Chart - Dynamic with Totals

Lecture 563 Donut Chart - Dynamic with Icon

Lecture 564 Sparklines

Lecture 565 Conditional Formatting

Lecture 566 Dynamic Titles

Lecture 567 Assemble Dashboard - Part 1

Lecture 568 Assemble Dashboard - Part 2

Section 101: Interactive Dashboard in Excel: HR Dashboard

Lecture 569 Dashboard Overview

Lecture 570 Data Setup

Lecture 571 Headline Stats with Custom Number Formatting

Lecture 572 Gender Breakdown Infographic

Lecture 573 Age Distribution - Funnel Chart with Custom Slicer

Lecture 574 Diversity Tracker

Lecture 575 Active Employees - Grouping PivotTable Data

Lecture 576 Full Time vs Part Time - PivotTable and Pivot Chart

Lecture 577 Form Controls - Option Button Setup

Lecture 578 Employees by Job Level - Dynamic Column Chart with Title

Lecture 579 Doughnut Chart - Dynamic with Icon

Lecture 580 Top 3 Employees - XLOOKUP and LARGE

Lecture 581 New Hires - Line Chart with Timeline Slicer

Lecture 582 Assemble Dashboard

Lecture 583 Updating the Dashboard

Lecture 584 Exercise 06

Section 102: Interactive Dashboard in Excel: Course Close

Lecture 585 Course Close

Section 103: Additional Training

Lecture 586 READ ME: This Additional Training Is Optional

Lecture 587 Ten Excel Tips and Tricks - Part 1

Lecture 588 Ten Excel Tips and Tricks - Part 2

Lecture 589 Ten Excel Tips and Tricks - Part 3

Suitable for learners of all levels,Excel users upgrading from a previous version and want to keep up to date with Excel 2021 developments,Business analysts or those looking to perform Data Analysis using Excel,Beginner Excel users who want to learn how to use Pivot Tables,Advanced Excel users who want to learn Power Pivot and Power Query,Those wanting to create visually appealing dashboards in Excel