The Ultimate Microsoft Excel Mastery Bundle - 7 Courses

Master Microsoft Excel with this huge-value beginner to advanced seven-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

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 7-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 dashboardsThis bundle includes:50+ hours of video tutorials480+ 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 Excel 2021 vs Excel for Microsoft 365

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

Lecture 7 Launching Excel

Lecture 8 The Start Screen

Lecture 9 Exploring the Interface

Lecture 10 Understanding Ribbons, Tabs and Menus

Lecture 11 The Backstage Area

Lecture 12 Customizing the Quick Access Toolbar

Lecture 13 Useful Keyboard Shortcuts

Lecture 14 Getting Help

Lecture 15 Exercise 01

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

Lecture 16 Working with Excel Templates

Lecture 17 Working with Workbooks and Worksheets

Lecture 18 Saving Workbooks and Worksheets

Lecture 19 Entering and Editing Data

Lecture 20 Navigating and Selecting Cells, Rows and Columns

Lecture 21 Exercise 02

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

Lecture 22 Formulas and Functions Explained

Lecture 23 Performing Calculations with the SUM Function

Lecture 24 Counting Values and Blanks

Lecture 25 Finding the Average with the AVERAGE Function

Lecture 26 Working with the MIN and MAX Functions

Lecture 27 Handling Errors in Formulas

Lecture 28 Absolute vs Relative Referencing

Lecture 29 Autosum and AutoFill

Lecture 30 Flash Fill

Lecture 31 Exercise 03

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

Lecture 32 What are Named Ranges?

Lecture 33 Creating Named Ranges

Lecture 34 Managing Named Ranges

Lecture 35 Using Named Ranges in Calculations

Lecture 36 Exercise 04

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

Lecture 37 Applying Number Formats

Lecture 38 Applying Date and Time Formats

Lecture 39 Formatting Cells, Rows and Columns

Lecture 40 Using Format Painter

Lecture 41 Exercise 05

Section 7: Microsoft Excel 2021 for Beginners: Formatting Worksheets

Lecture 42 Working with Rows and Columns

Lecture 43 Deleting and Clearing Cells

Lecture 44 Aligning Text and Numbers

Lecture 45 Applying Themes and Styles

Lecture 46 Exercise 06

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

Lecture 47 How to Structure a List

Lecture 48 Sorting a List (Single-Level Sort)

Lecture 49 Sorting a List (Multi-Level Sort)

Lecture 50 Sorting Using a Custom List (Custom Sort)

Lecture 51 Using Autofilter to Filter a List

Lecture 52 Format as a Table

Lecture 53 Creating Subtotals in a List

Lecture 54 Exercise 07

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

Lecture 55 Using Cut and Copy

Lecture 56 Paste Options

Lecture 57 Pasting from the Clipboard

Lecture 58 Linking to Other Worksheets and Workbooks

Lecture 59 3D Referencing

Lecture 60 Inserting Hyperlinks to Worksheets

Lecture 61 Exercise 08

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

Lecture 62 Looking up Information with VLOOKUP

Lecture 63 VLOOKUP Approximate Match

Lecture 64 Error Handling Functions

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

Lecture 66 Making Decisions with IF Statements

Lecture 67 Cleaning Data Using Text Functions

Lecture 68 Working with Time and Date Functions

Lecture 69 Exercise 09

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

Lecture 70 Choosing the Correct Chart Type

Lecture 71 Presenting Data with Charts

Lecture 72 Formatting Charts

Lecture 73 Exercise 10

Section 12: Microsoft Excel 2021 for Beginners: Conditional Formatting

Lecture 74 Highlighting Cell Values

Lecture 75 Data Bars

Lecture 76 Color Scales

Lecture 77 Icon Sets

Lecture 78 Exercise 11

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

Lecture 79 Inserting Pictures

Lecture 80 Inserting Shapes and Text Boxes

Lecture 81 Inserting Icons and 3D Models

Lecture 82 Creating Diagrams Using SmartArt

Lecture 83 Inserting Screenshots

Lecture 84 Inserting Comments

Lecture 85 Exercise 12

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

Lecture 86 Workbook Views

Lecture 87 Using Zoom in a Worksheet

Lecture 88 Arranging Workbooks and Worksheets

Lecture 89 Freezing Panes

Lecture 90 Exercise 13

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

Lecture 91 Setting Margins and Orientation

Lecture 92 Setting and Clearing the Print Area

Lecture 93 Inserting Page Breaks

Lecture 94 Setting Print Titles and a Background

Lecture 95 Inserting Headers and Footers

Lecture 96 Printing a Workbook

Lecture 97 Exercise 14

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

Lecture 98 Protecting Workbooks and Worksheets

Lecture 99 Spell Checking

Lecture 100 Inspecting the Workbook

Lecture 101 Saving the Workbook in Different Formats

Lecture 102 Sharing a Workbook

Lecture 103 Exercise 15

Section 17: Microsoft Excel 2021 for Beginners: Course Close

Lecture 104 Course Close

Section 18: Microsoft Excel 2021 Intermediate: Introduction

Lecture 105 Course Introduction

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

Lecture 107 Downloadable Course Transcript

Lecture 108 DOWNLOAD ME: Course Exercise Files

Lecture 109 DOWNLOAD ME: Course Instructor Files

Section 19: Microsoft Excel 2021 Intermediate: Designing Better Spreadsheets

Lecture 110 The Golden Rules of Spreadsheet Design

Lecture 111 Improving Readability with Cell Styles

Lecture 112 Controlling Data Input

Lecture 113 Adding Navigation Buttons

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

Lecture 114 Logical Functions (AND, OR, IF)

Lecture 115 The IF Function

Lecture 116 Nested IFs

Lecture 117 The IFS Function

Lecture 118 Conditional IFs (SUMIF, COUNTIF, AVERAGEIF)

Lecture 119 Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS)

Lecture 120 Error Handling with IFERROR and IFNA

Lecture 121 Exercise 01

Section 21: Microsoft Excel 2021 Intermediate: Looking Up Information

Lecture 122 Looking Up Information using VLOOKUP (Exact Match)

Lecture 123 Looking Up Information using VLOOKUP (Approx Match)

Lecture 124 Looking Up Information Horizontally using HLOOKUP

Lecture 125 Performing Flexible Lookups with INDEX and MATCH

Lecture 126 Using XLOOKUP and XMATCH

Lecture 127 The OFFSET Function

Lecture 128 The INDIRECT Function

Lecture 129 Exercise 02

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

Lecture 130 Performing Sorts on Multiple Columns

Lecture 131 Sorting Using a Custom List

Lecture 132 The SORT and SORTBY Functions

Lecture 133 Using the Advanced Filter

Lecture 134 Extracting Unique Values - The UNIQUE Function

Lecture 135 The FILTER Function

Lecture 136 Exercise 03

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

Lecture 137 Understanding How Dates are Stored in Excel

Lecture 138 Applying Custom Date Formats

Lecture 139 Using Date and Time Functions

Lecture 140 Using the WORKDAY and WORKDAY.INT Functions

Lecture 141 Using the NETWORKDAYS and NETWORKDAYS.INT Function

Lecture 142 Tabulate Date Differences with the DATEDIF Function

Lecture 143 Calculate Dates with EDATE and EOMONTH

Lecture 144 Exercise 04

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

Lecture 145 Importing Data into Excel

Lecture 146 Removing Blank Rows, Cells and Duplicates

Lecture 147 Changing Case and Removing Spaces

Lecture 148 Splitting Data using Text to Columns

Lecture 149 Splitting Data using Text Functions

Lecture 150 Splitting or Combining Cell Data Using Flashfill

Lecture 151 Joining Data using CONCAT

Lecture 152 Formatting Data as a Table

Lecture 153 Exercise 05

Section 25: Microsoft Excel 2021 Intermediate: PivotTables

Lecture 154 PivotTables Explained

Lecture 155 Creating a PivotTable from Scratch

Lecture 156 Pivoting the PivotTable Fields

Lecture 157 Applying Subtotals and Grand Totals

Lecture 158 Applying Number Formatting to PivotTable Data

Lecture 159 Show Values As and Summarize Values By

Lecture 160 Grouping PivotTable Data

Lecture 161 Formatting Error Values and Empty Cells

Lecture 162 Choosing a Report Layout

Lecture 163 Applying PivotTable Styles

Lecture 164 Exercise 06

Section 26: Microsoft Excel 2021 Intermediate: Pivot Charts

Lecture 165 Creating a Pivot Chart

Lecture 166 Formatting a Pivot Chart - Part 1

Lecture 167 Formatting a Pivot Chart - Part 2

Lecture 168 Using Map Charts

Lecture 169 Exercise 07

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

Lecture 170 Inserting and Formatting Slicers

Lecture 171 Inserting Timeline Slicers

Lecture 172 Connecting Slicers to Pivot Charts

Lecture 173 Updating PivotTable Data

Lecture 174 Exercise 08

Section 28: Microsoft Excel 2021 Intermediate: Interactive Dashboards

Lecture 175 What is a Dashboard?

Lecture 176 Assembling a Dashboard - Part 1

Lecture 177 Assembling a Dashboard - Part 2

Lecture 178 Assembling a Dashboard - Part 3

Lecture 179 Exercise 09

Section 29: Microsoft Excel 2021 Intermediate: Formula Auditing

Lecture 180 Troubleshooting Common Errors

Lecture 181 Tracing Precedents and Formula Auditing

Lecture 182 Exercise 10

Section 30: Microsoft Excel 2021 Intermediate: Data Validation

Lecture 183 Creating Dynamic Drop-down Lists

Lecture 184 Other Types of Data Validation

Lecture 185 Custom Data Validation

Lecture 186 Exercise 11

Section 31: Microsoft Excel 2021 Intermediate: WhatIf Analysis Tools

Lecture 187 Goal Seek and the PMT Function

Lecture 188 Using Scenario Manager

Lecture 189 Data Tables: One Variable

Lecture 190 Data Tables: Two Variables

Lecture 191 Exercise 12

Section 32: Microsoft Excel 2021 Intermediate: Course Close

Lecture 192 Course Close

Section 33: Microsoft Excel 2021 Advanced: Introduction

Lecture 193 Course Introduction

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

Lecture 195 Downloadable Course Transcript

Lecture 196 DOWNLOAD ME: Course Exercise Files

Lecture 197 DOWNLOAD ME: Course Instructor Files

Lecture 198 Exercise 01

Section 34: Microsoft Excel 2021 Advanced: Dynamic Arrays

Lecture 199 What are Dynamic Arrays?

Lecture 200 Introduction to Spills and Arrays

Lecture 201 Extract and Count Unique Entries

Lecture 202 Unique vs Distinct

Lecture 203 Extract Unique Values with Multiple Criteria

Lecture 204 Extract Unique Values by Column

Lecture 205 The SORT Function

Lecture 206 The SORT Function - Horizontal SORT

Lecture 207 The SORTBY Function

Lecture 208 The SORTBY Function - Horizontal SORT

Lecture 209 Simple SEQUENCE and Unstacking Records

Lecture 210 The FILTER Function

Lecture 211 FILTER with Logic: + Operator (OR)

Lecture 212 FILTER with Logic: * Operator (AND)

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

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

Lecture 215 Using RANDARRAY and RANDBETWEEN to Randomize Data

Lecture 216 Using XLOOKUP to Perform Complex Lookups

Lecture 217 Using XMATCH

Lecture 218 Exercise 02

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

Lecture 219 Performing 2-Way Lookups

Lecture 220 Using the CHOOSE Function

Lecture 221 Using the SWITCH Function

Lecture 222 Exercise 03

Section 36: Microsoft Excel 2021 Advanced: Statistical Functions

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

Lecture 224 Using LARGE and SMALL

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

Lecture 226 The COUNTBLANK Function

Lecture 227 Exercise 04

Section 37: Microsoft Excel 2021 Advanced: Math Functions

Lecture 228 Rounding Values using ROUND, ROUNDUP and ROUNDDOWN

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

Lecture 230 Using the AGGREGATE Function

Lecture 231 Exercise 05

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

Lecture 232 The LET Function

Lecture 233 The LAMBDA Function

Lecture 234 Exercise 06

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

Lecture 235 Creating a Custom PivotTable Style

Lecture 236 Applying Custom Number Formatting

Lecture 237 Sorting Using Custom Lists

Lecture 238 Applying Multiple Filters to Data

Lecture 239 Adjusting Slicer Settings

Lecture 240 Locking Workbooks but not Slicers

Lecture 241 Creating a Calculated Field

Lecture 242 Creating a Calculated Item

Lecture 243 Solve Order

Lecture 244 GETPIVOTDATA

Lecture 245 Using GETPIVOTDATA - Dependent Data Validation Lists

Lecture 246 Creating a Dynamic Pivot Chart Title

Lecture 247 Adding a Dynamic Series to a Pivot Chart

Lecture 248 Adding Totals to a Stacked Column Chart

Lecture 249 Exercise 07

Section 40: Microsoft Excel 2021 Advanced: Form Controls

Lecture 250 Introduction to Form Controls

Lecture 251 Combo Box - Select from a List

Lecture 252 Check Box - Check/Uncheck Option

Lecture 253 Option Button - Choose an Option

Lecture 254 Spin Button - Move Up and Down a List

Lecture 255 List Box - Select from a List of Items

Lecture 256 Scroll Bar - Scroll Through a List

Lecture 257 Exercise 08

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

Lecture 258 Power Query Overview

Lecture 259 Importing Data Using Get and Transform

Lecture 260 The Power Query Editor

Lecture 261 Combining Files

Lecture 262 Transforming Files

Lecture 263 Loading Files into Excel

Lecture 264 Exercise 09

Section 42: Microsoft Excel 2021 Advanced: Forecasting

Lecture 265 Getting Forecast Data with FRED

Lecture 266 Creating a Linear Sales Forecast

Lecture 267 Creating a Sales Forecast with Seasonality

Lecture 268 Adding Confidence Levels

Lecture 269 Using Forecast Sheets

Lecture 270 Exercise 10

Section 43: Microsoft Excel 2021 Advanced: Macros and VBA

Lecture 271 Using Macros to Automate Tasks

Lecture 272 Recording, Saving and Running Macros

Lecture 273 Recording a Macro with Relative Referencing

Lecture 274 Multi-step Macros

Lecture 275 The VBA Editor

Lecture 276 Assigning Macros to Buttons

Lecture 277 Creating a Custom Macro Ribbon

Lecture 278 Exercise 11

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

Lecture 279 Finding the Last Occurrence of a Value

Lecture 280 Finding the Closest Value

Lecture 281 Extract Data from the Middle of a String

Lecture 282 Exercise 12

Section 45: Microsoft Excel 2021 Advanced: Course Close

Lecture 283 Course Close

Section 46: Excel for Business Analysts: Introduction

Lecture 284 Introduction to Business Analysis

Lecture 285 DOWNLOAD ME: Course Exercise Files

Lecture 286 DOWNLOAD ME: Course Instructor Files

Section 47: Excel for Business Analysts: The Basics

Lecture 287 A Recap of Basic Formulas

Lecture 288 Absolute vs Relative Referencing

Lecture 289 Exercise 01

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

Lecture 290 Merging Data from Different Sources

Lecture 291 Looking Up Information with VLOOKUP (Exact)

Lecture 292 Looking Up information with VLOOKUP (Approx)

Lecture 293 How to Use VLOOKUP with Data that Expands

Lecture 294 Using HLOOKUP

Lecture 295 Combining VLOOKUP and MATCH

Lecture 296 Using INDEX, MATCH and XLOOKUP

Lecture 297 Data Validation Lists with Lookups

Lecture 298 Exercise 02

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

Lecture 299 How to Use the IF function

Lecture 300 More Examples of the IF Function

Lecture 301 Working with Nested Ifs

Lecture 302 The IFS function

Lecture 303 Error handling with IFERROR and IFNA

Lecture 304 Using MAX and MIN instead of the IF function

Lecture 305 Using SUMIFS and COUNTIFS

Lecture 306 Exercise 03

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

Lecture 307 Splitting Data Using Text Functions

Lecture 308 Using the Flash Fill Command

Lecture 309 Concatenating Data

Lecture 310 Standardizing Data

Lecture 311 Formatting Data as a Table

Lecture 312 Exercise 04

Section 51: Excel for Business Analysts: PivotTables

Lecture 313 An Introduction to PivotTables

Lecture 314 Creating a PivotTable

Lecture 315 Summarizing Data

Lecture 316 Calculations in PivotTables

Lecture 317 Using Slicers to filter information

Lecture 318 Exercise 05

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

Lecture 319 Creating a Pivot Chart

Lecture 320 Formatting a Pivot Chart

Lecture 321 Using Sparklines

Lecture 322 A Basic Interactive Dashboard

Lecture 323 Exercise 06

Section 53: Excel for Business Analysts: Forecasting

Lecture 324 Forecast Sheets

Lecture 325 The Forecast Function

Lecture 326 Exercise 07

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

Lecture 327 Conditional Formatting

Lecture 328 The INDIRECT Function

Lecture 329 The OFFSET Function

Lecture 330 Using Histograms

Lecture 331 Regression

Lecture 332 Exercise 08

Section 55: Excel for Business Analysts: WhatIf Analysis

Lecture 333 Goal Seek

Lecture 334 Scenario Manager

Lecture 335 Data Tables

Lecture 336 Solver

Lecture 337 Exercise 09

Section 56: Excel for Business Analysts: Course Close

Lecture 338 Course Close

Section 57: PowerPivot, PowerQuery, DAX: Introduction

Lecture 339 Welcome and Overview

Lecture 340 DOWNLOAD ME: Course Exercise Files

Lecture 341 DOWNLOAD ME: Course Instructor Files

Lecture 342 What is Power Query?

Lecture 343 What is Power Pivot?

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

Lecture 344 Exploring the Power Query Editor

Lecture 345 Common Power Query Transformations

Lecture 346 Editing an Existing Query

Lecture 347 Import Multiple Files from a Folder

Lecture 348 Connect to Data in Another Excel Workbook

Lecture 349 Troubleshooting Data Errors When Using the Sample Files

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

Lecture 351 Get Data From the Web

Lecture 352 Practise Exercise

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

Lecture 353 Unpivoting Columns

Lecture 354 Combine Data from Multiple Tables with Merge Queries

Lecture 355 Use Merge Queries to Compare Two Tables

Lecture 356 Stack Data into One Table with Append Queries

Lecture 357 Duplicating and Referencing Queries

Lecture 358 Grouping and Aggregating Data

Lecture 359 Conditional Columns in Power Query

Lecture 360 Practise Exercise

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

Lecture 361 Enable the Power Pivot Add-In

Lecture 362 Understanding the Power Pivot Window

Lecture 363 Creating Relationships Between tables

Lecture 364 Managing the Relationships of the Model

Lecture 365 Creating a PivotTable from the Data Model

Lecture 366 Hide Fields from Client Tools

Lecture 367 Grouping Queries

Lecture 368 Practise Exercise

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

Lecture 369 Why use DAX?

Lecture 370 Creating Calculated Columns with DAX

Lecture 371 Creating your First DAX Measure

Lecture 372 The COUNTROWS Function

Lecture 373 SUMX and RELATED Functions

Lecture 374 Practise Exercise

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

Lecture 375 Create a Date Table in Power Pivot

Lecture 376 The CALCULATE Function

Lecture 377 The DIVIDE Function

Lecture 378 Using the DATESYTD Function

Lecture 379 Calculate the Percentage of a Total

Lecture 380 Practise Exercise

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

Lecture 381 Create PivotTables and PivotCharts

Lecture 382 Using Slicers with your PivotTables

Lecture 383 Create a Top 10 PivotTable

Lecture 384 Practise Exercise

Section 64: PowerPivot, PowerQuery, DAX: Closing

Lecture 385 Wrap Up

Section 65: Pivot Tables for Beginners: Introduction

Lecture 386 Course Introduction

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

Lecture 388 DOWNLOAD ME: Course Exercise Files

Lecture 389 DOWNLOAD ME: Course Instructor Files

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

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

Lecture 391 The Importance of Cleaning Data

Lecture 392 Removing Blank Rows

Lecture 393 Removing Duplicate Rows

Lecture 394 Clearing Formatting

Lecture 395 Applying Number Formatting

Lecture 396 Changing the Case

Lecture 397 Removing Spaces and Non-printing Characters

Lecture 398 Merging and Splitting Columns

Lecture 399 Convert Numbers Stored as Text

Lecture 400 Finding and Replacing Text

Lecture 401 Spell Check

Lecture 402 Exercise 01

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

Lecture 403 Excel Tables vs Pivot Tables

Lecture 404 Format Data as a Table

Lecture 405 Exercise 02

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

Lecture 406 Using Recommended Pivot Tables

Lecture 407 Creating a Pivot Table from Scratch

Lecture 408 Pivot Table Ribbons and Fields

Lecture 409 Pivoting the Fields

Lecture 410 Adding Multiple Dimensions

Lecture 411 Deleting Fields and Locking the Report Layout

Lecture 412 Exercise 03

Section 69: Pivot Tables for Beginners: Aggregation and Grouping

Lecture 413 Methods of Aggregation

Lecture 414 Combining Methods of Aggregation

Lecture 415 Grouping and Ungrouping Data

Lecture 416 Exercise 04

Section 70: Pivot Tables for Beginners: Formatting Values

Lecture 417 Applying Number Formatting

Lecture 418 Handling Cells with No Data

Lecture 419 Exercise 05

Section 71: Pivot Tables for Beginners: Layout Options

Lecture 420 Subtotals and Grand Totals

Lecture 421 Choosing a Report Layout

Lecture 422 Inserting Blank Rows

Lecture 423 Exercise 06

Section 72: Pivot Tables for Beginners: Formatting Pivot Tables

Lecture 424 Applying Pivot Table Styles

Lecture 425 Modifying Pivot Table Styles

Lecture 426 Creating a Custom Pivot Table Style

Lecture 427 Exercise 07

Section 73: Pivot Tables for Beginners: Sorting and Filtering

Lecture 428 Sorting Pivot Table Data

Lecture 429 Filtering Pivot Table Data

Lecture 430 Top 10 Filter

Lecture 431 Exercise 08

Section 74: Pivot Tables for Beginners: Creating Pivot Charts

Lecture 432 Selecting the Right Chart Type

Lecture 433 Clustered Column and Bar Charts

Lecture 434 Line Charts

Lecture 435 Pie and Donut Charts

Lecture 436 Map Charts

Lecture 437 Applying Chart Layouts

Lecture 438 Exercise 09

Section 75: Pivot Tables for Beginners: Formatting Pivot Charts

Lecture 439 Adding a Slicer

Lecture 440 Formatting a Slicer

Lecture 441 Adding a Timeline

Lecture 442 Connecting Slicers and Timelines

Lecture 443 Exercise 10

Section 76: Pivot Tables for Beginners: Applying Conditional Formatting

Lecture 444 Highlight Cell Rules

Lecture 445 Data Bars

Lecture 446 Color Scales

Lecture 447 Icon Sets

Lecture 448 Exercise 11

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

Lecture 449 Adding Data to the Dataset

Lecture 450 Updating Pivot Tables and Pivot Charts

Lecture 451 Exercise 12

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

Lecture 452 Course Close and Next Steps

Section 79: Advanced PivotTables in Excel: Introduction

Lecture 453 Introduction to Advanced PivotTables

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

Lecture 455 DOWNLOAD ME: Course Exercise Files

Lecture 456 DOWNLOAD ME: Course Instructor Files

Lecture 457 PivotTables Recap

Section 80: Advanced PivotTables in Excel: Importing Data

Lecture 458 Importing Data From a Text File

Lecture 459 Importing Data From Access

Lecture 460 Exercise 01

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

Lecture 461 Cleaning Data

Lecture 462 Tabular Data

Lecture 463 Exercise 02

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

Lecture 464 Creating and Manipulating a PivotTable

Lecture 465 Combining Data from Multiple Worksheets

Lecture 466 Grouping and Ungrouping

Lecture 467 Report Layouts

Lecture 468 Formatting Error Values and Empty Cells

Lecture 469 Exercise 03

Section 83: Advanced PivotTables in Excel: Formatting PivotTables

Lecture 470 PivotTable Styles

Lecture 471 Custom Number Formatting

Lecture 472 Exercise 04

Section 84: Advanced PivotTables in Excel: Value Field Settings

Lecture 473 Summarizing Values

Lecture 474 Show Values As

Lecture 475 Exercise 05

Section 85: Advanced PivotTables in Excel: Sorting and Filtering

Lecture 476 Advanced Sorting

Lecture 477 Advanced Filtering

Lecture 478 Exercise 06

Section 86: Advanced PivotTables in Excel: Interacting with PivotTables

Lecture 479 Inserting and Formatting Slicers

Lecture 480 Inserting and Formatting Timelines

Lecture 481 Connecting Slicers to Multiple PivotTables

Lecture 482 Using Slicers in Protected Workbooks

Lecture 483 Exercise 07

Section 87: Advanced PivotTables in Excel: Calculations

Lecture 484 Creating a Calculated Field

Lecture 485 Creating a Calculated Item

Lecture 486 Solve Order and List Formulas

Lecture 487 GETPIVOTDATA

Lecture 488 Exercise 08

Section 88: Advanced PivotTables in Excel: Pivot Charts

Lecture 489 Creating a Pivot Chart

Lecture 490 Formatting a Pivot Chart - Part 1

Lecture 491 Formatting a Pivot Chart - Part 2

Lecture 492 Creating a Map Chart using Pivot Data

Lecture 493 Dynamic Chart Titles

Lecture 494 Include a Sparkline with your PivotTable

Lecture 495 Exercise 09

Section 89: Advanced PivotTables in Excel: Conditional Formatting

Lecture 496 Highlighting Cell Rules

Lecture 497 Graphical Conditional Formats

Lecture 498 Conditional Formatting and Slicers

Lecture 499 Exercise 10

Section 90: Advanced PivotTables in Excel: Dashboards

Lecture 500 Creating an Interactive Dashboard - Part 1

Lecture 501 Creating an Interactive Dashboard - Part 2

Lecture 502 Updating Pivot Charts and PivotTables

Lecture 503 Exercise 11

Section 91: Advanced PivotTables in Excel: Course Close

Lecture 504 Course Close

Section 92: Additional Training

Lecture 505 Ten Excel Tips and Tricks - Part 1

Lecture 506 Ten Excel Tips and Tricks - Part 2

Lecture 507 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