Master Data Analysis Using Excel Powerful Tools & Functions

Data Analysis With Excel

What you'll learn

Data Cleaning and Transformation

Power Query

Relative and Absolute cell references

Sumif, Countif, Averageif, Xlookup, Filter, Sort, Subtotal, and Take Functions in Excel

Conditional formatting in Excel

Data Validation

Pivot tables

Data Visualisation

Requirements

A basic understanding of Microsoft Excel's uses and functions

Description

This course will help you to visualize, analyze and gain insights from your data using excel functions such as SumIf, CountIf, AverageIf, Xlookup, Subtotal, Sort, Filter, Take, etc. and very powerful tools such as Pivot Tables, Dashboards, Power Query. You will develop employable data analyst skills, starting with the Excel basics, what it can do, and the data analysis steps you should follow.Topics included in this course:1- What is data analysis and why is it important.2- Data vs Information3- Clean Data vs Dirty Data4- Why would you want to convert a range into an Excel table5- Relative vs Absolute Cell Reference6- Data Validation7- Conditional Formatting8- Useful Excel Functions for Data AnalysisSumIf Function CountIf FunctionAverageif FunctionXlookup FunctionSubtotal FunctionSort FunctionFilter FunctionTake Function9- Pivot TablesBuilding dashboards to analyze data10- Power QueryGetting familiar with the “Data” tabGetting familiar with Power Query RibbonRemove ColumnsChoose ColumnsRemove RowsKeep RowsUsing Power Query to Transform/Clean - TextUsing Power Query to Transform/Clean - NumbersUsing Power Query to  Transform/Clean - Date and Time11- Get Data from a Folder using Power Query12- Get Data from a latest file in a folder Using Power Query

Overview

Section 1: Introduction

Lecture 1 What is Data Analysis

Section 2: Table, Cell References, Data Validation, and Conditional Formatting

Lecture 2 Excel Table, Cell References, Data Validation, Conditional Formatting

Section 3: Useful Excel Functions for Data Analysis - Part 1

Lecture 3 Using SumIf, CountIf, AverageIf and Xlookup Functions

Section 4: Useful Excel Functions for Data Analysis - Part 2

Lecture 4 Sort, Filter and Take Functions

Section 5: Pivot Tables - Part 1

Lecture 5 Pivot Tables - Part 1

Section 6: Pivot Tables - Part 2

Lecture 6 Pivot Tables - Part 2

Section 7: Power Query

Lecture 7 Power Query

Section 8: Power Query - Transform Text

Lecture 8 Using Power Query to transform Text

Section 9: Power Query - Transform Numbers

Lecture 9 Power Query - Transform Numbers

Section 10: Power Query - Transform Date and Time

Lecture 10 Power Query - Transform Date and Time

Section 11: Power Query - Get Data from Web, PDF file

Lecture 11 Using Power Query to get Data from Web, PDF file - Using Power Query Pivot/Unpi

Section 12: Power Query - Combine and Merge Tables

Lecture 12 Using Power Query to combine and merge tables

Section 13: Power Query - Get Data from a Folder

Lecture 13 Using Power Query to get data from a folder

This course is suitable for those who are interested in pursuing a career in data analysis or data science, as well as anyone looking to use Excel for data analysis in their own domain.