Advanced Data Modelling and Analytics Using Excel
Details
Course Overview:
Microsoft Excel stands as the most widely utilized software for analytics, reporting, and strategic purposes. To become a proficient data analyst, it is essential to achieve a mastery of advanced Excel skills. This course aims to elevate your Excel proficiency, enabling you to skilfully employ intricate functions to handle, manipulate, analyse, and present data effectively.
Target Participants
This course is aimed at professionals who have basic to intermediate Excel skills and want to advance those skills.
Course Duration
Online - 7 Days
Classroom β Based- 5 Days
What You Will Learn
By the end of this course the participants will be able to:
- Master advanced formulas and functions
- Consolidate data, link and export data
- Create sophisticated outputs including tables, charts, and graph
- Use What-If Analysis tools such as Goal Seek, Solver and Scenarios
- Increase interactivity by automating spreadsheets with macros and VBA
- Turn raw data into βmust makeβ decisions using PivotTables and Power Pivots.
Outline
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
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
- Interactive Dynamic Dashboards,
- MIS tools
- Custom Page Navigation using Links
- Custom filters using Slicers and timeline
- Configure Advanced Custom Charts & KPIs
- Configure Custom Pictographs in excel
- Configure Dynamic thermometer with dynamic colors
- Dynamic Chart Titles in Excel
- Share Excel Data and Reports to Power point, Share point, Power BI
- Case Study
Schedules
No. of Days: | 5 |
Total Hours: | 40 |
ποΏ½οΏ½Website: https://bit.ly/appcomsinstitute
Appcoms Analytics & Training Institute is a leading provider of Research, Training, and Consultancy Services to international development organizations. We collaborate with our partners to enhance the effectiveness and efficiency of their social interventions through innovative and sustainable solutions.
Contact Information:
πRegistration Link: https://bit.ly/appcomsregistration
ππ Whatsapp: +254 758 412 433
ππ§ Email: [email protected]
πLinkedIn: https://bit.ly/3QVmDqr
πFacebook: https://bit.ly/3shO8Af
πYouTube: https://bit.ly/3KVBYn7