MS Excel For Data Analysis (Intermediate Techniques)

The purpose of this program is to will build upon the foundational Microsoft Office Excel knowledge and skills already acquired and learn to create advanced workbooks and worksheets, including advanced formulas, tables and data analyzing tools.

Course Objectives:

• Manage the database effectively.
• Apply proper formula and function.
• Troubleshooting errors
• Link and Embed Data from other sources.
• Applying Conditional Formatting and Validation Rules

Learning Outcomes:

• Organize and structure data using tables and cell formatting.
• Apply sorting and filtering techniques to analyze and extract relevant information from a dataset.
• Consolidate and manage data from multiple worksheets or workbooks effectively.
• Understand data validation to maintain data accuracy and consistency.
• Utilize Excel’s built-in error-checking and tracing tools to diagnose issues in formulas and functions.
• Understand auditing tools for error detection and correction.
• Debug and correct formulas to achieve desired results.
• Utilize Excel’s built-in error-checking and tracing tools to diagnose issues in formulas and functions.
• Understand auditing tools for error detection and correction.
• D Utilize Excel’s built-in error checking and tracing tools to diagnose issues in formulas and functions.
• Understand auditing tools for error detection and correction.
• Debug and correct formulas to achieve desired results.

Methodology:

• Pre-Assessment
• Short lecture session
• Demonstrations
• Self-assessment
• Video presentation
• Post-Assessment

Target Audiences:

• Business Professionals
• Data Analysts and researchers
• Administrative and office staffs
• Educators
• Small Business Owners
• Lab Staffs
• Project Managers
• Other related positions

Course Outline:

• Module 1: Customizing the Excel Environment
• Configure Excel Options
• Customize the Ribbon and the Quick Access Toolbar
• Enable Excel Add-Ins
• Module 2: Organizing and Analyzing Datasets and Tables
• Manual Tables VERSUS Smart Tables
• Create and Modify Tables
• Sorting Data
• Filtering Data
• Use SUBTOTAL and Database Functions
• Enabling / Disabling Smart Tables
• Module 3: Creating Basic to Advanced Formulas
• Working with Cell References
• Working with Absolute Referencing and Relative Referencing
• Working with Array Formulas
• Working with Name Range
• Descriptive Names for Cell References
• Creating and Using a Named Range
• Creating Slightly Smarter Named Ranges
• Naming Formulas and Constants
• Managing Named Ranges
• Automatically Creating Named Ranges
• Applying Names to Existing Formulas
• Controlling Recalculation
• Solving Formula Errors
• Step-by-Step Evaluation
• Tracing Precedents and Dependents
• Error Checking
• Working with Formulas And Functions
• Creating a Basic Formula
• Excel’s Order of Operations (BODMAS)
• Cell References
• How Excel Formats Cells That Contain Cell References
• Functions
• Using a Function in a Formula
• Using Cell References with a Function
• Using Cell Ranges with a Function
• Excel Compatibility Functions
• Formula Errors
• Logical Operators
• Formula Shortcuts
• Point-and-Click Formula Creation
• Point-and-Click Formula Editing
• The Formulas Tab.
• Using the Insert Function Button
• Copying Formulas
• Absolute Cell References
• Partially Fixed References
• Referring to Other Worksheets
• Referring to Other Workbooks
• Module 4: Working with Statistical and Database Functions
• Working with Simple to Advanced Queries With :
• Calculating Sum , Sumif , Sumifs functions
• Working with Average, Averageif, Averageifs functions
• Counting with Count, Countif, Countifs Functions
• Analyzing Max , min , Large , Small , Rank Values
• Working with Complex Queries With :
• DSUM
• DAverage
• DCount
• DMax
• DMin