COURSES > ANALYTICS

Advanced Excel Training

Utilise Excel to Improve Performance

Chaired by
Alan Gilbert

Available Dates

Date & Time

5 June, 2024
09:25 - 16:15

1 October, 2024
09:25 - 16:15

Venue

Virtual Using Zoom

Virtual Using Zoom

Availability

Limited Spaces Available

Limited Spaces Available

Purchase

Overview

With access to a large amount of data, having expertise in Excel and data manipulation is a highly valued skill in the public sector.

Delivered by Microsoft expert Alan Gilbert, our Advanced Excel Training is designed to help you process large quantities of data using spreadsheets, graphs, data tables, calculations, and automation to produce results.

This course will provide in-depth training and expert guidance on advanced Excel tools including data analysis, subtotal formulas, advanced lookups, mixed plots and Goal Seek. Learn how to effectively import, clean, and validate data, and manipulate it to make more informed decisions.

Deepen your understanding of innovative Excel techniques and position yourself as an invaluable contributor to your organisations efficiency, productivity and decision-making process.

Please note for this course it will be useful to have two screens.


Microsoft Excel and Power BI Consultant

In his 20+ years of teaching Microsoft products, Alan has helped a vast array of industries from finance to construction, media, and government. In the past he has been both lead trainer for Go Courses, and training manager for Ultima Concepts. As work increased, he established his own IT consultancy in 2013, following on from 18 years as a full-time trainer.

Being a certified Microsoft Office Specialist, he found the demand for Microsoft Excel training and consultancy work, including Excel’s many facets, increased substantially. Subsequently, that insider’s perspective led him to establish a second consultancy specialising in Microsoft ...

Alan Gilbert


Learning Outcomes

  • Understand how to manipulate data effectively

  • Gain skills to utilise pivot tables, create macros and visualisations

  • Learn how to use more advanced Excel tools to benefit your organisation: Data analysis, Advanced lookups, Subtotal formulas, and Trending

  • Benefit from expert guidance and have your Excel questions answered in an interactive learning environment



All the Understanding ModernGov courses are Continuing Professional Development (CPD) certified,
with signed certificates available upon request for event.


Enquire About In-House Training

To speak to someone about a bespoke training programme, please contact us:

0800 542 9414
InHouse@moderngov.com



Agenda

09:00 - 09:30 Registration

09:30 - 10:00 Trainer’s Welcome and Clarification of Learning Objectives

10:00 - 10:45 Workshop: Importing, Shaping and Validating Data

  • Import tables from external files

  • Separate data into columns

  • Concatenate and other field manipulations

  • Validate data entry

  • Set up validation for data inputs

10:45 - 11:00 Morning Break

11:00 - 12:00 Workshop: Lookup and Criteria Functions, What-If Analysis

  • The INDEX and MATCH lookups

  • SUMIFS to sum by criteria

  • Use Goal Seek to find the right input

  • Use Scenario Manager to switch values

12:00 - 13:00 Workshop: Data Analyses and Visualisations

  • Analyse data in pivot tables

  • Group data in pivot tables

  • Conduct field calculations

  • Create report dashboards

  • Learn how to utilise combo charts

  • Understand Waterfall charts and their uses

13:00 - 14:00 Lunch

14:00 - 14:45 Workshop: Automating tasks in excel

  • Automate repetitive tasks with macros

  • Understand the process of a macro

  • How to execute macros

  • Editing VBA code

14:45 - 15:00 Afternoon Break

15:00 - 16:00 Workshop: Other Advanced Excel Tools

  • How to insert subtotal

  • Handle duplicate values in lookups

  • How to view trends

16:00 - 16:15 Feedback, Evaluation & Close

  • Effective Report Writing, Senior Engineering Specialist, Eastleigh Borough Council

    “This course provided an excellent understanding of how to write effectively and why it is important to do so.”

  • Effective Proofing and Editing Skills: Senior Events Officer, King's College London

    “I would really recommend the course. Sue is a fantastic trainer and the day passed by far too quickly. Lots of helpful resources to take away, also very impressed with the smooth transition to online learning. Thank you!

  • Effective Data Visualisation: Corporate Performance Management Officer, Carmarthenshire County Council

    “I have learned a great deal with many tips and ideas to enable to improve the authority’s reports”

  • Effective Business Process Mapping: Internal Quality and Improvement Officer, Social Work England

    “It was a fun course to take a part in. There was a lot of information given, and it was done in an engaging way with lots of interactivity, even over zoom. i really enjoyed the course and learned lots from it.”

Become Part of the Understanding ModernGov Community