Advanced Data Modelling and Analytics Using Excel COURSE

Course Outline

• Introduction to Excel
• Cell referencing, cell formatting and entering formula
• Variables in Excel
• Introduction to Excel Tables
• Filtering data
• Sorting data
• Use of conditional formatting

Integrating Excel with Other Data Sources, Software and Platforms

• Link with Google Sheets
• Link with SharePoint Lists
• Link with Mobile Data Collection Tools
• Link with Databases (Postgres, Oracle, SQL)
• Link with CRMs

Data Cleaning and Transformation Using Power Query

• Use first Row as Columns
• Rename Columns
• Replace Cell Data & Missing Values
• Remove Rows & Columns
• Data Types
• Auto IDs
• Find Duplicates
• Combine & Split Multiple Fields
• Custom Formatting (Currency, Phone numbers)
• Group Data with Merge & Append Queries

Developing Risk Mitigation Strategies

• Utilizing Technology for Risk Management

Advanced Excel Formula and Functions

• Conditional Functions
• Lookup Functions
• Text Functions
• Date Time Functions

Data Modelling Using Excel Power Pivot

• Activate Excel Power Pivot
• Add Data to the Data Model
• Create Relationships Between Tables
• Create Pivot Tables Using Data Models

Advanced Pivot Tables

• Data Refresh-Configure self-updating dynamic Pivot tables and Pivot charts
• Rank in pivot table
• Pivot Table Calculated Field
• Link multiple Pivot tables to one slicer
• Running Total in pivot table
• Create Many Pivot Tables from one
• Export Pivot Table to PowerPoint & Word

Build Interactive Dashboards Tools/instruments on excel

• Data Refresh-Configure self-updating dynamic Pivot tables and Pivot charts
• Rank in pivot table
• Pivot Table Calculated Field
• Link multiple Pivot tables to one slicer
• Running Total in pivot table
• Create Many Pivot Tables from one
• Export Pivot Table to PowerPoint & Word

More Details

Additional course-related details and resources will be provided here.