About Course
Course Objectives
-
Manage and secure Excel workbooks for collaboration.
-
Apply advanced formatting, data validation, and conditional formatting techniques.
-
Use advanced logical, lookup, and date functions for data analysis.
-
Perform complex data analysis using Excel analytical tools.
-
Troubleshoot and audit formulas effectively.
-
Automate repetitive tasks using Excel Macros.
-
Create advanced charts and data visualizations.
-
Analyze data using PivotTables and PivotCharts for professional reporting.
Course Outline
Module 1: Preparing Workbooks for Collaboration
-
Restrict editing
-
Protect worksheets and cell ranges
-
Protect workbook structure
-
Configure formula calculation options
-
Manage comments
Module 2: Filling Cells Based on Existing Data
-
Use Flash Fill
-
Use advanced Fill Series options
Module 3: Formatting and Validating Data
-
Create custom number formats
-
Configure data validation rules
-
Group and ungroup data
-
Insert subtotals and totals
-
Remove duplicate records
Module 4: Advanced Conditional Formatting and Filtering
-
Create custom conditional formatting rules
-
Use formulas in conditional formatting
-
Manage conditional formatting rules
Module 5: Logical Operations in Formulas
-
Use IF, IFS, SUMIF, AVERAGEIF, COUNTIF
-
Use SUMIFS, AVERAGEIFS, COUNTIFS
-
Use AND and OR functions
Module 6: Lookup Functions
-
Use XLOOKUP, VLOOKUP, and HLOOKUP
-
Use MATCH and INDEX functions
Module 7: Date and Time Functions
-
Use NOW and TODAY functions
-
Use WEEKDAY and WORKDAY functions
Module 8: Data Analysis Tools
-
Consolidate data from multiple ranges
-
Perform What-If analysis using Goal Seek and Scenario Manager
Module 9: Troubleshooting Formulas
-
Trace precedents and dependents
-
Monitor formulas using Watch Window
-
Validate formulas using error checking
-
Evaluate formulas
Module 10: Creating and Editing Macros
-
Record simple macros
-
Name and edit macros
-
Automate repetitive tasks
Module 11: Advanced Charts
-
Create dual axis charts
-
Create advanced charts (Histogram, Funnel, Combo, Box & Whisker)
Module 12: PivotTables
-
Create PivotTables
-
Modify fields and options
-
Create slicers
-
Group PivotTable data
-
Add calculated fields
Module 13: PivotCharts
-
Create PivotCharts
-
Modify PivotChart options
-
Apply styles to PivotCharts
-
Drill down into PivotChart data
