Excel Level III: Advanced

Advanced Navigation

Advanced Navigation

• Advanced navigation techniques

Fill Review

• Review of Autofill conventions and techniques

Cell Management

Advanced Cell Locking

• Create powerful formulas by locking either the column or the row

Hot Keys

• Transform the ribbon into a visual listing of pre-assigned shortcuts

Cell Auditing

• Observe the relationship between formulas and cells

Go To Special

• Quickly select cells that meet certain criteria

Special Formatting

Conditional Formatting-Formulas

• Create custom rules for Conditional Formatting with formulas

Date Functions

• Calculate dates with a variety of functions

Custom Number Formats

• Customize number formats to meet specific requirements

Advanced Functions

Nested IF statements

• Nested "IF" statements allow for more than just two possibilities in a single cell

IF statements with AND/OR

• Expand the functionality of the IF function by adding an AND / OR criteria

What If Analysis

Goal Seek

• Find the desired result by adjusting an input value

Data Tables

• Data Tables show the range of effects of one or two different variables on a formula

Advanced Analytical Tools

Calculation Options

• Minimize volatility by changing calculation options

Conditional SumProduct

• Use SumProduct with conditions to exclude data that does not meet certain criteria

Pivot Table-Base Fields & Sets

• Analyze data in a Pivot Table with increased granularity by defining base fields and sets

Pivot Table-Calculations

• Create calculated rows or columns in a Pivot Table that go beyond the source data

Pivot Charts

• Create dynamic, graphical representations of Pivot Table data

Advanced Database Functions

XMATCH function

• Return the relative position (column or row number) of a lookup value

INDEX-MATCH

• Efficiently return a value or reference from a cell at the intersection of the row and column

INDEX-Double MATCH

• Use a second Match function to create a powerful, two-way lookup tool

Introduction to Macros

Recording Macros

• Record macros that involve formatting and calculations

Dynamic Arrays

Dynamic Arrays

• Use formulas that can return arrays of variable size

End of Class Projects

Projects

• End of class project to review key concepts from the class