Code: 
MSEXCEL2
Duration: 
1-2 Day(s)
Price: 
$395-416

Overview

The skills and knowledge acquired in Microsoft Excel 2010 Intermediate are sufficient to be able to use and operate the software at an efficient level

Audience

Microsoft Excel 2010 Intermediate is designed for users who are keen to extend their understanding and knowledge of the software

Skills Gained

At the completion of Microsoft Excel 2010 Intermediate you should be able to

  • understand and use formula cell referencing to create more complex formulas
  • use a range of formula techniques and use a range of logical functions
  • use a range of lookup and reference functions
  • use the mathematical functions in Excel
  • use the date and time functions to perform calculations
  • use a range of text and information functions
  • apply a range of number formatting techniques to data
  • apply borders to cells and ranges
  • apply a variety of page setup techniques
  • create and work with headers and footers
  • use a range of find and replace techniques
  • sort data in a list in a worksheet
  • filter data in a table and use Advanced Filters to view or extract matching records from a list of data
  • use a range of techniques to enhance charts
  • modify Excel options
  • protect data in worksheets and workbooks
  • use data linking to create more efficient workbooks
  • group cells and use outlines to manipulate the worksheet
  • perform What-If Analysis on a range of data using Data Tables
  • create and work with customised views
  • use comments to provide additional context to your worksheet data
  • use the Formula Auditing tools to find and fix formula errors
  • analyse your data and visually enhance your findings using Conditional Formatting techniques
  • manage your data in a table format
  • apply built-in or customised cell styles to format worksheet data
  • apply formatting, copy formatting or clear formatting on worksheet data
  • open and arrange multiple workbooks

Prerequisites

Microsoft Excel 2010 Intermediate assumes the delegate has completed Excel 2010 Fundamentals or has equivalent knowledge

Course Specifics

Course Outline

Formula Referencing

  • Absolute Versus Relative Referencing
  • Relative Formulas
  • Problems With Relative Formulas
  • Creating Absolute References
  • Creating Mixed References

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
  • Pasting Formulas As Pictures

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

Maths Functions

  • Understanding Maths Functions
  • Using SQRT
  • Using ABS
  • Using INT
  • Using TRUNC
  • Using ROUND
  • Using ROUNDDOWN And ROUNDUP
  • Using ODD And EVEN
  • Using CEILING
  • Using MROUND
  • Using PRODUCT
  • Using SUMIF
  • Using SUMIFS
  • Using SUMPRODUCT

Date and Time Functions

  • Understanding Date And Time Functions
  • Using NOW
  • Using HOUR And MINUTE
  • Using TODAY
  • Calculating Future Dates
  • Using DATE
  • Using Calendaring Functions
  • Using WEEKDAY
  • Using WEEKNUM
  • Using WORKDAY
  • Using EOMONTH

Text Functions

  • Understanding Text Functions
  • Using PROPER
  • Using UPPER And LOWER
  • Using CONCATENATE
  • Using LEFT And RIGHT
  • Using MID
  • Using LEN
  • Using SUBSTITUTE
  • Using T
  • Using TEXT
  • Using VALUE

Information Functions

  • Understanding Information Functions
  • Using CELL
  • Using ISBLANK
  • Using ISERR
  • Using ISODD And ISEVEN
  • Using ISNUMBER And ISTEXT
  • Using TYPE

Number Formatting Techniques

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

Applying Borders

  • Understanding Borders
  • Applying A Border To A Cell
  • Applying A Border To A Range
  • Applying A Bottom Border
  • Applying Top And Bottom Borders
  • Removing Borders
  • The More Borders Option
  • Using The More Borders Option
  • Drawing and Erasing Borders

Page Setup

  • Understanding Page Layout
  • Using Built In Margins
  • Setting Custom Margins
  • Changing Margins By Dragging
  • Centring On A Page
  • Changing Orientation
  • Specifying The Paper Size
  • Setting A Print Area
  • Clearing A Print Area
  • Inserting Page Breaks
  • Using Page Break Preview
  • Removing Page Breaks
  • Setting A Background
  • Clearing The Background
  • Settings Rows As Repeating Print Titles
  • Settings Columns As Repeating Print Titles
  • Clearing Print Titles
  • Printing Gridlines
  • Printing Headings
  • Scaling To A Percentage
  • Fit To A Specific Number Of Pages
  • Strategies For Printing Larger Worksheets
  • The Page Setup Header And Footer Tab

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

Finding And Replacing

  • Understanding Find And Replace Operations
  • Finding Text
  • Finding Cell References In Formulas
  • Replacing Values
  • Using Replace To Change Formulas
  • Replacing Within A Range
  • Finding Formats
  • Finding Constants Using Go To Special
  • Finding Formulas Using Go To Special
  • Finding The Current Region
  • Finding The Last Cell

Sorting Data

  • Understanding Lists
  • Performing An Alphabetical Sort
  • Performing A Numerical Sort
  • Sorting On More Than One Column
  • Sorting Numbered Lists
  • Sorting By Rows

Filtering Data

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

Advanced Filtering

  • Understanding Advanced Filtering
  • Using an Advanced Filter
  • Extracting Records Using Advanced Filters
  • Using Formulas in Criteria
  • Understanding Database Functions
  • Using Database Functions
  • Using DSUM
  • Using DMIN
  • Using DMAX
  • Using DCOUNT

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
  • Showing 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

Validating Data

  • Understanding Data Validation
  • Creating a Number Range Validation
  • Testing Data Validation
  • Creating an Input Message
  • Creating an Error Message
  • Creating Drop-Down Lists
  • Using Formulas As Validation Criteria
  • Circling Invalid Data
  • Removing Invalid Data Circles
  • Copying Validation Settings

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 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

Data Tables

  • Data Table Components
  • Using a Simple What-If Model
  • Creating A One-Variable Table
  • Using One-Variable Data Tables
  • Creating A Two-Variable Data Table

Custom Views

  • Understanding Custom Views
  • Adding a Custom View
  • Creating a Custom View
  • Working with Custom Views

Working with Comments

  • Understanding Comments
  • Adding Worksheet comments
  • Managing Worksheet Comments
  • Printing Comments
  • Deleting Comments

Formula Auditing

  • Understanding Tracing Precedents
  • Understanding Tracing Dependents
  • Tracing Precedents and Dependents
  • Showing Formulas and Cell Arguments
  • Common Error Messages
  • Understanding Error Checking
  • Checking for Errors
  • Tracing Errors
  • Evaluating Formulas
  • Using the Watch Window
  • Dealing with Circular References

Conditional Formatting

  • Understanding Conditional Formatting
  • Highlighting Cells Containing Values
  • Highlighting Cells Containing Text
  • Highlighting Duplicate Values
  • Using Top and Bottom Rules
  • Using Data Bars
  • Using Colour Scales
  • Using Icon Sets
  • Creating Custom Rules
  • The Conditional Formatting Rules Manager
  • Managing Rules
  • Clearing Rules
  • Conditional Formatting - Examples and Guidelines

Table Formatting

  • Understanding Table Formatting
  • Applying a Table Style
  • Table Styles and Table Options
  • Filtering a Table
  • Sorting a Table
  • Converting a Table to a Range of Data

Cell Styles

  • Understanding Cell Styles
  • Applying Cell Styles
  • Creating Custom Styles
  • Managing Styles

Formatting Techniques

  • Copying Formats
  • Formatting Cells
  • The Format Cells Dialog Box
  • Multiple Workbooks
  • Working with Multiple Workbooks
  • Viewing Workbooks Side by Side
  • Arranging Multiple Workbooks

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

a Location Cost Days Date  
Christchurch $3391Onsite
Canberra $416128 Sep 17
Canberra $395128 Sep 17
Canberra $416128 Sep 17
Brisbane $416126 Sep 17
Sydney $416126 Sep 17
Brisbane $416126 Sep 17
Sydney $416126 Sep 17
Brisbane $395126 Sep 17
Sydney $395126 Sep 17
Perth $416121 Sep 17
Perth $416121 Sep 17
Adelaide $395119 Sep 17
Adelaide $416119 Sep 17
Adelaide $416119 Sep 17
Sydney 218 Sep 17
Canberra $395113 Sep 17
Canberra $416113 Sep 17
Canberra $416113 Sep 17
Perth $416130 Aug 17
Perth $416130 Aug 17
Brisbane $416130 Aug 17
Sydney $416130 Aug 17
Brisbane $395130 Aug 17
Sydney $395130 Aug 17
Brisbane $416130 Aug 17
Sydney $416130 Aug 17
Canberra $395129 Aug 17
Canberra $416129 Aug 17
Canberra $416129 Aug 17
Adelaide $416122 Aug 17
Adelaide $416122 Aug 17
Adelaide $395122 Aug 17
Adelaide $416110 Aug 17
Adelaide $395110 Aug 17
Adelaide $416110 Aug 17
Adelaide 231 Jul 17
Sydney $416127 Jul 17
Sydney $395127 Jul 17
Sydney $416127 Jul 17
Melbourne 227 Jul 17
Adelaide $395126 Jul 17
Adelaide $416126 Jul 17
Adelaide $416126 Jul 17
Brisbane $416125 Jul 17
Brisbane $416125 Jul 17
Brisbane $395125 Jul 17
Perth $416120 Jul 17
Perth $416120 Jul 17
Canberra $416118 Jul 17
Canberra $395118 Jul 17
Canberra $416118 Jul 17
Adelaide $395111 Jul 17
Adelaide $416111 Jul 17
Adelaide $416111 Jul 17
Christchurch $416110 Jul 17
Christchurch $416110 Jul 17
Christchurch $395110 Jul 17
Darwin $395110 Jul 17
Melbourne $395110 Jul 17
Darwin $416110 Jul 17
Melbourne $416110 Jul 17
Darwin $416110 Jul 17
Melbourne $416110 Jul 17
Brisbane $395129 Jun 17
Brisbane $416129 Jun 17
Brisbane $416129 Jun 17
Adelaide $395128 Jun 17
Adelaide $416128 Jun 17
Adelaide $416128 Jun 17
Sydney $395127 Jun 17
Perth $416127 Jun 17
Perth $416127 Jun 17
Sydney $416127 Jun 17
Sydney $416127 Jun 17