, Omesol [ Engineering, Management, Finance & Training Solutions Provider]

Advance Excel

Training Mode :  Online Courses  
Duration :  30 Hours   Fee :  600 USD  


  • After getting this training you will be able to use Excel every reporting & analysis tools in multiple ways, create sophisticated dashboards & bi (business intelligence) reports with Excel power pivot.
  • Create business modeling, dynamic and static reports with Excel VBA & form controls to give the multiple dimensions of your data & to achieve professional results in a fraction of the time.

Who Will Benefit

  • People who can already create moderately complex spreadsheets, and want to add more sophistication and automation.

Course Detail

Analyzing Data With Excel Power Functions

Summarizing Business Data With Excel Power Functions
  • Deciphering and correcting functions for data integrity.
  • Tabulating information with single & multiple criterions.
  • Creating interactive reports with Excel powerful formulas (array) for multiple dimension view of your data sets.
  • Tabulating information with advance formulas for single & multiple criterion implementing names to enhance your workbook model.
Controlling Calculations And Nested Formulas
  • Developing nested functions to cope with multiple conditions.
  • Applying techniques to implement and troubleshoot nested calculations.

Summarizing Business Information

  • Excel 2010 – business intelligence features
  • Slicer for pivot table, pivot charts & cube functions.
  • New search filter.
  • Sql server power pivot for Excel 2010
  • Pivot table what – if analysis (write back)
  • Data visualization tools
Sql Server Power Pivot For Excel
  • Sql server power pivot for Excel is a new add-in that extends Excel 2010 to support extremely large data sets that you create based on heterogeneous data from corporate databases on a network, public data on the web, or local data on your computer.
  • Virtually unlimited support of data sources.
  • Easy to create relationship among tables.
  • DAX (Data Analysis Expression).
  • New time intelligence function.
  • Dashboards with power pivot.

Condensing And Refining Data With Pivot Tables

Pivot Charts And Real-Time Data Analysis
  • Developing interactive pivot tables for real-time data analysis.
  • Building pivot charts to visually represent pivot tables data.
  • Defining data summaries interactively.
  • Macros integration with pivot reports.
Handling Large Datasets With Pivot Tables
  • Summarizing datasets with grouping and aggregation.
  • Comparing related totals dynamically.
  • Filtering details with report filters and slicers.
  • Presenting pivot table reports effectively with charts.
  • Slicing your report with Excel slicer
  • Integrating conditional formatting with pivot reports
  • Creating dynamic dashboard with pivot table.

Advanced Report Development

  • Calculating complex calculation with array formulas
  • Working with Excel power & aggregate functions
  • Creating dynamic report with form controls
  • Applying complex queries to extract data (lookup & references)
Data Consolidation For Summary & Reports
  • To summarize and report results from multiple worksheets/workbooks, you can consolidate data from each worksheet worksheets/workbooks into a master worksheet worksheets/workbooks.
  • Combining disparate data sets to make summary reports
  • Creating dynamic & static consolidated reports
  • Consolidation using pivot table for perfect analysis

Data Optimization & Business Modeling (What-If Analysis)

  • Planning for contingencies
  • Managing variables in worksheets with scenarios
  • Comparing and contrasting different data sets with scenario reports
  • Quantifying variables in a workbook model
  • Determining the magnitude of a variable with goal seek to achieve an end value
  • Create multiple scenarios with single data table
  • Complex calculation with Excel solver
Excel Database Management & Normalization
  • Understanding Excel database management operation & normalize technique to convert bad data into good data
  • Converting your reports into tabular form
  • Extracting required data from huge data sets
*applying complex queries

Data Integrity

  • Validating data
  • Controlling the limits of data
  • Setting up drop down parent & child list
  • Highlighting data anomalies with conditional formatting
  • Working with Excel 2010 enhanced conditional formatting options
  • Giving different format of your data for comparison by using conditional formatting with formulas
Invoking Conditional Formatting With Form Controls
  • Troubleshooting And Enhancing Professional Workbooks
  • Deciphering and correcting functions fir data integrity.
  • Accurately interpreting calculations.
Organizing workbooks and links
  • Arranging multiple workbooks with workspaces.
  • Managing external links.
Formulating Decisions From Database Information
  • Extracting information with look up & references-advance techniques
  • Calling data range in your formula to make your report dynamic.
  • Calculating data with multiple & complex criterion.
  • Offsetting data from remote cells.
Distilling Datasets For Data Analysis
  • Managing multiple datasets on a single worksheet with the table feature
  • Defining an Excel dataset to ensure appropriate use of built-in features
  • Extracting required data using advance filtering
  • Analyzing datasets with filters and aggregation
  • Calculating subtotals and grand totals for multiple dimensions of your report
Importing Data From Text Files, Web, Other Data Sources
  • Importing / exporting data to / from Excel.
  • Working with OLAP
  • Integrating Excel with other applications – word & Power Point

Macro Charged Reporting / Enhancing Excel Usage With Macros

Step-1: Automatically Repetitive Tasks
  • Simplifying complex tasks and reducing errors with recorded macros
  • Bulletproofing routine editing and formatting
  • Invoking macros with form controls
Step-2: Excel VBA In Depth
  • Introducing visual basic for application
  • Defining variables, constants & calculation
  • Adding logic to your VBA code
  • Debugging your VBA code
  • Managing workbooks elements to your workbook
  • Adding advanced elements to your workbook
  • Event driver programming
  • Building customized forms

Data Visualization With Excel 2010 Tools & Charts

Level-1: Visualization With Text Function
  • Creating bars with text charts
  • Comparative histogram chart
  • Conditional formatting with text charts
Level-2: Data Visualization With Conditional Formatting
  • Working with data bars
  • Analysis variance with color scale
  • Analysis trends with icon sets
  • Dynamic conditional formatting with form control
Level-3: Working With Built-In Charts
  • Analysis with 2d charts
  • Column
  • Bar
  • Pie
  • Line
  • Working with 3d charts
  • Analysis data with combination chart
  • Xy scatter
  • Area
  • Stock
  • Surface
  • Doughnut
  • Bubble
  • Radar
Level-4: Creating Excel Non-Native Charts
  • Comparative histogram chart
  • Tornado chart
  • Pareto chart
  • Control chart
  • Funnel chart
  • Progress chart
  • Delta chart
  • Dot plot chart
  • Box & WHIS chart
  • GANTT chart
  • Conditional charts
Level-5: Creating Dynamic Charts With Excel
  • Understanding Excel chart series function
  • Offset to make any chart dynamic
  • Using offset with match, index, columns & rows functions
  • Creating charts with combo box
  • Integrating scroll bar & radio button with charts
  • Line chart with spinner button
  • Column chart with check box
  • Dynamic chart labeling controlling charts with VBA
Level-6: Working With Spark Lines For Dashboards & Reports
  • Integrating spark lines (Tiny Charts) in your reports
  • Understanding spark lines features & design tab
Level-7: Controlling Charts With Vba
  • Change chart type with VBA & controls
  • Controlling the axis labels
  • Charts animation with VBA

Dashboard Reporting With Excel

  • This training will enable you to create quality dashboard reports in Excel. Dashboard reports present business information as a clear, concise picture that is easy to read, understand and update. A variety of results can be condensed into a single page for comparison purposes, simplifying and reducing the time taken to report on organizational performance.
Dynamic & Static Dashboard
  • Sales, finance, hr &operational dashboards
Getting The Command On Excel Dashboard Reporting Tools
  • Complex calculation with array & Excel aggregate formulas
  • Dynamic conditional formatting
  • Dynamic, interactive & complex charts
  • Working with form controls
Some Important Features:
  • Creating lay-out of your dashboard
  • Manage your data for reports extraction (consolidation & normalization)
  • Setting up calculation sheet for trends, variances, alerts, growth, comparison & calculations
  • Setting up charts sheet
  • Giving aesthetic & final look to your dashboards


To Top