Data Automation for Beginners

This 2-day course will introduce participants to the essential skills and techniques for automating data management and analysis using Microsoft Excel. Day 1 will focus on the fundamentals of structured data, data cleaning shortcuts, and formula automation. Day 2 will expand into using Power Query for advanced data transformation, combining data sources, and creating reports efficiently.

By the end of this course, participants will be able to:

1. Understand and work with structured data in Excel.
2. Clean and transform data with Excel shortcuts and basic functions.
3. Use PivotTables, slicers, and formulas for basic data analysis.
4. Apply Power Query for advanced data transformation and automation.
5. Create simple dashboards for data visualization.

DAY 1: Excel Data Automation Fundamentals

Module 1: Structuring Data in Excel

1. Understanding the importance of structured data.
2. Working with rows and columns efficiently.
3. Organizing data using tables.
4. Introduction to Table references in formulas.

Module 2: Data Cleaning with Excel Shortcuts

1. Using Text to Columns for splitting data.
2. Remove duplicates for data consistency.
3. Ctrl + E Flash Fill for quick text formatting.
4. Ctrl + A to select all data.
5. Ctrl + T for converting ranges to Tables.
6. Basic Excel Functions: TRIM, UNIQUE.
7. Data Validation: Ensuring correct data entry.
8. Practice Activity: Clean data with Excel shortcuts.

Module 3: Working with Common Errors in Data

1. Identifying and fixing blank rows.
2. Removing or replacing incorrect data.
3. Handling missing or duplicated data.
4. Error handling using Excel formulas (IFERROR).
5. Practice Activity: Identify and fix errors in sample datasets.

Module 4: Basic Data Analysis & Reporting

1. Using Formulas: SUM, AVERAGE, COUNT,vlookup and xlookup.
2. Using UNIQUE and COUNTIF to analyze sales data.
3. Using PivotTables for summarizing data.
4. Adding Slicers for interactive filtering.
5. Building a Simple Dashboard to track sales performance.
6. Practice Activity: Create a dashboard with PivotTables and slicers.

Module 5: Hands-On Practice & Group Activity

1. Task: List the highest sales, unique products, number of salespeople, and build a basic dashboard.
2. Group work and discussions on best practices.

DAY 2: Data Transformation & Automation with Power Query

Module 6: Introduction to Power Query

1. What is Power Query and why use it?
2. Navigating Power Query Editor.
3. Importing data from multiple sources (Excel, CSV, and folders).
4. Setting up automatic data refresh.

Module 7: Data Transformation & Cleaning with Power Query

1. Remove Columns and unwanted data.
2. Group Data: Aggregate by categories.
3. Merge Columns for data consolidation.
4. Use First Line as Header for proper data organization.
5. Remove Duplicates in Power Query.
6. Data Format: Changing data types (text, number, date, etc.).
7. Conditional Columns and creating custom columns.
8. Query References: Linking queries together for efficient workflow.
9. Practice Activity: Transform data using Power Query.

Module 8: Advanced Power Query Techniques

1. Appending Queries: Combine data from multiple sources.
2. Merging Queries: Consolidate data from different tables.
3. Handling Decimal Places: Round values appropriately.
4. Using Query Duplication: Work with duplicate queries for better data processing.
5. Working with Dates and Time: Filter, group, and analyze date-based data.

Module 9: Combining Data from Multiple Sources

1. Importing multiple data sets into Power Query.
2. Merging and appending queries for combined reports.
3. Automating the process of combining data from various sources.
4. Hands-On Activity: Create a consolidated report from multiple data files.

Module 10: Final Hands-On Project: Building a Comprehensive Data Report

1. Use Power Query to clean, transform, and merge data.
2. Create a final report with data analysis, PivotTables, and dashboards.
3. Group Activity: Build a final report and present findings.

We’d happy to help.
Feel free to talk to us today, however most suitable for you.

Mobile call: +603 – 7493 6868

WhatsApp: +6018 – 380 5878

Email: enquiry@apexitraining.com.my

Website: www.apexitraining.com.my