Excel Professional

Excel Professional

Course Duration: 38 Days

Advanced Excel for Business covers the advanced applications of Excel used frequently by business professionals.

About the course

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

Register for Course