Objective:

The aim of this 2 day session is provide the user with a comprehensive knowledge of the various features and facilities and how to apply them in practical situations.  We are happy to tailor these sessions if onsite, and can use live data for maximum effect.  The student will be led, via a series on in-depth examples on how to make the best use of Excel in their working environment and are invited to bring data with them for consultation.

Review of Excel Basics / Short Cuts Topics:

Advanced Formula
SUMIF / SUMIFS / COUNTIF / COUNTIFS
IF Statements / IF AND OR Statements
VLOOKUP / HLOOKUP / SUMPRODUCT / INDEX / ARRAYS
Nested Formula Formula In Linked Files
Date & Time Calculations
Using Named Ranges in Formula
Goal Seek / the Scenario Manager

Data Cleansing / Creating Uniformity
TRIM /UPPER / LOWER / PROPER CONCATENATE / LEFT / RIGHT / MID / LEN
Text to Columns Features

Working with Multiple Sheets / 3D Data Entry
Inserting / Deleting / Rearranging Worksheets
Linking the Sheets
3D Data Entry / 3D Formula
Using Multiple Windows  / Linked Files
Link and Pasting to other Applications
Sorting a Table / Sort Keys / Sorting Left to Right

Using AutoFilter / Advanced Filter
AutoFilter Criteria / Wildcards
Practical Users for Advanced Filter
Ranges /   Process Automation
Creating Subtotals & Outlines

Pivot Tables
Creating and Editing
Pivot Table Formula
Slicers / Charts / Reports
Pivot Table Drop Down Menu
Pivot Table Formula

Setting Up Validation Rules
Restricting Numeric Entry
Error Prompts and Error Alerts
Dates and Time
Text Lengths
Restricting to Drop down lists

Custom Options
Preventing spaces
Budget limits
Ensuring Unique Entries

Data Handling
Handling Volume Data
Optimising Large Files
Working with External Data Sources
Introduction to MSQuery
Setting Up Connections / Creating Live links
Choosing Fields / Applying Criteria
Refreshing the Download
Editing the Connection

Advanced Charting
Thermometer / Speedometer Charts
Waterfall Charts
Pseudo Gantt Charts
Interactive Charts

Data Entry Forms
The Camera Tool
The Development Ribbon
Working with Form Controls
Drop Down Lists
Check Boxes / Radio Buttons
Spinners /Scroll Bars

Creating Scrolling tables
Introduction to Macros
The Macro Recorder
Using Relative References
Macro Types
Running a Keyboard Macro
Adding Embedded Macro Buttons
Adding to the Ribbon
Editing the VB Code
Joining Macros
Advanced Reporting

Working with Dashboards
Creating / Using
Interactive Troubleshooting

QUESTIONS & ANSWERS