![]() 1.1 Office button Ribbon, Tabs, Groups Quick Access Toolbar Formula bar Rows and Columns Sheet Options 1.2 Working With Ranges Selection techniques Shortcuts Range names Goto Special, Find and Replace, Comments Fill Series 1.3 Format Data Format cells Cell styles Format as tables Format Painter Colors and borders Paste as Special options(Value, Transpose) ![]() Module 2 : Formulae & Functions 2.1 Simple calculations Excel formula(ex. Calculate percentage) Cell references 2.2 Functions Basic Functions(Sum, Max, Min, Average, Count, Counta, Countblank) Conditional Functions(Countif, Sumif, Averageif) Simple IF function Vlookup/Hlookup Module 3 : Sorting & Filtering 3.3 Sort Data Single column sort Multi column sort 3.4 Filter Data Autofilter Subtotals Single level subtotal Module 4 : Pivot Reports 4.1 Pivot Reports Pivot Table Need & Application Data Source Fields & Types Functions Filter By Field 4.2 Pivot Chart Create Pivot Chart Custom Pivot Chart Change Data Source Module 5 : Format | Text | Import 5.1 Conditional Format Highlight cell values in a column Highlight a record in a data 5.2 Text & Date Functions Date Functions - 8 Text Functions -12 Text to column Remove Duplicates Freeze Panes/Split 5.3 Import From Other Applications Data from text files Data from access tables . | - - - - ADVANCE EXCEL - - - - Module 1 Group of Worksheets Make Format easy Collective print Data Validation Text, numeric...built-in Us formulae, compare with other fields Create List based on other list values ![]() Module 2 Conditional Format Built-in Data bars Icons Formulae option With dropdown lists Paste Special Mathematical Operators Values, comments, blanks Link, transpose Hyper Link – Options Module 3 Name & Name in formula Range names, named constants Apply name in formula Edit names Advantages of us names Substitute names in exist formulae VLookup and Hlookup vLookup /hLookup Wild characters Repeated values Multiple values Return Multiple Fields Reverse Lookup Module 4 Match & Index Index Match Index-Match Substitute Repeat Values Conditional IF Simple, Nested, Multiple Simple IF Nested IF AND | OR |NOT IF and IFS functions Countif, Sumif, Averageif Countifs, Sumifs, Averageifs Module 5 Database Functions Database Functions Ease of usage Substitute for vLookup Subtotals Advantages Single level subtotal Multi-Level subtotal Array Functions Entry of array Pros-Cons Application of array functions Module 6 Auto Filters and Advanced Filters Merits Demerits Advance Filter Us AND / OR Criteria Copy to Other Dest. Remove Duplicates Table in Excel Advantage of Tables Table formula Autofill Create Dynamic Range Module 7 Date &Time Functions Calculations D&T Datatypes Upper & Lower Limits Date Functions Data Validation for Date Time Functions Data Validation for Time | Goal Seek Goal Seek For Analysis Hypothesis Financial Application Non-Financial Application Module 8 MSQuery to Import Data Import Data Form Different Worksheets / Workbooks MS Query Customize Query Options Parameter Query Consolidation Consolidate Data from Other Sheets / Books Different Consolidation Functions Consolidate By Value Consolidate By Position Solver for What-If Analysis Hypothesis Complex Analysis Solver Options Solver Optimization Summary Report Module 9 Chart Create Dynamic Charts Simple Charts Change Data Source, Series Values, Chart Type Format Axes, Labels, Legends, Titles Automate Chart Title Switch Axis, Plot on Secondary Axis Customize Items with Images Dynamic Charts Special Charts - Speedometer, Thermometer, Waterfall, Pareto, Gantt, Guage Etc... Module 10 Pivot Tables Introduction, Concepts Advantage of Pivot Table Data Range Create Simple Pivot Table Change Display Options, Totals Change Summary Functions Pivot Table for Analysis Create Calculated Field, Item Group Slicer Timeline Use Pivot Table To Create Frequency Work On Dynamic Range Internal Vs External Data Source Move, Refresh, Delete Pivot Charts Create Dynamic Pivot Charts Slicers to Automate Edit Charts Change Source Data Move, Edit, Delete Make a Static Chart Module 11 Create Dynamic Ranges App & Advantage of Dynamic Ranges App Offset Function App Excel Table Module 12 Optional Topics Indirect Function Choose Function Getpivotdata Function Subtotal Function Aggregate Function Financial Functions Dashboards – Introduction Shortcut Keys Protection & Track Changes Special Commands - - - - - - - - - - - - - - - - - - - - Placement Support Services available for Trainees at no additional cost Call 96505 15620 for a Free Demo Class - - - - - - - - - - - - - - - - - - - - Practical MIS & Analytics Training Provided by Experienced Sr. Managers with Leading Corporates & MNC's Interview Questions Covered throughout the Program |