<< Back to Courses

Parramatta - Excel 2010 Level 3


Cost: $880.00   Duration: 2 days


Schedule: Seat:

Overview

Microsoft Excel 2010 Level 3 aims to provide skills and knowledge in advanced concepts and techniques including  enhanced formatting, charting, and worksheet operation, as well as the use of complex analytical and automation tools.

Learning Outcomes

At the completion of this course you should be able to:

  •  use a range of formula techniques
  •  use a range of lookup and reference functions
  •  apply conditional formatting to ranges in a worksheet
  •  use a range of techniques to work with worksheets
  •  filter data in a table
  •  use a range of techniques to enhance charts
  •  modify Excel options
  •  create and use labels and names in a workbook
  •  protect data in worksheets and workbooks
  •  use the Data Consolidation feature to combine data from several workbooks into one
  •  group cells and use outlines to manipulate the worksheet
  •  understand and create simple PivotTable reports
  •  create and edit a PivotChart
  •  use goal seeking to determine the values required to reach a desired result
  •  create summaries in your spreadsheets using subtotals
  •  create recorded macros in Excel

Target Audience

Microsoft Excel 2010 Level 3 is designed for users who are keen to learn about more of the advanced features and techniques of Excel. It concentrates on improving current  knowledge through the use of more advanced and complex techniques and spreadsheet automation.

Prerequisites

Microsoft Excel 2010 Level 3 assumes some prior experience in using the software to create worksheets and charts and it would also be beneficial to have a general understanding of personal computers and the Windows operating system environment.

Duration

Scheduled classroom training (2 Days)

Contents

Formula Techniques

  • Scoping A Formula
  •  Developing A Nested Function
  •  Creating Nested Functions
  •  Editing Nested Functions
  •  Copying Nested Functions
  •  Using Concatenation
  •  Switching To Manual Recalculation
  •  Forcing A Recalculation
  •  Pasting Values From Formulas

Logical Functions

  •  Understanding Logical Functions
  •  Using IF To Display Text
  •  Using IF To Calculate Values
  •  Nesting IF Functions
  •  Using IFERROR
  •  Using TRUE And FALSE
  •  Using AND
  •  Using OR
  •  Using NOT

Lookup Functions

  •  Understanding Data Lookup Functions
  •  Using CHOOSE
  •  Using VLOOKUP
  •  Using VLOOKUP For Exact Matches
  •  Using HLOOKUP
  •  Using INDEX
  •  Using MATCH
  •  Understanding Reference Functions
  •  Using ROW And ROWS
  •  Using COLUMN And COLUMNS
  •  Using ADDRESS
  •  Using INDIRECT
  •  Using OFFSET

Number Formatting Techniques

  • Using Alternate Currencies
  •  Formatting Dates
  •  Formatting Time
  •  Creating Custom Formats

Conditional Formatting

  • Understanding Conditional Formatting
  •  Formatting Cells Containing Values
  • Clearing Conditional Formatting
  • More Cell Formatting Options
  • Top Ten Items
  • More Top And Bottom Formatting Options
  • Working With Data Bars
  • Working With Colour Scales
  • Working With Icon Sets
  • Understanding Sparklines
  • Creating Sparklines
  • Editing Sparklines

Worksheet Techniques

  • Inserting And Deleting Worksheets
  • Copying A Worksheet
  • Renaming A Worksheet
  • Moving A Worksheet
  • Hiding A Worksheet
  • Unhiding A Worksheet
  • Copying A Worksheet To Another Workbook
  • Moving A Worksheet To Another Workbook
  • Changing Worksheet Tab Colours
  • Grouping Worksheets
  • Hiding Rows And Columns
  • unhiding Rows And Columns
  • Freezing Rows And Columns
  • Splitting Windows

Headers And Footers

  • Understanding Headers And Footers
  • Adding A Quick Header
  • Adding A Quick Footer
  • Switching Between Headers And Footers
  • Typing Text Into Headers And Footers
  • Modifying Headers And Footers
  • Adding Page Numbering
  • Adding Date Information
  • Adding Workbook Information
  • Adding A Picture
  • Formatting Headers And Footers
  • Dragging Margins For Headers And Footers
  • Creating A Different First Page
  • Different Odd And Even Pages

Filtering Data

  • Understanding Filtering
  • Applying And Using A Filter
  • Clearing A Filter
  • Creating Compound Filters
  • Multiple Value Filters
  • Creating Custom Filters
  • Using Wildcards

Charting Techniques

  • Understanding Chart Layout Elements
  • Adding A Chart Title
  • Adding Axes Titles
  • Positioning The Legend
  • Showing Data Labels
  • Showing A Data Table
  • Modifying The Axes
  • Showing Gridlines
  • Formatting The Plot Area
  • Adding A Trendline
  • Adding Error Bars
  • Adding A Text Box To A Chart
  • Drawing Shapes In A Chart

Chart Object Formatting

  • Understanding Chart Object Formatting
  • Selecting Chart Elements
  • Using Shape Styles To Format Objects
  • Changing Column Colour
  • Changing Pie Slice Colour
  • Changing Bar Colours
  • Changing Chart Line Colours
  • Using Shape Effects
  • Filling The Chart Area And The Plot Area
  • Filling The Background
  • The Format Dialog Box
  • Using The Format Dialog Box
  • Using Themes

Setting Excel Options

  • Understanding Excel Options
  • Personalising Excel
  • Setting The Default Font
  • Setting Formula Options
  • Understanding Save Options
  • Setting Save Options
  • Setting The Default File Location
  • Setting Advanced Options

Labels And Names

  • Understanding Labels And Names
  • Creating Names Using Text Labels
  • Using Names In New Formulas
  • Applying Names To Existing Formulas
  • Creating Names Using The Name Box
  • Using Names To Select Ranges
  • Pasting Names Into Formulas
  • Creating Names For Constants
  • Creating Names From A Selection
  • Scoping Names To The Worksheet
  • Using The Name Manager
  • Documenting Range Names 47 

Protecting Data

  • Understanding Data Protection
  • Providing Total Access To Cells
  • Protecting A Worksheet
  • Working With A Protected Worksheet
  • Disabling Worksheet Protection
  • Providing Restricted Access To Cells
  • Password Protecting A Workbook
  • Opening A Password Protected Workbook
  • Removing A Password From A Workbook

Data Consolidation

  • Understanding Data Consolidation
  • Consolidating With Identical Layouts
  • ?Creating An Outlined Consolidation
  • Consolidating With Different Layouts

Data Linking

  • Understanding Data Linking
  • Linking Between Worksheets
  • Linking Between Workbooks
  • Updating Links Between Workbooks

Grouping And Outlining

  • Understanding Grouping And Outlining
  • Creating An Automatic Outline
  • Working With An Outline
  • Creating A Manual Group
  • Grouping By Columns

Pivot Tables

  • Understanding Pivot Tables
  • Creating A PivotTable Shell
  • Dropping Fields Into A PivotTable
  • Filtering A PivotTable Report
  • Clearing A Report Filter
  • Switching PivotTable Labels
  • Formatting A PivotTable Report
  • Understanding Slicers
  • Creating Slicers

PivotTable Techniques

  • Using Compound Fields
  • Counting In A PivotTable Report
  • Formatting PivotTable Report Values
  • Working With PivotTable Grand Totals
  • Working With PivotTable Subtotals
  • Finding The Percentage Of Total
  • Finding The Difference From
  • Grouping In PivotTable Reports
  • Creating Running Totals
  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting In A PivotTable

PivotCharts

  • Creating A PivotChart Shell
  • Dragging Fields For The PivotChart
  • Changing The PivotChart Type
  • Using The PivotChart Filter Field Buttons
  • Moving PivotCharts To Chart Sheets

Goal Seeking

  • Understanding Goal Seek Components
  • Using Goal Seek

Solver

  • Understanding How Solver Works
  • Installing The Solver Add-In
  • Setting Solver Parameters
  • Adding Solver Constraints
  • Performing The Solver Operation
  • Running Solver Reports

Summarising And Subtotalling

  • Creating Subtotals
  • Using A Subtotalled Worksheet
  • Creating Nested Subtotals
  • Copying Subtotals
  • Using Subtotals With AutoFilter
  • Creating Relative Names For Subtotals
  • Using Relative Names For Subtotals

Recorded Macros

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving A Document As Macro Enabled
  • Recording A Simple Macro
  • Running A Recorded Macro
  • Relative Cell References
  • Running A Macro With Relative References
  •  Viewing A Macro
  •  Editing A Macro
  •  Assigning A Macro To The Toolbar
  •  Running A Macro From The Toolbar
  •  Assigning A Macro To The Ribbon
  •  Assigning A Keyboard Shortcut To A Macro
  •  Deleting A Macro
  •  Copying A Macro

Recorder Workshop

  • Preparing Data For An Application
  • Recording A Summation Macro
  • Recording Consolidations
  • Recording Divisional Macros
  • Testing Macros
  • Creating Objects To Run Macros
  • Assigning A Macro To An Object

Concluding Remarks 

Call Drake Training on 1300 362 262 for more information

 



Zip File
Download Exercise Files