( 15 days ) 02Hrs / day
·· 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.
Analyzing 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.
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
· 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.
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
Available Payment Methods:
VISA, MASTERCARD, Paypal, Discover, American
Express, Diners Club International, JCB
On-line Fee Submission: