Code: 
MSEXCEL2
Duration: 
1-2 Day(s)
Price: 
198-730

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  
Leicester 199122 Dec 17
Glasgow 362129 Nov 17
London 362129 Nov 17
Leeds 362124 Nov 17
London 362130 Oct 17
Glasgow 730230 Oct 17
London 362125 Oct 17
Milton Keynes 730224 Oct 17
Leeds 362123 Oct 17
London 362128 Sep 17
Glasgow 362127 Sep 17
Leeds 362122 Sep 17
London 362121 Sep 17
Edinburgh 730229 Aug 17
Leeds 362125 Aug 17
London 362123 Aug 17
London 730221 Aug 17
Birmingham 730221 Aug 17
London 362131 Jul 17
Glasgow 730227 Jul 17
Leeds 362126 Jul 17
Birmingham 362126 Jul 17
Glasgow 362126 Jul 17
Manchester 362126 Jul 17
London 362125 Jul 17
Leicester 199125 Jul 17
Manchester 730224 Jul 17
London 362130 Jun 17
Leeds 362128 Jun 17
London 730228 Jun 17
Birmingham 362123 Jun 17
Birmingham 730221 Jun 17
London 730230 May 17
Leicester 199124 May 17
London 362123 May 17
Reading 730224 Apr 17
Reading 268121 Apr 17
Birmingham 268121 Apr 17
Coventry 268121 Apr 17
Bristol 268121 Apr 17
London 362121 Apr 17
Leicester 268121 Apr 17
Slough 268121 Apr 17
Nottingham 268121 Apr 17
Manchester 362121 Apr 17
Leicester 199124 Mar 17
London 362124 Mar 17
Birmingham 198123 Mar 17
Bristol 198123 Mar 17
Coventry 198123 Mar 17
Manchester 730223 Mar 17
Leicester 198121 Mar 17
Slough 198121 Mar 17
Nottingham 198121 Mar 17
Reading 198121 Mar 17
Birmingham 198113 Mar 17
Birmingham 198113 Mar 17
Edinburgh 730228 Feb 17
London 362128 Feb 17
Edinburgh 730228 Feb 17
Edinburgh 730228 Feb 17
Edinburgh 362127 Feb 17
Edinburgh 362127 Feb 17
Edinburgh 362127 Feb 17
London 730227 Feb 17
Leeds 362127 Feb 17
Reading 268124 Feb 17
Bristol 268124 Feb 17
Nottingham 268124 Feb 17
Leicester 268124 Feb 17
Birmingham 268124 Feb 17
Coventry 268124 Feb 17
Slough 268124 Feb 17
Coventry 198123 Feb 17
Coventry 198123 Feb 17
Birmingham 198123 Feb 17
Milton Keynes 730223 Feb 17
Bristol 198123 Feb 17
Coventry 198123 Feb 17
Slough 198121 Feb 17
Slough 198121 Feb 17
Leicester 198121 Feb 17
Slough 198121 Feb 17
Reading 198221 Feb 17
Reading 198221 Feb 17
Reading 198221 Feb 17
Nottingham 198121 Feb 17
Nottingham 198121 Feb 17
Nottingham 198121 Feb 17
Bristol 198120 Feb 17
Bristol 198120 Feb 17
Reading 198114 Feb 17
Reading 198114 Feb 17
Leicester 198114 Feb 17
Leicester 198114 Feb 17
Milton Keynes 730208 Feb 17
Milton Keynes 730208 Feb 17