- 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.
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
- 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
- Working with 3d charts
- Analysis data with combination chart
- Xy scatter
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