Learn Pivot Tables, VLookup, Advanced Functions & New Features of Microsoft Excel

• Creating Your First Custom Format
• Understanding the Date & Time Format Strings
• Using Date & Time Custom Format
• Understanding Conditional Formatting

Lesson 2: Using Range Names

• Assigning Names to Groups of Cells
• Managing Named Range
• Using Names in Formulas

Lesson 3: Using Formulas & Functions in Excel

• Understanding Formulas & Functions
• Create a Relative & Absolute Reference
• Working with Logic Functions
• Working with Text Functions
• Using More Text Function
• Working with Lookup Functions
• Understanding VLOOKUP() Function
• Understanding HLOOKUP() Function
• Challenge 03: Using Formulas & Functions

Lesson 4: Managing, Sorting & Filtering Tables

• Create and Modify Tables
• Sorting and Filtering Data in a Table
• Build an Advanced Filter
• Getting Summary Information in a Table
• Calculate Total Row in a Table
• Display Special Formatting for First or Last Column
• Summing with Subtotals & Grand Totals
• Validating Data During Entry
• Working with Database Functions
• Challenge 04: Organizing Worksheet & Table Data

Lesson 5: Working with Charts

• Summarizing Data Visually Using Charts
• Customizing Chart Data
• Format Chart Legend and Titles
• Changing the Chart Body
• Saving the Chart as a Template
• Creating a Pie Chart
• Creating Combination Charts
• Challenge 05: Working with Chart

Lesson 6:Working with Pivot Tables

• What is a Pivot Table
• Anatomy of a Pivot Table
• How does a Pivot Table Works
• Create a Basic Pivot Table Report
• Rearranging a Pivot Table Report
• Customizing a Pivot Table
• Changing Summary Calculations
• Adding and Removing Subtotals
• Sorting in a Pivot Table
• Filtering in a Pivot Table
• Filtering Using the Report Filter Area
• Challenge 06: Working with Pivot Table

• Working in a Group Environment
• Commenting in Cells
• Tracking Changes in Workbooks
• Turn on Track Changes
• Accepting or Rejecting Changes
• Review Changes
• Keeping a Backup of your Changes
• Create a Change History

Lesson 8: Working with Multiple Workbooks

• Linking to Other Workbook
• Create a Link to Other Workbooks
• Consolidating Multiple Sets of Data
• Consolidate Multiple Worksheets

Lesson 9: Protecting Your Worksheet & Workbook

• Protecting Your Worksheet
• Protect a Worksheet
• Unprotect a Worksheet
• Allowing Access to Parts of a Worksheet – I
• Allowing Access to Parts of a Worksheet – II
• Protecting Your Formulas
• Protecting Your Workbook
• Save Your Workbook with Password
• Encrypting your Workbook

Lesson 10: Working with Excel Macros

• Recording & Testing a Macro
• Using the Developer Tab
• Create a Macro
• Relative and Absolute Recording
• Testing the Macro
• Running and Deleting Macros
• Using the Macro dialog box
• Using a Shortcut key
• Placing a Macro on the Quick Access Toolbar
• Delete a Macro
• Saving a Workbook with a Macro
• Saving a Macro to the XLSM Workbook
• Saving a Macro to the Personal Macro Workbook
• Macro Security
• Creating Practical Macros
• Inserting a Header