Course Icon Excel Training

Microsoft Excel - Advanced

Designed for Excel versions 2016,2013,2010,2007

0800 1337 401

Course Objectives

Welcome to Seven Institute’s course for Microsoft Office Excel, a popular spreadsheet application designed for organising and analysing data, performing calculations, and charting information.

This advanced level is intended to help regular users of Excel become even more proficient by expanding their knowledge of Excel’s advanced functions and PivotTables. This course will also help experienced users who may be unfamiliar with the ribbon interface introduced in Excel.

This course will cover a variety of organising tools and What-If analysis tools that will help Excel users get the most out of their data. In addition, two key advanced spreadsheet tools (PivotTables and PivotCharts) will be discussed. Following that, advanced concepts dealing with Excel functions will also be covered.

Who Should Attend

This course presumes that the user has a basic knowledge of Windows, including concepts such as using the mouse and keyboard. With respect to Excel, we also presume that the user has completed the Intermediate level of this course (or has equivalent knowledge) and is familiar with concepts such as:

  • Using right-click menus
  • Working with dialog boxes
  • Worksheet organisation (rows, columns, labels)
  • Cutting, copying, pasting, selecting, and moving Excel data
  • Basic cell data formatting tasks
  • Working with cell references
  • Manipulating multiple open worksheets and/or workbooks
  • Creating and manipulating standard Excel data tables
  • Using Excel functions (in particular the IF function)
  • Building formulas in Excel
  • Creating and manipulating standard Excel charts
  • Understand of Absolute and Relative referencing

Course Syllabus

USING EXCELS WHAT-IF ANALYSIS TOOLS

  • Describe scenarios
  • Create a scenario
  • Save multiple scenarios
  • Create a scenario summary report
  • Use Goal Seek

WORKING WITH EXCEL PIVOT TABLES

  • Describe and create PivotTables
  • Use the contextual PivotTable Tools tabs
  • Add and remove PivotTable information using the Field List
  • Change the Field List layout
  • Pivot data
  • Expand and collapse data
  • Filter, sort, group, and refresh data
  • Edit a data source

WORKING WITH EXCEL PIVOT CHARTS

  • Create a PivotChart from scratch or from an existing PivotTable
  • Add data to a PivotChart
  • Pivot chart data
  • Use the PivotChart Tools contextual tabs (Design, Layout, Format, and Analyze)

USING LOOKUPS IN EXCEL

  • Create a VLOOKUP
  • Create an HLOOKUP
  • Differentiate between VLOOKUP and HLOOKUP
  • Find an exact or approximate match with VLOOKUP

ADVANCED LIST FUNCTIONS

  • DSUM
  • DAVERAGE
  • DCOUNT
  • Data Valadation to manage data entry criteria
  • Drop Down Combo Boxes

INTRODUCTION TO NESTED FUNCTIONS

  • Nested IF function
  • OR and AND functions
  • Nested IF, OR, AND functions

INTRODUCTION TO MACROS IN EXCEL

  • Record, edit, and play a macro
  • Modify macro security settings
  • Use the Visual Basic Editor when working with macros
  • Add code and comments to macros
  • Use and record relative reference macros
  • Assign a keystroke to a macro
  • Copy a macro from a workbook or template
See all of the available Seven Institute Excel Courses