3 days - Office 365 - Advanced Excel 2021 and Data Analytics - level 2
About This Course
This course "3 days – Office 365 – Advanced Excel 2021 and Data Analytics – level 2" is designed to integrate data from diverse sources, clean and optimize datasets, and categorize data for in-depth analysis using advanced tools like PivotTables and Pareto charts. The course also covers advanced data visualization, troubleshooting, automation with macros, and ensuring data accuracy through validation and standardization. These skills will empower learners to handle complex datasets and enhance their decision-making capabilities.
What You'll Learn
LO1: Able to use intermediate to advanced Excel functions and formulas to extract important data
LO2: Able to get from different data source and combine into useful dataset
LO3: Able to remove duplicates data, unpivot data, and keep relevant data for analysis
LO4: Able to group data into category and using Pareto analysis to solve problem and improve processes
Topics Covered:
• How to optimize Excel Table Design
• Duplicate worksheets within same workbook or another workbook
• Understand Cell Reference A1, $A$1, A$1, $A1
• Range Names and its uses
• Apply Conditional Formatting to data
• Understand how custom number format works in Excel
• Compute with Dates formulas NETWORKDAYS, WORKDAYS, WORKWEEK, TEXT
• Extract data with Xlookup, Vlookup, Hlookup, Index and Match
• Arrange data with VStack, HStack,
• Manage data with functions FILTER, SORT, UNIQUE, TEXTJOIN, TEXTSPLIT, TRANSPOSE
• Types of errors
• How to handle errors
• Extract data with countifs, and sumifs
• Evaluating conditions for data
• Creating 3D Reference
• Pivot table, Advanced Pivot
• Slicer, Timeline
• Excel table with Date Filters, Text Filters, Number Filters
• Custom Sort and Filter
• Advanced Filter
• SubTotal
• Data Validation
• Adding explaining Notes
• Trace errors with Auditing
• Track changes
• Macros
• Sighting of duplicate rows, inconsistent data or missing data
• Separating data - left, right, mid, find to extract data
• Data Tools to remove duplicates
• Advanced Filter to extract unique records
• Trimming extra spaces and cleaning of data
• Correcting inaccurate data
• Update missing data
• Unpivot data
• Standardized Text using Find and Replace
• Formatting Dates
• Analyzing your data with charts e.g. line, bar, column, pie
• Identify and list all problems
• Identify the root cause of each problem
• Pareto analysis charting and take actions to solve problems
Entry Requirements
• Apply basic formula and functions (i.e. SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND, IF)
• Understand how to use cell referencing (i.e. A1, $A$1)
• Minimum 1 year of working experience (with preference in using Excel)
• Speak, listen and read English at a proficiency level not lower than the WPL Level 4, and manipulate numbers at a not lower than the WPN Level 4.
• Minimal education qualification is GCE ‘O’ Level, GCE ‘N’ Level or ITE certificate.