Microsoft Excel Intermediate Course

At the completion of this Microsoft Excel Intermediate training course in Perth, participants will be able to:

• use the fill operations available to fill a data series
• use common worksheet functions
• create more complex formulas and functions
• create and use defined names in a workbook
• use goal seeking to determine the values required to reach a desired result
• use a range of techniques to work with worksheets
• apply a variety of page setup techniques
• apply borders to cells and ranges in a worksheet
• apply a range of number formatting techniques to worksheet cells
• apply conditional formatting to ranges in a worksheet
• understand and use Excel’s Quick Analysis tools
• create and work with tables
• use a range of elements and features to enhance charts
• select and change the format of objects in a chart

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
• Extracting With Flash Fill
• More Complex Flash Fill Extractions
• Extracting Dates and Numbers
• Challenge Exercise
• Challenge Exercise Data

Essential Functions

• Key Worksheet Functions
• Using IF With Text
• Using IF With Numbers
• Nesting IF Functions
• The CHOOSE Function
• The LOOKUP Function
• Using Counting Functions
• The ROUND Function
• Rounding Up and Rounding Down
• Manipulative Functions
• The MOD Function
• The TODAY Function
• The NOW Function
• The DATE Function
• The PMT Function
• Challenge Exercise
• Challenge Exercise Sample

Complex Formulas

• Scoping a Formula
• Long-Hand Formulas
• Preparing for Complex Formulas
• Creating the Base Formula
• Adding More Operations
• Editing a Complex Formula
• Adding More Complexity
• Copying Nested Functions
• Switching to Manual Recalculation
• Pasting Values From Formulas
• Documenting Formulas
• Challenge Exercise
• Challenge Exercise Sample

Defined Names

• Understanding Defined Names
• Defining Names From Worksheet Labels
• Using Names in Typed Formulas
• Applying Names to Existing Formulas
• Creating Names Using the Name Box
• Using Names to Select Ranges
• Pasting Defined Names Into Formulas
• Defining Names for Constant Values
• Creating Names From a Selection
• Scoping Names to a Worksheet
• Using the Name Manager
• Documenting Defined Names
• Challenge Exercise
• Challenge Exercise Sample

Goal Seeking

• Understanding Goal Seeking
• Using Goal Seek
• Challenge Exercise
• Challenge Exercise Sample
• Worksheet Techniques
• Inserting and Deleting Worksheets
• Copying a Worksheet
• Renaming a Worksheet
• Moving a Worksheet
• Hiding a Worksheet
• Unhiding a Worksheet
• Copying a Sheet to Another Workbook
• Changing Worksheet Tab Colours
• Grouping Worksheets
• Hiding Rows and Columns
• Unhiding Rows and Columns
• Freezing Rows and Columns
• Splitting Windows
• Challenge Exercise
• Challenge Exercise Sample

Page Setup

• Strategies for Printing Worksheets
• 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
• Challenge Exercise
• Challenge Exercise Sample

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 Command
• Using the More Borders Command
• Drawing Borders
• Drawing a Border Grid
• Erasing Borders
• Formatting the Drawing Pencil
• Challenge Exercise
• Challenge Exercise Sample

Number Formatting Techniques

• Applying Alternate Currencies
• Applying Alternate Date Formats
• Formatting Clock Time
• Formatting Calculated Time
• Understanding Number Formatting
• Understanding Format Codes
• Creating Descriptive Custom Formats
• Custom Formatting Large Numbers
• Custom Formatting for Fractions
• Padding Numbers Using Custom Formatting
• Aligning Numbers Using Custom Formats
• Customising the Display of Negative Values
• Challenge Exercise
• Challenge Exercise Sample

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
• Challenge Exercise
• Challenge Exercise Sample

The Quick Analysis Tools

• Understanding Quick Analysis
• Quick Formatting
• Quick Charting
• Quick Totals
• Quick Sparklines
• Quick Tables
• Challenge Exercise
• Challenge Exercise Sample
• Worksheet Tables
• Understanding Tables
• Creating a Table From Scratch
• Working With Table Styles
• Inserting Table Columns
• Removing Table Columns
• Converting a Table to a Range
• Creating a Table From Data
• Inserting or Deleting Table Records
• Removing Duplicates
• Sorting Tables
• Filtering Tables
• Renaming a Table
• Splitting a Table
• Deleting a Table
• Challenge Exercise
• Challenge Exercise Workspace

Chart Elements

• Understanding Chart Elements
• Adding a Chart Title
• Adding Axes Titles
• Repositioning the Legend
• Showing Data Labels
• Showing Gridlines
• Formatting the Chart Area
• Adding a Trendline
• Adding Error Bars
• Adding a Data Table
• Challenge Exercise
• Challenge Exercise Sample

Chart Object Formatting

• Understanding Chart Formatting
• Selecting Chart Objects
• Using Shape Styles
• Changing Column Colour Schemes
• Changing the Colour of a Series
• Changing Line Chart Colours
• Using Shape Effects
• Colouring the Chart Background
• Understanding the Format Pane
• Using the Format Pane
• Exploding Pie Slices
• Changing Individual Bar Colours
• Formatting Text
• Formatting With WordArt
• Changing WordArt Fill
• Changing WordArt Effects
• Challenge Exercise
• Challenge Exercise Sample