Knowledge Level

• Identify and analyze various data processing methods.
• Categorize commands based on their implementation methods.
• Understand different data formatting techniques.
• Recognize where and why each tool/smart tool should be used.

Skills Level

• Conduct an analysis of training needs using appropriate tools and techniques.
• Use a combination of tools/formulas to solve given tasks.
• Develop speed and accuracy through experience and practice across all areas, regardless of difficulty level.
• Clearly determine which command, method, tool, or function to use in each scenario and for each specific problem.
• Seek out new techniques for approaching and solving any problem that arises.
• Communicate effectively and treat all trainees in the classroom equally.
• Manage difficult situations during the training process.

Attitude Level

• Show willingness to adopt ideas, opinions, and methods from various experiences.
• Accept diversity and adapt to changes.
• Persuade with sound arguments and appropriate body language.
• Encourage and motivate all participants to engage actively in the seminar.

This seminar is designed for professionals who already possess a foundational knowledge of Microsoft Excel and are looking to enhance their skills to an advanced level. Ideal participants include:

• Business Analysts, Financial Analysts, and Accountants who regularly handle complex datasets and need advanced techniques for data analysis, visualization, and reporting.
• Project Managers and Team Leads who aim to leverage Excel for project tracking, budgeting, and resource management.
• Administrative and Operations Professionals who want to improve efficiency through automation, advanced formulas, and streamlined data processing.
• Anyone with a Strong Working Knowledge of Excel who is eager to expand their expertise and productivity by mastering advanced functions, macros, pivot tables, and data modeling tools.

Unit 1: Basic Principles of “Ms-EXCEL” Software

• Relative and Absolute Cell Addressing
• Freeze Panes, Split Screen
• Repeating Page Titles (Print Titles)
• Define Name/Name Manager for cell range management
• Data Validation / Drop Down Lists
• Removing Duplicate Entries in Data Tables
• Convert/Transfer Cell Content into Columns (Text to Columns – Delimited/Fixed Width)

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 2: Data Manipulation in Cells / Worksheet Workspace Customization

• Data Sorting and Filtering

  • Basic Filtering
  • Advanced Filtering with AND/OR Conditions

• Notes/Comments Usage

• Quick Access Toolbar Tools

• Links / Hyperlinks

• File Protection with Passwords (File > Save As > Tools > General Options):

  • Password to Open
  • Password to Modify

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 3: List Creation / Drop-Down Lists / Linking Cells with Numbers and Formulas / Conditional Statements

• Auditing: Relationship Between Cells (Precedence/Dependence)
• Custom Lists
• Auto-Fill Numbers (Sequential Numbers / Merged Cells)
• Camera Tool Usage (File > Options > Customize Ribbon)

Conditional Statements:

• IF and Nested IF Statements, IFS

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 4: Functions, Calculations, and Conditional Formatting in Tables

Important Functions/Calculations:

• SUMIF, SUMIFS, COUNTIF, COUNTIFS
• VLOOKUP, HLOOKUP, XLOOKUP

Conditional Formatting:

• Basic and Advanced Levels

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 5: Subtotals, Consolidation, and Pivot Tables

• Subtotals by Groups (SUBTOTALS)
• Consolidation of Data from Multiple Tables (CONSOLIDATION)
• Pivot Tables / Pivot Charts Creation
• Macros

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 6: Charts, Goal Seek, and Data Tables

• Linking Multiple Worksheets/Workbooks Cell by Cell
• Graphs / Charts (Editing/Formatting, including 3D Charts)
• Goal Seek
• Scenario Analysis
• One and Two-Input Data Tables

Hands-On Practice on All Above Topics

• Question Resolution – Q&A

Unit 7: Advanced Data Processing and Dashboard Creation

• Creating Dashboards with Data and Charts

• Linking and Combining Pivot Tables with:

  • Slicers
  • Timelines

• Converting Files Between PDF and Excel (Using “I Love PDF”)

• General Excel Options and Settings

Hands-On Practice on All Above Topics

• Question Resolution – Q&A