Module 1: Advance Excel                                                            Duration: 20 Hours

Synopsis

• Excel with Data Analytics will make your command in advanced analytical tools & also a data keeping tool. By Excel formulas, you can crunch data, analyze it and get answers to most complex questions. While anyone can use a simple SUM or IF formula, an advanced user of it would be able to seamlessly write & combine formulas like SUMIFS, SUMPRODUCT, INDEX, MATCH, LOOKUP formulas.
• Apart from knowing the formulas, advanced Excel users know how to make these formulas dynamic, how to audit them and how to use which formula for which occasion (and they also know few alternatives for any given formula problem).

 Program

1. Introduction to Excel 2016, Use of Excel in Corporate, Understanding the hierarchy in Excel and basics of Application, Workbook, Worksheet, Range, Cells, Columns, Rows

2. Understanding how we can control Excel Application from Excel Option Window, Use of Quick Access Toolbar and will go through all parts of Excel application

3. Learn how to write some basic function in Excel Like: - Datedif, Concatenate, Lower, Upper, Proper, Trim, CountBlank, Count, Counta, Countif, Countifs, Sum, Sumif,Sumifs, Istext, Isblank, Iserror, Isnumber, Cell, Round, Roundup, Roundown

4.Types of Operators: - Arithmetical, Logical and Conditional

5. Learn to write some dynamic String & Text Functions - Exact, Find, Len, Left, Right, Mid, Replace, Search, Substitute, Text, Code, Char

6. Lear how to validate columns having data type as Date & Time Using these functions - Weekday, Weeknum, Networkdays, NetworkdaysINTL, Time, Hour, Minute, second, Today, Now, Date, Day, Month, Year

7. Logical Functions – IF, And, Or, Iferror, Nestedif

8. 10 Different ways to map value using Lookup Functions - Vlookup, Hlookup, Index, Match, Row, Column

9. Learn how Name Manager, Offset, Indirect and Choose function can help you improve your Productivity and Efficiency by developing dynamic template and reports

10. Database Functions - DSum, Dcount, Daverage, Dmax, Dmin, Dcounta

11. What is Array – Learn how we can us Vlookup, if, Sum, Max, Min, Average, Large, Small as an array (15 Examples which can help you in building your logics)

12. Advance Validation and Conditional Formatting

13. Sheet Protection, Workbook Protection and Share Workbook

14. Organising big data and creation of Reports through Pivot Tables and Pivot Charts Project on creating Dynamic Reports and Summarizing your data through Pivot Tables and analysing it to generate business insights

15. Filter, Advance Filter, Sorting and Remove Duplicates in Data

16. Increasing Efficiency and Productivity through calculation type in excel (Automatic and Manual)

17. Type of Reference in Excel - Absolute, Mixed and Relative
18. Data Analysis - Percentage Calculation, Growth Analysis
19. Understanding some Basic Terms like: MTD, QTD, YTD, MAT, CYTD, FYTD, FTM, QTM, YTM, L3M, L6M, L12M

20. Creating Reports and User Manuals through Hyperlinking

21. Time calculation, Paste Special Command, Freeze Pane & Text to Column

22. Learn how to create Interactive and Creative Graphs like - Column Chart, Pie Char, Line Chart & Bar Chart

23. Use of Form Controls in Excel: Combo Box, List Box, Button, Option Button, Check Box

24. Learn How multiple Visualization can be shown at the same space by using Camera Tool in excel.

Project

How to Create a Dynamic Dashboard on Sales Data Using Offset, Name Manager, Form Controls, Graphs, Tables and Some Functions

Dashboard Reports Allow User to Get High-Level Overview of the Business and Help Them Make Quick Decisions. Dashboards are Often Called as Management Information System (MIS), Which Provides Information that Organizations Require to Manage Themselves Efficiently and Effectively.