- Competency I: massaging and normalizing unstructured data.
- Competency II: Performing reporting and analysis using Pivot Tables. Also, creating customized scorecards and management reports.
Business, finance and accounting professionals, senior and junior accountants, business analysts, research professionals, marketing and sales, administrative staff, supervisors, general business professionals.
- Massaging and Normalizing Data
- Pivot Tables Reporting
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.
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
- 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
- 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
- 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'