A highly developed understanding of Excel can help organisations to monitor and manipulate data.

This Advanced Excel Training course has been specifically designed to help those with at least an intermediate understanding of Excel, to make better sense of their data.

By attending this Advanced Excel Training course, gain a better understanding of using more advanced Excel tools, such as auto filters, subtotals formulas and advanced lookups; to help import, clean and validate data. You will also learn more advanced tools to manipulate data through using mixed plots and Goal seek.

Practise using Excel tools on public sector data and benefit from expert guidance from our trainer to increase your skills and give you a firmer understanding of more innovative techniques.

Delegates will be asked to bring their own laptops.

About the Chair: Branko Pecar
Branko spent most of his career working for Emerson, a global and one of the largest process control equipment manufacturing corporations. His last position was VP Educational Services. In the past, he also lectured at University of Gloucestershire in several subjects, including quantitative methods and applications of Excel.

He published a series of books in statistics with a variety of publishers, such as McGraw Hill, Oxford University Press and Amazon. Almost all of these books are based around applying a variety of sophisticated methods using the simplest Excel syntax. He has been using Excel for over thirty years and has experience in applying it to numerous and diverse problems.

At present, Branko is working on algorithms for a Norwegian start up, applying Fast Fourier Transforms to problems related to process safety valves. Currently, he uses Excel extensively as a modelling tool to validate some of the process assumptions required to optimize the algorithms.

09:15 - 09:45

Registration

09:45 - 10:00

Trainer’s Welcome and Clarification of Learning Objectives

  • Gain a quick overview of Excel
  • Refresh your Excel knowledge
  • Confidently navigate yourself around Excel
10:00 - 10:45

Workshop: Importing, Cleaning and Validating Data

When accessing and importing data from any external source, such as: databases, the web, cloud applications, etc., the data will almost always need some processing to make it easier to use in Excel. The list below covers some of these processing steps:

  • Importing and separating data into multiple fields
  • Concatenation and other field manipulations
  • Date format clean-ups
  • Validating data entry
  • Setting up validation for data inputs
  • Using data validation
10:45 - 11:00

Morning Break

11:00 - 12:00

Workshop: Data Manipulation Part 1

Some of the less often used functions can be a great time saver when it is necessary to sort data, or identify the correct entries, or re-shape the table of data. Also, several built-in Excel routines, such as Goal seek and Solver, can be used to solve very practical challenges.

  • Conditional statements, Index, Match, Offset
  • Grouping data, SumIfs
  • Transposing data, multiply/divide with a constant
  • CAGR and other calculations
  • Goal seek, Solver
12:00 - 13:00

Workshop: Data Manipulation Part 2

Pivoting data, especially if a large data set is used, is essential tool that will make life easier for anybody who is even superficially crunching the data. Besides pivoting the data set, visualisation of different data views is also a great help in achieving clarity.

  • Creating and manipulating pivot tables
  • Visualising pivot tables
  • Mixed plots (two axes, bar/line combo, etc.)
  • Waterfall charts
  • Editing chart data
13:00 - 14:00

Lunch

14:00 - 14:45

Workshop: Beyond Executing a Single Function

To have just an elementary understanding of how the macros and VBA work in Excel can be of great use. There is no need to go into great depth if Macros are used only sporadically and opportunistically. This brief introduction aims to achieve precisely this.

  • The basics statements and loops in macros and VBA Visualisation
  • Creating macros
  • Debugging and executing macros
14:45 - 15:00

Afternoon Break

15:00 - 16:00

Workshop: More Advanced Excel Tools

A variety of tools, based on either dedicated Excel functions or on a variety of pop-down options are available. This closing session will provide an introduction to some of the eclectic functionality available to users grasp a deeper insight into their data.

  • Auto filters
  • Subtotals formulas
  • Advanced lookups
  • Trending
16:00 - 16:15

Feedback, Evaluation and Close

Branko Pecar

Statistics Consultant

Branko spent most of his career working for Emerson, a global and one of the largest process control equipment manufacturing…

Read more

etc. Venues – Marble Arch, London

Garfield House
86 Edgware Road
London
W2 2EA

020 7793 4200

Download directions

Q: Can I make a provisional booking?

A: Unfortunately, we do not accept provisional bookings. Registrations are subject to our terms and conditions. View terms and conditions here

Q: Can I book without a purchase order number?

A: Yes. Simply email [email protected] after you have booked the course, with your Purchase Order Number. Please quote your order number and the course you are booked onto.

Q: Is lunch included?

A: Yes, a two-course hot buffet is served at lunch. Tea and coffee are served throughout the day.

Q: I have special dietary requirements, how can I request this?

A: Special dietary requirements can be catered for, please ensure you include this in the further information box when registering your place. If you have forgotten to add this, you can also send your requirements to [email protected] or call 0800 542 9440. Please let us know as soon as possible so we can ensure your needs are met.

Q: I have just registered for the course, when will I receive the joining instructions?

A: You will receive the joining instructions and reminders, 6 weeks, 2 weeks and 3 days before the course date. Please check your spam box to see whether the joining instructions were sent there, if not please call 0800 542 9440 so we can have these sent to you immediately.

Q: I can no longer attend the course, can a colleague attend in my place?

A: Substitutions may be made at any time but must be made no later than 48 hours prior to the event. Please call 0800 542 9440 or email [email protected] with the replacement's details.

Q: What is your cancellation policy?

A: Cancellations must be received in writing 30 working days before the date of the event and will be subject to a £195+VAT administration fee. Cancellations received after this time will be subject to the full delegate fee.

Q: Why haven’t I received the speaker’s presentation?

A: Speakers presentations are sent a week after the event date. Please contact a member of the UMG team on 0800 542 9440 or [email protected] if it has been more than a week.

Q: Will there be WI-FI?

A: Yes, all our venues have the latest technology, offer full audio visual support and WI-FI.

Q: Can’t find what you are looking for?

A: Why not contact a member of the UMG team on 0800 542 9440 or email [email protected]