* All fees are exclusive of vat
** PREMIUM - Customize your learning experience
This 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 is an Excel-based course allowing you to develop a new level of expertise and adding immediate value to your job.
What Do Participants Learn?
Apply Excel reporting expertise in business, finance, and accounting by enhancing data slicing and dicing, data massaging, and data analysis skills
Use pivot tables and pivot charts to perform automated report writing, analysis and reconciliation most efficiently
Develop special flash and management reports by linking-up Excel with Access, web, text, SQL, and other databases
Repeat tasks and generate reports efficiently by recording, running and editing macros
Acquire numerous tips and tricks that will improve working efficiency
Who Should Attend?
Business, finance, and accounting professionals, senior and junior accountants, business analysts, research professionals, marketing and sales professionals, administrative staff, supervisors, general business professionals and staff from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliation, and analysis.
What Will the Learning Experience Include?
Comprehensive pre-program activities include:
Web-based information forms & surveys completed by attendee.
Direct consultation with the attendee about the expectations.
During the training, participants engage in data, activities, and conversations that lead to insight and knowledge.
Participants learn from expert trainers who have both academic and business experiences.
Highly applicable training content & instructive activities for adding depth to training topics.
**A half-day site visit for integrating the experience & plan next steps. Opportunities to provide connections, ideas & support.
Explore & Practice
Apply & sustain the learning experience by using this ongoing support:
To ensure participant has new skills or behavior progress.
Optional, fee-based mentoring & coaching with the trainer.
Training materials & additional documents (e-books, pdf files, presentations and articles)
Evaluate your training experience by giving us feedbacks and help us to reach our organizational goals.
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
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
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
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
Introduction to learning the ultimate tool in Excel: ''Macros''