Code: 
MSEXCEL2
Duration: 
1-2 Day(s)
Price: 
190-751

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  
Manchester 751222 Jan 18
Leicester 199122 Dec 17
London 372129 Nov 17
Glasgow 372129 Nov 17
Leeds 372124 Nov 17
London 751223 Nov 17
London 372120 Nov 17
Reading 751220 Nov 17
London 395131 Oct 17
London 372130 Oct 17
Glasgow 751230 Oct 17
Manchester 751226 Oct 17
London 372125 Oct 17
Milton Keynes 751224 Oct 17
Leeds 372123 Oct 17
London 751223 Oct 17
London 372120 Oct 17
Leicester 199120 Oct 17
London 395120 Oct 17
Reading 229 Sep 17
London 295128 Sep 17
London 295128 Sep 17
London 295128 Sep 17
London 295128 Sep 17
Glasgow 372127 Sep 17
Leeds 372122 Sep 17
London 372121 Sep 17
London 751221 Sep 17
Manchester 372120 Sep 17
London 395120 Sep 17
London 751219 Sep 17
Edinburgh 751230 Aug 17
Edinburgh 751229 Aug 17
London 198124 Aug 17
Manchester 751224 Aug 17
Reading 198124 Aug 17
London 223 Aug 17
London 372123 Aug 17
Slough 198122 Aug 17
Milton Keynes 198122 Aug 17
Birmingham 751221 Aug 17
Bristol 751221 Aug 17
Leeds 372121 Aug 17
London 751221 Aug 17
London 198127 Jul 17
London 751227 Jul 17
Glasgow 751227 Jul 17
Reading 198127 Jul 17
Glasgow 372126 Jul 17
Manchester 372126 Jul 17
Birmingham 372126 Jul 17
Slough 198125 Jul 17
Leicester 199125 Jul 17
London 372125 Jul 17
Milton Keynes 198125 Jul 17
Leeds 751224 Jul 17
Manchester 751224 Jul 17
London 395121 Jul 17
London 372120 Jul 17
Edinburgh 372119 Jul 17
London 751219 Jul 17
Cardiff 198119 Jul 17
Bristol 198119 Jul 17
London 198129 Jun 17
Sheffield 198129 Jun 17
Leeds 198129 Jun 17
London 751229 Jun 17
Milton Keynes 198129 Jun 17
Leeds 372128 Jun 17
London 751228 Jun 17
London 751228 Jun 17
Liverpool 198127 Jun 17
Reading 372127 Jun 17
Manchester 198127 Jun 17
Birmingham 372123 Jun 17