Office Productivity

Advanced Techniques in 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.


  • Basics of Excel – Operating common menu items, entering formulas.
  • Basics of Excel – Familiarity with lookups and charts.
  • No programming experience required


Advanced Techniques in Modern Excel. 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.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button


Turn off the ad blocker