Close

Advanced Excel 2016

  • 14th July 2020
    9:30 am - 5:00 pm

Course duration: 1 day

Additional notes for course:

Venue:  

Details Price Qty
Advanced Excel 2013show details + €100.00 (EUR)  

Course overview

The Excel 2016 Advanced training course builds on the techniques and concepts presented in the Intermediate course. It is designed to help you to extend your knowledge of some of the more specialised and advanced capabilities of Excel.

Aims and objectives

This course focuses on how to perform more efficient Data Analysis using features such as Pivot Tables, Data Models, Power Pivots and “What If” tools. It covers more advanced use of Calculations and Functions including using formulas to control Conditional Formats. It also looks at controlling and protecting content in a spreadsheet by use of Data Validation and selective password security. Automation using recorded Macros is also covered.

Who should attend

This course is designed for Microsoft Excel users who wish to develop their skills so they can use the advanced techniques to manipulate and interrogate Excel data.

Prerequisites

It is recommended that proposed delegates have attended a Microsoft Excel 2016 Intermediate course or have equivalent knowledge.

Course content

FUNCTIONS AND CALCULATIONS
Extending the scope of formulas by using functions such as SUMIF, COUNTIF, IFERROR
Creating multi-level formulas by “nesting” functions.
Naming cells or cell ranges
Using Names in formulas
Limiting the scope of Names and managing Names in a workbook.
Using Array formulas
Using a formula to control Conditional Formats

CONTROLLING CONTENT
Applying Data Validation
Using levels of password protection to control content in spreadsheet.

USING WHAT IF ANALYSIS
Setting up and Using Goal Seek
Creating and Saving Scenarios
Loading and Viewing Scenarios
Creating a Data Table of results

WORKING WITH SOLVER
Enabling Solver Add-in
Setting Constraints in Solver
Modifying Constraints
Setting Solver Options
Generating Reports and Scenarios with Solver

ADVANCED USE OF PIVOT TABLES
Using Data Tables in the creation of Pivot Table reports
Using the “summarize values by” & “show values as” to adjust and customise the Pivot Table.
Using calculated fields and items in a Pivot Table.
Creating a Pivot Chart and using it to pivot data in report.
Using drill-down features including new pivot chart drill down buttons.
Using slicers and timelines to filter data in the report

DATA MODELS AND POWER PIVOTS
Using the Data Model feature to pivot data from multiple Excel sources.
Adding to the Data Model
Creating pivot table reports from the Data Model
Using Power Pivot
Importing data from an external source (such as access)
Use of new automatic relationship detection.
Pivoting data in Power Pivot
An explanation of the new “get and transform” group on the data tab.

USING MACROS
Purpose of a Macro
Recording and naming a macro
Editing the recorded code in the visual basic editing window.
Running a macro
Assigning a macro to a button on the ribbon or the QAT

Corona Virus Update

Positive2Work Skillnet is fully supportive of the Government’s efforts to effectively address and limit the spread of COVID-19 and to safeguard the protection of public health and that of our stakeholders.

Positive2Work Skillnet management team are continuing to work and are available to discuss any queries that our businesses or trainees have regarding our scheduled training programmes and supports for businesses.

In support of this national effort, Positive2Work Skillnet has postponed its public training courses until 19th April 2020.

Positive2Work Skillnet will continue to take bookings for programmes commencing at a later date in 2020, and for our online programmes.

We are also available to discuss your queries about any other future programmes we offer to our members.

Our Network contact details are.
NAME: Mark Brennan
PHONE: 085 1745779
EMAIL: mark@p2w.ie

We would remind our stakeholders that the HSE website www.hse.ie is the authoritative source of information and advice on the situation regarding COVID-19 in Ireland.

We will keep this information updated in the coming weeks. Further information is also available on the Skillnet Ireland website at www.skillnetireland.ie