Excel 2016: Introduction to Formulas and Functions

Introduction

• Welcome
• What you should know
• Using the exercise files

1. Introducing Excel 2016 Functions and Formulas

• Familiarize yourself with functions and formulas
• Create a formula
• Introduce arithmetic operators
• Use cell references in formulas
• Use absolute and relative references in formulas
• Copy and paste formulas and values
• Edit a formula
• Get help using Excel functions

2. Creating Basic Summary Formulas

• Create a SUM or AVERAGE formula
• Create an AutoSum formula
• Create MIN, MAX, MEDIAN, and MODE formulas
• Create formulas to count cells
• Create formulas to 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
• Create a dynamic named range
• Edit and delete named ranges
• Summarize list data by creating subtotals
• Group and outline list data
• Summarize data using an Excel table
• Refer to Excel table cells in formulas
• Find data using VLOOKUP formulas

4. Importing, Exporting, and Validating Data

• Import data into Excel
• Connect to an external data source
• Clean data imported into Excel
• Convert text to columns
• Create data validation rules
• Use lists to limit values entered into a cell

5. Auditing Worksheet Formulas

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

6. Performing What-If Analysis

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

Conclusion

• Next steps