Master Excel And Solve 500 Problems (Beginner - Advanced)

Learning MS Excel Functions, Tools, Dashboards, Power Pivot, Power Query and VBA with Practice.

What you'll learn

Solve easy, medium and difficult problems using functions;

Use various tools to clean and arrange the data;

Build automated dashboards using Power Pivot, Power Query and Pivot Tables;

Record and write VBA codes to automate operations;

Master shortcuts.

Requirements

No technical experience needed. You will learn everything you need in this course.

Description

This MS Excel course covers all levels - from beginner to advanced. The course includes 500 chronologically structured video tutorials in which student can learn how different complexity level problems can be solved using various functions and tools. Every video tutorial has its own worksheet where problem definition is explained in detail.The main point the course covers are:Formulas and functions - the course is highly concentrated on formulas and functions. We start from the most basic mathematical formulas, like how to write 2+2=4 in a cell. In later video tutorials, as the problems become more and more complex, students will learn how to write nested functions and some of the solutions even require nesting up to 10 different functions inside each other;Tools - covered up to 20 different excel tools that help to clean and arrange the data, create user templates, visualize the data, etc.;Charts and Dashboards - the course includes creating different types of charts and building dashboards to visualize the data using Power Query, Power Pivot and Pivot Tables;VBA Macros - included videos on how to record macros, write VBA codes manually, build user forms, build codes that run on specific triggers and creating user defined functions using VBA.In addition to the points listed above, the course includes some of the most commonly used shortcuts, using $ signs in functions, working with different worksheets at the same time and much more.As an additional material, glossary supporting file is provided where students can find the list of all 500 videos with the indication of:Problem complexity;Functions/tools to be used to solve the problem;Suggestion on every video whether the problem should be solved independently (Do it yourself) or should the user watch the video and solve the problem along with instructor (Watch video).This file makes it easy for the users to navigate through the course and find the videos they are interested in.Let the practice begin!

Overview

Section 1: Introduction to Excel

Lecture 1 Section 1 Introduction

Lecture 2 S01P01 - General overview

Lecture 3 S01P02 - Freeze Panes, Gridlines, Formula Bar, Headings, Split Window, Comments

Lecture 4 S01P03 - Freeze Panes, Gridlines, Headings, Split Window, Comments

Lecture 5 S01P04 - Sort

Lecture 6 S01P05 - Sort

Lecture 7 S01P06 - Sort

Lecture 8 S01P07 - Sort

Lecture 9 S01P08 - Filter

Lecture 10 S01P09 - Filter

Lecture 11 S01P10 - Filter, Add current selection to filter, * symbol

Lecture 12 S01P11 - Filter

Lecture 13 S01P12 - Filter

Lecture 14 S01P13 - Remove Duplicates

Lecture 15 S01P14 - Remove Duplicates, Sort

Lecture 16 S01P15 - Hide, Unhide

Lecture 17 S01P16 - Filter, Unhide

Lecture 18 S01P17 - Insert Row, Delete Row

Lecture 19 S01P18 - Delete Row, Sort

Lecture 20 S01P19 - Group

Lecture 21 S01P20 - Group, Freeze Panes

Lecture 22 S01P21 - Find, Replace

Lecture 23 S01P22 - Replace, * symbol

Lecture 24 S01P23 - Replace, Format cells

Lecture 25 S01P24 - Replace, Format Cells, Find in comments

Lecture 26 S01P25 - Delete Comments, Go to Special, Filter

Lecture 27 S01P26 - Delete Row, Filter, Go to Special

Lecture 28 S01P27 - Sort, Remove Duplicates

Lecture 29 S01P28 - Sort, Remove Duplicates

Lecture 30 S01P29 - Horizontal Sort

Lecture 31 S01P30 - Hyperlink

Section 2: Introduction to Formulas

Lecture 32 Section 2 Introduction

Lecture 33 C02P01 - Introduction to formulas: mathematical calculation

Lecture 34 C02P02 - Mathematical calculation

Lecture 35 C02P03 - Mathematical calculation

Lecture 36 C02P04 - Mathematical calculation

Lecture 37 C02P05 - Mathematical calculation

Lecture 38 C02P06 - Mathematical calculation

Lecture 39 C02P07 - Mathematical calculation

Lecture 40 C02P08 - Mathematical calculation

Lecture 41 C02P09 - Introduction to Absolute and Relative references, $ signs

Lecture 42 C02P10 - Absolute and Relative references, $ signs

Lecture 43 C02P11 - Absolute and Relative references, $ signs

Lecture 44 C02P12 - Absolute and Relative references, $ signs

Lecture 45 C02P13 - Absolute and Relative references, $ signs

Lecture 46 C02P14 - Absolute and Relative references, $ signs

Lecture 47 C02P15 - Absolute and Relative references, $ signs

Lecture 48 C02P16 - Absolute and Relative references, $ signs

Lecture 49 C02P17 - Absolute and Relative references, $ signs

Lecture 50 C02P18 - Copy + Paste special

Lecture 51 C02P19 - Copy + Paste special

Lecture 52 C02P20 - Copy + Paste special

Lecture 53 C02P21 - Copy + Paste special

Lecture 54 C02P22 - Copy + Paste special

Lecture 55 C02P23 - Copy + Paste special

Lecture 56 C02P24 - Copy + Paste special

Lecture 57 C02P25 - Copy + Paste special

Lecture 58 C02P26 - Copy + Paste special

Lecture 59 C02P27 - Copy + Paste special

Lecture 60 C02P28 - Copy + Paste special

Lecture 61 C02P29 - Copy + Paste special

Lecture 62 C02P30 - Copy + Paste special

Section 3: Mathematical Formulas 1

Lecture 63 Section 3 Introduction

Lecture 64 C03P01 - SUM

Lecture 65 C03P02 - SUM, AVERAGE

Lecture 66 C03P03 - SUM, AVERAGE

Lecture 67 C03P04 - SUM, AVERAGE, $ signs

Lecture 68 C03P05 - SUM, AVERAGE, $ signs

Lecture 69 C03P06 - SUM, reference cells from other worksheets

Lecture 70 C03P07 - SUM, reference cells from other worksheets

Lecture 71 C03P08 - SUM, working on multiple worksheets at the same time

Lecture 72 C03P09 - AVERAGE, working on multiple worksheets at the same time

Lecture 73 C03P10 - working on multiple worksheets at the same time

Lecture 74 C03P11 - SUM, reference cells from other worksheets

Lecture 75 C03P12 - COUNT

Lecture 76 C03P13 - COUNT, COUNTA, COUNTBLANK

Lecture 77 C03P14 - COUNT, SUM

Lecture 78 C03P15 - COUNT, reference cells from other worksheets

Lecture 79 C03P16 - COUNT, reference cells from other worksheets

Lecture 80 C03P17 - COUNT

Lecture 81 C03P18 - COUNTBLANK

Lecture 82 C03P19 - SUM, COUNT, COUNTA

Lecture 83 C03P20 - SUM, COUNT, COUNTA, filter

Lecture 84 C03P21 - SUM, COUNT, COUNTA, sort

Lecture 85 C03P22 - MIN, MAX, SUM AVERAGE

Lecture 86 C03P23 - MIN, MAX

Lecture 87 C03P24 - MIN, MAX, reference cells from other worksheets

Lecture 88 C03P25 - MIN, MAX, reference cells from other worksheets

Lecture 89 C03P26 - SUM, ABS

Lecture 90 C03P27 - ABS, Paste Special

Lecture 91 C03P28 - PRODUCT

Lecture 92 C03P29 - PRODUCT, $ signs

Lecture 93 C03P30 - AVERAGE, reference cells from other worksheets, power operator ^

Section 4: Text Formulas 1

Lecture 94 Section 4 Introduction

Lecture 95 C04P01 - & symbol

Lecture 96 C04P02 - CONCATENATE

Lecture 97 C04P03 - CONCATENATE / & symbol

Lecture 98 C04P04 - SUM, AVERAGE, CONCATENATE / & symbol

Lecture 99 C04P05 - LEFT, RIGHT

Lecture 100 C04P06 - LEFT, RIGHT

Lecture 101 C04P07 - LEFT, CONCATENATE / & symbol

Lecture 102 C04P08 - FIND / SEARCH

Lecture 103 C04P09 - FIND

Lecture 104 C04P10 - MID

Lecture 105 C04P11 - LEN

Lecture 106 C04P12 - LEN, SUM

Lecture 107 C04P13 - RIGHT, LEN

Lecture 108 C04P14 - LEFT, RIGHT, LEN, FIND

Lecture 109 C04P15 - MID, FIND

Lecture 110 C04P16 - LEFT, MID, FIND, CONCATENATE / & symbol

Lecture 111 C04P17 - REPLACE

Lecture 112 C04P18 - REPLACE, RIGHT

Lecture 113 C04P19 - REPLACE, RIGHT, FIND

Lecture 114 C04P20 - SUBSTITUTE

Lecture 115 C04P21 - SUBSTITUTE

Lecture 116 C04P22 - LEN, SUBSTITUTE

Lecture 117 C04P23 - LEN, SUBSTITUTE, LEFT, FIND

Lecture 118 C04P24 - LEN, SUBSTITUTE, SUM

Lecture 119 C04P25 - LEN, SUBSTITUTE

Lecture 120 C04P26 - TRIM

Lecture 121 C04P27 - SUBSTITUTE, TRIM

Lecture 122 C04P28 - SUBSTITUTE, TRIM, CONCATENATE / & symbol

Lecture 123 C04P29 - SUBSTITUTE, TRIM

Lecture 124 C04P30 - SUBSTITUTE, FIND

Lecture 125 C04P31 - SUBSTITUTE, FIND, MID

Section 5: Text Formulas 2

Lecture 126 Section 5 Introduction

Lecture 127 C05P01 - UPPER, LOWER, PROPER

Lecture 128 C05P02 - PROPER, TRIM

Lecture 129 C05P03 - LEFT, FIND, UPPER, MID, SUBSTITUTE

Lecture 130 C05P04 - REPT

Lecture 131 C05P05 - REPT, COUNT

Lecture 132 C05P06 - REPT

Lecture 133 C05P07 - REPT, CONCATENATE / & symbol

Lecture 134 C05P08 - N, T

Lecture 135 C05P09 - CONCAT

Lecture 136 C05P10 - TEXTJOIN

Lecture 137 C05P11 - TEXTJOIN

Lecture 138 C05P12 - Text to Columns

Lecture 139 C05P13 - Text to Columns

Lecture 140 C05P14 - Text to Columns

Lecture 141 C05P15 - Text to Columns

Lecture 142 C05P16 - Text to Columns

Lecture 143 C05P17 - Text to Columns

Lecture 144 C05P18 - Text to Columns

Lecture 145 C05P19 - Text to Columns

Lecture 146 C05P20 - Text to Columns

Lecture 147 C05P21 - Flash Fill

Lecture 148 C05P22 - Flash Fill

Lecture 149 C05P23 - Flash Fill

Lecture 150 C05P24 - Convert to Number, SUM

Lecture 151 C05P25 - VALUE, SUM

Lecture 152 C05P26 - VALUE, MID, FIND, SUM

Lecture 153 C05P27 - VALUE, MID, FIND, SUM, Filter, Select Visible Cells

Lecture 154 C05P28 - FORMULATEXT

Lecture 155 C05P29 - FORMULATEXT, VALUE, MID, FIND, SUM

Lecture 156 C05P30 - FORMULATEXT, VALUE, MID, FIND, SUM

Section 6: Date & Time Formulas

Lecture 157 Section 6 Introduction

Lecture 158 C06P01 - Mathematical calculation

Lecture 159 C06P02 - Mathematical calculation

Lecture 160 C06P03 - Mathematical calculation

Lecture 161 C06P04 - DATE

Lecture 162 C06P05 - DATE

Lecture 163 C06P06 - DATE

Lecture 164 C06P07 - DAY, MONTH, YEAR

Lecture 165 C06P08 - DATE, MONTH, DAY

Lecture 166 C06P09 - DAY, MONTH, YEAR, DATE, EDATE

Lecture 167 C06P10 - EOMONTH

Lecture 168 C06P11 - EOMONTH

Lecture 169 C06P12 - TODAY

Lecture 170 C06P13 - TODAY, Filter, Select Visible Cells

Lecture 171 C06P14 - TODAY, EOMONTH, EDATE, $ signs

Lecture 172 C06P15 - TEXT

Lecture 173 C06P16 - TEXT

Lecture 174 C06P17 - TEXT, CONCATENATE / & symbol

Lecture 175 C06P18 - WEEKDAY

Lecture 176 C06P19 - WEEKDAY

Lecture 177 C06P20 - WORKDAY.INTL

Lecture 178 C06P21 - WORKDAY.INTL

Lecture 179 C06P22 - WORKDAY.INTL

Lecture 180 C06P23 - WORKDAY.INTL, CONCAT

Lecture 181 C06P24 - NETWORKDAYS.INTL

Lecture 182 C06P25 - NETWORKDAYS.INTL

Lecture 183 C06P26 - NETWORKDAYS.INTL

Lecture 184 C06P27 - NETWORKDAYS.INTL, TODAY

Lecture 185 C06P28 - TIME

Lecture 186 C06P29 - TIME

Lecture 187 C06P30 - HOUR, MINUTE, SECOND

Lecture 188 C06P31 - Mathematical calculation

Section 7: Logical Formulas 1

Lecture 189 Section 7 Introduction

Lecture 190 C07P01 - Comparison operators

Lecture 191 C07P02 - IF

Lecture 192 C07P03 - IF

Lecture 193 C07P04 - Comparison operators, SUM

Lecture 194 C07P05 - IF

Lecture 195 C07P06 - Double IF

Lecture 196 C07P07 - Double IF

Lecture 197 C07P08 - Double IF

Lecture 198 C07P09 - OR

Lecture 199 C07P10 - OR

Lecture 200 C07P11 - IF, OR

Lecture 201 C07P12 - IF, OR

Lecture 202 C07P13 - Double IF, OR

Lecture 203 C07P14 - IF, OR, YEAR

Lecture 204 C07P15 - Double IF, OR, YEAR

Lecture 205 C07P16 - Double IF, OR

Lecture 206 C07P17 - AND

Lecture 207 C07P18 - AND

Lecture 208 C07P19 - AND, OR

Lecture 209 C07P20 - IF, AND

Lecture 210 C07P21 - IF, AND

Lecture 211 C07P22 - IF, AND

Lecture 212 C07P23 - IF, AND, OR, YEAR, MONTH

Lecture 213 C07P24 - Double IF, AND

Lecture 214 C07P25 - Double IF, AND

Lecture 215 C07P26 - Double IF, AND, OR

Lecture 216 C07P27 - Double IF, AND, OR, YEAR

Lecture 217 C07P28 - Double IF, AND, OR, YEAR

Lecture 218 C07P29 - Double IF, AND, OR, YEAR

Section 8: Mathematical Formulas 2

Lecture 219 Section 8 Introduction

Lecture 220 C08P01 - LARGE, SMALL

Lecture 221 C08P02 - RANK.AVG, RANK.EQ

Lecture 222 C08P03 - SUBTOTAL, AGGREGATE

Lecture 223 C08P04 - SUBTOTAL, AGGREGATE, Filter

Lecture 224 C08P05 - SUBTOTAL, AGGREGATE, Filter, Group

Lecture 225 C08P06 - SUBTOTAL, AGGREGATE, Filter

Lecture 226 C08P07 - ROUND, ROUNDUP, ROUNDDOWN

Lecture 227 C08P08 - SUMIFS

Lecture 228 C08P09 - SUMIFS

Lecture 229 C08P10 - SUMIFS

Lecture 230 C08P11 - SUMIFS

Lecture 231 C08P12 - SUMIFS with reference on different worksheet

Lecture 232 C08P13 - SUMIFS horizontal

Lecture 233 C08P14 - Name Manager

Lecture 234 C08P15 - Name Manager, SUM, AVERAGE

Lecture 235 C08P16 - Name Manager, SUMIFS

Lecture 236 C08P17 - Name Manager, SUMIFS with * symbol

Lecture 237 C08P18 - Name Manager, AVERAGEIFS

Lecture 238 C08P19 - Name Manager, AVERAGEIFS, YEAR

Lecture 239 C08P20 - Name Manager, AVERAGEIFS with > & < symbols

Lecture 240 C08P21 - Name Manager, COUNTIFS

Lecture 241 C08P22 - Name Manager, COUNTIFS with > & < symbols

Lecture 242 C08P23 - Name Manager, MINIFS, MAXIFS

Lecture 243 C08P24 - Name Manager, MINIFS with > & < symbols

Lecture 244 C08P25 - Name Manager, MAXIFS with * symbol

Lecture 245 C08P26 - Name Manager, SUMIFS, MAXIFS

Lecture 246 C08P27 - SUMPRODUCT

Lecture 247 C08P28 - SUMPRODUCT

Lecture 248 C08P29 - SUMPRODUCT

Lecture 249 C08P30 - SUMPRODUCT

Lecture 250 C08P31 - Name Manager, SUMPRODUCT with * symbol

Lecture 251 C08P32 - Name Manager, SUMPRODUCT with * symbol

Lecture 252 C08P33 - Name Manager, SUMPRODUCT with * symbol

Section 9: Logical Formulas 2

Lecture 253 Section 9 Introduction

Lecture 254 C09P01 - EXACT, PROPER

Lecture 255 C09P02 - EXACT, SUMPRODUCT

Lecture 256 C09P03 - IFERROR

Lecture 257 C09P04 - IFERROR

Lecture 258 C09P05 - IFS, IFERROR

Lecture 259 C09P06 - IFS, IFERROR, AND

Lecture 260 C09P07 - IFS, IFERROR, OR

Lecture 261 C09P08 - IFS, IFERROR, OR, AND, MONTH

Lecture 262 C09P09 - SWITCH

Lecture 263 C09P10 - SWITCH, AND

Lecture 264 C09P11 - SWITCH, OR

Lecture 265 C09P12 - SWITCH, OR, AND

Lecture 266 C09P13 - LAMBDA

Lecture 267 C09P14 - LAMBDA

Lecture 268 C09P15 - LAMBDA

Lecture 269 C09P16 - LAMBDA, IF

Lecture 270 C09P17 - LAMBDA, IFERROR, IFS

Lecture 271 C09P18 - SCAN, LAMBDA

Lecture 272 C09P19 - SCAN, LAMBDA, IF

Lecture 273 C09P20 - SCAN, LAMBDA, IF, SUM

Lecture 274 C09P21 - REDUCE, SCAN, LAMBDA

Lecture 275 C09P22 - REDUCE, LAMBDA, SEQUENCE, TRIM, SUBSTITUTE

Lecture 276 C09P23 - REDUCE, LAMBDA, SEQUENCE, TRIM, SUBSTITUTE, CHAR

Lecture 277 C09P24 - REDUCE, LAMBDA, SUBSTITUTE, CHAR

Lecture 278 C09P25 - LET, IF

Lecture 279 C09P26 - LET, SUMIFS, IFS

Lecture 280 C09P27 - LET, SUMIFS, IFS, IFERROR

Lecture 281 C09P28 - Form Control, IF

Lecture 282 C09P29 - Form Control, IF

Lecture 283 C09P30 - Form Control, LET, SUMIFS, IF, IFS

Section 10: Lookup Formulas

Lecture 284 Section 10 Introduction

Lecture 285 C10P01 - VLOOKUP, HLOOKUP

Lecture 286 C10P02 - VLOOKUP, HLOOKUP

Lecture 287 C10P03 - LOOKUP

Lecture 288 C10P04 - LOOKUP

Lecture 289 C10P05 - LET, SUMIFS, IFERROR, LOOKUP

Lecture 290 C10P06 - LOOKUP

Lecture 291 C10P07 - LOOKUP

Lecture 292 C10P08 - MATCH, VLOOKUP

Lecture 293 C10P09 - MATCH

Lecture 294 C10P10 - INDEX

Lecture 295 C10P11 - INDEX, MATCH

Lecture 296 C10P12 - INDEX, MATCH

Lecture 297 C10P13 - INDEX, MATCH

Lecture 298 C10P14 - XLOOKUP

Lecture 299 C10P15 - XLOOKUP

Lecture 300 C10P16 - XLOOKUP

Lecture 301 C10P17 - XLOOKUP

Lecture 302 C10P18 - XLOOKUP

Lecture 303 C10P19 - XLOOKUP

Lecture 304 C10P20 - INDIRECT, ADDRESS

Lecture 305 C10P21 - INDIRECT, ADDRESS

Lecture 306 C10P22 - INDIRECT, ADDRESS, ROW

Lecture 307 C10P23 - INDIRECT, ADDRESS, ROW

Lecture 308 C10P24 - INDIRECT, ADDRESS, ROW, COLUMN, Combo Box

Lecture 309 C10P25 - INDIRECT, ADDRESS, MATCH

Lecture 310 C10P26 - OFFSET

Lecture 311 C10P27 - SUM, OFFSET

Lecture 312 C10P28 - SUM, OFFSET

Lecture 313 C10P29 - INDEX, MATCH, XLOOKUP, OFFSET

Lecture 314 C10P30 - SUM, OFFSET, MATCH

Lecture 315 C10P31 - SUM, OFFSET

Section 11: Data Validation & Conditional Formatting

Lecture 316 Section 11 Introduction

Lecture 317 C11P01 - Data Validation whole number

Lecture 318 C11P02 - Data Validation whole number, date, decimal

Lecture 319 C11P03 - Data Validation text length

Lecture 320 C11P04 - Data Validation text length, IF

Lecture 321 C11P05 - Data Validation text length, VLOOKUP

Lecture 322 C11P06 - Data Validation list

Lecture 323 C11P07 - Data Validation list, Remove Duplicates

Lecture 324 C11P08 - Data Validation list, OFFSET, MATCH

Lecture 325 C11P09 - Data Validation list, OFFSET, MATCH, COUNTA

Lecture 326 C11P10 - Data Validation list, OFFSET, MATCH, COUNTA

Lecture 327 C11P11 - Data Validation error text

Lecture 328 C11P12 - Data Validation error types

Lecture 329 C11P13 - Data Validation, AND

Lecture 330 C11P14 - Data Validation, MIN, MAX, XLOOKUP

Lecture 331 C11P15 - Conditional Formatting cells values

Lecture 332 C11P16 - Conditional Formatting cells values

Lecture 333 C11P17 - Conditional Formatting cells values

Lecture 334 C11P18 - Conditional Formatting duplicate and unique values

Lecture 335 C11P19 - Conditional Formatting color scale

Lecture 336 C11P20 - Conditional Formatting data bar

Lecture 337 C11P21 - Conditional Formatting icon set

Lecture 338 C11P22 - Conditional Formatting, comparison operator

Lecture 339 C11P23 - Conditional Formatting, comparison operator

Lecture 340 C11P24 - Conditional Formatting, comparison operator, Data Validation

Lecture 341 C11P25 - Conditional Formatting, comparison operator, Data Validation, AND

Lecture 342 C11P26 - Conditional Formatting, comparison operator, MAX

Lecture 343 C11P27 - Conditional Formatting, comparison operator, MAX

Lecture 344 C11P28 - Conditional Formatting, comparison operator, MAX, AND

Lecture 345 C11P29 - Conditional Formatting, comparison operator, WEEKDAY

Lecture 346 C11P30 - Conditional Formatting, comparison operator, WEEKDAY, Check Box

Section 12: Database and Array Formulas

Lecture 347 Section 12 Introduction

Lecture 348 C12P01 - DSUM

Lecture 349 C11P02 - DSUM

Lecture 350 C12P03 - DSUM

Lecture 351 C12P04 - DSUM

Lecture 352 C12P05 - DSUM

Lecture 353 C12P06 - DSUM

Lecture 354 C12P07 - DCOUNTA

Lecture 355 C12P08 - DCOUNTA

Lecture 356 C12P09 - DSUM, DAVERAGE

Lecture 357 C12P10 - Advanced Filter

Lecture 358 C12P11 - Advanced Filter

Lecture 359 C12P12 - Advanced Filter

Lecture 360 C12P13 - Advanced Filter

Lecture 361 C12P14 - Advanced Filter

Lecture 362 C12P15 - Array functions: SUM

Lecture 363 C12P16 - Array functions: SUM

Lecture 364 C12P17 - Array functions: SUM

Lecture 365 C12P18 - Array functions: SUM

Lecture 366 C12P19 - Array functions: SUM

Lecture 367 C12P20 - Array functions: MAX

Lecture 368 C12P21 - Array functions: SUM

Lecture 369 C12P22 - Array functions: SUM

Lecture 370 C12P23 - Array functions: SUM, SUMIFS

Lecture 371 C12P24 - Array functions: SUM, SUMIFS, YEAR

Lecture 372 C12P25 - UNIQUE

Lecture 373 C12P26 - UNIQUE, TRANSPOSE, SUMIFS

Lecture 374 C12P27 - FILTER

Lecture 375 C12P28 - FILTER

Lecture 376 C12P29 - FILTER

Lecture 377 C12P30 - FILTER, YEAR

Lecture 378 C12P31 - SORT

Lecture 379 C12P32 - SORT, UNIQUE, FILTER

Section 13: Pivot Tables, Visualizations and Dashboards

Lecture 380 Section 13 Introduction

Lecture 381 C13P01 - Column chart

Lecture 382 C13P02 - Column chart

Lecture 383 C13P03 - Pie chart

Lecture 384 C13P04 - Pie chart, UNIQUE, SUMIFS

Lecture 385 C13P05 - Column chart

Lecture 386 C13P06 - Column chart, Data Validation

Lecture 387 C13P07 - Line chart

Lecture 388 C13P08 - Line chart, TRANSPOSE, UNIQUE, SORT, Array SUM, YEAR, VALUE

Lecture 389 C13P09 - Combo chart

Lecture 390 C13P10 - Combo chart, secondary axis

Lecture 391 C13P11 - Waterfall chart

Lecture 392 C13P12 - Waterfall chart

Lecture 393 C13P13 - Sparkline

Lecture 394 C13P14 - SmartArt product list

Lecture 395 C13P15 - SmartArt process

Lecture 396 C13P16 - SmartArt organizational structure

Lecture 397 C13P17 - Pivot Table

Lecture 398 C13P18 - Pivot Table

Lecture 399 C13P19 - Pivot Table

Lecture 400 C13P20 - Pivot Table, Slicer

Lecture 401 C13P21 - Pivot Table, Timeline

Lecture 402 C13P22 - Pivot Table, Slicer, Timeline

Lecture 403 C13P23 - Pivot Table

Lecture 404 C13P24 - Pivot Table % values

Lecture 405 C13P25 - Pivot Table % values

Lecture 406 C13P26 - Pivot Chart

Lecture 407 C13P27 - Pivot Chart, Slicer

Lecture 408 C13P28 - Pivot Chart, Pivot Table, Slicer, Timeline, Dashboard

Lecture 409 C13P29 - Pivot Chart, Pivot Table, Slicer, Timeline, Dashboard

Section 14: Combined Functions

Lecture 410 Section 14 Introduction

Lecture 411 C14P01 - SUMIFS

Lecture 412 C14P02 - SUMIFS, &

Lecture 413 C14P03 - SUMIFS, VLOOKUP

Lecture 414 C14P04 - SUMIFS, INDEX, MATCH

Lecture 415 C14P05 - SUM, SUBTOTAL, OFFSET, IF, ROW

Lecture 416 C14P06 - SUMIFS, Check Box, Conditional Formatting, Sparklines

Lecture 417 C14P07 - CONCAT, COUNTIFS, MATCH, Filter

Lecture 418 C14P08 - CONCAT, COUNTIFS, INDEX, MATCH, LARGE

Lecture 419 C14P09 - SUM, OFFSET

Lecture 420 C14P10 - SUMIFS, OFFSET, MATCH

Lecture 421 C14P11 - SUMIFS, OFFSET, MATCH

Lecture 422 C14P12 - SUMIFS, OFFSET, MATCH

Lecture 423 C14P13 - SUMIFS, OFFSET, INDIRECT, ADDRESS

Lecture 424 C14P14 - SORT, UNIQUE, FILTER, OFFSET, INDIRECT, ADDRESS

Lecture 425 C14P15 - UNIQUE, TRANSPOSE, LET, SUMIFS, IF, OFFSET, MATCH

Lecture 426 C14P16 - OFFSET, MATCH, Name Manager, Geography

Section 15: Power Query and Power Pivot

Lecture 427 Section 15 Introduction

Lecture 428 C15P01 - Power Query, Change Type, Merge Columns, Add Index Column, Reorder Col.

Lecture 429 C15P02 - Power Query, Split Columns, Merge Columns, Add Suffix, etc.

Lecture 430 C15P03 - Power Query, Add Custom Column

Lecture 431 C15P04 - Power Query, Remove Columns, working on dates

Lecture 432 C15P05 - Power Query, Remove Columns, working on dates, Merge Columns, etc.

Lecture 433 C15P06 - Power Query, Merge Queries, Close and Load To

Lecture 434 C15P07 - Power Query, Append Queries, Close and Load To

Lecture 435 C15P08 - Power Query, Append Queries, Close and Load To

Lecture 436 C15P09 - Power Query, Merge Queries, Split, Add Custom Column, date format, etc.

Lecture 437 C15P10 - Power Query, Group By, Sort, Close and Load To

Lecture 438 C15P11 - Power Query, Group By, Sort, Pivot, Close and Load To

Lecture 439 C15P12 - Power Query, Append Queries, Pivot Table, Close and Load To

Lecture 440 C15P13 - Power Query, Sort, Merge Queries, Close and Load To, Split, etc.

Lecture 441 C15P14 - Power Query, Duplicate query, Delete Columns, Remove Duplicates, etc.

Lecture 442 C15P15 - Power Pivot, Create Relationship, Pivot Table

Lecture 443 C15P16 - Power Query, Duplicate query, Delete Columns, Remove Duplicates, etc.

Lecture 444 C15P17 - Power Query, Duplicate query, Delete Columns, Remove Duplicates, etc.

Lecture 445 C15P18 - Power Query, Unpivot, Pivot Table, Close and Load To

Lecture 446 C15P19 - Power Query, Duplicate query, Delete Columns, Remove Duplicates, etc.

Lecture 447 C15P20 - Power Pivot, Measures: SUM, AVERAGE, Pivot Table

Lecture 448 C15P21 - Power Pivot, Measures: SUM, CALCULATE, Pivot Table, Slicer

Lecture 449 C15P22 - Power Pivot, Measures: SUM, CALCULATE, ALL, Pivot Table

Lecture 450 C15P23 - Power Pivot, Measures: SUM, AVERAGE, CALCULATE, Pivot Table, Slicer

Lecture 451 C15P24 - Power Pivot, Power Query, Merge Queries, Measures: SUM, CALCULATE, etc.

Lecture 452 C15P25 - Power Pivot, Measures: SUMX, Pivot Table

Lecture 453 C15P26 - Power Pivot, Measures: SUM, CALCULATE, DATESYTD, Pivot Table, Slicer

Lecture 454 C15P27 - Power Pivot, Measures: SUM, CALCULATE, DATEADD, Pivot Table, Slicer

Lecture 455 C15P28 - Power Pivot, Measures: SUM, CALCULATE, DATEADD, DATESYTD, etc.

Lecture 456 C15P29 - Dashboard

Section 16: VBA 1

Lecture 457 Section 16 Introduction

Lecture 458 C16P01 - Record Macro

Lecture 459 C16P02 - Record Macro

Lecture 460 C16P03 - Record Macro, Remove Duplicates, SUM, SUMIFS, Paste Special

Lecture 461 C16P04 - Write VBA Code Sub, MsgBox

Lecture 462 C16P05 - Write VBA Code Sub, MsgBox

Lecture 463 C16P06 - Write VBA Code Sub, MsgBox, Cells

Lecture 464 C16P07 - Write VBA Code Sub, MsgBox, Worksheets, Cells

Lecture 465 C16P08 - Write VBA Code Sub, Cells

Lecture 466 C16P09 - Write VBA Code Sub, Activecell

Lecture 467 C16P10 - Write VBA Code Sub, Cells, InputBox

Lecture 468 C16P11 - Write VBA Code Sub, Activecell, InputBox

Lecture 469 C16P12 - Write VBA Code Sub, InputBox, Worksheets, Cells

Lecture 470 C16P13 - Write VBA Code Sub, Range

Lecture 471 C16P14 - Write VBA Code Sub, Application, Selection

Lecture 472 C16P15 - Write VBA Code Sub, Range, Copy

Lecture 473 C16P16 - Write VBA Code Sub, Selection, Range, Copy

Lecture 474 C16P17 - Write VBA Code Sub, Variable Declaration, Cells, MsgBox

Lecture 475 C16P18 - Write VBA Code Sub, Variable Declaration, Cells, MsgBox

Lecture 476 C16P19 - Write VBA Code Sub, Variable Declaration, Set, Range, Rows

Lecture 477 C16P20 - Write VBA Code Sub, Variable Declaration, Set, Range, Columns, etc.

Lecture 478 C16P21 - Write VBA Code Sub, Variable Declaration, Set, Range, Cells, Inputbox

Lecture 479 C16P22 - Write VBA Code Sub, For…Next, Cells

Lecture 480 C16P23 - Write VBA Code Sub, For…Next, Step, Cells

Lecture 481 C16P24 - Write VBA Code Sub, Cells, Comparison operators, MsgBox

Lecture 482 C16P25 - Write VBA Code Sub, Cells, If…End If, MsgBox

Lecture 483 C16P26 - Write VBA Code Sub, Cells, If…End If, And, For…Next

Lecture 484 C16P27 - Write VBA Code Sub, Cells, If…End If, And, For…Next, Step

Lecture 485 C16P28 - Write VBA Code Sub, Variable Declaration, Cells, If…End If, And, etc.

Lecture 486 C16P29 - Write VBA Code Sub, Variable Declaration, Cells, If…End If, And, etc.

Lecture 487 C16P30 - Write VBA Code Sub, Variable Declaration, Cells, If…End If, And, etc.

Section 17: VBA 2

Lecture 488 Section 17 Introduction

Lecture 489 C17P01 - Write VBA Code Function, Application.Volatile, Cells

Lecture 490 C17P02 - Write VBA Code Function, Application.Volatile, Variable Declaration

Lecture 491 C17P03 - Write VBA Code Function, Variable Declaration, For Each…Next

Lecture 492 C17P04 - Write VBA Code Function, Variable Declaration, For Each…Next, IsNumeric

Lecture 493 C17P05 - Variable Declaration, For Each…Next, IsNumeric, If…End If, Mid, Not

Lecture 494 C17P06 - Write VBA Code Function, Variable Declaration, If…End If

Lecture 495 C17P07 - Variable Declaration, For Each…Next, If…End If

Lecture 496 C17P08 - Variable Declaration, For Each…Next, If…End If, Cells.Count

Lecture 497 C17P09 - Variable Declaration, For Each…Next, If…End If, Cells.Count

Lecture 498 C17P10 - Variable Declaration, For Each…Next, If…End If, And, Or, Cells.Count

Lecture 499 C17P11 - Variable Declaration, For Each…Next, If…End If, And, Cells.Count

Lecture 500 C17P12 - Variable Declaration, Application.WorksheetFunction, EoMonth

Lecture 501 C17P13 - Variable Declaration, Application.WorksheetFunction, EoMonth, etc.

Lecture 502 C17P14 - Variable Declaration, Application.WorksheetFunction, Count, CountA

Lecture 503 C17P15 - Variable Declaration, Application.WorksheetFunction, etc.

Lecture 504 C17P16 - Variable Declaration, Selection.Row, MsgBox, If…End If, etc.

Lecture 505 C17P17 - Write VBA Code Sub, Create User Form, Variable Declaration, etc.

Lecture 506 C17P18 - Write VBA Code Sub, Create User Form, Cells, Userform.Show, Unload Me

Lecture 507 C17P19 - Create User Form, Variable Declaration, Cells, Date, etc.

Lecture 508 C17P20 - Create User Form, Variable Declaration, Cells, Date, If…End IF, etc.

Lecture 509 C17P21 - Create User Form, Variable Declaration, Cells, If…End IF, etc.

Lecture 510 C17P22 - Create User Form, Variable Declaration, Cells, If…End IF, Or, etc.

Lecture 511 C17P23 - Write VBA Code Sub on Trigger, MsgBox

Lecture 512 C17P24 - Write VBA Code Sub on Trigger, MsgBox

Lecture 513 C17P25 - Write VBA Code Sub on Trigger, Selection, InputBox

Lecture 514 C17P26 - Write VBA Code Sub on Trigger, InputBox, Application.ActiveSheet.Name

Lecture 515 C17P27 - Write VBA Code Sub on Trigger, Cells, Date, Time, &

Lecture 516 C17P28 - Write VBA Code Sub, MsgBox, changes in menu

Lecture 517 C17P29 - MsgBox, If…End If, Rows.EntireRow.Hidden, etc.

Beginner user - if you want to know the basic tools and formulas;,Intermediate user - if you know the basics and want to advance the knowledge;,Advanced user - if you want to get familiar with VBA, Power Pivot, Power Query and be able to write complicated nested functions;,If you want to get practice, because: PRACTICE IS THE BEST WAY TO LEARN!