Excel Data Analysis Bootcamp For Finance Professionals

A Comprehensive Excel Training for the Serious Financial Data Analyst

What you'll learn

Start-off with an easy-to-follow crash course that covers everything you need to start using Excel like a pro.

Master Excel essentials: cell references, named ranges, tables, Auto-fill, Flash-fill, Text-to-columns, dynamic arrays, data validation and drop-down lists.

Begin to dabble with basic formulas and functions such as SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, UPPER, LOWER, PROPER, TRIM, CONCAT, TEXTJOIN and more.

Feel at ease creating formulas that search and fill data automatically with VLOOKUP, INDEX, MATCH, XLOOKUP.

Wrap your mind around Excel dates. Discover DATE, TIME, DAYS, DAYS360, WORKDAY, EDATE, CHOOSE, TEXT and other must-know functions for working with Excel dates.

Learn how to create your very own custom Excel functions with LET and LAMBDA.

Employ ChatGPT or other large language models to speed up your Excel learning journey.

Requirements

No prior Excel knowledge needed. This course teaches everything you need to know.

You have access to a computer with Microsoft Office suite installed so you can follow the course and practice.

You are willing to put time to acquire serious data-analysis skills using Excel.

You want to shine above the rest with your expertise of Excel tools.

Description

Dear Aspiring Excel Wizard,Are you ready to elevate your Excel skills to new heights? If you answer YES, I invite you to embark on a learning journey with me into the wonderful world of Excel. If you join this course, I practically guarantee that your skills will jump to a higher orbit almost overnight.I am so confident because I meticulously designed it for people like you who are serious about their skills and aspire to master Excel as quickly and as deeply as humanly possible. That’s why I believe this comprehensive course is going to be your gateway to mastery of one of the most sought-after skills in today’s job market.This is not one of those regular courses, which are a dime a dozen. No way! It is a transformative learning experience that helps you build your skills step by step, nourishes your mind with fresh knowledge and creative ideas and gently leads you towards mastery. So you can transform yourself into a true Excel wizard.Here’s what you are getting with this course:Lifetime access to the entire training so you can hone your skills anytime you choose.Excel-basics crash course: Where it all begins. A smooth journey through the fundamentals, paving the way for mastery. This is where you build a strong understanding of the most important Excel topics. Such as user-interface tools, Flash-fill, Auto-fill, Text-to-columns, cell references, tables, named ranges, dynamic arrays.How to create drop-down lists with data validation tools in Excel.Formulas and Functions: The backbone of Excel.Lookup and reference tutorials: VLOOKUP, INDEX, MATCH, XLOOKUP.Concatenation: Formulas to join text strings with CONCAT, TEXTJOIN or the ampersand operator.How to transform text values with UPPER, LOWER, PROPER, TRIM functions.The basics of professional formatting of spreadsheets.Date and Time Mastery: A comprehensive training on how to work with dates and times in Excel. From formatting to calculations. Master date and time functions: DATE, TIME, DATEDIF, WORKDAY, WORKDAY.INTL, YEARFRAC, NETWORKDAYS, NETWORKDAYS.INTL, DAYS, DAYS360.How to calculate fiscal years based on dates.How to create custom functions with LET and LAMBDA.Excel what-if analysis tools: Goal seek, Data Table, Scenario Manager.Data Visualization: A picture is worth a thousand numbers. Bring your data to life. Learn to present data with Excel charts that impress, captivate and inform. Pivot Tables: Unravel the secrets of summarizing and analyzing large data sets effortlessly. This is where data bows to you, revealing its hidden gems through your newfound mastery.Practical homework (Lab) assignments with solution walk-throughs.And more…

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Which Excel?

Section 2: Excel-Basics Crash Course

Lecture 3 About This Section

Lecture 4 Basic Excel Concepts and the User Interface

Lecture 5 Creating Worksheets and Entering Data

Lecture 6 The AutoFill Tool

Lecture 7 The Flash-Fill Tool

Lecture 8 Text-to-columns

Lecture 9 Introducing Excel Formulas and Functions (Part 1)

Lecture 10 Introducing Excel Formulas and Functions (Part 2)

Lecture 11 Excel A1-Style Cell References

Lecture 12 Excel Named Ranges

Lecture 13 Excel Tables and Structured References

Lecture 14 Array Formulas and Dynamic Arrays

Lecture 15 Concatenating Text Strings

Lecture 16 Text Functions

Lecture 17 Excel-Formatting Basics

Lecture 18 Drop-down Lists with Data Validation

Lecture 19 Conditional Summary Functions (SUMIFS AVERAGEIFS COUNTIFS)

Lecture 20 Excel-Basics Crash Course Homework (ASSIGNMENT)

Lecture 21 Excel-Basics Crash Course Homework (SOLUTION)

Section 3: Lookup and Reference Functions

Lecture 22 VLOOKUP Tutorial (Approximate Match)

Lecture 23 VLOOKUP Tutorial (Exact Match)

Lecture 24 MATCH Function Tutorial

Lecture 25 INDEX Function Tutorial

Lecture 26 MATCH and INDEX (Exact Match)

Lecture 27 MATCH and INDEX (Approximate Match)

Lecture 28 XLOOKUP Tutorial (Exact Match)

Lecture 29 XLOOKUP Tutorial (Approximate Match)

Lecture 30 Excel Lookups Practice (ASSIGNMENT)

Lecture 31 Excel Lookups Practice (SOLUTION)

Section 4: Excel Dates and Times (Basics)

Lecture 32 Overview of Excel Dates-and-Times System

Lecture 33 Entering Dates and Times in Excel Spreadsheets

Lecture 34 Regional Settings on Windows

Lecture 35 Regional Settings on Mac

Lecture 36 Playing with Random Date Values

Lecture 37 Playing with Random Time Values

Lecture 38 Playing with Random Date-and-Time Values

Lecture 39 Formatting Dates (Part I)

Lecture 40 Formatting Dates (Part II)

Lecture 41 Formatting Time Values

Lecture 42 Formatting Date-and-Time Values

Lecture 43 Formatting for Elapsed Time

Section 5: Excel Date and Time (Calculations)

Lecture 44 The DATE and the TIME Functions

Lecture 45 Date & Time Calculations

Lecture 46 The TEXT Function

Lecture 47 Separating Date from Time

Lecture 48 The WORKDAY and WORKDAY.INTL Functions

Lecture 49 The DATEDIF Function

Lecture 50 YEARFRAC Function

Lecture 51 The DAYS and DAYS360 Functions

Lecture 52 The NETWORKDAYS and NETWORKDAYS.INTL Functions

Lecture 53 The EDATE Function

Lecture 54 The CHOOSE Function

Lecture 55 Calculating the Fiscal Year of a Date

Lecture 56 Working with Dates and Times Stored as Text

Lecture 57 Introduction to the LET Function

Lecture 58 The LET Function Example (Parsing a String)

Lecture 59 The LET Function (Microsoft Support Example)

Lecture 60 The LET Function (Fiscal Year Calculation Example)

Section 6: Creating Custom Functions

Lecture 61 LAMBDA Function (Introduction)

Lecture 62 Custom AGE function with LAMBDA and DATEDIF

Lecture 63 Custom Fiscal Year Function with LAMBDA

Section 7: Excel What-if Analysis Tools

Lecture 64 Calculate Loan Payments (PMT Function Tutorial)

Lecture 65 Excel Goal-Seek Tutorial

Lecture 66 Excel Data-Table Tutorial

Lecture 67 Excel Scenario-Manager Tutorial

Section 8: Data Visualization With Excel Charts

Lecture 68 Excel Charts Basics

Lecture 69 Chart Elements

Lecture 70 The Select-Data Tool

Lecture 71 Combination Charts (PC)

Lecture 72 Combination Charts (Mac)

Lecture 73 Excel Charts Practice (ASSIGNMENT)

Lecture 74 Excel Charts Practice (PC SOLUTIONS)

Lecture 75 Excel Charts Practice (MAC SOLUTION)

Section 9: Traditional Excel Pivot Tables Tutorial

Lecture 76 Pivot Tables - Getting Started

Lecture 77 Pivot Table Design Tab Totals and Subtotals

Lecture 78 Pivot Tables Summarization Functions

Lecture 79 Pivot Charts

Lecture 80 Pivot Tables Show Values As Options

Lecture 81 Pivot Tables Refresh

Lecture 82 Pivot Tables - Grouping Dates

Lecture 83 Pivot Tables - Grouping Numbers

Lecture 84 Pivot Tables - Grouping Selection

Lecture 85 Pivot Tables - Filters Area

Lecture 86 Pivot Tables - Slicers and Timelines

Lecture 87 Pivot Tables - Practice Lab (ASSIGNMENT)

Lecture 88 Pivot Tables - Practice Lab (SOLUTION)

Section 10: Transform and Clean Data with Power Query

Lecture 89 Power Query Introduction 1

Lecture 90 Power Query Introduction 2

Lecture 91 Power Query-Remove Rows

Lecture 92 Power Query-Split Columns

Lecture 93 Power Query-Pivot and Unpivot

Lecture 94 Power Query-The Format Tool

Lecture 95 Power Query-The Extract Tool

Lecture 96 Power Query-Merge Columns

Lecture 97 Power Query-Custom Columns

Lecture 98 Power Query-Dates and Times

Lecture 99 Append Data (Part 1)

Lecture 100 Append Data (Part 2)

Lecture 101 Connect Power Query to a Folder

Lecture 102 Merge Queries (JOINS)

Lecture 103 Merge Queries (VLOOKUP)

Everyone who wants to master Excel and stand out from the crowd.,Savvy business and data analysts.,Banking and finance professionals.,Future, current and former MBA students.,Real-estate professionals.