Advanced Excel Course

Duration: 1 day

Description

This is a 1 day advanced Excel course that covers the top-end functions of Excel. It is aimed at experienced Excel users who work with large volumes of data and require methods to better handle and maintain this data.

Topics

What’s new in the latest version(s) of Excel?

      • An overview of the newest features in Excel

Pivot Tables & Pivot Charts

      • Creating a pivot table
      • Exploring pivot-table components
      • Filtering data using report, column and row fields
      • Using the Slicer tool (Excel 2010 onwards)
      • Using the Timeline filter (2013 onwards)
      • Moving, adding and removing data from the pivot table.
      • Updating the original data and refreshing the pivot-table
      • Changing the data function
      • Renaming a field
      • Using the Show Pages tool
      • Showing how a data total was generated
      • Formatting a pivot-table
      • Exploring the pivot-table options
      • Collapsing and expanding data
      • Using calculated fields and calculated items
      • Grouping text items, dates and numerical ranges
      • Sorting data manually and automatically
      • Creating a custom PivotChart Report

Macros

      • ​What is a macro and why would you use one?
      • Recording a macro
      • Running a macro from the Quick Access toolbar or a graphic
      • Exploring macro code using VBA
      • Going a little deeper with VBA fundamentals
      • Debugging VBA code using stop points and step-through

Advanced Formula Functions

      • Lookup functions - VLOOKUP, HLOOKUP, LOOKUP, MATCH and INDEX and how to use in combination
      • TEXT functions - UPPER, LOWER, PROPER, LEFT, MID, RIGHT, SEARCH, LEN, CONCATENATE and TEXT and how to combine them to be useful
      • Financial functions (if time or if requested)
      • Database functions (if time or if requested)
      • Advanced date and time functions (if time or if requested)

Custom Formatting

      • Beyond the standard Excel formats
      • Creating custom formats for numbers, currency and dates
      • How to set formats using a formula

Consolidating worksheet data from different places

      • Consolidating data by position
      • Consolidating data by category

"What If?" Analysis

      • Using Goal Seek to set a specific answer and ask Excel  to calculate what the input value(s) need to be
      • Using Solver to find the best solution based on a set of given constraints
      • Adding constraints
      • Keeping a solution or restoring original data
      • Save a Solver solution as a scenario
      • Creating a Solver Report
      • Scenario Manager (if time)
      • Data tables (if time)
x