This course is recommended for end users seeking proficiency in the use of Microsoft Excel 2010 at an advanced level or seeking to obtain Microsoft Office Specialist (MOS) certification in Microsoft Excel 2010 at Expert level or an equivalent.

Topics Covered

Advanced Data Analysis using Excel 2010 Introduction to Excel Pivot tables & Reports Creation of Pivot table Using summary functions in pivot table Pivot table calculations like functionality using comparative analysis i.e. Using custom calculation in pivot table. Creating own formulas to perform calculations using elements of the pivot table. Using grouping and ungrouping in pivot tables. Creating of and working with Pivot chart Difference between regular and pivot chart Publishing a pivot table. Using summary functions in pivot table table calculations like functionality using comparative analysis i.e. Using custom calculation in pivot table. Creating own formulas to perform calculations using elements of the pivot table. Using grouping and ungrouping in pivot tables. Creating of and working with Pivot chart Difference between regular and pivot chart Publishing a pivot table. Goal seek Part of What if Analysis Solver function How to work with Solver tool Using conditions/Constraints in solver tool Working with solver results Preparing different kinds of summary report using solver tool. Scenarios How to work with Scenario tool as reporting tool Using prediction analysis in scenarios Adding different types of scenario How to set the variables for different scenarios Editing and deleting a scenario Merging different scenarios Creating of summary of different scenarios Using pivot table in scenarios. Data tables Introducing data tables Creating one and two variable data table Using Data tables for different kinds of database Calculating multiple results for a formula in one operation Data Management using Excel 2010 Sorting Rearranging of data Sorting by alphabet's, Numbers and by Time Filtering data Using Auto filter option Custom Filtering using different options Advanced filtering based on the criteria Find the unique records using advanced filtering Data subtotalling Criteria behind subtotalling How to calculate subtotals Nesting subtotals Grouping and outlining data Auto outlining of database Manual grouping and outlining Data consolidation Combining different spreadsheet using data consolidation Using summary functions in data consolidation Creating link to the source data Formatting and conditional formatting Formatting and Auto formatting Data validation Use of validation in excel Validating a database with different kinds of validation Validating a formula Using input message in data validation Using error message in data validation Custom Formatting Conditional Formatting using normal values and formulas. Advanced functions in Excel Lookup functions Lookup array Lookup vector V-Lookup H-Look up Mathematical functions Sumif function Sum product Round function Roundup Rounddown Ceiling Floor Factorial Text Functions All 24 types of text functions Database functions Using all D-functions like D-average, Dget, D-sum, D-product, D-max, D-min, D-count, D-countA, D-std, D-var. Data and Time functions Calculating the age using date functions Network days Weekdays Days360 E-date Finding today's date Finding current time Information From Information category using function such as Cell, Errortype, Info ,Isblank, Iserr, Iserror, Islogical, Iseven, Isodd, ISNA functions. Getting external Data From Access From sql From web From ODBC General functions Converting text data into columns Freezing and unfreezing the database Macros Basics Recording and running of macros Introduction to VBA

Who should attend

End users seeking proficiency in the use of Microsoft Excel 2007 at an advanced level or seeking to obtain Microsoft Office Specialist (MOS) certification in Microsoft Excel 2007 at Expert level or an equivalent.

Pre-requisites

Some knowledge of basic word processing, computing, and spreadsheets; familiarity with the Microsoft Office work environment; an understanding of the Internet and web-page functionality.

What you need to bring

Note book,

Key Takeaways

Data Management Connect the data from different sources (eg: MS-Access, Text File, CSV File, SQL Server, ODBC etc) Creating a pivot table, utilizing a pivot table to manipulate, analyze and publish data Utilizing the solver tool Using sort, filter and group tools Data Analysis Using functions including mathematical, look-up, text, database & date-time functions Data Validation Summarizing Reports Dashboard Reporting MIS Reporting Preparing Chart reports