Advanced Excel

Advanced Excel for Business covers the advanced applications of Excel used frequently by business professionals. This course is perfect for those with strong working knowledge and practice in Excel looking to apply more complex Excel features. In this advanced course, you will learn Goal Seek, Data Tables, advanced functions, and basic macros. You will build upon intermediate-level concepts, making your lookup functions more flexible, taking Pivot Tables to the next level, and facilitating workflow with cell management techniques and Hot Keys. In this hands-on course you will work on real-world examples, reinforcing the concepts with practice throughout the class, as well as a cumulative project at the end.

Syllabus:
Cell Management

1) Advanced Cell Locking
Create powerful formulas by locking either the column or the row
2) Hot Keys
Transform the ribbon into a visual listing of pre-assigned shortcuts
3) Windows
Techniques and keyboard shortcuts to allow editing of active windows
4) Cell Auditing
Observe the relationship between formulas and cells
Special Formatting

5) Date functions
Calculate dates with a variety of functions
6) Conditional Formatting-Formulas
Create custom rules for Conditional Formatting with formulas
Advanced Functions

7) Nested If statements
Nested "IF" statements allow for more than just two possibilities in a single cell
8) If statements with And/Or
Expand the functionality of the IF function by adding an "AND" or an "OR" criteria into the logical test
What If Analysis

9) Goal Seek
Find the desired result by adjusting an input value
10) Data Tables
Data Tables show the range of effects of one or two different variables on a formula
Advanced Analytical Tools

11) Data Consolidation
Summarize data from separate ranges and consolidate into a specified output range
12) Conditional SumProduct
Use SumProduct with conditions to exclude data that does not meet certain criteria
13) Pivot Table-Calculations
Create calculated rows or columns in a Pivot Table that go beyond the source data
14) Pivot Charts
Pivot Charts are dynamic, graphical representations of Pivot Table data that work in tandem with Pivot Tables
Advanced Database Functions

15) MATCH function
Return the relative position (column or row number) of a lookup value
16) VLOOKUP-MATCH
Create a more accurate VLookUp by enhancing the determination of the Column Index number
17) INDEX-MATCH
Efficiently returns a value or reference from a cell at the intersecton of the row and column
18) INDEX-Double MATCH
Use a second Match function to create a powerful, two-way lookup tool
Introduction to Macros

19) Recording Macros
Record Macros that involve formatting and calculations
20) Relative Macros
Record relative reference macros for ad hoc reporting

Time Saving Tools

21) ASAP Utilities

Business Intelligence Tools

22) Microsoft Power BI