Module 1: Basic Fundamentals
Introduction to spreadsheets, navigation and practical applications, as well as protecting cells, sheets, and workbooks. Difference between data ranges and data tables, absolute and relative cell coordinates, and data entry validation.
Module 2: Functions
Excel functions: Classification, arguments, and syntax; applications of logical functions, database functions, INDIRECT function, and hierarchy. Application of AI for database generation.
Module 3: Matrix Functions and Dynamic Matrices
Array ranges and overflow ranges, applications with dynamic array functions: Filter(), Sort(), SortBy(), Unique(), Lookup(), Offset(), etc.
Module 4: Managing data sorting
Sorting lists, filtering and slicering data, dynamic tables and charts, slicers in dynamic tables, calculated fields and items. Creating a Dashboard.
Module 5: Time Data and Advanced Filters
Date and Time functions, text function, and custom formats
Module 6: Forecasting Tools: Histograms and Financials
Creation of frequency tables and histograms (Pareto charts), linear estimation, forecasting and prediction, hypothesis and sensitivity analysis (goal seek, tables and scenarios), and results evaluation. Applications of financial functions and image processing.
Module 7: Macros
Introduction to Macros, activating the Developer tab, macro security. Relative and absolute references. Macro tools (shortcut, buttons, editing and deletion). Example of macro recording, creating functions, and form template.
Module 8: Excel and its add-in applications (Power Query – Power Pivot)
General database concept. Power Query: definition and applications. Example of data normalization using AI. Power Pivot and data modeling applications. Multiple pivot tables and dashboards with Excel.