<< Back to Courses

Excel 2013 Level 2


Cost: Contact us for quote.   Duration: 1 day


Contact Us to book.

Overview

The skills and knowledge acquired in Microsoft Excel 2013 - Level 2 are designed for existing users of Excel who would like to expand their knowledge and learn more of the techniques associated with creating better and more productive workbooks. It covers formulas and function techniques, advanced formatting, setting complex printing options, using advanced charting features and data manipulation tools such as Pivot Tables and goal seeking. A large range of functions are examined which cover a broad occupational spectrum and requirement.

Learning Outcomes

At the completion of this course you should be able to:

  • use filling techniques
  • 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
  • use the dozen or so most popular and common worksheet functions
  • create more complex formulas and functions
  • use a variety of financial functions
  • use the date and time functions to perform calculations
  • create and use defined names in a workbook
  • apply a range of number formatting techniques to worksheet cells
  • apply conditional formatting to ranges in a worksheet
  • use goal seeking to determine the values required to reach a desired result
  • understand and use Excel's Quick Analysis tools
  • work with tables in Microsoft Excel
  • understand and create simple PivotTables
  • use a range of elements and features to enhance charts
  • select and change the format of objects in a chart

Prerequisites

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

Duration

Scheduled classroom training (1 Day)

Contents

Fill Techniques

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

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

Moving 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

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

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

 

 

Essential Functions

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

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

Financial Functions

Understanding Financial Functions

Using PMT

Using FV

Using NPV

Using PV

Using RATE

Using EFFECT

Using NOMINAL

Date And Time Functions

Understanding Date And Time Functions

Using NOW

Using HOUR And MINUTE

Using TODAY

Calculating Future Dates

Using DATE

Using Calendar Functions

Using WEEKDAY

Using WEEKNUM

Using WORKDAY

Using EOMONTH

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

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

Goal Seeking

Understanding Goal Seeking

Using Goal Seek

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

Pivot Tables

Understanding Pivot Tables

Recommended Pivot Tables

Creating Your Own PivotTable

Defining The PivotTable Structure

Filtering A PivotTable

Clearing A Report Filter

Switching PivotTable Fields

Formatting A PivotTable

Understanding Slicers

Creating Slicers

 

Chart Elements

Understanding Chart Elements

Adding A Chart Title

Adding Axes Titles

Repositioning The Legend

Showing Data Labels

Showing Gridlines

Formatting The Chart Plot Area

Adding A Trendline

Adding Error Bars

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 The Format Pane

Using The Format Pane

Exploding Pie Slices

Changing Individual Bar Colours

Formatting Text

Formatting With WordArt

Changing WordArt Fill

 



Zip File
Download Exercise Files