We generally train face-to-face at client site. We also offer remote training, usually via Microsoft Teams or Zoom, if this is your preference. Click for more information about remote training.

Microsoft Excel training

Microsoft Excel is a spreadsheet program enabling you to create simple to very complex calculations, lists, databases, charts, and forms. Microsoft Excel is an essential business tool. Our Microsoft Excel training courses will help you to master the package.

Number on coursePrice per day
1 - 1 training£375 + VAT
2 - 4 people£575 + VAT
5 - 8 people£795 + VAT

Excel Power Query / Power Pivot – provisional Course Outline

All outlines can be tailored to meet the needs of the individuals or organisation.

Aims of the course

This course aims to give delegates the skills necessary for working with large volumes of data in Excel.

Course Objectives

On completion of the course delegates will be able to:

  • Transform data
  • Pivot data
  • Transpose data
  • Work with Power Query
  • Work with Power Pivot
Who This Course Is For Anyone who wants to understand the most powerful tool introduced into Excel since Pivot Tables — especially if you combine data manually, search online for Excel formula workarounds, work with large volumes of data, want an easier alternative to VBA coding, or want to automate parts of your Excel workflow.

The course is of one day’s duration.

How the course is run – This instructor-led course has a hands-on approach, with plenty of opportunity for delegates to practise the new skills they learn at the computer.

Pre-requisites – Delegates must be confident in using Excel.

Course Outline  

Overview

  • Power Query Editor and basic transformations
  • Quick insights, data quality and distribution
  • Connecting to multiple data sources
  • Loading data from sources such as SharePoint, CSV, the web, and email
  • Refresh data and Power Query refresh options

Common Transformations

  • Split columns (delimiter, character count, position, digit to non-digit, etc.)
  • Format columns (UPPERCASE, lowercase, Capitalize Each Word, Trim, Clean, etc.)
  • Statistic functions (Sum, Min, Max, Median, Average, Standard Deviation, Count Distinct)
  • Replace values
  • Rounding data
  • Filtering columns (number and text filters)
  • Spotting duplicates between two columns
  • Understanding data types and why they matter
  • Fill and replace values
  • Finding and correcting errors in data

Pivoting & Transposing Data

  • Pivoting and unpivoting columns
  • Transposing columns

Powerful Transformations

  • Conditional columns (equivalent to IF / nested IF, but easier to amend)
  • Merge data with another file (more efficient than VLOOKUP)
  • Column from Example (an enhanced version of Flash Fill)
  • Appending data between different files
  • Combining all files in a folder
  • Extracting text based on a pattern
  • Fuzzy matching
  • Group By — grouping data by a chosen factor
  • Power Query navigation tips
  • Finding and correcting errors in data
  • Creating custom columns
  • Excel’s Data Model — combine sources for bespoke Pivot Tables

Time Transformations

  • Date and time transformations: building calendars, calculating age, time/date differences, durations, and more

Using Power Query with Power Pivot

  • Loading queries into the Data Model instead of worksheets
  • Why Power Pivot: moving beyond a single flat table
  • Creating relationships between multiple queries
  • Building measures with DAX (e.g. SUM, AVERAGE, CALCULATE)
  • Calculated columns vs. measures — when to use each
  • Time intelligence (year-to-date, prior period comparisons)
  • Building Pivot Tables from the Data Model
  • Refreshing the full Query-to-Model pipeline

Call us to discuss yourtraining requirements.
We provide Power Query/ Power Pivot training in:

  • Dorset
  • Wiltshire
  • Hampshire
  • Somerset

Training Dorset locations include:

Bournemouth Poole Sherborne Shaftesbury
Gillingham Christchurch Ferndown Wimborne
Blandford Forum Dorchester Weymouth Verwood
Merley Wareham Bridport Lulworth
St Leonards

Training Wiltshire locations include:

Salisbury Swindon Trowbridge Chippenham
Devizes Marlborough Warminster Tisbury
Melksham Westbury Corsham Amesbury
Tidworth Bradford-on-Avon Bulford Camp Porton
Calne Highworth Wootton Bassett Wroughton
Malmesbury

Hampshire training locations include:

Southampton Winchester Portsmouth Basingstoke
Andover Petersfield Ringwood Havant
Gosport Farnborough Aldershot Romsey
Eastleigh Fleet Alton Fareham
Totton Hedge End Stubbington
Hythe New Milton Emsworth Romsey
Bordon Tadley South Hayling Lymington
Yateley Blackfield Hook Blackwater
Bursledon North Baddesley Liphook Netley
Bishop’s Waltham Liss Marchwood Denmead
New Alresford Oakley

Training Somerset locations include:

Bath Yeovil Wincanton Frome
Taunton Weston-Super-Mare Midsomer Norton Shepton Mallet
Radstock Paulton Bridgwater Burnham-on-Sea
Chard Cheddar Clevedon Crewkerne
Glastonbury Portishead Nailsea Keynsham
Street Minehead Wellington Wells
Yatton Ilminster Long Ashton

We have also trained home-based businesses and users in some of the Dorset, Somerset, Wiltshire and Hampshire villages, so please do get in touch. To request further details of any of our courses, or to make a booking, please contact us by ringing 01747 820396 or 07477 578417 or by sending an email to info@archwayct.co.uk or filling in our contact form.