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

Advance Excel

Training Mode : Online Courses

Duration : 30 Hours Fee : 600 USD

Objectives

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

Register Now Submit Fee

Available Courses

To Top