Excel: Introduction to Formulas and Functions (2018)

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. Excel: Introduction to Formulas and Functions shows beginner-level users how to summarize and analyze data with these powerful data analysis features. Instructor Curt Frye shows 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. Plus, get an introduction to the What-If Analysis toolset, in an example that combines scenarios, data tables, and Goal Seek to generate accurate revenue projections.

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

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 - Introduction to formulas and functions
02 - What you should know

1. Introducing Excel 2019 Formulas and Functions

03 - Familiarize yourself with functions and formulas
04 - Create a formula
05 - Introduce arithmetic operators
06 - Use cell references in formulas
07 - Use absolute and relative references in formulas
08 - Copy and paste formulas and values
09 - Edit a formula
10 - Get help using Excel functions

2. Creating Basic Summary Functions

11 - Create a SUM or AVERAGE formula
12 - Create an AutoSum formula
13 - Create MIN, MAX, MEDIAN, and MODE formulas
14 - Create formulas to count cells
15 - Create formulas to summarize cells conditionally
16 - Summarize data using conditional functions
17 - Summarize data on the status bar
18 - Round cell values up and down

3. Managing Named Ranges and Excel Tables

19 - Summarize data using named ranges
20 - Create a dynamic named range
21 - Edit and delete named ranges
22 - Summarize list data by creating subtotals
23 - Group and outline list data
24 - Summarize data using an Excel table
25 - Refer to Excel table cells in formulas
26 - Find data using VLOOKUP formulas

4. Preparing Data for Analysis in Excel

27 - Import data into Excel
28 - Connect to an external data source
29 - Clean data imported into Excel
30 - Convert text to columns
31 - Create data validation rules
32 - Use lists to limit values entered into a cell

5. Auditing Worksheet Formulas

33 - Manage Excel formula error indicators
34 - Trace formula precedents and dependents
35 - Identify and trace errors
36 - Evaluate Excel formulas step by step
37 - Watch cell values

6. Performing What-If Analysis

38 - Create and apply scenarios to an Excel worksheet
39 - Edit, delete, and summarize scenarios
40 - Create a single-input data table
41 - Create a two-input data table
42 - Find target values using Goal Seek

Conclusion

43 - Further information