Excel 2016 – Advanced

Duration: 1 day

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.

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.

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

Excel 2016 – Intermediate

Duration: 1 day

Overview
The Intermediate Excel 2016 training course builds on the techniques and concepts presented in the Excel Introduction course. It focuses on how to work more efficiently with calculations, how to further enhance the appearance of the data in the spreadsheet and introduces some data analysis features of Excel.

Objectives
This course will demonstrate how to control more complex formulae and functions and how to use formulae auditing. It will cover the use of various tools to analyse data such as sorting, filtering and a basic introduction to Pivot tables. You will also learn how to organize and present your data using tables, charts and conditional formatting.

Who Should Attend
This course is designed for Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.

Content

WORKING WITH MORE ADVANCED CALCULATIONS AND FUNCTIONS
Use of Brackets in Calculations.
Use of Absolute References (Dollar Signs) In Calculations
Building “3-D” Calculations by Linking Formulas across Sheets or across
Workbooks. Controlling 3-D Links.
Using Formula Auditing
Using some Statistical Functions – Average, Max, Min, Count and CountA
Use of Dates in Calculations.
Some basic Date and Time Functions
An introduction to Logical Functions IF, AND, OR
Basic Use of VLOOKUP and HLOOKUP

USING EXCEL TABLE FEATURE
Advantages of Table feature and applying it to data.
How to working with Data Tables
Creating Calculated Columns
Filtering and Sorting in Tables

CONDITIONAL FORMATTING
How to applying basic Conditional Formatting
Using “Traffic Lights” Conditional Formats
Sorting and Filtering based on Conditional Formats

AN INTRODUCTION TO PIVOT TABLE REPORTS
What are Pivot Table Reports?
Creating a basic Pivot Table Repot
The Design Window and how to use it
“Pivoting” and Refreshing the Data
Using the Pivot Table Ribbons

WORKING WITH GRAPHIC OBJECTS
More Advanced Charting:
Formatting Charts.
Adding trend lines to Charts.
Use of Spark lines.
Dual Axis charts and combination charts.
Using SmartArt
Use of Screenshot Tool

Excel 2016 – Introduction

Duration: 1 day

Overview
Microsoft Excel is a useful tool for generating spreadsheets from a number of data sources. Using calculations and functions, users can easily manage any type of data including budgets, financial data, statistical data, reports, charts, graphs and small lists.

Objectives
This Excel course introduces the necessary features and skills which enable a user to begin working effectively and efficiently with Excel.

Who Should Attend
This course is designed for those who wish to gain the necessary skills to begin working with Excel 2016 spreadsheets. It will focus on how to create and edit spreadsheets, how to format content and create calculations and how to print and present the results.

Content

CREATING A BASIC WORKBOOK
Getting Started & Signing In (Where Appropriate)
Explanation of the Excel Screen and the Ribbons.
The Purpose of the Quick Access Toolbar.
Changing Ribbon Display Options and Customising the QAT
Using the New “Tell Me What You Want To Do” Help and Search Feature of 2016
Creating a New Workbook or Opening an Existing Workbook
The Purpose of Templates. New templates introduced in 2016
Saving a Workbook.

WORKING WITH DATA
Moving Around the Spreadsheet
Understanding Cell References
Entering, Deleting and Updating Content in the Spreadsheet. Using
Undo and Redo.
Creating basic Formulas
Using quick Functions such as AutoSum and Average
Using Copy, Cut and Paste. Purpose of Paste Special
Saving Time With AutoFill and FlashFill
Using the Quick Analysis Tool to Add in Quick Calculations or Formats.
Using quick Sort and Filter

FORMATTING DATA AND THE WORKSHEET
Adjusting the Size of Rows and Columns
Applying Number Formats
Formatting Headings – Including Font, Colours, Borders, Wrap Text and
Merge and Centre.
Using the Format Painter and the Styles Gallery
Clearing Formatting
Naming Worksheets and Formatting Worksheet Tabs
Freezing Titles on Screen

CREATING GRAPHIC CONTENT
Creating and Formatting a Basic Chart
Adding Text Boxes and Comments.
Adding Pictures using the Automatic Orientation New to 2016
Drawing Shapes – including the new 2016 pre-sets

VIEWING, PRINTING AND SHARING THE WORKBOOK
Inserting and Adjusting Page Breaks.
Using Print Preview and “Page Break Views” in the Work Area.
Using the Page Setup Dialog to Control Print Output
Saving a Workbook as a PDF or XPS file
Emailing a Workbook as an Attachment.
Using the “Inspect Document” and “Check Compatibility” Features.

Successful Performance Management: The Route to Success

Duration

One-day

Programme Objective

The key objectives of this programme are to inform managemers and employees of the potential benefits of a good Performance Management system and : to alert them to the key skills and behaviours associated with the delivery of their own responsibilities, to ensure that this potential can be realised.

Continue reading “Successful Performance Management: The Route to Success”

Successful Negotiation Skills

Course Duration 1 Day

Programme Objective

Successful negotiations are the bedrock upon which successful individuals successful organisations and successful economies are built. When engaged in negotiations – where your skill as a negotiator can prevent losses, increase gains or enhance circumstances for you and your members\constituents \organisation – it is crucial that you get it right. The cost of failed negotiations is often too costly to contemplate.
Allied to this is the fact that we are negotiating almost all of the time. Whether with or as workplace representatives, trade union officials, business partners, clients, suppliers, colleagues or family members, the success of our endeavours depends entirely on our understanding of the negotiation and communication process. However frequently line managers, team leaders, key executives, function heads and human resource professionals fail in negotiations because they have limited or no grounding in the basic skill set associated with effective bargaining practices. This training course is designed to remedy such a deficiency, enabling all parties to negotiation to ‘get to yes’.

Continue reading “Successful Negotiation Skills”