Advanced Business and Financial Reporting - Next Generation Excel

Day 1:

Data massaging: tools and techniques

Consolidating data from separate files and sheets

Advanced data validation using lists, dates and custom validation

Cell management tools: left, right, mid, concatenate, value

Naming, editing, and managing cells and ranges.Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs

Looking-up data, texts, and values using Vlookup .The incredible table-tools technique

Slicing dates into day names, weeks, week numbers, month names, years and quarters

Text to columns and dynamic trimming using Trim, Len

Managing texts and numbers using replace, find and substitute

Text change functions

Day 2:

Reporting, analysis, and reconciliations using pivot tables

The 20 must learn rules

Creating pivot tables

Number formatting techniques  Designing report layout

Sorting in ascending, descending and more sort options

Filtering labels and values

Expanding and collapsing reports

Summarize data by sum, average, minimum, maximum, count

Show values as % of total and % of

Pivot table options

Drill down option

Showing report filter pages

Inserting formulas

Date analysis

Copying pivot tables

Creating pivot charts

Dynamic chart labeling

Mastering the slicer

Linking pivot tables and pivot graphs with PowerPoint

Conditional formatting with pivot tables

Designing reports using GetPivotData

Day 3:

Modeling and integration techniques

Perform 'what-if' analysis using spinner

Checkbox data modeling with 'if' function

Option button data modeling with 'if' function

List box data modeling with 'Choose' function

Linking Excel with text files

Linking Excel with databases (Access)

Linking Excel with multiple Excel files and SQL

Linking Excel with internet

Linking Excel with Excel

Scenario manager

Day 4:

Introduction to learning the ultimate tool in Excel: ''Macros''

Macro basics

Planning a macro

Designing your control board

Recording macro

Testing macro

Editing macro

Macro workshops

Advanced filter with macro

Day 5:

Tips and tricks in Excel

Data entry form

Custom list

Camera tool

Text to speech

Protecting worksheets and workbooks