Excel: Introduction to Formulas and Functions

Introduction

• Create powerful Excel formulas

1. Introducing Excel Formulas and Functions

• Familiarize yourself with Excel formulas and functions
• Create your Excel formulas
• Describe arithmetic operators
• Use cell references in formulas
• Use absolute and relative references in formulas
• Copy and paste formulas and values
• Edit a formula

2. Creating Summary Functions

• Create a SUM or AVERAGE formula
• Create an AutoSum formula
• Create formulas to count cells
• Use IF and IFS formulas to process data
• Summarize cells conditionally
• Summarize data on the status bar
• Round cell values up and down

3. Managing Named Ranges and Excel Tables

• Summarize data using named ranges
• Edit and delete named ranges
• Summarize data using an Excel table
• Refer to Excel table cells in formulas
• Sort and filter Excel table data

4. Using Dynamic Array Formulas

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

5. Managing Data in Excel

• Manage data using text functions
• Separate data using Flash Fill
• Create data validation rules
• Restrict data entry using lists
• Find data using VLOOKUP formulas
• Look up data using XLOOKUP formulas

6. Auditing Worksheet Formulas

• Trace formula precedents and dependents
• Evaluate Excel formulas step by step
• Manage Excel formula error indicators
• Identify and trace errors

Conclusion

• Next steps