Published 11/2022
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 27.95 GB | Duration: 54h 49m

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
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
Screenshots