Excel for Everyone Data Management Course

You will learn basic topics of Data Management like, Data Cleaning, Power Query, Relational Data Model, Power Pivot

Created byBilal khan

Last updated 11/2024

Urdu

What you'll learn

Tables in Microsoft Excel & benefits of using Tables
Types of Bad Data and different methods of Data Cleaning
Importing Data from different sources using Power Query
Creating Relational Data Model

Course content

1 section • 19 lectures • 3h 6m total length

Introduction1:46
Introduction of Data Management Module
Introduction to Tables14:48
In this lesson you will learn basics of Tables in Microsoft Excel
Data Forms and Data Validation in Data Forms13:02
In this lesson you will learn complete use of Data Forms and input restriction in Data Forms
Data Validation and Sorting14:31
In this lesson you will learn how to create dropdown list using Data Validation and sorting
Bad Data and Data Cleaning6:58
In this lesson you will learn type of Bad Data and how to remove empty cells from data
Data Cleaning 0210:05
In this lesson you will learn how to remove blanks and duplicates as a data cleaning methods.
Data Cleaning 038:41
In this lesson you will learn UPPER, LOWER & PROPER Functions and Spell Check
VLOOKUP & HLOOKUP18:33
In this lesson you learn the most powerful functions in Excel like VLOOKUP & HLOOKUP
MATCH & INDEX7:02
In this lesson you will learn more functions MATCH & INDEX
IF nested IF with AND, OR11:07
In this lesson you will learn IF and nested IF with AND & OR Functions
CONCAT, CONTENATE & SUMPRODUCT12:05
In this lesson you will learn more functions of Microsoft Excel like CONCAT, CONCATENATE & SUMPRODUCT
Introduction to Pivot Tables10:58
In this lesson you will be introduced with Pivot Table in Microsoft Excel
Pivot Tables & Slicers8:29
In this lesson you will learn Pivot Table Filters & Slicers
Introduction to Power Query10:16
In this lesson you will be introduced with the use of Power Query
Power Query 0210:17
In this lesson you will learn how to import data from another workbook and from Text or CSV (Comma Separated Values) file using Power Query.
Power Query 037:34
In this lesson you will learn how to import data from Web and PDF file using Power Query.
Introduction to Data Modeling8:07
In this lesson you will be introduced with very powerful feature Data Modeling in Microsoft Excel.
Creating Relational Data Model6:17
In this lesson you will learn to create relation among different sets and creating Relational Data Model
Power Pivot5:32
In this lesson you will learn using Power Pivot on Relational Data Model

Requirements

Basic Knowledge of Microsoft Excel

Description

This course is designed to transform you into an Excel expert. We'll delve into the fundamentals of Excel, including creating and managing tables, cleaning and transforming data, and utilizing powerful functions like VLOOKUP, HLOOKUP, MATCH INDEX, CONCATENATE, CONCAT, SUMPRODUCT, IF AND OR for efficient data manipulation.

The course will also introduce you to the world of Excel Power Query, a game-changing tool that empowers you to import, clean, and transform data from various sources with ease. You'll learn how to leverage Power Query's capabilities to streamline your data preparation processes and create powerful data models.

Brief of contents: -

Tables and Data Cleaning

Creating and Formatting Tables:

Benefits of Using Tables
Structured References and Formulas
Filtering and Sorting Table Data
Data Cleaning Techniques:
Removing Duplicates
Text-to-Columns
Find and Replace
Data Validation

Lookup Functions:

VLOOKUP
HLOOKUP
INDEX-MATCH
Text Functions:
CONCATENATE
CONCAT
TRIM
Logical Functions:
IF, AND, OR
Nested IF Statements

Excel Power Query

Introduction to Power Query:

Getting Started with Power Query
Basic Query Operations (Combine, Append, Merge)
Transforming Data (Add Column, Remove Column, Rename Column)
Cleaning Data (Remove Rows, Filter Rows)
Data Modeling
Creating Data Models:
Understanding Relationships Between Tables
Building Data Models in Power Pivot

and you will learn much more from this course

Who this course is for:

Everyone who want to increase Microsoft Excel Skills