Excel 2013 Advanced

This course builds off of the fundamentals and intermediate Excel courses to help you get the most of your Excel experience. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips.

The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.

Upon successful completion of this course, you will be able to:

Perform advanced data analysis and calculations, and automate workbook functionality.

Course Outline:

Module 1: Advanced Worksheet Setup

• Working With Named Ranges
• Naming A Range
• Selecting A Named Range
• Editing The Name Definitions
• Using Named Ranges In A Formula
• Validating Entries In A Cell
• Documenting Your Workbook With Comments
• Adding And Editing Comments
• Displaying And Printing Comments
• Flagging Entries With Conditional Formatting
• Find Cells That Have Conditional Formats
• Change, Add Or Remove Conditional Formats

Module 2: Advanced Calculation Techniques

• Function Overview
• Placing Functions On The Worksheet
• Utilizing More Advanced Functions
• Pmt
• Round
• Vlookup
• Countif
• If
• Nested Functions
• The And/Or Clauses
• Text Manipulation Functions

Module 3: Troubleshooting Formulas

• Using Formula Auditing Tools
• Tracing Precedents And Dependents
• Evaluating Formulas And Monitoring Cell Values

Module 4: Working With Pivot Tables

• Pivot Tables
• Rearranging Fields
• The Report Filter
• The Slicer
• Grouping Column Or Row Headings
• Changing The Display Of Data
• Showing Detail
• Show Report Filter Pages
• Refreshing The Pivot table
• Pivot table Options
• Pivot Charts
• Modifying The Data Displayed In A Pivot Chart Report

Module 5: An Introduction To Macros

• Overview Of Macros
• Planning And Rehearsing The Macro
• Recording A Macro
• Running The Macros
• Opening A File Containing A Macro
• Running A Macro From A Shortcut Key
• Changing A Macro’s Options

Module 6: Protecting A Worksheet

• Overview Of Worksheet Or Workbook Protection

Module 7: Forecasting With Excel

• Working With Scenarios
• Using The Goal Seek Tool
• Working With The Solver
• Appendix: Importing Data From Outside Excel
• Importing A Text File
• Copying External Data Into