Advanced Microsoft Excel
18 Hours
Advanced
10 Modules
- LE 1,499.00
LE 3,500.00- LE 1,499.00
- Unit price
- per
10 customers are viewing this product
The Advanced Microsoft Excel course is designed to equip users with comprehensive skills to handle complex data management, analysis, and reporting tasks in Excel. This course delves into advanced Excel features and functions, building on foundational knowledge to enhance productivity and data accuracy.
Key topics include:
Advanced Formulas and Functions: Mastering complex functions such as VLOOKUP, INDEX/MATCH, IF, and nested functions, enabling participants to perform detailed calculations and data manipulations.
Data Analysis Tools: Learning to use PivotTables, Power Query, and data models for efficient data summarization and analysis.
Data Visualization: Creating impactful charts, conditional formatting, and dashboards to convey insights effectively.
Advanced Data Management: Techniques for managing large datasets, including sorting, filtering, and using Excel’s data validation for data quality control.
This course is ideal for professionals who rely on Excel for in-depth data analysis, reporting, and automation, providing valuable skills for roles in finance, marketing, operations, and beyond.
Module 1: Excel Basics
Understanding the Difference Between Functions and Formulas in Excel
Working with Basic Functions:
Core functions like SUM, MAX, MIN
Using LARGE and SMALL functions to identify largest and smallest values
Counting Functions:
COUNT (counts cells with numbers)
COUNTA (counts non-empty cells)
COUNTBLANK (counts empty cells)
Module 2: Logic and Conditional Control
Using the IF Function:
Basic IF function and nested IFs
Using IF with AND and OR functions
Conditional Formatting:
Applying conditional formatting to highlight data
Advanced conditional formatting techniques for enhanced data display
Module 3: Customizing Excel Tools and Settings
Customizing the Quick Access Toolbar
Customizing the Ribbon
Protecting the Workbook and Worksheet
Module 4: Working with Tables and Organizing Data
Creating and Managing Tables
Total Row Actions
Adding Hyperlinks
Using Slicers to Filter Data
Module 5: Data Validation and Input Control
Module 5: Data Validation and Input Control
Data Validation
Creating Number Range Validation
Creating Drop-Down Lists
Module 6: Analysis Using Conditional and Lookup Functions
Using Lookup Functions:
VLOOKUP and HLOOKUP for data search
INDEX and MATCH for locating and searching data
Analysis with Conditional Functions:
SUMIF and SUMIFS
COUNTIF and COUNTIFS
IFERROR to handle errors
Module 7: Charts and Data Visualization
Creating Charts
Pivot Tables
Pivot Charts
Using Slicers to Filter Data
Creating Dashboards in Excel
Using Smart Art Graphics to Illustrate Workflows
Module 8: Text Manipulation and Formatting
Text Functions:
CONCATENATE for text merging
LEFT, RIGHT, MID functions for text manipulation
TEXT Function:
Converting numbers to text in a specific format
Module 9: Date and Time Functions
Date and Time Functions:
TODAY, DATE, DAYS
Module 10: Controlling Decimal Places
Rounding Functions:
ROUND, ROUNDUP, ROUNDDOWN
What you will learn
Build and customize pivot tables and charts.
Trace data dependencies and precedents with ease.
Convert, validate, and consolidate data efficiently.
Collaborate by protecting sheets and workbooks.
Record, run, and manage macros for task automation.
Seamlessly import and export data across platforms.
Audience profile
Advanced Excel users looking to master topics in this course will gain expertise within the 2019 and above interface.
Related Products
CLA
Example course title
- LE 1,499.00
LE 3,500.00- LE 1,499.00
- Unit price
- per
CLA
Example course title
- LE 1,499.00
LE 3,500.00- LE 1,499.00
- Unit price
- per
CLA
Example course title
- LE 1,499.00
LE 3,500.00- LE 1,499.00
- Unit price
- per
CLA
Example course title
- LE 1,499.00
LE 3,500.00- LE 1,499.00
- Unit price
- per