Microsoft Excel - Advanced Formulas & Functions With Genai

Microsoft Excel - Advanced Formulas & Functions With Genai
Published 10/2024
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 1.50 GB | Duration: 2h 25m

Build advanced and Complex formulas in Excel with 300+ business situations and build your logical capabilities with AI

What you'll learn

Master Excel's lookup functions to seamlessly combine data from different sources, making your work faster and smarter.

Transform messy data into insights by learning powerful cleaning and analysis tools that simplify even the most complex datasets.

Create custom, formula-driven formats that make your data pop, turning dull sheets into dynamic, readable visuals.

Automate everyday tasks in Excel so you can spend less time on repetitive work and more time on what really matters—no coding required!

Requirements

Basic Knowledge of Excel

Description

Ready to take control of Excel and make it work for you? Whether you're just starting or aiming to become an Excel expert, this course is designed to help you master the formulas and functions that transform Excel from a simple spreadsheet into a powerful tool for data analysis.We focus on more than just showing you what each formula does. We dive deep into why these formulas are so valuable and how you can apply them in real-life situations. You'll learn through hands-on examples and practical exercises that bring Excel's power to life.By the end of the course, you'll be creating formulas that allow you to:Build dynamic dashboards to analyze and visualize your dataCustomize your own Excel formatting rules using formulasCombine data from different sources with XLOOKUP, INDEX, and MATCHWork with dates, times, text, and arrays to handle more complex dataClean and prep your data quickly and efficientlyAutomate routine tasks, saving time and effort (no need for VBA!)Pull live data like weather reports or stock prices straight into ExcelWhat You'll Learn:Lookup and reference functions for combining dataTools for cleaning and analyzing dataHow to create custom, formula-based formattingDate, time, and text functions for better data managementUsing dynamic arrays for more powerful calculationsTechniques to pull in live data from the webThis course isn't just about memorizing formulas – it's about teaching you to think like Excel. You'll gain practical skills that you can use every day, making your work smarter and more efficient.Join now and let's take your Excel skills to the next level!

Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: Fundamentals of Excel Functions and Formulas

Lecture 2 Environment and Inserting our First Formula

Lecture 3 Inserting our First Function

Lecture 4 Formula Referencing - Relative Referencing

Lecture 5 Formula Referencing - Fixed Referencing

Lecture 6 Named Ranges

Section 3: Basic Aggregation formulas

Lecture 7 SUM and Practicing Sum

Lecture 8 Practicing SUM on business use cases

Lecture 9 Introduction to AVERAGE

Lecture 10 Practicing AVERAGE on business use cases

Lecture 11 Introduction to COUNT and COUNTA

Lecture 12 Practicing COUNT and COUNTA on business use cases

Lecture 13 Limitations of COUNT and COUNTA functions

Lecture 14 Introduction to MAX and MIN functions

Lecture 15 Practicing MAX and MIN on business use cases

Section 4: Office 365 Functions

Lecture 16 Unique Function and its use cases

Lecture 17 TOCOL function and its use cases

Lecture 18 TOROW Function and its use cases

Section 5: Advanced Logical Aggregations with IF, IFS and IFS family of formulas

Lecture 19 Introduction to SUMIF function

Lecture 20 SUMIF with WILDCARDS

Lecture 21 Introduction to AVERAGEIF formula

Lecture 22 Introduction to IFS and SUMIFS Functions

Lecture 23 SUMIFS with WILDCARDS

Lecture 24 AVERAGEIFS and COUNTIFS

Section 6: Basic DATE and TIME Functions

Lecture 25 Introduction to TODAY function

Lecture 26 Introduction to DAY function

Lecture 27 Introduction to MONTH function

Lecture 28 Introduction to YEAR function

Lecture 29 Introduction to TIME functions

Lecture 30 Introduction to DAYS function

Lecture 31 Introduction to NETWORKDAYS

Lecture 32 Introduction to NETWORKDAYS INTL

Section 7: Advanced TEXT functions

Lecture 33 Extracting Numbers from Date information using TEXT function

Lecture 34 Extract month info using TEXT function

Lecture 35 Extracting AM or PM from timestamp using TEXT function

Lecture 36 Some more basic use cases of TEXT function

Lecture 37 Another Advanced text function - SUBSTITUTE

Lecture 38 Another advanced text function - REPLACE

Lecture 39 SWITCH function for managing dynamic logical changes

Section 8: Doing analysis of orders table with Excel functions and formulas + GenAI

Lecture 40 Dataset for Practice Download

Lecture 41 Find out many orders were placed each hour

Lecture 42 Find out the distribution of order channels by hour

Section 9: Conclusion

Lecture 43 Conclusion

Beginners eager to master Excel's core formulas and take control of their data effortlessly.,Professionals looking to sharpen their Excel skills for real-life problem-solving at work.,Entrepreneurs needing dynamic dashboards to analyze and visualize their business data quickly,Analysts who want to streamline data cleaning and automate repetitive tasks without VBA.,Learners ready to make Excel work smarter for them by integrating live data like stock prices.