Excel Formulas and Functions Quick Tips (2020)

Course details

The myriad formulas and functions in Excel are, in large part, 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 then digs into a variety of useful Excel functions. Learn 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. Common Excel Functions

06 - Count cells that contain numbers with COUNT
07 - Count nonblank cells with COUNTA
08 - Return a number of occurrences with COUNTIF
09 - Calculate a total with SUM
10 - Sum values that meet specific criteria with SUMIF
11 - Find the mean value with AVERAGE
12 - Test for valid data with IF
13 - Create complex tests with AND and OR
14 - Format numbers and dates with TEXT
15 - Extract characters with LEFT and RIGHT
16 - Extract substrings with MID
17 - Count characters with LEN
18 - Identify where a character occurs with FIND
19 - Remove leading spaces with TRIM
20 - Join strings with delimiters with TEXTJOIN
21 - Change text with SUBSTITUTE and REPLACE
22 - Find record values with MAX, MIN, SMALL, and LARGE
23 - Perform date calculations with TODAY
24 - Retrieve a specific value with XLOOKUP
25 - Use INDEX and MATCH to retrieve a value
26 - Include IFERROR to trap errors in formulas

Conclusion

27 - Learn more complex features of Excel