* 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.
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
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.
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
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
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
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
Data entry form
Custom list
Camera tool
Text to speech
Protecting worksheets and workbooks
Averest Training Certificate of Completion or delegates who attend and complete the training course
This course is certified by:
This Course can be customized delivered to a group at your facility saving time and money.