Introduction to Advanced Excel

Meaning and Definition of Excel, Customizing common options in Excel, Absolute and relative cells, Protecting and un-protecting worksheets and cells, An overview of the screen, navigation and basic spreadsheet concepts, Protecting and un-protecting worksheets and cells, Increase interactivity by automating your spreadsheets with macros and VBA, Use advanced graphs in minutes instead of wasting hours trying to figure them out, Solve complex problems with superpower functions, Turn raw data into ‘must make’ decisions using PivotTables and Power Pivots.

Sorting and Filtering

Filtering on Text, Numbers & Colors, Sorting Options, Advanced Filters on 15-20 different criteria(s), Using multiple-level sorting, Using custom sorting,   Filtering data for selected view (AutoFilter), Using advanced filter options, Sort from Smallest to Largest, sort in Alphabetical Order, Grouping Cells Using the Subtotal Command, Collapse or Display the Group, Create Group With Subtotals, Ungroup Select Cells, Ungroup Entire Worksheet, Steps of Sorting And Filtering, types of Sorting: Quick Sort  and Radix Sort.

Function in Advance Excel

Writing conditional expressions (using IF), Using logical functions (AND, OR, NOT), Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX), VlookUP with Exact Match, Approximate Match, Nested VlookUP with Exact Match, VlookUP with Tables, Dynamic Ranges, Nested VlookUP with Exact Match, Using VLookUP to consolidate Data from Multiple Sheets, Text Function: Clean Function, Flash Fill, Text Function, Control + E and Flash Fill, Using Functions – Sum, Average, Max, Min, Count, Counta,  Absolute, Mixed and Relative Referencing, Mathematical Function: SumIf, SumIfs CountIf, CountIfs AverageIf , AverageIfs, Upper, Lower, Proper, Left, Mid, Right, Len, Trim, Exact, Concentrate, Find, Substitute.

Formatting and Proofing

Meaning of Formatting and Proofing, uses and Importance of Formatting and Proofing in Excel, Formatting Cells with Number formats, Font formats, Alignment, Borders, etc, Basic conditional formatting, Modify cell alignment and indentation, Wrap text within cells, Manually Formatting Cell Contents, Inserting and Deleting Cells, Copying Cell Formatting with the Format Painter, Understanding Paste Special Options, Formatting Cells with Styles, Working with Hyperlinked Data, Applying Conditional Formatting to Cells, Insert Cells into a Worksheet, Aligning and Indenting Cell Contents, Choosing Fonts and Font Sizes, Change the Font Color, Apply Special Character Attributes.

Printing Workbook

Meaning and Definition, Uses of Printing Workbook, Importance Of Printing Workbook, Setting up Print Area, Customizing Headers and Footers, Designing the Structure Of a Template, Print Titles, Repeat Rows/ Columns, Inspect a workbook for hidden properties or personal information, Protecting a Worksheet, Using the Document Inspector, Comments and annotations, Document properties and personal information, Headers and footers, Hidden rows, columns, and worksheets,  Marking a Document as Final, Turning Track Changes On and Off, Tracking changes, TRACKING CHANGES TO A WORKBOOK, Setting Track Change Options, Inserting Tracked Changes, Deleting Your Changes, Accepting Changes from Another User, Rejecting Changes from Another User.

Advanced Paste Special Techniques