Description

This version of Excel incorporates some new features and connectivity options in efforts to make collaboration and production as easy as possible.

By the end of this course users should be comfortable in creating advanced formulas, analysing data with functions, analysing data using functions and PivotTables, working with tables, visualising data with charts, inserting graphics, and enhancing workbooks.

These courses are intended to help all users get up to speed on the different features of Excel and to become familiar with its more advanced selection of features. We will cover how to create and use advanced formulas, analyse data, organise worksheet data with tables, visualise data with charts, insert graphics, and enhance workbooks.

Microsoft Excel – Intermediate 

• Lesson 1: Creating Advanced Formulas
  • Apply Range Names
  • Use Specialised Functions
• Lesson 2: Analysing Data with Logical and Lookup Functions
  • Leverage Questions and Testing to Write Formulas
  • Use Logical and Lookup Functions to Find Answers to Questions
• Lesson 3: Organising Worksheet Data with Tables
  • Create and Modify Tables
  • Sort and Filter Data
  • Use Summary and Database Functions to Calculate Data
• Lesson 4: Analysing Data with Pivot Tables, Slicers, and Pivot Charts
  • Create a PivotTable Report
  • Filter Data by Using Slicers
  • Analyse Data Using Pivot Charts
• Lesson 5: Inserting Graphic
  • Insert and Modify Graphic Objects
  • Layer and Group Graphics Objects
  • Incorporate SmartArt
• Lesson 6: Enhancing Workbooks
  • Customise Workbooks
  • Manage Themes
  • Create and Use Templates
  • Protect Files

 

Microsoft Excel – Advanced 

You will automate some common Excel tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and share Excel data with other applications.

In this course, you will extend your knowledge into some of the more specialised and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets, collaborating on worksheets with others, and sharing Excel data with other applications.

 

• Lesson 1:Automating Worksheet Functionality
  • Update Workbook Properties
  • Create and Edit a Macro
  • Apply Conditional Formatting
  • Add Data Validation Criteria
• Lesson 2: Auditing Worksheets
  • Trace Cells
  • Troubleshoot Invalid Data and Formula Errors
  • Watch and Evaluate Formulas
  • Create a Data List Outline
• Lesson 3:Analysing and Presenting Data
  • Create Spark lines
  • Create Scenarios
  • Perform a What-If Analysis
  • Perform a Statistical Analysis with the Analysis Tool Pack
  • Create Interactive Data with Power View
    • The Power View Add-In
    • How to Create a Power View Report
    • Creating a Power View Worksheet
• Lesson 4: Working with Multiple Workbooks
  • Consolidate Data
  • T Link Cells in Different Workbooks
  • Merge Workbooks
• Lesson 5: Exporting Excel Data
  • Export Excel Data
  • Import a Delimited Text File
  • Integrate Excel Data with the Web
  • Create a Web Query