LOOKUP FUNCTIONS

  • CHOOSE
  • VLOOKUP
  •  HLOOKUP
  • Reference functions - INDEX, MATCH, ADDRESS, ROW, COLUMN

EXCEL OPTIONS

  • Personalising Excel
  • Setting default font
  • Setting formula options
  • Saving a new workbook
  • Setting Save options
  • Saving a new workbook
  • Setting the default file location
  • Advanced options

LABELS AND NAMES

  • Creating name using text labels
  • Using names in formulas
  • Using the Name Manager
  • Using the Name box to go to named references

SELECTING TECHNIQUES

  • Selecting contiguous ranges
  • Selecting non-contiguous ranges
  • Selecting rows and columns
  • Using ranges with the keyboard

PROTECTING DATA

  • Understanding data protection
  • Protecting a worksheet
  • Disabling worksheet protection
  • Restricting access to cells
  • Password protecting a workbook

SUMMARISING AND SUBTOTALLING

  • Creating subtotals
  • Creating nested subtotals
  • Creating relative names for subtotals

DATA LINKING

  • Linking between worksheets
  • Linking between workbooks
  • Updating links between workbooks

DATA CONSOLIDATION

  • Consolidating with identical layouts
  • Creating an outlined consolidation

PIVOT TABLES

  • Creating a PivotTable shell
  • Dropping fields into a PivotTable
  • Filtering a PivotTable report
  • Formatting a PivotTable report
  • Inserting and removing page breaks
  • Using Slicers

PIVOT TABLE TECHNIQUES

  • Using compound fields
  • Counting in a PivotTable report
  • Working with PivotTable totals
  • Finding the percentage of a total
  • Creating running totals
  • Grouping in PivotTable reports
  • Creating calculated items
  • Sorting in a PivotTable

PIVOT CHARTS

  • Creating a PivotChart shell
  • Dragging fields to the PivotChart shell
  • Changing the PivotChart type
  • Using the PivotChart filter field buttons

GOAL SEEK

  • Understanding the components of Goal Seek
  • Using Goal Seek
  • Sorting on more than one level

GROUPING AND OUTLINING

  • Creating an automatic outline
  • Working with an outline

SOLVER

  • Installing the Solver Add-In
  • Choosing the chart type
  • Setting Solver parameters
  • Adding Solver constraints
  • Performing the Solver operation
  • Running Solver reports

OVERVIEW OF EXCEL MACROS

  • Understanding Excel macros
  • Setting macro security
  • Saving a document as macro-enabled
  • Recording a simple macro
  • Running a recorded macro
  • Running a macro with relative cell references
  • Viewing and editing a macro
  • Assigning a macro to a toolbar
  • Copying a macro
  • Deleting a macro

EXCEL ADVANCED