Become an Expert in Excel & Google Sheet Formula & Functions

Name: Become an Expert in Excel & Google Sheet Formula & Functions
Rating: 4.4 (79 reviews)

Master key formulas and functions in Excel and Google Sheets for better data insights and faster workflows.

Created byClick Learning

Last updated 9/2024

English

What you'll learn

Understanding Excel & Google sheet Formulas & Functions
User Interface Overview
Basic Arithmetic Operations
Using Cell References: Relative, Absolute, Mixed
SUM, AVERAGE, MIN, MAX
COUNT and COUNTA Functions
CONCATENATE and TEXTJOIN Functions
Formatting Dates and Times
VLOOKUP and HLOOKUP Functions
INDEX and MATCH Functions
Using Named Ranges for Enhanced Readability
Statistical Analysis with Functions: MEDIAN, MODE, STDEV, etc.
Analyzing Data Distributions and Variability
Nested Functions and Formula Nesting
Array Formulas for Complex Calculations
Financial Functions: PV, FV, NPV, IRR
Summarizing Data with PivotTables
Filtering and Analyzing Data with Slicers
Introduction to Power Query for Data Transformation
Integrating Power Query Results with Excel Functions
Error Checking and Correction Techniques
Utilizing Keyboard Shortcuts for Formula Writing

Course content

3 sections • 40 lectures • 3h 44m total length

Understanding Formulas and Functions5:14

Basic Arithmetic Operations4:42
Writing and Editing Formulas3:40
Using Cell References: Relative, Absolute, Mixed4:18
SUM, AVERAGE, MIN, MAX4:03
COUNT and COUNTA Functions3:05
IF Function and Conditional Logic7:33
CONCATENATE and TEXTJOIN Functions4:59
LEFT, RIGHT, MID Functions7:37
DATE, TODAY, and NOW Functions3:05
Formatting Dates and Times3:24
VLOOKUP and HLOOKUP Functions7:15
INDEX and MATCH Functions6:02
Using Named Ranges for Enhanced Readability7:10
SUMIF, SUMIFS, COUNTIF, COUNTIFS7:13
Statistical Analysis with Functions: MEDIAN, MODE, STDEV, etc.6:07
Analyzing Data Distributions and Variability4:14
Nested Functions and Formula Nesting4:41
Array Formulas for Complex Calculations5:48
Financial Functions: PV, FV, NPV, IRR8:31
Master Excel financial functions like present value, future value, net present value, and internal rate of return to analyze investments and make informed financial decisions.
Summarizing Data with PivotTables4:56
Filtering and Analyzing Data with Slicers6:57
Introduction to Power Query for Data Transformation4:49
Integrating Power Query Results with Excel Functions6:18
Learn to integrate Power Query results with Excel functions to perform advanced analysis, using sumif formulas to calculate totals by category after loading cleaned data into Excel.
Formula Auditing and Error Handling7:15
Using Trace Precedents and Dependents4:46
Error Checking and Correction Techniques3:01
Optimizing Formulas for Performance9:23
Learn to speed up large Excel workbooks by optimizing formulas: choose appropriate functions, limit volatile calculations, use absolute references, and named ranges for faster recalculation.
Using Excel's Formula AutoComplete Feature4:52
Utilizing Keyboard Shortcuts for Formula Writing7:32
Master essential Excel formula shortcuts for faster writing and copying, including starting formulas with =, using autocomplete, and handling relative and absolute references with dollar signs.
Customizing Excel's Calculation Options5:42

Math Functions (SUM, AVERAGE, MAX, MIN, COUNT )5:29
Logical Functions (AND, OR IF)5:26
Text Functions (LOWER, UPPER, CONCATENATE, PROPER)5:20
Learn to apply text functions in Google Sheets—lower, upper, proper, and concatenate—to standardize case, join text with spaces, and format names, titles, and addresses efficiently.
Left and Right (LEFT, RIGHT)3:40
Master the left and right functions in Google Sheets to extract characters from the start or end of text, using product codes as examples.
Line, Area, Bar & Pie Charts in Google Sheets4:54
VLOOKUP & HLOOKUP5:28
XLOOKUP7:41
Data Import (Web and HTML Table)6:28
Macros5:39
Learn to automate repetitive tasks in Google Sheets by recording macros, applying formatting, assigning shortcuts, running and managing macros, and testing in a copy to prevent data loss.

Requirements

No Prior Excel and Google Sheets Knowledge is Needed.

Description

Unlock the full potential of your spreadsheets with our comprehensive course, Become an Expert in Excel & Google Sheets Formula & Functions. Designed for both beginners and seasoned professionals, this course will take you from basic to advanced proficiency in using formulas and functions across both Microsoft Excel and Google Sheets.

What You'll Learn:

Foundational Skills: Start with the basics of spreadsheet formulas and functions. Understand how to navigate Excel and Google Sheets, and learn the fundamental principles that drive their powerful formula systems.
Essential Functions: Dive deep into essential functions such as SUM, AVERAGE, VLOOKUP, HLOOKUP, INDEX, and MATCH. Learn how to use these functions to analyze and manipulate data effectively.
Advanced Formulas: Master complex formulas and functions including nested formulas, array formulas, and dynamic ranges. Discover how to tackle intricate data analysis and solve challenging problems with ease.
Data Analysis & Visualization: Learn to create insightful data analyses using conditional formatting, pivot tables, and charts. Understand how to present data visually to make informed decisions.
Real-World Applications: Apply your skills to real-world scenarios with practical exercises and projects. From financial modeling and business analysis to personal budgeting and data management, see how formulas and functions can solve everyday problems.
Excel & Google Sheets Differences: Understand the unique features and capabilities of Excel and Google Sheets. Learn how to leverage the strengths of each platform and seamlessly transition between them.
Troubleshooting & Best Practices: Develop strategies for troubleshooting formula errors and ensuring formula accuracy. Adopt best practices for creating clean, efficient, and maintainable formulas.

Course Features:

Interactive Lessons: Engage with video lectures, hands-on exercises, and real-world projects designed to reinforce learning and build confidence.
Expert Guidance: Benefit from the expertise of experienced instructors who provide clear explanations, practical insights, and personalized feedback.

End of this course, you will have a thorough understanding of how to utilize Excel and Google Sheets formulas and functions to streamline your workflow, analyze data with precision, and make informed decisions. Whether you're looking to boost your career, improve your business operations, or simply gain valuable skills, this course is your gateway to becoming an expert in spreadsheet formulas and functions.

Enroll today and transform your spreadsheet skills from basic to expert!

Who this course is for:

Anyone interested in becoming a proficient Excel and Google Sheet user.

Become an Expert in Excel & Google Sheet Formula & Functions

What you'll learn

Explore related topics

Course content

Introduction to Microsoft Excel & Google Sheet1 lecture • 5min

Microsoft Excel30 lectures • 2hr 49min

Google Sheet9 lectures • 50min

Requirements

Description

Who this course is for: