Excel: Introduction to Formulas and Functions

Course details

To unlock the power of Excel, you need to use formulas and functions. These calculation tools help you bring information to the surface and make better decisions. This course shows beginner-level users how to summarize and analyze data with these powerful data analysis features. Instructor Curt Frye explains how to create formulas with mathematical operations such as addition, subtraction, multiplication, and division, and how to use cell references in your formulas. He explains how to summarize data with functions like SUM and AVERAGE, and work with data in named ranges and Excel tables. Curt also covers procedures to ensure data integrity, such as data validation, and demonstrates auditing techniques to ensure formulas are correct.

Note: This course was recorded on Excel for Microsoft 365. Most of the course contents will also be useful to those working with the most recent standalone versions of Office, including Office 2021.

Learning objectives
Define “formula.”
Identify three comparison operators.
Name two keys used to end editing mode in a cell.
Recall the key used to open the Help pane in Excel.
Recognize a valid time range in the subtotal dialog box.
Determine which formula would calculate the sum of a sales column in a sales data table.
Identify the file type that can be opened directly into Excel.
Explain how to enable flash fill.
Summarize the purpose of a precedent in a formula.

Concepts

0. Introduction

01 - Create powerful Excel formulas

1. Introducing Excel Formulas and Functions

02 - Familiarize yourself with Excel formulas and functions
03 - Create your Excel formulas
04 - Describe arithmetic operators
05 - Use cell references in formulas
06 - Use absolute and relative references in formulas
07 - Copy and paste formulas and values
08 - Edit a formula

2. Creating Summary Functions

09 - Create a SUM or AVERAGE formula
10 - Create an AutoSum formula
11 - Create formulas to count cells
12 - Use IF and IFS formulas to process data
13 - Summarize cells conditionally
14 - Summarize data on the status bar
15 - Round cell values up and down

3. Managing Named Ranges and Excel Tables

16 - Summarize data using named ranges
17 - Edit and delete named ranges
18 - Summarize data using an Excel table
19 - Refer to Excel table cells in formulas
20 - Sort and filter Excel table data

4. Using Dynamic Array Formulas

21 - Create and refer to dynamic array formulas
22 - Find all distinct values in a range using UNIQUE
23 - Change the order of data using SORT
24 - Limit the data in formula output using FILTER

5. Managing Data in Excel

25 - Manage data using text functions
26 - Separate data using Flash Fill
27 - Create data validation rules
28 - Restrict data entry using lists
29 - Find data using VLOOKUP formulas
30 - Look up data using XLOOKUP formulas

6. Auditing Worksheet Formulas

31 - Trace formula precedents and dependents
32 - Evaluate Excel formulas step by step
33 - Identify and trace errors
34 - Manage Excel formula error indicators

Conclusion

35 - Next steps