Excel Formulas and Functions Quick Tips (2023)

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 users, figuring out how to apply these formulas and functions to solve everyday problems can seem overwhelming. In this course, staff instructor 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 get started with functions and formulas, such as how to programmatically reference cells located in other worksheets and combine cell data with other characters. She goes over identifying values, then digs into a variety of useful Excel functions. Jess highlights how to count cells that meet a specific set of criteria using COUNTIF, extract characters with LEFT and RIGHT, use INDEX and MATCH to retrieve a value, and much more.

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

2. Identifying Values

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

3. Count Functions

13 - Count cells that contain numbers with COUNT
14 - Count nonblank cells with COUNTA
15 - Return a number of occurrences with COUNTIF
16 - Check-in with Jess

4. Text Functions

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

5. Index, Match, and Lookups

24 - Retrieve a specific value with XLOOKUP
25 - Use INDEX and MATCH to retrieve a value

6. Avoiding Errors and Invalid Formulas

26 - Remove leading spaces with TRIM
27 - Include IFERROR to trap errors in formulas

Conclusion

28 - Learn more complex features of Excel