Advanced MS Excel 2010

1. Excel Abilities and its Functions

Learning Objectives - In this module, you will learn what are the new features added in Excel 2010. How to customize quick access toolbar and ribbon. Differences between relative and mixed cell references, to perform complex calculations.

Topics - Overview of Microsoft Excel 2010, Improved ribbon, Microsoft Office Backstage view, Excel Mobile 2010 for Windows 7 Phone, Sparklines – An Introduction, Slicers – An Introduction, Customize the Quick Access Toolbar, Customizing common options in Excel Setting, Absolute, Relative and Mixed Cell References, Work with Comments, Using Hyperlinks, Introduction to Excel Functions, Adding a Function to a Formula, Writing conditional expressions (using IF), Building an AutoSum Formula, Adding a Range Name to a Formula.

2. Data Security, Formatting and Analyzing

Learning Objectives - In this module, you will learn to protect and unprotected workbook, Inserting the existing file and new file as an object, share a workbook with others and filtering a table.

Topics - Protecting and un-protecting worksheets and cells, Hiding Formulas, Track Workbook Changes, Share a workbook with other users, Inserting, Formatting and Deleting Object, Changing the font and font size, Applying effects to fonts, Changing font color, Working with text within a cell, Wrapping text within a cell, Applying auto format to a range, Applying a number format, Applying a style to a range, Sorting a Table, Filtering a Table, Filtering a Range, Filtering data for selected view (AutoFilter), Using advanced filter options, Sort by colorSorting in a Pivot Table, Filtering in a Pivot Table, Filtering Using the Report Filter Area.

3. Data Reports

Learning Objectives - In this module, you will learn aggregate functions, reference functions and specialized functions. This module also deals with Pivot Tables and Pivot Charts.

Topics - Using aggregate Functions like Dsum, Dcount, Dmax, Sumif, Countif, Averageif, Sumifs, Countifs, Averageifs, ISERROR, ISBLANK, Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX), Summarizing Data with subtotals, Pivot Tables, Using advanced options of Pivot tables, Types of Pivot Report, Consolidating data from multiple sheets and files using Pivot tables, Import and export data in Excel.

4. Graphical Representations of Data

Learning Objectives - In this module, you will learn Data Visualizing and Automating Excel.

Topics - Examining Chart Elements, Understanding Chart Types, Create a Chart, Format Charts, Create a Chart Template – Combination chart, Summarizing Data by Using Sparklines, Creating Dynamic Charts by Using Pivot Charts, Changing the Chart Layout and Style, Introduction to Macros, Recording and Playing a Macro, Editing Using VBA, Mapping a Simple Document.

5. Specialized Functions

Learning Objectives - In this module, you will learn scenario based analysis of data with ‘what if’ analysis & specialized functions.

Topics - Date and time functions, Time Function, Text functions, Proper Function, Concatenate Financial Functions Pmt, Fv, Named Ranges, Creating a Data Table, Introduction to Scenarios, Managing Scenarios, Using Goal Seek, Solver, Saving Solver Models, Configuring Solver.

6. Working with Other Microsoft Office Programs

Learning Objectives - In this module, you will learn restrictions in Worksheet, manipulating External Data, and working with other Microsoft Office Programs.

Topics - Specifying a valid range of values for a cell, Input Message, Custom Error Alert, Specifying custom validations based on formula, Getting External Data in Excel, Refreshing data, Add own error messages, Object Linking and Embedding, Insert Drawing Object, Data Forms, Drop Down List, Including Office Documents in Workbooks, Pasting Charts into Other Documents, Preparing Worksheets for Printing, New Methods For Headers And Footers In 2010, Insert Specific Elements In A Header Or Footer, Add A Predefined Header Or Footer, Custom Header Or Footer For A Chart.