EXCEL ADVANCED

This course is aimed at fairly experienced Excel users whom need to learn more complex functions, nesting, data manipulation and protect data using the security features. Our "Hands-on" advanced excel training focuses on creating sophisticated excel solution using advanced formulas, dashboards, pivot tables, pivot charts, goal seek etc. we also teach you how to develop these advanced solution on the worksheet and to assemble the proper excel tools.

you will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information.


Download

MODULE NAME DETAILED TOPICS
Building Formula, Reference Auditing Arithmetic Functions-Sum, Product Average. Absolute/ Relative Cell Referencing. Mixed Reference, AutoFill Options Inserting Deleting Moving and copying sheets. Formula auditing tools, formal error checking tracing Precedence/ Dependents, Goto.
Implementing Mathematical, Trigonometry, Statistical Functions using named ranges Naming individual or range cells. Deleting and amending named ranges. Using named cells/ranges in formulae. Using FLOOR/CELLING/ MROUND/MOD/QUOTEIENT Functions. ADNVANCED SUMIFS. COUNTIFS AVERAGEIFS, MIN,MAX SMALL LARGE using complex criteria . 2-Case Studies
What If Analysis Scenarios, Goal Seek, Solver
Graphs Nine Different kinds of Graph , Combination Charts , Project management Chart 1-case Study
Logical Functions Combining IF, AND OR functions, Using IF/ISERROR Functions and NESTED IF 1-case Study
TEXT Function Using them Mid, Search, Left, Right Function. Using the trim, clean, Upper Lower, Dollar functions Using the Substitute, Text Function
Lookup's VLOOKUP and HLOOKUP, IFERROR VLOOKUP, MULTIPLE COLUMN VLOOKUP, INDEX AND MATCH Function , OFFSET / INDIRECT function 4-Case Studies
Data Analysis Advanced Pivot tables with grouping features, Pivot charts, filters and Slicers. Using the Pivot table wizard . Changing the Pivot table layout. Formatting . Grouping items. Inserting Calculated fields. Pivot table Options. Display and hide data in fields Lay out reports directly on worksheet Pivot Chart 2-case studies
Filters and Data Sorting Using Advanced Filter-advanced command on the data menu lets you use complex criteria to fillers a range of data . Using formulas In Critter
Introduction to Macros Purpose of Macros. Recording Macros. Where to save macros. Running macro. Custom buttons, Menu items, Keyboard short cuts