Microsoft Excel Training - Level 2

Learning Outcomes:    When you have completed this course, you will be able to create more productive workbooks covering comprehensive formulas and function techniques, advanced formatting and charting features and data manipulation tools like goal seeking.

Course Pre-Requisites:     This level assumes the learner has the knowledge required to create, edit, print and chart simple worksheets. Understanding ranges and copying is also important.

Duration:  3 hours

Filling Data

Understanding Filling

Filling A Series & A Growth Series

Filling A Series Backwards

Filling Using Options

Creating & Modifying A Custom Fill List

Deleting  A Custom Fill List

Extracting With Flash Fill

Extracting Dates & Numbers

 

Worksheet Techniques

Inserting & Deleting Worksheets

Copying & Renaming A  Worksheet

Moving A Worksheet

Hiding & Unhiding A Worksheet

Copying A Sheet to Another Workbook

Changing Worksheet Tab Colours

Grouping Worksheets

Hiding & Unhiding Rows & Columns

Freezing Rows & Columns

Splitting Windows

 

Page Set-up

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 & Clearing the Background

Settings Rows as Repeating Print Titles

Clearing Print Titles

Printing Gridlines & Headings

Scaling to a Percentage

Fit to a Specific Number of Pages

 

Applying Borders

Understanding Borders

Applying a Border to a Cell

Applying a Border to a Range

Applying a Bottom & Top Border

Removing Borders

Using the More Borders Command

Drawing Borders &  a Border Grid

Erasing Borders

Formatting the Drawing Pencil

 

Absolute Referencing

Absolute Versus Relative Referencing

Relative Formulas

Creating Absolute Referencing

Creating Mixed References

 

Essential Functions

Key Worksheet Functions

Using IF With Text

Using IF With Numbers

Nesting IF Functions

 

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

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

Documenting Formulas

 

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

 

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

 

The Quick Analysis Tools

Understanding Quick Analysis

Quick Formatting

Quick Charting

Quick Totals

Quick Sparklines

Quick Tables

 

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

 

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

Pasting Values From Formulas

Adding A Data Table

 

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 & Using the Format Pane

Exploding Pie Slices

Changing Individual Bar Colours

Formatting Text

Formatting With Word/Art

Changing Word Art Fill

Changing WordArt Effects