Knowledge Level

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

Skills Level

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

Attitude Level

1. Show willingness to adopt ideas, opinions, and methods from various experiences.
2. Accept diversity and adapt to changes.
3. Persuade with sound arguments and appropriate body language.
4. 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