Excel Training Class - Advanced

Excel Training Class - Advanced

This advanced Excel course teaches functions necessary for working with complext data sets and performing advanced analysis. Take Pivot Tables to the next level, and learn to automate tasks using macros. You’ll also learn advanced Excel functions and formatting, advanced analytical tools, and close out the course with a comprehensive project. Attend this advanced Excel training learn:

  • Advanced Cell Locking: Learn about mixed cell referencing, which locks either a row or column. This is especially useful when dragging formulas across multiple rows and columns.
  • Cell Auditing: Review your formulas for errors, and trace dependents and precedents.
  • Conditional Formatting with Formulas: Learn how to make conditional formatting more powerful in Excel by using formulas. For example, format cells that are greater than the value in cell in D55 with a light red fill.
  • Nested IF Statements: Basic IF statements in Excel restrict you to two options: TRUE or FALSE. With nested IFs, you can put an IF statement inside another IF statement to allow for multiple options. For example, if A1=2, then show “Low”, if A1=4 then show “Medium”, if A1=6 then show “High.”
  • IF Statements with AND/OR: This allows you to write logical statements in Excel that meet one of multiple conditions or meet multiple conditions. The AND function allows us to specify that two or more conditions are met. The OR function says that one of two or more conditions need to be met.
  • Goal Seek: This powerful Excel tool allows the user to find a specific value that creates the desired output. For example, if we have a set of grades and want to know what score we need to get on the final exam to reach a certain grade level, then we can use the goal seek tool to solve.
  • Data Tables: With Data Tables, you can create sensitivity analysis and see how changing one or two inputs impact the specified output. For example, if we want to see how various investment amounts will grow at various rates, we can use an Excel Data Table.
  • Pivot Charts: Once data is summarized in a Pivot Table, we can use Excel’s Pivot Charts to seamlessly create a visualization based on our selected data.
  • VLOOKUP-MATCH: Make the Excel VLOOKUP function more dynamic by adding the MATCH function to find the column input
  • INDEX-MATCH: A more flexible lookup function, allowing users to “lookup backward” which the VLOOKUP does not.
  • INDEX- Double MATCH: Use two MATCH’s in the INDEX function to create the ultimate dynamic Excel formula to extract data from tables.
  • Recording Macros: Record macros in Excel to automate a series repeated actions. For example, if every time you receive a specific report, you need to move certain columns, filter based on a specific criteria, and then create a chart based on the data, you can record a macro to automate this workflow.
  • Hot Keys: Work with Excel without using the mouse by activating Hot Keys. Any command in Excel can be reached with a series of keyboard strokes, which can substantially expedite workflow. For example, center align text with ALT + H + AC.

Excel Training Class - Advanced Course Dates

All classes in Boston, New York City, and Philadelphia start at 9:30 a.m. and end at 4:30 p.m.
and are led by a live instructor in the same classroom with you.
All online classes are led by a live instructor and run from 10:00 a.m. to 5:00 p.m. Eastern Time.

Course Title Course Dates Course Location Fee Register
Excel Training Class - Advanced

Boston

American Graphics Institute
Boston
American Graphics Institute
AGI Training Boston
150 Presidential Way
Boston - Woburn, MA 01801
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Online

American Graphics Institute
Online
Live instructor-led class online
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Boston

American Graphics Institute
Boston
American Graphics Institute
AGI Training Boston
150 Presidential Way
Boston - Woburn, MA 01801
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Online

American Graphics Institute
Online
Live instructor-led class online
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Boston

American Graphics Institute
Boston
American Graphics Institute
AGI Training Boston
150 Presidential Way
Boston - Woburn, MA 01801
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Online

American Graphics Institute
Online
Live instructor-led class online
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Boston

American Graphics Institute
Boston
American Graphics Institute
AGI Training Boston
150 Presidential Way
Boston - Woburn, MA 01801
2020-03-31 13:13:23 $249.00
Excel Training Class - Advanced

Online

American Graphics Institute
Online
Live instructor-led class online
2020-03-31 13:13:23 $249.00

Advanced Excel Training Class Topics Covered

The Advanced Microsoft Excel training course covers the following topics:

Excel Cell Management

  • Cell Locking: Learn to create powerful formulas by locking columns or rows.
  • Cell Auditing: Understand the relationship between formulas and cells.
  • Windows: Discover techniques for editing active windows.
  • Hot Keys: Learn to use the ribbon as a custom set of personalized shortcuts.

Using Special Formatting in Excel

  • Date functions: Learn to calculate dates using several functions
  • Conditional Formatting of Formulas: Discover how to use conditional formatting of formulas using custom rules.

Understanding Advanced Excel Functions

  • Nested IF statements: Learn to use nested IF statements to create multiple options for populating cells
  • IF statements using AND/OR: Learn additional IF functions available by using AND / OR

Using Excel for What If Analysis

  • Goal Seek: Adjust input values to find desired results
  • Data Tables: Display the range of effects for one or two variables on a formula using Data Tables.

Understanding Advanced Analytical Tools in Excel

  • Data Consolidation: Learn to summarize data from separate ranges, consolidating it to a specific output range
  • Conditional SumProduct: Learn how using SumProduct with conditions excludes data that fails to meet specific criteria
  • Pivot Table-Calculations: Learn to create calculated columns and rows using a Pivot Table to expand beyond source data
  • Pivot Charts: Learn to build dynamic visual representations of data from pivot tables

Using Excel’s Advanced Database Functions

  • MATCH function: Learn to return the column or row number of a lookup value
  • VLOOKUP-MATCH: Create more accurate VLookUps through enhancing the determination of the Column Index number
  • INDEX-MATCH: Efficiently return values and references from cells at the intersection of the row and column
  • INDEX-Double MATCH: Learn to use a second Match function to create powerful two-way lookups

Learning Macros in Excel

  • Recording Macros: Learn to record Macros involving formatting and calculations
  • Relative Macros: Understand how to create relative reference macros used in ad-hoc reporting

 

 

Intermediate Excel Course prerequisites

This course requires knowledge of Excel equivalent to the topics in our Introductory Excel Course, including knowledge of basic formulas and functions, charts, formatting, and absolute cell references. A computer with Excel is provided for use during the course.

Course materials

You will receive a comprehensive Excel course manual for this class.

Available Delivery Methods For This Class

CLASSROOM
LIVE ONLINE
PRIVATE
MY LOCATION