Share Description
In this course, you will :
- demonstrates how to create impressive spreadsheets using the application's built-in features.
- demonstrates how to summarise values in groups of cells, create conditional formulas with IF and other related functions, and use validation rules to improve data entry accuracy.
- demonstrates how to use the VLOOKUP and HLOOKUP functions to search for data within a list or table. This course includes exercise files.
Syllabus :
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
