iTrain Scotland IT Training Edinburgh image
 
Home > Courses > Microsoft courses > Microsoft Excel Level 3

Microsoft Excel Level 3

Suitable for versions 365 / 2019 / 2016

Whether you are an occasional user or have been using Excel for years, this course will vastly increase your understanding of Excel. Existing users will learn how to get the most out of Excel, break any bad habits, learn shortcuts and discover a massive range of hints and tips from our expert Excel trainers.

This course is aimed at intermediate users of Excel who wish to exploit the full capabilities of their spreadsheet software. A complete understanding of the topics in the level 1 course is suggested.
Delegates will learn:
  • Work with data, transform data sets, create and import from Cloud
  • Understand and build Pivot Tables to analyse data
  • Automate processes using Macros,
  • Apply data analysis functions including VLOOKUP, XLOOKUP
  • The course also covers a vast array of advanced formulas and functions including new Array functions, nested IFs, Date functions, formula auditing
  • Delegates will also learn shortcuts and tips about how to use Excel in the most efficient way

Data Tables

  • Importing data from CSV
  • Understanding tables and database architecture
  • Filtering with Slicers
  • Dynamic named ranges, dynamic references in functions
  • Extending a table
  • Styles
  • Removing duplicates

Pivot Tables

  • Understanding what Pivot tables are and when to use them
  • Creating from data tables
  • Updating / refreshing
  • Connecting to a data source
  • Data models
  • Adding Fields
  • Creating calculated fields
  • GETPIVOTDATA functions
  • Manipulating
  • Consolidation
  • Pivot Charts
  • Dashboards
  • Slicers and timelines

Arrays
  • Arrays explained
  • Dynamic array functions
  • Spilling
  • Unique, Sort array functions

Advanced Functions
  • XLOOKUP
  • VLOOKUP / HLOOKUP
  • MATCH & INDEX
  • Logical IF, AND, OR
  • Nested IFs
  • Mathematical
  • Date/Time
  • Text
  • Information
  • Statistical
  • Database Functions
  • Financial 
  • General Nesting
  • Function hints and tips


Scenario Manager

  • What If Analysis
  • Adding
  • Updating
  • Merge
  • Summary
  • Printing

Tips & tricks
  • Using Excel efficiently using shortcuts
  • Previewing all formulas on a worksheet
  • Auto calculations
  • Autofill
  • Keyboard shortcuts
  • Mouse shortcuts
  • Autoresize
  • Keyboard selection techniques
  • Mouse selection techniques

Excel's secrets & expert advice
  • Creating a proper database
  • Navigating a worksheet
  • Hidden views

Macros

  • Demystifying macros
  • Recording Macros
  • Keyboard shortcuts
  • Running macros
  • Relative Vs Absolute referencing
  • Where to store a macro
  • Macro Recorder
  • Editing a macro
  • Introduction to VB
  • Macro security

General Advanced Features  

  • Custom Formatting Numbers
  • Conditional Formatting Rules
  • Goal Seek
  • Text Joining
  • Styles
  • Custom Lists
  • Auditing
  • Mixed References

Advanced Charts
  • Adding 2nd Y Axis
  • Custom Charts
  • Trend Lines

Introducing Power BI

  • An overview of Power BI and how it can be used to Display and analyse Excel data

Integration with Microsoft 365
  • Cloud storage and OneDrive and SharePoint Libraries
  • Signing into 365
  • Office 365 Cloud Apps
  • Signing-in to Microsoft 365
  • Using Microsoft 365 on multiple devices
  • Easier file sharing
Prices below are for scheduled courses held in our Edinburgh Training Centre or Live Virtual Classroom. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.
Group discount
1 place £ 250 n/a
2 places £ 500 n/a
3 places £ 750 £ 683
4 places £ 1000 £ 900
5 places £ 1250 £ 1113
6 places £ 1500 £ 1320
7 places £ 1750 £ 1523
8 places £ 2000 £ 1720