Guarantee to run course

Course Overview

There is no doubt that Excel is the accountant's, finance and business professional's best friend! You and I know how overwhelmed we are with the abundance of unstructured data. Monthly, quarterly and annual closing are immensely data driven and require moving and exporting data from 'ERPs' and databases to Excel. This hands-on course will advance your data massaging, modeling, integration and automation skills to new levels. You will also master normalization and massaging of noisy data, preparation of reports, analysis and reconciliation.
This course is Excel based allowing you to develop an exclusive level of expertise and adding immediate value to your job.

Course Methodology

This is a hands-on course with about 20% on design and structure and 80% uses MS Excel as a commanding tool to perform routine and periodic tasks. Individuals will be required to complete exercises, case studies, and projects daily.

Course Objectives

By completely attending this course, participants will be able to:
  • Competency I: massaging and normalizing unstructured data.
  • Competency II: Performing reporting and analysis using Pivot Tables. Also, creating customized scorecards and management reports.
  • Competency III: Design dynamic reporting models using different modelling techniques. Perform What-If-Analysis.
  • Competency IV: Integrate Excel with many different file types such as access, web, text, SQL, and other databases. Reports will update automatically.
  • Competency V: Perform repetitive tasks and generate reports efficiently by recording, running and editing.

Target Audience

Business, finance and accounting professionals, senior and junior accountants, business analysts, research professionals, marketing and sales, administrative staff, supervisors, general business professionals.

Target Competencies

  • Massaging and Normalizing Data
  • Pivot Tables Reporting
  • Modeling Techniques
  • Integration and Linking
  • Macros and Automation
Note:
This course requires the use of laptops with Excel 2010/2013/2016 installed. On any courses conducted in the UAE, VIF will provide laptops to all delegates for the duration of the course. For courses outside the UAE, delegates must bring their own laptops with Windows-based Excel 2010+ fully installed.

Course Outline

Data Massaging Tools and Techniques

  • Merge and consolidate data
  • Data validation using numbers, lists, dates, and text length
  • The magical select, shift, select
  • The surgeon functions: left, right, mid, concatenate, value
  • To name or not to name: Naming, editing, and managing cells and ranges
  • Sum and brothers: Subtotal, sumif, sumifs, sumproduct, Count and sisters: count, countif, countifs
  • Finding things around: 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 find, substitute, trim, len, and replace
  • Make me look professional: Text change functions

Pivot Tables: The One and Only

THE 19 RULES

DESIGN RULES

  • Designing Pivot Tables
  • Number formatting techniques
  • Designing report layout
  • Sorting in ascending, descending and more sort options
  • Filtering labels and values
  • Expanding and collapsing reports

ANALYTICS RULES

  • Summarize values by sum, average, minimum, maximum, count
  • Show values as % of total and % of
  • Pivot table options
  • Inserting formulas
  • Date analysis
  • Copying pivot tables

VISUALIZATION RULES

  • Creating pivot charts
  • Dynamic chart labeling
  • Mastering the slicer
  • Showing report filter pages
  • Linking pivot tables with PowerPoint
  • Conditional formatting with pivot tables
  • Extracting data using the 'GetPivotData'

Modeling and Integration Techniques

  • Performance 'what-if' using spinner
  • Check box data modeling with 'if' function
  • Option box 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
  • Scenario manager

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

Tips and Tricks in Excel

  • Conditional formatting
  • Recover unsaved workbooks
  • New functions
  • Protect all formula cells
  • 40 important shortcuts
  • Controlled list
  • Fill in a flash
  • Sort and filter by color
  • Data entry form
  • Custom list
  • Camera tool
  • Text to speech
Location & Dates
Call us at +971 4 430 8394