Advanced Excel

  • Logical Functions: IF, AND, OR, IFERROR, SWITCH
  • Lookup & Reference Functions: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, OFFSET, INDIRECT
  • Text Functions: CONCATENATE, TEXT, LEFT, RIGHT, MID, TRIM, UPPER, LOWER, FIND, SUBSTITUTE
  • Date & Time Functions: DATE, MONTH, YEAR, DAY, NETWORKDAYS, WEEKDAY, TEXT, EOMONTH
  • Statistical Functions: AVERAGEIFS, COUNTIFS, SUMIFS, MEDIAN, MODE, STDEV, VAR
  • Array Formulas: Dynamic Arrays, SEQUENCE, FILTER, SORT, UNIQUE
  • Advanced Mathematical Functions: SUMPRODUCT, TRANSPOSE, POWER, SQRT

  • Removing duplicates and blank cells
  • Identifying and handling missing values
  • Text-to-Columns for data extraction
  • Data validation and error-checking
  • Working with inconsistent data formats (dates, numbers, text)
  • Using the “Flash Fill” feature
  • Combining and splitting columns
  • Normalizing and standardizing data

  • Creating PivotTables: Summarizing data, grouping data
  • Advanced PivotTable Functions: Calculated fields, calculated items, grouping data by date (month, quarter, year)
  • Slicers and Timelines: Filtering data interactively
  • PivotCharts: Creating charts from PivotTables, customizing PivotCharts
  • Working with Multiple PivotTables: Linking and comparing data from different sources
  • Using PivotTable with Power Pivot for advanced analysis

  • Basic Charts: Column, Bar, Line, Pie, Area
  • Advanced Charts: Histogram, Box plot, Waterfall, Heatmap
  • Conditional Formatting: Using rules to highlight data points (Top 10, color scales, icon sets)
  • Dynamic Charts: Using named ranges and dynamic charts to update as data changes
  • Combo Charts: Combining different chart types for effective visualization
  • Creating Interactive Dashboards: Combining multiple charts and PivotTables into a dashboard

  • What-If Analysis: Scenario Manager, Data Tables, Goal Seek, Solver
  • Trend Analysis: Using linear regression, exponential smoothing
  • Forecasting: Using Excel’s built-in forecasting tool, regression analysis
  • Data Tables and Sensitivity Analysis: One-variable and two-variable data tables
  • Power Query for Data Transformation: Importing, cleaning, and transforming large datasets

  • Overview of Power Pivot and its advantages for large data models
  • Importing data into Power Pivot from multiple sources
  • Creating relationships between tables in Power Pivot
  • DAX Functions: Introduction to Data Analysis Expressions (DAX)
  • Calculated columns and measures
  • Basic DAX functions: SUM, COUNT, AVERAGE, MIN, MAX
  • Time intelligence functions: YEAR-TO-DATE, MONTH-TO-DATE, SAMEPERIODLASTYEAR
  • Using CALCULATE and FILTER
  • Advanced DAX functions for custom measures and complex aggregations

  • Using Macros & VBA: Introduction to Excel Macros, automating repetitive tasks
  • Recording Macros: Steps for recording and executing macros
  • Editing Macros: Understanding VBA code basics
  • Creating Custom Functions in VBA: Writing and using custom functions
  • Automating Reports: How to automate report generation and emailing with VBA

  • Data Models: Building and managing data models with Power Pivot
  • Creating Relationships: Linking data from different tables using primary and foreign keys
  • Power Query: Extract, Transform, Load (ETL) process for large datasets
  • Data Merging & Appending: Combining datasets from multiple sources
  • Advanced Filtering and Sorting: Using Power Query and filters

  • Importing data from external sources: SQL databases, Access, Web, JSON, XML, etc.
  • Connecting to live data and refreshing reports
  • Handling large datasets in Excel (Optimizing Excel performance with large files)
  • Using external databases and analyzing data from them using Power Query & Power Pivot
  • Web scraping techniques (basic) for extracting data from web pages into Excel

  • Interactive Dashboards: Using slicers, timelines, and dynamic charts for interactive reporting
  • Building Reports with Power BI Integration: Leveraging Power BI with Excel for advanced visualizations
  • Excel as a Reporting Tool: Creating structured reports and applying conditional formatting for highlighting key metrics
  • Consolidating Data from Multiple Workbooks: Working with large datasets across multiple files, using linked workbooks

  • Data Organization: Using named ranges, tables, and structured references
  • Error Checking and Debugging Formulas: Using the Formula Auditing tools
  • Documenting Workbooks: Adding metadata, annotations, and comments for clarity
  • Collaboration Features: Working with Excel in teams, sharing and protecting workbooks

No Content
12 Hours of training
Real-life projects
Certification Guidance
Contact Now!
Schedule a demo
Schedule a demo