Excel 2013 Level 2 (Intermediate)

Course Length: 6hrs (1 Day)

Microsoft Excel uses simple formulas to calculate viable data and a wide variety of charts to display data in a professional, easy to read manner. Excel 2013 has added several new functions to its list of formulas, and has added a new series of recommended chart types so you don't have to guess. The Quick Analysis tool replaces many of these features so you can create formulas, charts, and trend-producing Spark lines quickly.

Learning Objectives:
In this course, you will create advanced formulas, utilize and maintain database records, and add graphic elements to your worksheets for greater visual impact. You will:
- Use VLOOKUP, HLOOKUP, PMT, FV, and IF Functions
- Understand Relative and Absolute Cell References
- Work with Named Ranges
- Use Advanced Functions and Resolve Formula Errors with Built In Tools
- Work with Recommended Charts
- Insert Sparklines
- Use the Quick Analysis Tool
- Work with Database Tables, using new Table Formats and Contextual Tabs
- Use Data Validation Rules and Database Functions

Target Student:
This course is for individuals who are familiar with the basics of Excel and who wish to learn mathematical functions that simplify the use of spreadsheets.

Course Outline:
Section 1: Advanced Formula Tasks

Understanding Relative and Absolute Cell References
Using Multiple Cell References
Using 3D References
Using Array Formulas

Section 2: Working with Named Ranges
Understanding Named Ranges
Defining Named Ranges
Editing Named Ranges
Deleting Named Ranges
Using Named Ranges in Formulas

Section 3: Using Advanced Functions
Using the PMT Function
Using VLOOKUP and HLOOKUP Functions
Using IF, AND, OR Functions

Section 4: Working with Charts
Charting Data
Creating Sparklines
Inserting Charts

Section 5: Resolving Formula Errors
Tracing Formula Precedents and Dependents
Showing Formulas
Evaluating Formulas
Setting Error Checking Options
Using Error Option Buttons
Running an Error Check

Section 6: Working with Tables
What is a Table?
Creating Tables
Resizing the Table
Choosing a Table Style
Clearing the Table
Using Sort and Filter

Section 7: Working with Records & Fields
What are Records and Fields?
Adding Fields by Inserting Columns
Adding Records by Inserting Rows
Deleting Records or Fields

Section 8: Using Excel as a Database
Filtering with Wildcard Characters
Validating Your Data
Data Validation Using Lists
Data Validation Using Formulas
Using Database Functions