MESOL

Octagon Management & Engineering Solutions

Advance Excel


Duration

( 15 days ) 02Hrs / day

Fee

600 USD

Training Mode

On-line


Courses Description

Objectives

··         This course will expose students to the challenges involved in managing supply chains and understand the complexity of inter-firm and intra-firm coordination


Who will benefit

· Excel Advanced is for 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 criterion.

· 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 PivotTable, Pivot Charts & Cube functions.

· New search filter.

· SQL server PowerPivot for Excel 2010

· PivotTable What – IF Analysis (WriteBack)

·Data visualization tools



SQL Server PowerPivot for Excel

SQL Server PowerPivot 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 PowerPivot.


Condensing and refining data with PivotTables

Pivot Charts and Real-Time Data Analysis

· Developing interactive PivotTables for real-time data analysis.

· Building Pivot Charts to visually represent PivotTables data.

· Defining data summaries interactively.

· Macros integration with Pivot Reports.

Handling large Datasets with PivotTables

· Summarizing datasets with grouping and aggregation.

· Comparing related totals dynamically.

· Filtering details with Report Filters and Slicers.

· Presenting PivotTable 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 PivotTable 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 & PowerPoint




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



On-line Registration:

http://www.omesol.com/student



Available Payment Methods:

VISA, MASTERCARD, Paypal, Discover, American

Express, Diners Club International, JCB

On-line Fee Submission:

http://www.omesol.com/submit-fee-online