Advanced Excel course (1 day) | Two Rivers Software Training

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.

Prerequisites

This course is designed for people who have used Excel regularly for a minimum of 12 months. Please view the contents of the Intermediate Excel course and make sure that you are comfortable with at least 80% of the contents before considering this course.

If you're not sure, you are welcome to take our free Excel Skills Health Check and get an instant scorecard.

Intermediate Excel Skills Health Check

Advanced Excel Skills Health Check

Versions Taught

Excel 2016, Excel 2013, Excel 2010

Want To Customise Your Training Content?

Don't see exactly what you want in the topic list below?

You are welcome to swap topics in and out from the different courses available to create your own custom course.

We can accommodate all requests. Customisation is 100% free. 

Let us deliver a powerful course with useful content that you or your staff will actually use. Tailor the training around your own needs and requirements.

Contact us right now and tell us what you need.

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
      • 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)
>