Excel – intermediate

A one-day workshop

This one-day course focuses on issues such as writing formulas and accessing help while writing them, and taking formulas to the next level by nesting one inside another for a powerful formula result.  It also looks at ways of analysing data with reports, summarised by varying criteria. A range of time-saving tips and tricks are shared.

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

  • Participants need to be familiar with the ‘Excel – Introduction’ content before attending this course
  • Designed for Office 365 versions: 2016, 2013 and 2010

Format

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

Expert trainer

Alan is a highly experienced and very popular IT skills trainer. After eighteen years as an employed IT trainer and training manager he went independent in 2013, specialising in Microsoft Office courses, primarily Excel, PowerPoint, Word, Outlook and Access, although he also delivers training in other programs when required. His clients come from all sectors and include such organisations as The Economist, Hyundai, Marston Group, MediaCom, London Borough of Harrow, Paragon Customer Communications, ICP Global Creative, Bywaters, etc, etc.

See what some of the participants have said about his workshops:

‘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.’

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 Calculating with absolute reference

  • The difference between a relative and absolute formula
  • Changing a relative formula to an absolute
  • Using $ signs to lock cells when copying formulas

2 Grouping worksheets

  • Grouping sheets together
  • Inputting data into multiple sheets
  • Writing a 3D formula to sum tables across sheets

3 Linking to tables

  • Linking to a source table
  • Using paste link to link a table to another file
  • Using edit links to manage linked tables

4 The function library

  • Benefits of writing formulas in the function library
  • Finding the right formula using insert function
  • Outputting statistics with COUNTA and COUNTBLANK
  • Counting criteria in a list with COUNTIFS

5 Logical IF Function

  • Outputting results from tests
  • Running multiple tests for multiple results
  • The concept of outputting results from numbers

6 Conditional formatting

  • Enabling text and numbers to standout
  • Applying colour to data using rules
  • Managing rules
  • Copying rules with the format painter

7 View side by side

  • Comparing two Excel tables together
  • Comparing two sheets together in the same file

8 Pivot table reports

  • Analysing data with pivot tables
  • Managing a pivot table’s layout
  • Outputting statistical reports
  • Controlling number formats
  • Visualising reports with pivot charts
  • Inserting slicers for filtering data

9 Data validation

  • Restricting data input with data validation
  • Speeding up data entry with data validation

10 VLOOKUP function

  • Best practices for writing a VLOOKUP
  • A false type lookup
  • A true type lookup
  • Enhance formula results with IFNA

11 Print options

  • Getting the most from print
  • Printing page titles across pages
  • Scaling content for print

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