• Focus on real scenarios of Reporting / Data Management / Automation
• Difference between basic and advance Excel, Excel Basics, Excel Settings
• Advanced Formulas including complete coverage of Logical functions, Text Functions, Date & Time Functions, Look up functions & Mathematical Functions.
• Complete Excel ribbons including Name Manager, Absolute and Relative references, field setting, Goal Seek / Scenario manager, Managing custom Lists, Cross addressing.
• Pivoting and Slicing along with Power Pivot and Power Slicing
• Graphs, Charts along with MIS reports generations
• Excel Setting, enabling and disabling Excel options including add ins, creating own tabs  and menu bars
• Handling multiple files with the Excel File System (Exporting and Handling Files) + Database file connectivity, Remote file connectivity, Text file connectivity and many more…

• Conditional formatting, Data Validation, Consolidation, Grouping and Sub Grouping, User Settings for Excel Application Level
• Data Management and Analysis, Reporting Techniques, Macros, List box, Combo box, Buttons and many more…
• Geographical charts on global data sets
• More than 250+ formulas along with 100+ assignments, notes, e-books and many more ACLM’s branded videos to help you forever.

Set of Important Formulas and Related assignments & Notes

• Logical Function Sets – And, Or, Not, Nand, Nor, Xor, True, False, Iferror, Iserror, Iserr, Isna, Isblank, Istrue, IsFalse, IsNumber, IsText, IsEven, Isodd, IsLogical
• Usage of Operators – Logical Operator, Arithmetic Operator, Assignment Operator and Unary Operators
• Logical / Conditional Statement – If, If else, if else if (nested if), if with operator, if with the use of text functions, if with the use of numbers, if with date function, if with time functions, nested if with operators including text, numbers & date & time functions.
• Text Function Set – Upper, Lower, Proper, Left, Mid, Right, Concatenate, Char, Clean, Code, Find, Search, Replace, Substitute, trim, text value, Text set, T, BahtText, Unichar, Unicode. The array formulae including various other text functions.
• Date & Time Function Set – Date, Day, month, Year, time, today, now, Hour, minute, Second, Datedif, Datevalue, Time Value, Weeknum, Weekday, Age Calculator, Usage of scientific and moderate calculator, Days360, General Date Diff, EDate, EOmonth, Workdays, Workdays.intl, Networkdays, Networkdays.intl, now, Today, Yearfrac
• Lookup and References set – Address, Area, Choose, Vlookup, Hlookup, Lookup, Reverse Lookup, Index, Match, Row, Rows, Column, Columns, Get Pivot Data, Hyperlink, Indirect, offset, Transpose, RTD, Small, Large, Rank, Formula Text, 
• Mathematical Function – General Use Function – Sum, sumif, Sumifs, Count, countif, Countifs, Average, Averageif, Averageifs, Product, Sumproduct, Sumproduct Array Formula, Round, Roundup, Rounddown, Floor, Ceiling, INT, Sqrt, Power, Subtotal, Fact, Even, Odd
• Advanced Formulas including Array Formulas and its applications, Sorting Formulas, Filtering Formulas, Query Formulas
• Differentiating Google Sheets and MS-Excel (Pros & Cons)

Data Filtering & Formatting

• Understanding Different Data Types, Using String / Text / Number / Long Data Types with examples, Managing Date Time Format, Handling Long Date time Format, Data Correction, Identifying bad data, Conversion of different data types, Importing and accessing different file types in excel

ADVANCE EXCEL SECURITY

• Protecting Excel File, Protecting worksheet, Workbook, Enable / Disable Range, Allow users to edit range, Protecting users to edit range, Field Controls using customized user access.

Pivot Tables

Excel Pivot Tables, What Is A Pivot, Why You Should Use It, Creating Your First Pivot Report, Formatting Pivots, Summarizing Data, Playing With Pivots, Grouping Values, Calculated Fields, Calculated Items, Making A Pivot Chart, Copying Pivot Tables, Example Uses Of Pivots, Pivot Table Tricks, Guest lecture by Debra Dalgleish, Preparing your data, Pivot and chart, Group dates and data, Calculate, Focus with filters, sort

Data Filters

Data Filters & Sorting, Uses of Filters, Basic Filtering, Short-cuts for filtering, Visual Filters – Using Conditional Formats, Sorting, Sorting left to right

Validation

Data Validation, Uses of Data Validation, Simple Data Validation Examples, Using drop-down box, to validate, Using Formulas, Showing Error Messages, Advanced DV Tricks,

Working with Data

Importing External Data, What is the purpose of Excel Import?, Understanding various import options, Importing Text Files – Example: Dynamic Data Validation, Importing Web Pages – Example: Movie Tracker, Importing Access DBs – Example: Sales Pivot Report, Using Text Import Utility – Example: Converting Dates, Excel Access Integration, Excel to Access, Access to Excel, Integrating Excel & Access thru Automation, Working With Complex Worksheets, What is a complex sheet?, Tips for handling complexity, Designing a complex workbook,

MS-Access in Action

• MS Access SQL VBA Ribbon, File, Save a Database as Templates, Home, Create, External Data, Database Tools, Contextual Tabs, Quick Access Toolbar, Object Navigation Pane, Access Work Surface, Access Option Dialog Box, Object Overview, Tables  Queries,  Forms,  Reports, Macros & Modules, Process Overview, Exercise: Getting Around the Access Environment, Create Database using a wizard

Fundamentals

• Approach to Database Design, Top down Database Design, Bottom up Database design, Reality, Gather Information, Data Mapping, Sample Data Map, Normalization, First Normal Form, Second Normal Form, Third Normal Form, Table Relationship

Table & their View

• Datasheet View, Selection Tools, Table Layout Tools, Sorting & Filtering Tools, Print Records, Record Navigation, Working in Datasheet View, Working with Table in Datasheet View, Design View, Open a table in Design View, Design View layout

Tables

• Creating an Access Database, Creating a New Database, Data Types, Determine Data Type, Create a Table in Design View, Delete a Fields, Rename a Table, Linking Table from External Source, Import table from other database, Tables and Templates and Application Parts

Queries

• Definition & Purpose, Types, Views, Select Queries with criteria, Logical Operators, Group & Total with Queries, Total Row with group By, Action Queries, Make Table Queries    Append , Queries,     Update Queries, Delete Queries Creating Crosstab Queries, Delete Queries, Rename Queries

Form

• Definition & Purpose, Layout Design & Views, Adding a Header & Footer, Adding Logo to the Header, Adding a Title to the header, Arranging Filed on a form, Spacing Objects o a form, Alter a Form, Remove form Control, Format Forms, Adding Data Record in Form View, Form Record Navigation, One Click Forms, Basic Data Entry Form, Datasheet Form, Split Form, Create a Form with Form Wizard

Reports

• Definition & Purpose, Views, Report Header, Page Header, Group Header, Details Group Footer,   Page Footer, Report Footer, Adding a Report Header/Footer, Adding a logo to the Header, Adding a Title to the Header, Adding fields to Reports, Add Calculated Fields, Arranging Fields on a Report, Resizing the Details Sections, Grouping 7 Sorting, Setting Properties on a Report, Report Property Setting, Section Property Sheet, Object Property Sheet, Adding Records Counts, Special Report Fields, Dates, Page Numbering, Controls, Application Parts, Deleting a Report, Formatting a Reports, One Click Report, Formalize the Relationship between Tables

Relationship

• Data Type and Relationship, Views, One Click Report, Formalize the Relationship between Tables

Macro

• Open a Table, Form and Record, Run a Query, Print a Report, Running a Macro, Creating Simple Macros

Completing the Desktop Application

• The Navigation Form, Running Macros from a Navigation Form, Setting the Navigation Form as a Default Form, Creating a navigation Form, Splitting the database, Encrypt with password, Distributing the Front-End Database, Database Maintenance, Compact & Repair, Backup recovery a database, Maintain Backup Compatibility