Data Modelling and Analytics Using Excel

OVERVIEWCOURSE OUTLINEWHEN & WHEREREQUEST IN-HOUSE

Microsoft Excel is the most commonly used analytics, reporting and strategy software. Becoming an expert data analyst requires you to have mastery of advanced Excel skills. This course is aimed at taking your Excel skills to the next level. You will be able to apply complex functions within Excel to manage, manipulate, analyze and visualize data.

Target Participants

This course is aimed at professionals who have basic to intermediate Excel skills and want to advance those skills.

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.
Introduction to Excel Environment

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

In person, or online, with live instructor

Course Information

Estimated Time: 7 DAYS

Categories:

Course Instructor

Owi_ghola Owi_ghola Author
This course does not have any sections.

Also see:

×

Hello!

Click one of our contacts below to chat on WhatsApp

× Chat with us