Data Analysis & Work Efficiently Using Excel | Novice To Pro

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

Improve your productivity and Advanced Excel skills by mastering variety of topics with diverse Datasets on different domains eg. Manufacturing, Sales, Medical, Finance and more.Increase your value and efficiency in your career and life by learning practical and applicable Excel productivity tools and knowledge with 9 chapters and more than 100 learning videos with a number of assignments and quizzes by the end of each chapter. In this course you should learn the concept, have the opportunity to practice and see examples of how to actually apply this knowledge in the field.Course Breakdown Structure:Chapter 1 (Working Efficiently): In the first chapter you will be introduced for my personal guidelines and principles that I found to be effective to follow to achieve high and rapid speed when working in any software especially in Excel, these techniques were developed in an accelerated working environment were speed and accuracy is a must, following and applying these guidelines should allow you to accomplish your tasks in scalable and automated ways.Chapter 2 (Excel Shortcuts & Basic Tools): Learn most frequently used keyboard shortcuts and other useful tools such as Quick Access Toolbar, Freeze Pans, Sorting and Filtering. Also in this chapter we will study a number of formatting techniques such as cells formatting and conditional formatting.Chapter 3 (Data Preprocessing and Formulas): In this chapter we will learn many of the data processing techniques for the common data types (text, numeric and dates), we will also study a number of useful mathematical functions and techniques such as calculating the cumulative sum for given numbers.Chapter 4 (Data Analysis): After learning to have our data ready, in this chapter we will learn more advanced data tools such as grouping and joining multiple table together, we will also learn how to visualize our data using Excel charts and building a dashboard to analyze the data from different perspectives. Chapter 5-7: In these three chapters we will introduce several automation tools (Power Query, Macros and PY) and they can be used to automate data preprocessing and analysis and other tasks such as creating and modifying Excel objects eg. sheets, cells and ranges. By automating our tasks we can have our job done by the machine in a single click.Chapter 8 (Optimization using Excel Solver):  Learn one of Excel's tools that can be used to perform numeric based decisions, it will help us to find best mixture of numbers to achieve  best performance or result, in this chapter we will learn how to use this tool on variety of applications eg. Capital Budgeting and Resources Allocation. Chapter 9 (Practical Examples): You will get the opportunity to apply most of the topics, productivity and automation methods we studied so far in practical examples, most of them taken from my actual work eg. Automated Reports Creation, Data Extraction and Transformation and Using Excel's Solver effectively.

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 75 Excel Charts (Basic Charts)

Lecture 76 Excel Charts (Categories Charts)

Lecture 77 Excel Charts (Time Series, Distribution and Scatter Plot Charts)

Lecture 78 Project 1: Create Financial Dashboard (Pivot Tables, GetPivotData, Charts)

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 89 VBA Coding (Define Variables, MsgBox, InputBox)

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 99 Example 2: Formatting tables with variable lengths

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