Enable Technology

Excel 

Excel 2016 Advanced




Course Overview


A one-day course covering Microsoft Excel 2016 advanced topics.

Duration: 1 day course



Course Content


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

PivotTables
Understanding PivotTables
Recommended PivotTables
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
Inserting a Timeline Filter
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 PivotTable Reports
Creating Running Totals
Creating Calculated Fields
Providing Custom Names
Creating Calculated Items
PivotTable Options
Sorting in a PivotTable

PivotCharts
Inserting a PivotChart
Defining the PivotChart Structure
Changing the PivotChart Type
Using the PivotChart Filter Field
Buttons
Moving PivotCharts to Chart Sheets

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






Email us about this courses         Telephone on 01952 305730






Training Locations: Enable Technology run courses in Wolverhampton, Telford, Shrewsbury, Birmingham, Cannock, Walsall, Dudley, Sandwell, Bridgnorth, Lichfield, Burton-upon-Trent, Stafford, Redditch, Bromsgrove, Solihull, Tamworth, Worcester, Shropshire, Staffordshire, Worcestershire and across the West Midlands.




West Midlands Scheduled Courses: Excel VBA; MS Project, SSRS; SQL; Crystal Reports; HTML;Adobe Illustrator; ASP.NET; Dreamweaver; Flash; Photoshop; InDesign; Google Scripting; Visio; PHP; Microsoft Access; JQuery JavaScript; Web Development; Office;

Birmingham Scheduled Courses: SQL; Excel VBA; MS Access;Photoshop; Adobe Illustrator; InDesign; Dreamweaver; MS Project;Visio; Flash; SSRS;PHP; JavaScript JQuery; HTML CSS; Microsoft Office;

Wolverhampton Scheduled Courses: Microsoft Access; Excel VBAMS Project; Visio; Adobe Illustrator; Photoshop; Dreamweaver; InDesign; Flash; HTML CSS; JavaScript; SQL; Microsoft Office; Adobe

Telford Scheduled Courses: SQL; Excel VBA; MS Project; Visio; PowerPoint; Photoshop; Adobe InDesign; Illustrator; Dreamweaver; Web Design; Office;

Cannock Scheduled Courses: Excel VBA; PHP; Microsoft Access; Visio; MS Project; PowerPoint; Dreamweaver; Photoshop; InDesign; Adobe Illustrator; Flash; JavaScript; HTML CSS;

Walsall Scheduled Courses: Excel; Access; MS Project; Visio; Photoshop; InDesign; Adobe Illustrator; Dreamweaver; Flash; SQL; JavaScript; HTML CSS;

Dudley Scheduled Courses: Excel; MS Access; SQL; MS Project;Visio; Adobe Illustrator; InDesign; Photoshop; Dreamweaver; Flash; JavaScript; HTML CSS;

Sandwell Scheduled Courses: Access;Excel; Microsoft Project; Visio; PowerPoint; Adobe Illustrator; Photoshop; Adobe InDesign; Dreamweaver; Flash; JavaScript; HTML CSS; SQL;

Shropshire Scheduled Courses: Illustrator; InDesign; Adobe; Dreamweaver; Flash; Photoshop; Excel; Project; Visio; PowerPoint; Office; JavaScript; HTML; Access;

Staffordshire Scheduled Courses: Illustrator; InDesign; Photoshop; Dreamweaver; Flash; Adobe; Excel;