Microsoft Excel Advanced - course outline

Microsoft Excel is a widely used, powerful spreadsheet package. The primary objective of this one-day Advanced course is to broaden your knowledge of Excel. This practical course allows you to investigate the more powerful and advanced features of Excel. You will be encouraged to discuss your individual requirements relevant to your work. By the end of the course you will be competent with the following skills:

Advanced data entry and formatting techniques

  • restricting cell entries to specific numbers, dates or times
  • restricting cell entries to specific entries in a list
  • creating a custom number format
  • applying conditional formatting to a worksheet

Naming ranges

  • defining a range name
  • using a range name in a formula
  • selecting a named range

Using advanced functions

  • using the VLOOKUP function, using the HLOOKUP function
  • using the DSUM and DAVERAGE functions

Creating a custom workbook template

  • creating a new workbook template
  • modifying the default workbook template style
  • basing a new workbook on a custom workbook template

Auditing a worksheet

  • tracing precedents and dependents in a worksheet
  • tracing errors in a worksheet, watch and evaluate formulas

Linking multiple workbooks

  • entering a formula to link multiple workbooks
  • creating a workspace

Filtering and summarizing worksheet data

  • adding subtotals to a list
  • applying an advanced filter to a list, outlining a worksheet

Analyzing worksheet data

  • creating a pivottable report
  • resetting the summary function in a pivottable report
  • creating a pivotchart report , creating and displaying a scenario
  • using the analysis toolpak, using solver

Working with other Excel users

  • tracking changes in a workbook
  • responding to proposed changes and comments in a workbook
  • protecting a shared workbook, protecting an unshared workbook

Additional advanced Excel features

  • using the data consolidation feature
  • adding a trendline to a chart, format charts and graphics
  • manage workbook properties, modify Excel default settings

This course outline is a guide. All our courses can be tailored to customer requirements.

Target audience
Anyone wishing to investigate the more powerful and advanced features of Excel.

Assumed knowledge
Delegates should be familiar with the basic features of Excel, and should have some experience of the topics covered in our Introduction and Intermediate Courses, either from attending these courses or by knowledge gained from their own experience.