Excel – intermediate

A one-day workshop

This course takes a balanced look at writing formulas, analysing data and outputting reports. Use Excel’s help tools to find and write formulas and learn how to use rules to automatically format data to flag it with colours.

You will also look at ways to easily compare different sets of data and create charts for a visual representation of data. Use lookup functions to retrieve data from external sources. Those attending will also learn time-saving tips and tricks.

Learning objectives

This course will help participants:

  • Calculate with absolute reference
  • Group worksheets
  • Link to tables
  • Use the function library effectively
  • Get to grips with the logical IF function
  • Use conditional formatting
  • Create pivot table reports
  • Use data validation
  • Master the VLOOKUP function

Pre-requisites

Attendees are required to be familiar with some of Excel’s basic functionality, for example, AutoSum, filters, and basic formatting.

Format

A very practical, interactive one-day session for a maximum group size of 12. Comprehensive materials provided.

Expert trainer

Alan became a professional IT trainer in 1995, when on completion of an NVQ accreditation in IT, the organisation providing the course, Apex North London, asked if he would like to join their team, which he happily did. Alan went on to become a City & Guilds Assessor with them.

Alan now freelances and almost exclusively specialises in analytics using Microsoft Power BI as a platform, however he still provide clients with Excel training when requested to. His previous 25+ years within this sector includes both freelancing, and full time employment at various companies including, Lead Trainer at Go Courses, and Training Manager at Ultima Concepts.

See what some of the participants have said about the workshops he’s delivered for us:

‘Fantastic. Well-paced and easy to follow.’

‘Great experience.’

‘Very personable, relatable, patient and able to present complicated processes in an understandable way.’

‘Friendly and good at checking people are up to speed.’

‘Very good and explained and answered all questions.’

‘Explains everything well.’

‘Excellent teacher and very intelligent.’

‘Very knowledgeable would love to have him again if there is another course – thank you.’

‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’

Feedback

  • ‘Great training that has improved my knowledge in skills’
  • ‘You can gain a lot of info from it’
  • ‘Really patient instructor great knowledge very enjoyable’
  • ‘Very knowledgeable’
  • ‘Really helpful learning more about Excel’

1 Calculations with absolute references

  • The difference between a relative and an absolute formula
  • Changing a relative formula to an absolute formula by using $ signs to lock onto cells when copying a formula

2 Grouping worksheets

  • Group sheets together to enable the input of data into multiple worksheets
  • Write a 3D formula to sum tables across multiple worksheets

3 Linking tables

  • Creating links across tables
  • Using paste link to link a table to another Excel file
  • Using edit links to manage linked tables

4 The function library

  • The benefits of writing formulas in the function library
  • Using the insert function to search for formulas
  • Output statistics with COUNTA and COUNTBLANK
  • Count criteria in a list with COUNTIFS

5 Logical IFS function

  • Outputting results from tests
  • Running multiple tests for multiple result outputs

6 Conditional formatting

  • Use rules to apply colour to data to allow text and numbers to standout
  • Managing conditional formatting rules
  • Copy rules with the format painter

7 View tables side by side

  • Compare two excel files together
  • Compare two worksheets in the same file together

8 Pivot table reports

  • Analysing excel data with a pivot table
  • Managing a pivot table’s layout
  • Output statistical reports
  • Controlling value formatting
  • Making a reports visual by adding charts
  • Filter reports with slicers

9 Creating charts

  • Compare different sets of data in a Combo chart
  • Create a Waterfall chart to represent the cumulative effect of a series of positive and negative values

10 The VLOOKUP and XLOOKUP functions

  • Retrieve data from external files by using the VLOOKUP function and XLOOKUP function
  • Why use an XLOOKUP when a VLOOKUP will do the job?
  • Understanding the function arguments

Any questions? Please just give us a call on 01582 463463 – we’re here to help!