Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Excel Mastery: Formulas and Functions
Rating: 4.6 out of 5(191 ratings)
3,199 students

Excel Mastery: Formulas and Functions

Learn all about formulas and functions. The ins and outs, the shortcuts, best practices and tips.
Created byTom Hinkle
Last updated 7/2024
English

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

Course content

2 sections • 13 lectures • 1h 42m total length

  • Workbook0:01

    Download this workbook to follow along with the videos and practice!

  • Formula Basics4:32

    This lesson will guide you through the very basics of entering formulas. It will also show several different ways to enter formulas so that you can choose the method that works best for you.

  • Function Builder13:46

    This lesson will show you how to utilize Excel's function builder tool. With over 500 functions, it's not realistic to learn EVERY single one, but the function builder will help you find and learn how to do every single one.

  • Absolute References9:24

    Absolute and Relative referencing are key concepts you need in order to start building bigger and more intricate Excel worksheets. This is all you ever need to know about them!

  • Named Ranges5:55

    This lesson shows you how to attach a meaning full name to a cell or a group of cells to make your workbook more readable and functional.

  • Aggregation: IF and IFS functions11:38

    This video teaches you all about aggregation functions. An aggregation is simply doing 'something' to a group of data. Summing up numbers in a column is an example of an aggregation. The IF and IFS functions just give us much greater flexibility in setting up the functions.

Requirements

  • Should be able to open and move around in Excel
  • Download the example file in each module
  • Be curious to learn and try new things!!!

Description

In this course, you'll gain comprehensive knowledge to become proficient in writing formulas and using functions in Excel.


Formulas Section 1:

  1. Basics: We'll start with the basics, focusing on writing simple formulas. This foundation is crucial for understanding more complex functions later on.

  2. 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.

  3. 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.

  4. 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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Who this course is for:

  • This course is for people that are new to Excel, or not quite sure how formulas work
  • If you don't know the difference between absolute and relative referencing
  • If you don't know how to do lookups (especially Xlookup)

Report abuse