Excel 2007: Introduction to Formulas and Functions

Introduction

• Welcome
• Using the exercise files

1. Introducing Excel 2007 Functions and Formulas

• Familiarizing yourself with functions and formulas
• Creating a formula
• Introducing arithmetic operators
• Using cell references in formulas
• Copying and pasting formulas and values
• Editing a formula
• Getting help using Excel functions

2. Creating Basic Summary Formulas

• Creating a SUM or AVERAGE formula
• Creating an AutoSum formula
• Creating MIN, MAX, MEDIAN, and MODE formulas
• Creating formulas to count cells
• Creating formulas to summarize cells conditionally
• Summarizing data on the Status bar
• Rounding cell values up and down

3. Sorting and Filtering PivotTable Data

• Summarizing data using named ranges
• Creating a dynamic named range
• Editing and deleting named ranges
• Summarizing list data by creating subtotals
• Grouping and outlining list data
• Summarizing data using an Excel table
• Referring to Excel table cells in formulas
• Finding data using VLOOKUP and HLOOKUP formulas

4. Preparing Data for Analysis in Excel

• Importing data into Excel
• Connecting to an external data source
• Cleaning up data imported into Excel
• Creating data validation rules
• Using lists to limit values entered into a cell

5. Auditing Worksheet Formulas

• Managing Excel formula error indicators
• Identifying and tracing errors
• Tracing formula precedents and dependents
• Evaluating Excel formulas step by step
• Watching cell values

6. Performing What-If Analysis

• Creating scenarios and applying them to an Excel worksheet
• Editing, deleting, and summarizing scenarios
• Creating a single-input data table
• Creating a double-input data table
• Finding target values using Goal Seek

Conclusion

• Goodbye