At AWT we offer flexibility and range:

Getting To Know Microsoft Excel
Starting Microsoft Excel
The Excel 2010 Screen
How Microsoft Excel 2010 Works
Using The Ribbon
Using Ribbon Key Tips
Minimising The Ribbon
Understanding The Backstage View
Accessing The Backstage View
Using Short Cut Menus
Understanding Dialog Boxes
Launching Dialog Boxes
Understanding The Quick Access Toolbar
Adding Commands To The QAT
Understanding The Status Bar
Exiting Safely From Excel
 

Creating A New Workbook
Understanding Workbooks
Creating A New Workbook
Typing Text
Typing Numbers
Typing Dates
Typing Formulas
Saving A New Workbook
Easy Formulas
Checking The Spelling
Making Basic Changes
Printing A Worksheet
Safely Closing A Workbook
 

Working With Workbooks
Opening An Existing Workbook
Navigating A Workbook
Navigating Using The Keyboard
Using Go To
Understanding Data Editing
Overwriting Cell Contents
Editing Longer Cells
Clearing Cells
 

Selecting Ranges
Understanding Cells And Ranges
Selecting Contiguous Ranges
Selecting Non-Contiguous Ranges
Using Special Selection Techniques
Selecting Larger Ranges
Selecting Rows
Selecting Columns

Formulas And Functions
Understanding Formulas
Creating Formulas That Add
Creating Formulas That Subtract
Formulas That Multiply And Divide
Understanding Functions
Using The SUM Function To Add
Summing Non-Contiguous Ranges
Calculating An Average
Finding A Maximum Value
Finding A Minimum Value
More Complex Formulas
What If Formulas
 

Copying Data
Understanding Copying In Excel
Using Fill For Quick Copying
Copying From One Cell To Another
Copying From One Cell To A Range
Copying From One Range To Another
Copying Relative Formulas
Copying To A Non-Contiguous Range
 

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

Font Formatting
Understanding Font Formatting
Working With Live Preview
Changing Fonts
Changing Font Size
Growing And Shrinking Fonts
Making Cells Bold
Italicising Text
Underlining Text
Changing Font Colours
Changing Background Colours
Using The Format Painter
 

Cell Alignment
Understanding Cell Alignment
Aligning Right
Aligning To The Centre
Aligning Left
Rotating Text
Indenting Cells

Row And Column Formatting
Approximating Column Widths
Setting Precise Columns Widths
Setting The Default Column Width
Approximating Row Height
Setting Precise Row Heights
Hiding Rows And Columns
Unhiding Rows And Columns
 

Number Formatting
Understanding Number Formatting
Applying General Formatting
Formatting As Currency
Formatting Percentages
Formatting As Fractions
Formatting As Dates
Using The Thousands Separator
 

Printing
Understanding Printing
Previewing Before You Print
Selecting A Printer
Printing A Range
Printing An Entire Workbook
Specifying The Number Of Copies
The Print Options
 

Creating Charts
Understanding The Charting Process
Choosing The Chart Type
Creating A New Chart
Working With An Embedded Chart
Resizing A Chart
Dragging A Chart
Printing An Embedded Chart
Creating A Chart Sheet
Changing The Chart Type
Changing The Chart Layout
Changing The Chart Style

Printing A Chart Sheet
Embedding A Chart Into A Worksheet

Learning Outcome:
 navigate your way around Microsoft Excel 2010

 create a new workbook

 open and navigate within workbooks and worksheets

 understand and work with ranges in a worksheet

 understand, create and work with formulas and functions used to perform calculations

 copy and paste data in Excel

 understand and use formula cell referencing to create more complex formulas

 use font formatting techniques to greatly enhance the look of a worksheet

 align the contents of cells in a number of ways

 format rows and columns in a worksheet

 understand and use the number formatting features in Excel

 print your workbook data

 create effective charts in Microsoft Excel

Prerequisites: Little or no knowledge of the software is required but a general understanding of the Windows environment is beneficial

Training Options:

Classroom: One-on-one tuition, Course Length: 6hrs, Cost: $570

Self Paced, Distance Learning: Learn in your own time, at your own pace. All you require is a computer and email address. We supply step by step lessons and email support

Cost: $285

Filling Data
Understanding Filling
Filling A Series
Filling A Growth Series
Filling A Series Backwards
Filling Using Options
Creating A Custom Fill List
Modifying A Custom Fill List
Deleting A Custom Fill List
 

Moving Data
Understanding Moving In Excel
Moving Cells And Ranges
Moving Data To Other Worksheets
Moving Data To Other Workbooks
 

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
 

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

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 Options
Using The More Borders Option
 

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 The Print Area
Clearing The Print Area
Inserting Page Breaks
Using Page Break Preview
Removing Page Breaks
Setting A Background
Clearing The Background
Settings Rows 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

Working With A Worksheet
Understanding Worksheets
Changing Worksheet Views
Worksheet Zooming
Viewing The Formula Bar
Viewing The Gridlines
Viewing The Ruler
Inserting Cells
Deleting Cells
Inserting Columns
Inserting Rows
Deleting Rows And Columns
Switching Between Worksheets
 

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
 

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

Learning Outcome:
 use the fill operations available to fill a data series, and move the contents of cells and
ranges within and between workbooks
 work with logical functions in Excel
 use a range of formula techniques
 apply a range of number formatting techniques to data
 apply conditional formatting to ranges in a worksheet
 apply borders to cells and ranges
 apply a variety of page setup techniques and work with various elements of a worksheet
 use a range of find and replace techniques
 sort and filter data in a list in a worksheet
 use a range of techniques to enhance charts

Prerequisites: Level 2 assumes the learner has the skills and knowledge required to
create, edit, print and chart simple worksheets. Understanding ranges and the copying
process is important. It would also be beneficial to have a general understanding of personal
computers and the Windows operating system environment

Training Options:

Classroom: One-on-one tuition, Course Length: 6hrs, Cost: $600

Self Paced, Distance Learning: Learn in your own time, at your own pace. All you require is a computer and email address. We supply step by step lessons and email support

Cost: $300

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

Learning Outcome:
 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

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

Training Options:

Classroom: One-on-one tuition, Course Length: 6hrs, Cost: $720

Self Paced, Distance Learning: Learn in your own time, at your own pace. All you require is a computer and email address. We supply step by step lessons and email support

Cost: $360