Intro to formulas
- Workbook
- Formula Basics
- Function Builder
- Absolute References
- Named Ranges
- Aggregation: IF and IFS functions
Formulas Part 2
- workbook
- Text functions
- Text to columns
- Remove Duplicates
- IF statement
- Lookups
- Index Match
What you'll learn
- Taught by a 2 time Excel MVP
- Learn the basics of entering formulas
- Learn how to use the Insert Function tool to understand every function in Excel
- Understand what absolute and relative referencing are
- Utilize named ranges to make your spreadsheets more efficient
- Learn lookup functions like: Xlookup, Vlookup, and Index-Match
- Use IF statements to add dynamic flexibility to your spreadsheet models
- Get a primer on the most frequently used text functions
Description
In this course, you'll gain comprehensive knowledge to become proficient in writing formulas and using functions in Excel.
Formulas Section 1:
Basics: We'll start with the basics, focusing on writing simple formulas. This foundation is crucial for understanding more complex functions later on.
Relative and Absolute Referencing: You'll learn these key concepts, which are essential for creating larger and more intricate spreadsheets. Knowing when to use relative versus absolute references can make your formulas more dynamic and accurate.
Named Ranges: Discover how named ranges can save time and make your spreadsheets easier to maintain. Named ranges not only enhance readability but also simplify formula creation and management.
Aggregate Functions: We’ll cover essential aggregate functions, including IF and IFS functions (SUMIF, COUNTIFS, etc.). These functions are the workhorses for analyzing groups of data in various ways, allowing you to perform conditional calculations efficiently.
Formulas Section 2:
Most Utilized Functions: Begin with an introduction to 11 of the most utilized functions in Excel, plus one additional function for fun. These functions are fundamental for data analysis and manipulation.
Text Splitting and Duplicate Removal: Learn how to split text from one column into many, a useful skill for cleaning and organizing data. Additionally, we’ll demonstrate how to remove duplicates, ensuring your datasets are accurate and streamlined.
IF Statement: Understand the versatile IF statement, which allows you to perform different actions based on specified criteria. This function is indispensable for creating logical tests and conditional outcomes in your spreadsheets.
Lookup Functions: Delve into crucial lookup functions such as XLOOKUP, VLOOKUP, and INDEXMATCH. These functions are essential for finding and retrieving data from large datasets. While XLOOKUP is the recommended go-to for its versatility and ease of use, VLOOKUP and INDEXMATCH remain important for ensuring compatibility with older spreadsheets.
By the end of this course, you'll have the confidence and skills to master formulas and functions in Excel. Whether you're managing personal finances, handling business data, or conducting complex analyses, you'll be equipped to tackle any challenge Excel throws your way.
The University of Sheffield
Other Courses
iOS SDK 7 Bootcamp
A 10 video-tutorial series that takes absolute beginners with no iOS development experience and teaches them the basics.
The Complete Guide to iOS 7 - iPad Edition
A Go-To resource for any iPad question you have.
Reaching Your Kickstarter Goals
A preview of my paid course. It contains lots of information that can be used to create a great Kickstarter campaign.
Kendo UI Scheduler
Learn how the Kendo UI Web Scheduler widget helps you create and manage tasks and appointments very easily.
About the instructors
- 4.43 Calificación
- 1729 Estudiantes
- 4 Cursos
Tom Hinkle
Master of Excel, SQL, Tableau (and other tools...)
Meet Tom: The IT Maestro Revolutionizing Financial Services and Beyond!
For over two decades, Tom has been a powerhouse in the IT and computer world, transforming end-user tools into masterpieces of efficiency and innovation. With expert command over Excel, SQL, Tableau, and other tools engineered groundbreaking solutions that revolutionize business processes
Tom's brilliance truly shines in the financial services sector, where he has dedicated over 20 years across operations, capital markets, finance and sales support. His classical programming background has been the cornerstone of creating impeccable and robust reporting solutions, making him an indispensable asset to every team he's part of.
But there's more to Tom than just his professional prowess. He's a vibrant soul who cherishes every moment with his wonderful family, whether they are skiing down snowy mountains or gliding across water. His passion for fitness keeps him energized and ready for new adventures.
Tom is not just a tech expert; he's a dynamic force of nature, seamlessly blending professional excellence with personal passions to inspire everyone around him. He firmly believes that anyone can master Excel and become a skilled analyst, and he'll be your biggest supporter as you embark on this exciting journey of discovery and growth.
Student feedback
4.55
Course Rating
Reviews
Katie Ryder
14-09-2024
Tom does a fantastic job of explaining the use of the functions and the different ways to use and access them. He goes through multiple examples of many different functions. The interactive workbook provided is very helpful in following along and practicing. I learned more than I expected to and it greatly exceeded my expectations!
Dudley Long
14-08-2024
this course filled in some of the gaps i was struggling with
Ari Myron
13-08-2024
Super helpful! Very clear teaching which was easy to follow.
Justin
10-08-2024
very good course highly recommended ..simple and fun to learn
Ibrahim Ahmed
10-08-2024
Nice review of some basic and important functions. The examples are good and explained very well. Five stars
Shimal Namrata Sharma
10-08-2024
easy to understand
George Krichauff
06-08-2024
Easy to follow well explained and increased my knowledge