Excel Formulas and Functions Quick Tips

Course details

The myriad formulas and functions in Excel are a large part of what makes it such a uniquely powerful program. But for many of us, figuring out how to apply these formulas and functions to solve everyday problems can seem overwhelming. In this course, tech trainer and M365 expert Jess Stratton demystifies these powerful features, sharing bite-sized tips designed to help you work faster and more efficiently in Excel. Jess begins by covering the fundamental concepts you need to start mastering Excel formula and function basics, including creating formulas, referencing cells, and using key functions like SUM, AVERAGE, and IF. Along the way, discover essential tools for counting, manipulating text, and retrieving specific data with advanced lookup functions. Jess also covers helpful techniques to avoid errors, create dynamic reports using GROUPBY and PIVOTBY, and improve data accuracy with functions like TRIM and IFERROR. By the end of this course, you’ll be prepared to handle a wide range of data tasks efficiently and effectively in Excel.

Learning objectives
Identify fundamental Excel functions such as SUM, AVERAGE, COUNT, and IF, and explain their purpose.
Describe how to build and reference formulas, use named ranges, and apply logical operators like AND/OR in Excel.
Create and modify complex formulas, use text functions like LEFT, MID, and TEXTJOIN, and incorporate lookup functions like XLOOKUP and INDEX-MATCH.
Analyze data using aggregation functions like GROUPBY and PIVOTBY to summarize large datasets across multiple columns.
Evaluate the accuracy of Excel formulas by using IFERROR to trap errors and apply troubleshooting techniques like TRIM to remove invalid spaces from data.

Concepts

0. Introduction

01 - Calculate data with ease in Excel

1. Formula and Function Basics

02 - Build a formula in a cell with
03 - Create a named range
04 - Reference cells and worksheets
05 - Join text with an ampersand
06 - Follow along with Excel when creating functions

2. Identifying Values

07 - Calculate a total with SUM
08 - Sum values that meet specific criteria with SUMIF
09 - Find the mean value with AVERAGE
10 - Test for valid data with IF
11 - Create complex tests with AND and OR
12 - Find record values with MAX, MIN, SMALL, and LARGE
13 - Perform date calculations with TODAY()

3. Count Functions

14 - Count cells that contain numbers with COUNT
15 - Count nonblank cells with COUNTA
16 - Return a number of occurrences with COUNTIF
17 - Checking in on your course progress

4. Text Functions

18 - Format numbers and dates with TEXT
19 - Extract characters with LEFT AND RIGHT
20 - Extract substrings with MID
21 - Count characters with LEN
22 - Identify where a character occurs with FIND
23 - Join strings with delimiters with TEXTJOIN
24 - Change text with SUBSTITUTE and REPLACE

5. Index, Match, and Lookups

25 - Retrieve a specific value with XLOOKUP
26 - Use INDEX and MATCH to retrieve a value
27 - Use GROUPBY to aggregate data
28 - Use PIVOTBY to aggregate data with more columns

6. Avoiding Errors and Invalid Formulas

29 - Remove leading spaces with TRIM
30 - Include IFERROR to trap errors in formulas

Excel Formulas and Functions Quick Tips

Authors

Jess Stratton