Advanced Excel

• Introduction
• Basic Calculations
• Microsoft Office Button
• Using worksheets effectively
• Formatting text and numbers
• Saving Excel 2007 files
• Inbuilt Functions
• Sort Function
• More on sort data
• Formatting data as tables
• Absolute and relative cell reference
• Page layout and print preview
• Priting: display gridlines
• Conditional Formatting
• Charts
• Text to columns
• Autofilter

INTERMEDIATE

• Library functions use
• How to avoid auto hyperlinks
• Fill data across worksheets
• How to use comments
• How to remove duplicates
• Template based workbook
• Themes and smartart diagrams
• ‘IF’ function
• Countif
• Nested IF functions
• Advanced filter
• Named ranges
• Subtotals
• Formatting in-cell graphs(conditional format)
• Data validation
• Custom Data Validation

ADVANCED

• Pivot Tables
• Pivot charts
• Slicer Pivot tables with charts
• Drop Down Pictures
• User choice using with Vlookup/Hlookup
• Using sum with offset functions
• Absolute range calculations
• Goal Seek
• Get external data – text files
• Import data from databases
• Import data from an Excel file
• Array Formulas
• Calculations in Word
• Printing: Repeat specific rows
• Excel Help – Vlookup

MISCELLANEOUS 1

• How to use comments
• Convert function
• Using 3-D references
• Consolidate data
• RANK function
• Named ranges use
• Advanced filter – unique records
• Highlighting duplicates
• Database functions
• Data Analysis and Vlookup
• Vlookup using lists
• Data Analysis and Hlookup
• Left Lookups: Overcome limitations of Vlookup (using index with matchfunctions)
• nested functions
• Work with Excel data containing blank rows
• Highlight blank cells
• Compound interest calculations using user form
• Insert a watermark
• CHOOSE function
• SUBSTITUTE function

MISCELLANEOUS 2

• Data Analysis and MATCH function
• Max, Match, Vlookup and LARGEfunctions
• Breakeven analysis using Goalseek
• How to avoid duplicate entries
• Data validation, Named Ranges,Indirect Function
• Trim,find , left, right. etc,, functions
• Text functions
• Link an Excel worksheet to Word
• Keyboard shortcuts
• Manage Inventory
• Accessing data from another workbook
• Sumif&S – multiple criteria
• Average if &S
• Countif&s
• “D”sum ,average,min,max,count
• Sum product
• Is error,Iferror
• Vlookup with iferror
• If,Istext with using Vlookup
• Integrated with vlookup,match
• Integrated with vlookup,columns
• Data entry only into specific cells
• MS-Access Table using MS-Excel data
• using the comments and conditional formatting
• Depreciation using the straight line method
• Consolidate data from Excel worksheets

MISCELLANEOUS 3

• Checkbox form control
• Dynamic ranges
• Datedif Function
• Sensitivity analysis with ScenarionManager
• Export data from Outlook to Excel
• Track changes
• countif – multiple criteria
• Excel tips for beginners and advancedusers
• What is macro | why macro will beright in Excel
• How to write a macro code