Microsoft Excel Stage 2 for Business Professionals

Course Overview

Day 1

Application Options and File Properties

·          View and change application options

·          View and change workbook properties

Using Named Ranges

·          Apply a name to a range of cells

·          Apply a name to a constant value

·          Modify and delete range names

·          Print a list of range names

·          Use a range names and labels in formulas

·          Define and modify a list range

Custom Number and Conditional Formatting

·          Use formatting options with Paste Special

·          Create a custom number or date format

·          Use scientific and fractional number formats

·          Use conditional formatting

Linking Worksheets and Workbooks

·          Create a formula to link worksheets and workbooks

·          Consolidate data using 3D references in common functions

·          Use Paste Special to link worksheets and workbooks

·          Use Paste Special to manipulate data

·          Manage and update linked workbooks

Consolidating Data

·          Use Consolidate to summarise data in multiple lists

Summarising a List

·          Extract data using advanced filters

·          Group and subtotal data using the outlining tools

·          Use D-functions to perform calculations on a data list

PivotTable and PivotChart Reports

·          Create a PivotTable Report

·          Modify PivotTable field layout

·          Modify field settings and grouping intervals

·          Use PivotTable AutoFormat

·          Create a PivotChart Report

Lookup Functions

·          Use VLOOKUP and HLOOKUP to get values from multi-column tables

·          Sort values in rows or from left-to-right

·          Use other lookup functions (LOOKUP, MATCH, INDEX, OFFSET)

·          Prevent lookup errors

Customising a Chart

·          Change the source data for a chart and modify data series

·          Explode segments of a pie chart

·          Insert an image into a chart

·          Add a text box to a chart

·          Store a custom chart as a chart template

Importing Text Data

·          Import data from a text file

Inserting Spreadsheets and Charts

·          Insert a worksheet from Microsoft Excel

·          Insert a chart from Microsoft Excel

Publishing to a Web Page

·          Save a worksheet or chart as a web page

·          Use Web Page Preview

Working with Objects

·          Select, position, and delete an object

·          Resize an object

·         Move or copy an object

Day 2

Templates and Add-Ins

·          Create a template file for frequently used worksheet layouts

·          Edit and apply a template file

·          Use workgroup templates

·          Load an Add-in program

Using Analysis Tools

·          Use one- and two-input data tables to resolve What-If? calculations

·          Solve a problem using Goal Seek

·          Use Solver to calculate complex problems

·          Create What-If analyses using the Scenario Manager

Scatter and Combination Charts

·          Create an XY (Scatter) or Bubble chart

·          Add a trendline to a chart

·          Create a combination chart

·          Add a second value axis to a chart

Protecting Data

·          Apply and remove worksheet protection

·          Lock and unlock cell ranges

·          Apply and remove file passwords to open or change a workbook

Validating Data Entry

·          Apply data validation rules and display user prompts

·          Validate data on a worksheet

Auditing a Worksheet

·          Display formulas in worksheet cells

·          Trace precedents and dependents

·          Trace and fix errors

·          Use the Formula Error Checker

·          Use the Formula Evaluator to obtain results in part of a formula

·          Use the Watch Window to view results in cells

Sharing a Workbook

·          Create a shared workbook

·          Track changes and resolve conflicting changes

·          Merge workbooks

·          Route a workbook to several reviewers

Querying a Database

·          Set up a data source

·          Create a query to import data as an external range

·           Format and refresh an external data range

·           Import a table from an HTML web page

·           Round trip HTML data in Excel

Using XML

·          Understand the use and capabilities of XML

·          Use Access to generate a schema

·          Apply a schema to a Word document

·          Import and export XML lists in Excel

Customising Command Bars

·          Add and remove a command from a toolbar

·          Customise button images and groups

·          Create a new command bar

·          Assign keyboard shortcuts to actions

Using Views and Workspaces

·          Save different views of a workbook

·          Use a workspace file

Recording a Macro

·          Record a macro

·          Run a macro

·          Edit a macro

·          Delete a macro

·          Assign macros to menus, shortcut keys, and toolbars

·          Set up macro virus protection

Digitally Signing a File

·          Create, view, and remove a digital signature