Advanced

Microsoft Excel 2003/2007/2010

Advanced Level

This course is designed for those who have completed the intermediate course, or who have a good working knowledge of the software.  By the end of this course, delegates will be able to use some of the more advanced features of Excel, to produce complex financial models.

Pre-Requisite:  Delegates attending this course should be already working with Microsoft Excel and wish to expand their knowledge, or are converting from another Electronic Spreadsheet package and have a good working knowledge of the product.

Duration:           1 Day

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 name 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
  • Create a formula to link workbooks
  • Use Paste Special to link worksheets/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 Dfunctions to perform calculations on a data list

    • ?

 Pivot Table and PivotChart Reports

  • Create a Pivot Table 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 (Match, Index, Offset)
  • Prevent lookup errors

 Customising a Chart

  • Change  the source data for a chart
  • Explode segments of a pie chart
  • Store a custom chart as a chart template

 Importing Text Data

  • Import data from a text file

    • ?

 Publishing to a Web Page

  • Save a worksheet or chart as a web page
  • Use Web Page Preview