Data is the new currency but analysing it in Excel has many challenges. In this intermediate course we look at a range of functions that can help us automate the process of cleaning and transforming the data, so it is ready for analysis. We will also look at using tables to automate processes and perform simple analysis. We will then link data sets using lookup functions and finally use pivot tables and pivot charts to perform a more detailed analysis which we will present in a dashboard.

CLEAN AND TRANSFORM DATA
• Joining text & changing case
• Splitting text values
• Cleaning and replacing text values
• Converting Data Types
• Date Functions for Data Enrichment

AGGREGATION FUNCTIONS
• Naming Ranges
• SUMIFS and COUNTIFS

TABLES
• Converting from a range to a table
• Customising tables and working with the total row
• Sorting and Filtering table data
• Slicers
• Automating processes with tables

LOOKUP FUNCTIONS
• VLOOKUP range lookup
• VLOOKUP exact match

PIVOT TABLES
• Analyse data with Pivot table reports
• Modify calculations within Pivots
• Change the formatting of PivotTables
• Group, Sort and Filter within PivotTables

INTERACTIVE DASHBOARDS
• Dashboard Design
• Create and add Pivot Charts
• Customise Pivots
• Add interactive filters using slicers
• Finish off with themes, graphics and hyperlink

Ideal for individual delegates who have specific requirements from the software, or where previous knowledge makes group training inappropriate. The pace and content can be adjusted to the individuals exact needs.

PREQUALIFICATIONS
Advanced Excel training is for people who can already create moderately complex spreadsheets and want to add more sophistication and automation. Candidates should have completed the Intermediate level, or feel competent they have an intermediate knowledge of Excel.

• 1-day Face to Face instructor-led program
• 2 x 3-hour web-based virtual sessions

  • Extensive Learning & Reference Guides provided on USB
  • Participation in the full day workshop
  • Access to a Microsoft technical expert throughout and post the workshop