Excel User Plus & Dashboard Reporting

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