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.

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

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'
Location & Dates
Call us at +971 4 430 8394