Advanced Excel (Power Query) Online Training

Simply Easy Learning

Created by Pavan Lalwani, Last Updated 21-Sep-2019, Language:English

Description

Microsoft Excel is a commercial spreadsheet application, written and distributed by Microsoft for Microsoft Windows and Mac OS X. At the time of developing these training modules, we have used Microsoft Excel version 2016. Microsoft Excel is a spreadsheet tool capable of performing calculations, analyzing data, and integrating information from different programs.

Audience

This training has been designed for computer users who would like to learn Microsoft Excel in easy and simple steps. It will be highly useful for those learners who do not have prior exposure to Microsoft applications.

Course Content

introduction

2 Lectures 00:07:15
- Introduction
  
  Preview
  
  00:04:38
- Installation of excel
  
  00:02:37
Basic Functions

7 Lectures 00:39:33
- Text Functions Part - 1
  
  Preview
  
  00:05:46
- Text Functions Part - 2
  
  00:08:23
- Date function
  
  00:05:47
- Date Function Calculate Age
  
  Preview
  
  00:04:15
- Date Format
  
  00:04:08
- Number Function Part -1
  
  00:07:15
- Number Function Part -2
  
  00:03:59
Append

5 Lectures 00:32:26
- Append Excel table
  
  00:06:46
- Append 100 excel tables
  
  00:04:08
- Append CSV files
  
  00:04:51
- Append Excel files
  
  00:10:07
- Merge Queries
  
  00:06:34
Excel Columns

15 Lectures 01:17:43
- Unpivot columns
  
  00:05:09
- Fill down
  
  00:02:18
- Conditional Column Example - 1
  
  00:03:28
- Conditional Column Example - 2
  
  00:02:47
- Conditional Column Example - 3
  
  00:04:51
- Merge Queries From two excel files
  
  00:08:26
- Merge Queries from two data source
  
  00:08:45
- Merge Queries with 3 lookup columns
  
  00:07:10
- Column from example - 1
  
  00:07:09
- Column from example - 2
  
  00:03:33
- Column from example - 3
  
  00:03:35
- Column from example - 4
  
  00:03:51
- Date Functions
  
  00:05:14
- Append tables with different number of columns
  
  00:06:47
- Manage columns
  
  00:04:40
Sort and Filter

1 Lectures 00:07:12
- Sort and Filter
  
  00:07:12
Excel Rows

3 Lectures 00:17:54
- Row transformation remove rows
  
  00:07:40
- Row transformation Keep rows
  
  00:06:08
- Understanding data types and errors
  
  00:04:06
Replace errors and values

1 Lectures 00:04:23
- Replace errors and values
  
  00:04:23
Transpose and headers

1 Lectures 00:04:19
- Transpose and headers
  
  00:04:19
Group by

1 Lectures 00:05:39
- Group by
  
  Preview
  
  00:05:39
Functions

6 Lectures 00:32:55
- M Function date - ( Current )
  
  00:06:53
- M Function date - (Next)
  
  00:04:56
- M Function date - (Previous)
  
  00:04:01
- M Function date - (Previous N and Next N)
  
  00:06:39
- M Function date - (add , Subtract)
  
  00:05:26
- M Function date - ( all date functions )
  
  00:05:00
Case Study

6 Lectures 00:42:29
- Matching rows in two tables Case study - 1
  
  00:05:59
- V lookup approximate match Case study - 2
  
  00:06:43
- Find working day's only Case study - 3
  
  00:06:05
- Cleaning payrole data Case study - 4
  
  00:09:58
- Clean financial data Case study - 5
  
  00:07:51
- Get name of all files in a folder Case study - 6
  
  00:05:53
Power Query

8 Lectures 01:02:30
- Power Query List
  
  00:07:18
- Create list from column
  
  00:04:41
- Dynamic calendar
  
  00:08:32
- Power Query records
  
  00:07:47
- Get data from same file
  
  00:09:57
- Get data from other files
  
  00:10:31
- Load and refresh
  
  00:08:17
- Changing file path
  
  00:05:27

Pavan Lalwani

Pavan Lalwani hold a B.E. in Computer Science from M.I.T College, Pune. Pavan is a Corporate Trainer with 6 Years of Training Experience on various technologies. He specializes in MS Office Tools, IBM Rational Tools, HP Testing Tools, and Software Testing.