<< Back to Courses

Excel 2013 Level 3


Cost: Contact us for quote.   Duration: 1 day


Contact Us to book.

Overview

The skills and knowledge acquired in Microsoft Excel 2013 Level 3 are sufficient to be able to protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and advanced PivotTable techniques, macros, and much more.

Learning Outcomes

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

  •  modify Excel options
  •  protect data in worksheets and workbooks
  •  import data into Excel and export data from Excel
  •  use data linking to create more efficient workbooks
  •  group cells and use outlines to manipulate the worksheet
  •  create summaries in your spreadsheets using subtotals
  •  use the Data Consolidation feature to combine data from several workbooks into one
  •  create, use and modify data tables
  •  create and work with scenarios and the Scenario Manager
  •  use data linking to create more efficient workbooks
  •  construct and operate PivotTables using some of the more advanced techniques
  •  work with tables in Microsoft Excel
  •  use advanced filters to analyse data in a list
  •  use a variety of data validation techniques
  •  use a range of Information functions
  •  use a range of text functions
  •  create and use a range of controls in a worksheet
  •  share workbooks with other users
  •  create recorded macros in Exce

 Prerequisites

Microsoft Excel 2013 Level 3 assumes some knowledge of the software as well as a general

understanding of personal computers and the Windows operating system environment.

Duration

Scheduled classroom training (1 Day)


Contents

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

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

Importing And Exporting

Understanding Data Importing

Importing From An Earlier Version

Understanding Text File Formats

Importing Tab Delimited Text

Importing Comma Delimited Text

Importing Space Delimited Text

Importing Access Data

Working With Connected Data

Unlinking Connections

Exporting To Microsoft Word

Exporting Data As Text

Inserting A Picture

Modifying An

Inserted Picture

Data Linking

Understanding Data Linking

Linking Between Worksheets

Linking Between Workbooks

Updating Links Between Workbooks

Grouping And Outlining

Understanding Grouping And

Outlining

Creating An Automatic Outline

Working With An Outline

Creating A Manual Group

Grouping By Columns

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 Consolidation

Understanding Data Consolidation

Consolidating With Identical Layouts

Creating A Linked Consolidation

Consolidating From Different Layouts

Consolidating Data Using The SUM

Function

Data Tables

Understanding Data Tables

And What-If Models

Using A Simple What-If Model

Creating A One-Variable Table

Using One-Variable Data Tables

Creating A Two-Variable Data Table

Scenarios

Understanding Scenarios

Creating A Default Scenario

Creating Scenarios

Using Names In Scenarios

Displaying Scenarios

Creating A Scenario Summary Report

Merging Scenarios

PivotCharts

Inserting A PivotChart

Defining The PivotChart Structure

Changing The PivotChart Type

Using The PivotChart Filter Field Buttons

Moving PivotCharts To Chart Sheets

PivotTable Features

Using Compound Fields

Counting In A PivotTable

Formatting PivotTable Values

Working With PivotTable Grand Totals

Working With PivotTable Subtotals

Finding The Percentage Of Total

Finding The Difference From

Grouping In A PivotTable

Creating Running Totals

Creating Calculated Fields

Providing Custom Names

Creating Calculated Items

PivotTable Options

Sorting In A PivotTable

Worksheet Tables

Understanding Tables

Creating A Table From Scratch

Working With Table Styles

Inserting Table Columns

Removing Tab le 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

Advanced Filters

Understanding Advanced Filtering

Using An Advanced Filter

Extracting Records With Advanced Filter

Using Formulas In Criteria

Understanding Database Functions

Using Database Functions

Using DSUM

Using The DMIN Function

Using The DMAX Function

Using The DCOUNT Function

Validating Data

Understanding Data Validation

Creating A Number Range Validation

Testing A Validation

Creating An Input Message

Creating An Error Message

Creating A Drop Down List

Using Formulas As Validation Criteria

Circling Invalid Data

Removing Invalid Circles Copying

Validation Settings

Information Functions

Understanding Information Functions

Using The CELL Function

Using The ISBLANK Function

Using The ISERR Function

Using The ISODD And ISEVEN Functions

Using The ISNUMBER And ISTEXT Functions

Using The TYPE Function

Text Functions

Understanding Text Functions

Using The PROPER Function

Using The UPPER And LOWER

Functions

Using The CONCATENATE Function

Using The LEFT And RIGHT Functions

Using The MID Function

Using The LEN Function

Using The SUBSTITUTE Function Using

The T Function

Using The TEXT Function

Using The VALUE Function

Controls

Understanding Types Of Controls

Understanding How Controls Work

Preparing A Worksheet For Controls

Adding A Combo Box Control

Changing Control Properties

Using The Cell Link To Display The Selection

Adding A List Box Control

Adding A Scroll Bar Control

Adding A Spin Button Control

Adding Option Button Controls

Adding A Group Box Control

Adding A Check Box Control

Protecting A Worksheet With Controls

Sharing Workbooks

Sharing Workbooks

Via The Network

Sharing Workbooks Via SkyDrive

Saving To SkyDrive

Sharing Documents

Opening Shared Workbooks

Enabling Tracked Changes

Accepting Or Rejecting Changes

Disabling Tracked Changes

Adding Worksheet Comments

Navigating Worksheet Comments Editing

Worksheet Comments

Deleting Comments

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



Zip File
Download Exercise Files