COURSE INFORMATION

This course introduces features to Automate tasks with macros; unleash the power of PivotTables and PivotCharts; troubleshoot complex formulas; and more. The aim of the course is to allow you to explore further formula options and display your data in charts for quick visual analysis. You will also learn how to work and manage lists of data.

  • Pivot Tables and Charts
  • Troubleshoot Formulas
  • Advanced Formatting
  • Advanced Formulas
  • Data Analysis Tools
  • Macro recording

Pivot Tables and Charts

  • Create Pivot Tables
  • Add data fields
  • Add calculated fields
  • Change Calculation type
  • Format Pivot Tables
  • Refresh Pivot tables
  • Slicers and Timelines
  • Create Pivot Charts
  • Pivot Chart styles
  • Filter Pivot data & Charts
  • GETPIVOTDATA function

Troubleshoot Formulas

  • Handling errors
  • The Watch Window
  • Evaluate Formula tool

Advanced Formatting

  • Customise Conditional Formatting
  • Using Formulas in Conditional Formatting
  • Managing Conditional Formatting
  • Custom Themes
  • Form Controls (Developer Tab)

Advanced Formulas

  • Nested Functions
  • LOGICAL Functions (IFS, AND, OR)
  • SUMIF. AVERAGEIF, COUNTIF
  • TEXT functions (TEXTSPLIT, CONCATENATE)

Data Analysis Tools

  • Goal Seek
  • Scenario Manager
  • Consolidate Data

Macros

  • Macro Overview
  • Recording a Macro
  • Macro Settings and File types
  • Run Macros
  • 1 day trainer led or virtual.
  • £195
  • Attendance of the Intermediate Excel course or a sound knowledge of formula creation with Functions.
  • Working with lists of data and conditional formatting are recommended.
  • Introduction to Excel VBA
  • Introduction to Power BI Desktop