Advanced Techniques in Data Analysis

Introduction

In today’s business environment, it’s common for staff to feel overwhelmed by vast amounts of unorganized data. With the development of ERP systems and databases, companies now have the capability to store enormous quantities of information. The critical question is: how can this information be put to productive use? This Advanced Techniques in Data Analysis program offers practical training in Business Intelligence (BI), enabling you to efficiently work with large datasets by cleaning and normalizing them.

Through this course, participants will learn to analyze past trends, assess current situations, and develop strategies for the future. BI can be applied across tactical, strategic, and operational decisions within any department or discipline. It empowers users at all levels to engage in data cleansing, reporting, analysis, modeling, integration, and automation.

This program goes beyond basic Excel knowledge, using MS Excel exclusively as a powerful yet user-friendly BI tool. It helps individuals gain advanced skills, enabling them to deliver immediate value both personally and organizationally.

Objectives

Upon completion of this Advanced Techniques in Data Analysis course, participants should be able to:

• Enhance Excel Business Intelligence (BI) skills in data slicing, dicing, massaging, aggregation, and modeling.
• Perform data normalization, consolidation, report writing, analysis, and reconciliation.
• Integrate Excel with Access, Web, Text, Internet, SQL, ERPs, and other databases to create dynamic BI models, dashboards, scorecards, and management reports.
• Improve reporting and visual presentation using dynamic visualization techniques.
• Learn various tips and tricks for higher productivity.

Training Methodology

• Hands-on workshops
• Case studies and real-world applications
• Interactive demonstrations
• Role-playing and simulations
• Group discussions and collaborative activities
• Self-assessment and reflection exercises
• Expert-led lectures and presentations

Course Outline

Unit 1: Data Analysis Tools and Techniques

• Data consolidation from separate files and sheets
• Advanced data validation using drop-down lists, date restrictions, or custom validation
• Powerful array formulas
• Cell management tools: left, right, mid, concatenate, value
• Naming cells and ranges; editing and managing range names
• Subtotals, SUMIF, SUMIFS, SUMPRODUCT, COUNT, COUNTIF, COUNTIFS
• Lookup data using VLOOKUP for values and text
• Table tools technique
• Slice dates into week numbers, day names, weeks, month names, years, quarters
• Text-to-columns; dynamic trimming (TRIM), LEN
• Replace texts with others or find numbers in texts by substituting
• Summarize functions such as SUMIFs, COUNTIFS, SUBTOTALS, SUMPRODUCT, and COUNT
• Text change functions

Unit 2: Mastering Data Reporting

• 20 must-learn PivotTable tools
• Create PivotTables
• Number formatting tricks
• Create report layout designs
• Sort in ascending, descending order; more sort options
• Label filters and value filters
• Collapse and expand reports
• Add the Drill Down feature
• Summarize by summing, averaging, minimizing, maximizing, and counting
• Summarize totals/as a percentage
• PivotTable options
• Insert formulas
• Data analysis
• Copy PivotTables
• Create PivotCharts
• Dynamic chart labeling
• Master Slicer
• Show report filter pages
• Link PivotTables and PivotGraphs with PowerPoint
• Conditional formatting with PivotTables
• Design reports using the GETPIVOTDATA function

Unit 3: Data Modeling and Integration

• Roll-up spinners
• Radio button data modeling (using IF function)
• List box data modeling (using CHOOSE function)
• Scenario manager
• Link Excel to text files
• Connect Excel with Access databases
• Understand the link between Excel and SQL
• Link Excel to the Internet
• Link Excel to itself

Unit 4: The Look and Feel

• Draw charts and use visualization techniques
• Use the camera tool
• Create formula-driven visualizations
• Use advanced typescripts
• Utilize symbols in formulas creatively
• Create in-cell mini charts
• Develop unconventional style charts