Special Notices

Please note: for Attend from Anywhere customers an additional screen is required for this course to work through remote desktop labs and view training information.

Overview

In this course 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.

Prerequisites

  • Create, edit and format spreadsheets
  • Navigate within worksheets and books
  • Use Insert Function to create built-in functions
  • Work with absolute references
  • Create named ranges
  • Create Tables within excel
  • Insert SmartArt graphics
  • Work with Themes
  • Sort and filter data
  • Open and navigate in a Word document
  • Browse the Internet

Delegates will learn how to

  • Record, basic edit and Run Macros
  • Use and manage Conditional Formatting
  • Control values with Data Validation
  • Collaborate with other workbook users
  • Audit worksheets
  • Analyse data
  • Work with multiple workbooks
  • Import and export data
  • Integrate Excel data with the web
  • Structure workbooks with XML

Outline

Module 1: Macros

  • Creating a Macro
  • Comparing Relative and Absolute Macros
  • Running a Macro
  • Editing a Macro
  • Assigning Shortcuts to Macros
  • Macro Security

Module 2: Conditional Formatting

  • Apply Conditional Formatting
  • Modify existing Conditional Formatting
  • Applying Conditional Formatting based on a Formula
  • Sorting and Filtering by Colour

Module 3: Data Validation

  • Number and Date Validation
  • Data List Validation
  • Message Prompts and Alerts
  • Conditional Data Validation
  • Data Validation Errors

Module 4: Analysing Data

  • What-If Analysis Using Goal Seek
  • What-If Analysis working with Data Tables
  • Creating Scenarios
  • Consolidating Data

Module 5: Analysing Chart Trends

  • Overview of Trendlines in Excel
  • Analyse Data using Sparklines

Module 6: Collaborating with Other Users

  • Adding a Password to a File
  • Protecting a Workbook
  • Protecting a Worksheet
  • Track Changes
  • Accept and Reject Changes
  • Workbook Merging