Support

Microsoft Excel 2010 Course | Level 3

3 sessions available starting from $900.00

Subject: Microsoft Excel

Ages: All ages

Skill level: All difficulty levels

Course Code: DT203
Course Duration: 2 days

This course aims to provide skills and knowledge which will allow the attendee to create lookup functions, set Excel working options, enhance charts, protect worksheet data, perform advanced data operations using summarising, PivotTables, data consolidations, goal seeking, and Solver, and create and use macros.

Skills Gained

* use a range of lookup and reference functions
* modify Excel options
* customise the formatting of charts in Excel
* create and use labels and names in a workbook
* protect data in worksheets and workbooks
* create summaries in your spreadsheets using subtotals
* use data linking to create more efficient workbooks
* use the Data Consolidation feature to combine data from several workbooks into one
* understand...

see more

Course Code: DT203
Course Duration: 2 days

This course aims to provide skills and knowledge which will allow the attendee to create lookup functions, set Excel working options, enhance charts, protect worksheet data, perform advanced data operations using summarising, PivotTables, data consolidations, goal seeking, and Solver, and create and use macros.

Skills Gained

* use a range of lookup and reference functions
* modify Excel options
* customise the formatting of charts in Excel
* create and use labels and names in a workbook
* protect data in worksheets and workbooks
* create summaries in your spreadsheets using subtotals
* use data linking to create more efficient workbooks
* use the Data Consolidation feature to combine data from several workbooks into one
* understand and create simple PivotTable reports
* construct and operate PivotTables using some of the more advanced techniques
* create and edit a PivotChart
* use goal seeking to determine the values required to reach a desired result
* group cells and use outlines to manipulate the worksheet
* use Solver to solve more complex and intricate problems
* create recorded macros in Excel
* use the macro recorder to create a variety of macros

Course Modules

* 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

* 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

* 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

* 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

* 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

* 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

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

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

* 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

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

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

* 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

Who Should Attend

This course is designed for users who need to use some of the more advanced features of Microsoft Excel 2010

Prerequisites

This course assumes a good understanding of spreadsheets using Microsoft Excel 2010 and how to create, print and chart workbooks. It would also be beneficial to have a general understanding of personal computers and the Windows operating system environment.

see less

Choose from 3 available sessions:

When

Please contact us for upcoming dates and/or more information about this class.

Where

  • 5/159-175 Church St
    Parramatta, NSW 2150

Price: $900.00

Type

  • Class / Group Sessions

When

Please contact us for upcoming dates and/or more information about this class.

Where

  • 5/159-175 Church St
    Parramatta, NSW 2150

Price: $900.00

Type

  • Class / Group Sessions

When

Please contact us for upcoming dates and/or more information about this class.

Where

  • 5/159-175 Church St
    Parramatta, NSW 2150

Price: $900.00

Type

  • Class / Group Sessions



Reviews

John Weber
Good experience!
  • 5/5 stars

I took CCNA class in MIS. It was pretty good, the course was hands-on and teacher was instructional. I was also...
John Weber

see all reviews »


My Other Listings

Microsoft Dynamics Training Course |Applications in CRM 4.0
Microsoft Dynamics Training Course |Applications in CRM 4.0
5 sessions available starting from $2,490.00
SQL Server 2008 Training Course | For the Experienced Oracle Database Administrator
SQL Server 2008 Training Course | For the Experienced Oracle Database Administrator
3 sessions available starting from $2,720.00
Java 2.0 Programming Course
Java 2.0 Programming Course
6 sessions available starting from $1,800.00

see all listings »


Found In