Advanced Techniques in Modern Excel
Learn how to take full advantage of the features in modern Excel versions, such as Dynamic arrays and Spill ranges. This course covers concepts like Boolean arrays, Dynamic and static named ranges, and the nuances of the FILTER function. You’ll also learn how to use Excel tables effectively and create interactive and dynamic formulas, charts, and validation dropdowns. Whether you’re an analyst, manager, or data professional, this course will help you unleash the full power of modern Excel.
What you’ll learn
- Take full advantage of features Modern excel-Excel 365/2021 and above.
- Concepts – Dynamic arrays, Spill ranges, ,Boolean arrays, Dynamic and static named ranges, nuances of FILTER function and Excel tables to use them effectively.
- Formulas – Dynamic formulas with Tables & Dynamic array functions.
- Functions – V/X lookup, Index, Filter, Rows, Unique, Match, Search, Text, Year, Value, Indirect, IFERROR.
- Lookups – Multi value & Multi column with formula in a single cell.
- Lookups – Using partial words as lookup values.
- Lookups – Lookup all matching values from a dynamic list of lookup values
- Lookups – Lookup all matching values, not just the first, without worrying about errors.
- Summaries – Support Pivot Tables with calculations not available in standard pivots.
- Pivot operations – Create, change summary function, switch between modern and classic display, change layout.
- Validation – Dynamic & related dropdowns using only tables & without using Named ranges.
- Charts – Dynamic & Interactive charts – Named ranges, table columns, dynamic array spill ranges
- Charts – Plot different types of data in a single data series – allow user selection.
- Charts – Plot different charts in the same area of dashboard – allow user selection.
- Charts – Operations: Multiple series on Primary, use Secondary axis, Dynamic series name & axis labels.
- Proper scenarios for the use of FILTER function.
Show moreShow less
This course looks at how to take full advantage of the features of modern Excel versions.
Working with the features of Excel 365 or Excel 2021 and above.
The new Dynamic Array feature is used to its fullest.
The aim is to provide the learners with a suite of techniques that can be used to build powerful solutions for general data processing tasks. Solutions that are easy to maintain and update.
Along the way, we will look at the basic and intermediate operations of Pivot tables and Excel charts.
Students will learn:
How modern Excel deals with a collection of values, both as input and output of formulas.
How to make full use of Excel tables in new versions.
How to use Existing Excel functions in new ways with modern features.
How to create – interactive and dynamic single-cell lookup formulas, interactive charts using modern features, and efficient validation dropdowns.
Nuances of Excel Tables and the new and powerful FILTER function.
We will also look at scenarios for the correct usage of the FILTER function.
Dynamic and Static named ranges will be used, and we will see examples of how the features built into modern Excel allow us to avoid Dynamic named ranges but still perform the same tasks.
Who this course is for:
- Analysts and Managers who want more efficient ways of anayzing data.
- Data professionals who pre-process data for tasks like, analytics, machine learning or database load.
- Anyone wanting to unleash the full power of modern Excel.
