Fundamental Excel Skills: Beginner to Intermediate

About Course

Learn Microsoft like never before. Solve most of your job’s data, analysis, and reporting needs and start your journey to becoming an analytics expert with the world’s most used office spreadsheet application.

Leverage our expertise from over 20 years of spreadsheet knowledge to jumpstart your career or even leapfrog your career with the knowledge that will save you hours and hours of boring, repetitive, and even nagging tasks. The result is a course that addresses day-to-day job requirements and imparts excellent magical and efficient utilities that save worker hours every week.

Our systematically crafted curriculum with imparting deep knowledge and skills relating to:

-Basics of Using Excel
-Data Analysis, sorting, and filtering including advanced filters, slicers, excel tables, and many more
-Graphs and Charting
-Pivot Tables
-Excel Functions and formulas
-Visualizing data with conditional formats, graphs, and charts
-Introduction to Power Query; arguably the simplest yet very powerful data laundry for extraction, transformation, and loading of data.
-Over 10 hours of video tutorials with spreadsheets supplied for your practice.

Get to practice with our careful sources data sets and spreadsheets for your practice and enjoy a full year of unlimited subscription.

BY TAKING OUR SELF-PACED COURSES, YOU SAVE MONEY THAT WOULD BE SPENT ON PHYSICAL COURSES WHILE ENJOYING THE BENEFITS OF BEING ABLE TO PLAY BACK ANY VIDEO ANYTIME.

Course Content

Module 1 – Fundamentals of Ms. Excel
Fundamentals of Ms. Excel

How to open Excel

01:34
Enter Data (Save)

01:22
Open Saved Data

02:08
Structure Rows and Columns

01:34
Overview of the Excel Window/Graphic User Interface

07:59
Basics of Data Analysis

05:44
Overview of Excel Formats

04:58
Difference between a Workbook and Worksheet

00:47
Copy and paste or move sheet in or outside a workbook

02:43
Add columns and Rows in a worksheet

03:02
Hide or Unhide rows and Columns

02:34
Freeze or unfreeze panes to view large datasets

02:15
Introduction to Microsoft Excel

Module 2 – Essential tools for Data Analysis

Introducing Data Manipulation Skills in Excel

02:22
How To Use Data Filters in Excel

06:09
Using Advanced Filters for data management in Excel

09:14
Basics of number formatting in Excel

03:36
Number Format – Advanced

01:03
How to copy or Move Sheet in an excel workbook

01:25
Select and Boarder

03:05
Form for Data Entry

07:27
Formatting Data as an Excel Table

04:60
Benefits of Managing data in an Excel Table format

07:10
Excel Table data management

03:46
Multiple Sheet Operation

03:34
How to Sum all worksheets in an excel workbook

03:25
Sum All Sheets Use Start-End Sheets

03:41
Data Validation

05:24
How to remove duplicates in a list

02:53
Text to Columns

05:31
PageLayOut – PrintTitles PrintOptions Gridlines Freezepanes

03:05

Module 3 – Data analysis using Pivot Tables
Data analysis using Pivot Tables

Introduction to PivotTables using ADD DRAG AND DROP functionality

03:34
Filter for PivotTables

02:29
Sort Values-SLICERS

00:58
Filter Top_Highest Values

02:53
Analysis of Sum and Count of Values and percentage values

03:29
Filter for PivotTables-SLICERS

02:23
Understanding a frequency distribution

03:44
Constructing Frequency Distribution in Pivot Table

12:39
Using Smart Number Format and Conditional Formatting to visualize data in a Pivot Table

03:14
Pivot Table reports using Smart Number Formats and Conditional Formatting

04:49

Module 4 – Overview of formulas and functions
Overview of formulas and functions

Fundamentals of formulas and functions, basic functions, relative and absolute references

25:20
Syntax and application of the COUNTIFS function

02:40
Syntax and application of the SUMIFS function

03:53
Syntax and application of the VLOOKUP

02:23
Understanding the application of the MATCH function

05:19
Understanding the application of the INDEX-MATCH function combination

07:23
The SUMIFS function with multiple criteria vs SUMPRODUCT function

04:03
Introduction to SUMPRODUCT

03:20
The extended power of the SUMPRODUCT function

16:52
The ROW and COLUMN functions and how they compare to the ROWS and COLUMNS functions

07:26
Introducing the power of the INDIRECT function

07:56
The application of the INDIRECT with ROW function combination

05:56
The extended application of the INDIRECT with ROW function combination

08:23
The syntax and application of the OFFSET function

11:53
Understanding the IF functions and NESTED Ifs (AND/OR) vs the OFFSET function

22:58
Advanced Applications of Offset function Part 1

03:17
Advanced Applications of Offset function Part 2

03:57
Advanced Applications of Offset function Part 3

03:42
Advanced Applications of Offset function Part 4

02:55
Advanced Applications of Offset function Part 5

09:27

Module 5- Conditional Formatting for Data Visualization
Master Conditional formats starting from standard formatting rules, color scales, icon sets to use of formulas and functions to highlight cells that meet set conditions.

Module 6-What-If-Analysis for scenario management and sensitivity analysis
Learn the Ins and Outs of Goal Seek, Scenario Manager and the Excel Data Table

Module 7-Excel Keyboard Shortcuts
Explore the efficiency of spreadsheets using keyboard shortcuts

Module 8-Charts and Graphs for enhanced data Visualization and Analysis
Master Excel Graphs and Charts for enhanced and impressive reporting using line graphs, bar and column graphs, pie charts, bubble charts, combo charts and box plots.

Overview of different charts in excel

09:23
Determining the best chart to fit a dataset or a required analysis

04:21
General Chart attributes and overview of pie charts

13:45
Bubble Charts

13:10
Analysis and reporting using Combo Charts

05:30
Understanding line graphs

03:38
Basics of bar and column charts in excel

05:04
Overview of box plots in excel

02:49
Box plot display options

03:02
Box Plot Axes and Data Labels

03:00
Interpreting a box plot

02:46

Module 9-Case Study: Applying advanced formulas of OFFSET and SUMPRODUCT in data analysis
Explore data analysis and reporting using the OFFSET and the SUMPRODUCT formulas

Module 10-Basics of Excel Power Query
Explore the effectiveness of Power Query for data cleaning and analysis and excel.

Overview of Power Query

04:14
A glimpse into sample data problems solvable and transformations in Power Query

10:17
Power Query application to data transformation: Wide to Long Conversion

05:56
Power Query Wide to Long, Add data-Data Refresh

01:47
Power Query Long-to-Wide data transformation of data

10:27
Using Power Query to Merge files from a folder without opening the folder

07:19
Overview of web upload for importation into Power Query

03:52
Import data from web, transform and automate reporting

32:31
Using power query to undertake Data cleaning and Transformation of delimited unclean data

05:41
Merging two queries as a trick to address lookup challenges

06:26
Using the Power Pivot Data Model to create relationships and exploring new column additions in the Data Model

14:47
Creating Measures for data analysis from a Data Model in Power Pivot

09:02

Student Ratings & Reviews

5.0

Total 1 Rating

1 Rating

0 Rating

Atuhaire Dan

1 year ago

Brilliant, well curated course. An absolute marvel.

About Course

Course Content

Module 1 – Fundamentals of Ms. Excel Fundamentals of Ms. Excel

How to open Excel

Enter Data (Save)

Open Saved Data

Structure Rows and Columns

Overview of the Excel Window/Graphic User Interface

Basics of Data Analysis

Overview of Excel Formats

Difference between a Workbook and Worksheet

Copy and paste or move sheet in or outside a workbook

Add columns and Rows in a worksheet

Hide or Unhide rows and Columns

Freeze or unfreeze panes to view large datasets

Introduction to Microsoft Excel

Module 2 – Essential tools for Data Analysis

Introducing Data Manipulation Skills in Excel

How To Use Data Filters in Excel

Using Advanced Filters for data management in Excel

Basics of number formatting in Excel

Number Format – Advanced

How to copy or Move Sheet in an excel workbook

Select and Boarder

Form for Data Entry

Formatting Data as an Excel Table

Benefits of Managing data in an Excel Table format

Excel Table data management

Multiple Sheet Operation

How to Sum all worksheets in an excel workbook

Sum All Sheets Use Start-End Sheets

Data Validation

How to remove duplicates in a list

Text to Columns

PageLayOut – PrintTitles PrintOptions Gridlines Freezepanes

Module 3 – Data analysis using Pivot Tables Data analysis using Pivot Tables

Introduction to PivotTables using ADD DRAG AND DROP functionality

Filter for PivotTables

Sort Values-SLICERS

Filter Top_Highest Values

Analysis of Sum and Count of Values and percentage values

Filter for PivotTables-SLICERS

Understanding a frequency distribution

Constructing Frequency Distribution in Pivot Table

Using Smart Number Format and Conditional Formatting to visualize data in a Pivot Table

Pivot Table reports using Smart Number Formats and Conditional Formatting

Module 4 – Overview of formulas and functions Overview of formulas and functions

Fundamentals of formulas and functions, basic functions, relative and absolute references

Syntax and application of the COUNTIFS function

Syntax and application of the SUMIFS function

Syntax and application of the VLOOKUP

Understanding the application of the MATCH function

Understanding the application of the INDEX-MATCH function combination

The SUMIFS function with multiple criteria vs SUMPRODUCT function

Introduction to SUMPRODUCT

The extended power of the SUMPRODUCT function

The ROW and COLUMN functions and how they compare to the ROWS and COLUMNS functions

Introducing the power of the INDIRECT function

The application of the INDIRECT with ROW function combination

The extended application of the INDIRECT with ROW function combination

The syntax and application of the OFFSET function

Understanding the IF functions and NESTED Ifs (AND/OR) vs the OFFSET function

Advanced Applications of Offset function Part 1

Advanced Applications of Offset function Part 2

Advanced Applications of Offset function Part 3

Advanced Applications of Offset function Part 4

Advanced Applications of Offset function Part 5

Module 5- Conditional Formatting for Data Visualization Master Conditional formats starting from standard formatting rules, color scales, icon sets to use of formulas and functions to highlight cells that meet set conditions.

Introduction Standard conditional formatting rules, Data bars, color scales and Icon sets and how to Clear Rules

Using functions and formulas to define conditional formatting rules

Conditional formats to highlight multiple columns and how to use multiple criteria to format a range

Module 6-What-If-Analysis for scenario management and sensitivity analysis Learn the Ins and Outs of Goal Seek, Scenario Manager and the Excel Data Table

Introduction to What-if-analysis

Using the Goal Seek Command

Scenario Manager Command for scenario management

Using Scenario Manager with Named Cell

Introducing loan repayment computation scenario

Sensitivity Analysis with a one variable Data Table

Sensitivity Analysis with a two variable Data Table

Additional Case study with a two variable Data Table