Data Analysis & Work Efficiently Using Excel | Novice To Pro

Data Analysis & Work Efficiently Using Excel | Novice To Pro
Published 5/2025
MP4 | Video: h264, 1920×1080 | Audio: AAC, 44.1 KHz

Work efficiently using Excel by utilizing practical Excel Shortcuts, Data analysis, Automation and Optimization tools.

What you’ll learn

Learn productivity guidelines and how to work efficiently

Master essential Excel Shortcuts to work faster

Learn to process, analyze Data and build dynamic Dashboards using Excel

Automate repetitive tasks and Data preprocessing using Power Query and Macros

Optimize quantitative and numeric based decisions using Excel’s Solver

Real life practical examples to apply these productivity tools in work

Requirements

Basic Office knowledge is helpful

Microsoft Excel installed, latest Excel version is preferred, but not required

Description

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Recommendations

Lecture 3 Course Materials

Lecture 4 Important Note

Section 2: Working Efficiently

Lecture 5 Productivity and Efficiency

Lecture 6 Mouse Settings

Section 3: Excel shortcuts & Basic Tools

Lecture 7 Moving through cells

Lecture 8 Move between sheets and tabs

Lecture 9 Rows and Columns (Select, Add, Remove and Hide)

Lecture 10 Entering Data

Lecture 11 Quick Access ToolBar

Lecture 12 Alt Shortcut

Lecture 13 Freeze Panes

Lecture 14 Excel Sheets (Create, Copy, Delete and Rename)

Lecture 15 Sorting Tool

Lecture 16 Filtering Tool

Lecture 17 Data Validation

Lecture 18 Find Tool

Lecture 19 Select Objects

Lecture 20 Cells Formatting

Lecture 21 Format Painter

Lecture 22 Clear Tools

Lecture 23 Conditional Formatting

Lecture 24 Number Formatting

Lecture 25 Dates Formatting

Section 4: Data Preprocessing and Formulas

Lecture 26 Formulas and Functions

Lecture 27 Cells Referencing

Lecture 28 Assignment: Mixed Referencing

Lecture 29 Solution: Mixed Referencing

Lecture 30 Cells Referencing from other (Sheets, Workbooks)

Lecture 31 Paste Special

Lecture 32 Note about CONCAT

Lecture 33 CONCAT

Lecture 34 Assignment: TextJoin

Lecture 35 Solution: TextJoin

Lecture 36 LEFT MID RIGHT Functions

Lecture 37 Text To Column

Lecture 38 Assignment: TextSplit

Lecture 39 Solution: TextSplit

Lecture 40 Assignment: TextBefore & TextAfter

Lecture 41 Solution: TextBefore & TextAfter

Lecture 42 Trim Function

Lecture 43 Remove Duplicates and Unique

Lecture 44 Optional: Filter

Lecture 45 Optional: Filter TextJoin Combination

Lecture 46 Text Function

Lecture 47 Dates Functions

Lecture 48 If Function

Lecture 49 Find and Replace

Lecture 50 Go To Special (Blanks, Visible Cells Only)

Lecture 51 Unpivot Columns

Lecture 52 Std Function

Lecture 53 Basic Math

Lecture 54 Conditional Functions (SumIf AverageIf CountIf MinIf MaxIf)

Lecture 55 Generate Random Numbers (Rand RandBetween)

Lecture 56 SumProduct Function

Lecture 57 Show Formulas

Lecture 58 Window Functions (Basics)

Lecture 59 Window Functions (Cumulative SUM)

Lecture 60 Assignment: Cumulative Sum for Groups

Lecture 61 Solution: Cumulative Sum for Groups

Section 5: Data Analysis

Lecture 62 Data (What is Data)

Lecture 63 Data (Knowledge Pyramid)

Lecture 64 Data (Primary & Foreign Keys)

Lecture 65 Data (Joining & Grouping)

Lecture 66 Data (Aggregation Level)

Lecture 67 Data (Data Normalization)

Lecture 68 Lookup Functions (Vlookup Hlookup Xlookup)

Lecture 69 Assignment: Vlookup Match

Lecture 70 Solution: Vlookup Match

Lecture 71 Pivot Tables

Lecture 72 Pivot Tables (group by multiple columns)

Lecture 73 Pivot Tables (Slicers and Timeline)

Lecture 74 Pivot Tables (Define Name & Create Table)

Lecture 79 Creating Dashboards using BI tools

Lecture 80 Assignment: Project 2: Mental Health Survey Analysis

Lecture 81 Solution: Project 2: Mental Health Survey Analysis

Section 6: Power Query

Lecture 82 Introduction to Automation

Lecture 83 Import Data

Lecture 84 Data Preprocessing (Transform Vs Add Column)

Lecture 85 Merge Queries

Lecture 86 Introduction for Data Model

Lecture 87 Introduction for DAX

Section 7: Macros

Lecture 88 Example 1: (Record Simple Macro, Relative References)

Lecture 90 VBA Comments and Comment Blocks

Lecture 91 VBA Functions (Left Len Split)

Lecture 92 VBA Objects (Worksheets)

Lecture 93 VBA Objects (Range)

Lecture 94 VBA Objects (Cells)

Lecture 95 VBA (Find number of Columns or Rows in Excel sheet)

Lecture 96 VBA If Statement

Lecture 97 VBA For Loop

Lecture 98 VBA While Loop

Lecture 100 Example 3: Add new rows to separate different cells values

Lecture 101 Power Query Vs Macros and when to use each

Section 8: Python (Optional)

Lecture 102 Introduction to PY

Lecture 103 Build Machine Learning Model using PY

Lecture 104 Data Visualization using PY

Section 9: Optimization using Excel Solver

Lecture 105 Introduction to Optimization

Lecture 106 Optimization – Simple Example

Lecture 107 Optimizing Product Mix

Lecture 108 Optimizing Capital Budgeting

Lecture 109 Optimizing Resources Allocation

Lecture 110 Optimizing Resources Allocation (Complex Objective)

Section 10: Practical Examples

Lecture 111 Reports Creation (Manual)

Lecture 112 Reports Creation (Automated)

Lecture 113 Data Extraction, Transformation and Loading ETL (Manual)
Lecture 114 Data Extraction, Transformation and Loading ETL (Automated)
Lecture 115 Using AI (DeepSeek) to Generate Formulas

Lecture 116 Using AI (DeepSeek) to Generate VBA Codes

Lecture 117 Practical Resources Allocation (Optional)

Section 11: What next

Lecture 118 What Next

Any one deals with Data (Data Analysts, Operations Specialists, Financial Analysts, Accountants and Business Owners, etc. ),Any one who is interested in Data and Automation