تفاصيل الدورة
Course Description:
Excel Level 3 is an intensive two-day instructor-led course designed to provide participants with a deep, hands-on understanding of Microsoft Excel's advanced functions and formulas. This course focuses on equipping users with the skills to perform complex calculations, analyze large datasets, and manipulate data efficiently. In today's data-driven environment, mastering these techniques is crucial for anyone looking to enhance their Excel proficiency. Upon… + المزيد
تفاصيل الدورة
Course Description:
Excel Level 3 is an intensive two-day instructor-led course designed to provide participants with a deep, hands-on understanding of Microsoft Excel's advanced functions and formulas. This course focuses on equipping users with the skills to perform complex calculations, analyze large datasets, and manipulate data efficiently. In today's data-driven environment, mastering these techniques is crucial for anyone looking to enhance their Excel proficiency. Upon successful completion, participants will be able to quickly and effectively manage large data sets, utilize advanced functions, and leverage powerful reporting tools to streamline data analysis and decision-making.
Duration: 2 Days (8 Hours Per Day)
Course Objectives:
- Build advanced data reports and dashboards using pivot tables, slicers, and charts
- Manage large data sets with advanced functions and formulas, including logical, statistical, and lookup functions
- Use Power Query Editor to clean, transform, and analyze data efficiently
- Create dynamic and customized data validation rules
- Automate repetitive tasks with macros and improve data protection
- Utilize advanced filtering, sorting, and conditional formatting techniques
- Improve productivity with super shortcuts and efficient workbook management strategies
Who Should Attend:
This course is designed for intermediate to advanced Excel users who wish to enhance their Excel skills for data analysis, reporting, and automation. Ideal for business analysts, financial analysts, data analysts, project managers, and anyone who regularly works with large datasets and complex Excel tasks.
Course Outline:
´Data Analysis and Reporting with Dashboards
- Report preparation by pivot table
- Grouping and ungrouping reports in pivot table
- Slicer techniques & Pivot Charts
- Dashboard preparation
- Retrieving external data using Microsoft query
- Multiple consolidation range
- Advanced Pivot Table Options
- Data Consolidation
´Formatting and functions for Data Management
- Logical functions (if, ifs, sum if, count if, count ifs, sum ifs, count, count A, and, or, not, If error etc.)
- Count unique distinct values that meet multiple criteria
- Statistical and mathematical functions
- Conditional formatting and formula
- Custom Formatting
- Track individual details from database by VLOOKUP, HLOOKUP &
´Power Query Editor
- Text Formatting in Power query Editor
- Number Formatting in power query Editor
- Date formatting in Power query
- Merging & Append Query
- Conditional Custom Column
- Pivot & Unpivoting Options
- Data Segregation by column from Example
´Advanced Data Validation
- Creating Custom Rule for Data Validation
- Normal Dropdown list & Dynamic Dropdown List
- Drop down List by Name Range & indirect Function
- Restriction of Entries and Data Validation settings
´Advanced Conditional Formatting
- Conditional formatting by Criteria
- Conditional formatting by using Dropdown list
- Conditional formatting by multiple Conditions
- Dynamic Conditional formatting for Calendar’s
- Conditional Formatting for unique and Duplicate Values
´Advanced Filtering & Sorting Techniques
- Filtering unique list and unique combination
- Advanced Filtering by multiple Combination
- Advanced Filtering by Formula
- Filtering into a new worksheet.
- Sorting data in various ways - quickly, properly, horizontally and vertically
- Auto-, Multiple- and Advanced filters
- Alternative techniques
´Concept of Macros & Automation
- Creating Macros
- Running Macros
- Advanced Filtering by using Macros
- Data Protection by using Macros
´Workbook administration and Super shortcut Options in Excel
- Changing permanent and default Excel settings
- Frequency of auto-recovery
- Autocorrect and Ribbon alterations
- Flash Fill
- Fill Series
- List Range
- Referencing Techniques
- Date & Time Functions
تحديث بتاريخ 29 January, 2025 - أقل