Excel Advanced NI

Summary

This hands-on practical course will give you the skills to tackle more complex data analysis.  With the use of the If Function, Vlookup and Pivot Tables you will have the necessary skills to extract information from your spreadsheets.  By completing structured exercises, this course will increase your competence in data analysis, helping you to meet targets more efficiently.

Venue details:  
Belfast, 8-10 Amelia Street, Belfast
Start date & time:  
23 October 2019 09:30
End date & time:  
23 October 2019 16:30
Price: €215.32  (€204.55 Member price)
By registering for this course you have accepted the terms and conditions
Training ticket cost:  
8.00
CPD hours:  
7.00
Speaker details
First nameLast name
Mullan Training

Description

Product type:  
CPD course
Category:  
Information technology

Who should attend?

This course is suitable for someone who has experience with the fundamental use & features of Microsoft Excel, including the ability to create formulae & basic functions.

Course overview

Linking Spreadsheets

  • Link Worksheets Within A Single File
  • Link Worksheets to External Files

Advanced Functions

  • IF Function & Nested IF Statements
  • And & Or Functions
  • Lookup Functions (Horizontal And Vertical Lookup)
  • Subtotals
  • Conditional Formatting

3D Ranges

  • Create & Maintain (Within Worksheets In a Single File & Between External Files)
  • Consolidate Data (via 3D Ranges & Using The Consolidate Tool)

Pivot Tables

  • Create a Pivot Table
  • Work With Row/ Column Fields
  • Data Fields
  • Customise Table Display

Data

  • Enter & Select Records Using The Input Form
  • Sort Data Into Lists
  • Filter Data - Autofilter, Advanced Filter
  • Database Functions
  • Validation Rules

Macros

  • Create Macros – Record & Modify the VBA
  • Code Generated
  • Relative Referencing
  • Adding Macros to the Quick Access Toolbar
  • Make Available To All New Documents/
  • Current Document Only

Outlines

  • Group Row/ Column Data To Create Outline
  • Levels Based On Worksheet Formulae
  • Display Summary Rows And Summary Styles

Sparklines (v. 2010)

  • Inserting Sparklines
  • Customizing Sparklines

Custom Views

  • Create, Modify, Delete
  • Include Hidden Rows, Columns, Filter Settings

Key learning outcomes 

  • At the end of this course, you will have a complete understanding of the higher functions of Excel.
  • You will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and ‘advanced’ analysis, record macros and integrate with other office applications.
  • Having practiced all of these new skills extensively during the course; on your return to the office you will be able to put into practice what you have learnt and complete your Excel work more efficiently.
  • You will be qualified to progress to our Excel VBA course.