MS Excel 2016 – Level 3

Request info

Excel 2016 Training

Course Description

In this 1 day course you will build on the previous Excel courses, introducing more advanced topics.

Who is this course for:

Delegates who wish to learn more about some of the additional advanced features of Excel, including PivotTables, Data Validation and Advanced Formulas.

 

Delegate Requirements: Before starting this course, students are should be able to do the following:

  • Use Names Ranges
  • Calculate across worksheets/workbooks
  • Create and use Charts
  • Use Basic Sort and Filters
  • Work with Excel Tables
  • Apply Conditional Formatting
  • Protect Views
  • Track Changes
  • Merge Workbooks

 

Objectives

Upon successful completion of this course, students will be able to:

  • Validate Data
  • Use Advanced Formulas
  • Use Auditing Tools
  • Create and manipulate PivotTables and PivotCharts
  • Work with Templates
  • Insert Illustrations

 

Course Content:

Module 1: Work with Data Validation

Number Validation

Data List Validation

Message Prompts and Alerts

Conditional Data Validation

Data Validation Errors

 

Module 2: Advanced Formulas

Statistical Functions to include MAXIFS, MINIFS

Working with Text Functions

Calculating with Financial Functions

Date and Time Functions

Database Functions

 

Module 3: Auditing a Worksheet

Display formulas in worksheet cells

Show precedent and dependent cells

Finding and Resolving Errors

Using the Watch Window

Evaluating a Formula

 

Module 4: Creating PivotTables, PivotCharts and Slicers

Creating a PivotTable

Amending the fields in a PivotTable

Updating the PivotTable

Adding Calculations to a PivotTable

Add Grouping to a PivotChart

Creating a PivotChart

Create and use Slicers

PivotTable Timelines

Creating from Tables and Related Tables.

Work with PowerPivot

 

Module 5: Working with Templates

Create a Hyperlink

Creating and adding Cell Comments

 

Module 6: Inserting Illustrations

SmartArt

Insert Images into a Spreadsheet and/or with correct orientation

Insert Shapes into a Spreadsheet

Group and Layer Graphics

Quick Shape Formatting

 

 

Duration: 1 Day

Cost: £235 + VAT

SELECT wp_posts.*, wp_p2p.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_p2p WHERE 1=1 AND wp_posts.post_type = 'schedule' AND (wp_posts.post_status = 'publish') AND ( ( wp_postmeta.meta_key = 'start_date' AND CAST(wp_postmeta.meta_value AS DATE) >= '2017-11-20' ) ) AND (wp_p2p.p2p_type = 'schedule_to_courses' AND wp_posts.ID = wp_p2p.p2p_from AND wp_p2p.p2p_to IN (SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (10365) AND wp_posts.post_type = 'courses' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC )) GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS DATE) ASC