Excel Advanced: Unlock Expert-level Skills
Master advanced Excel techniques including conditional functions, lookup functions, auditing tools, what-if analysis, worksheet protection, and automating tasks with macros and VBA.
Conditional Functions
Named ranges, IF, AND, COUNTIF, SUMIF, and IFERROR for dynamic and powerful formulas.
Lookup Functions
VLOOKUP, HLOOKUP, INDEX & MATCH, and advanced lookup examples for precise data retrieval.
Text-Based Features
LEFT, RIGHT, MID, LEN, SEARCH, and concatenation to manipulate and analyze text data.
Auditing Worksheets
Trace precedents/dependents, use the Watch Window, and show formulas to monitor your data.
Protecting Worksheets
Protect specific cells, workbook structure, and entire spreadsheets to maintain integrity.
What-If Tools
Goal Seek, Solver, data tables, and scenarios for advanced analysis and decision-making.
Automating Tasks
Learn macros, VBA, developer tools, and recording/editing macros to save time and improve efficiency.
Conditional Functions
- Working with Named Ranges: advantages, disadvantages, editing
- Using the IF function, combining with named ranges
- Nesting functions: AND within IF
- COUNTIF and SUMIF for conditional calculations
- Using IFERROR with VLOOKUP
Lookup Functions
- VLOOKUP syntax and applications
- HLOOKUP for horizontal lookups
- INDEX & MATCH functions
- Combining INDEX & MATCH for advanced lookups
- Further examples with VLOOKUP and HLOOKUP
Text-Based Features
- LEFT, RIGHT, MID functions to extract text
- LEN function to calculate text length
- SEARCH function to locate characters/substrings
- Concatenating text for combining strings
Auditing Worksheets
- Tracing precedents
- Tracing dependents
- Using the Watch Window
- Showing formulas
Protecting Worksheets
- Protecting specific cells
- Protecting workbook structure
- Protecting entire spreadsheets
What-If Tools
- Goal Seek tool
- Solver tool
- Building effective data tables
- Creating scenarios
Automating Tasks
- Understanding Excel macros
- Activating the Developer tab
- Recording macros with Macro Recorder
- Editing macros with VBA
- Running macros efficiently