Advanced MIS & Data Analyst

Module 1 - Basic and Advanced Excel With Dashboard and Excel Analytics

Introduction to Excel:-

Basic Understanding Menu and Toolbar, Introduction to different category of functions like Basics, Mathematical and Statistical, Date and Time, Logical, Lookup and References, Text and Information.

Mathematical Functions:-

Sum, Sumif, Sumifs, Count, Counta, Countblank, Countif, Countifs, Average, Averagea, Averageif, Averageifs, Subtotal

Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

Date and Time Function:-

Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time

Text Functions & Data Validation:-

Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value, Large, Small Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

Statistical Function & Other Functions:-

Isna, Isblank, Iserr, Iseven, Isodd, Islogical, Isytext, Max, Min, Len, Right, Left, Mid, ,Maxa, Maxifs, Median, Minifs, Mina, Vara, Correl, Geomen

Logical Functions:-

And, Or, If, Iferror, Not, Nested If

Lookup & Reference Functions:-

VLookup, HLookup, Index, Match, Offset, Indirect, Address, Column, Columns, Row, Rows, Choose, Arrays Concept In Lookup Formula’s, Past Special, Past link

Pivot Table – MIS, Data Analysis & Visualization:-

Pivot Table- What-if Analysis, Data Table –One Variable and Two Variables, Data Analysis Using Statistics, Descriptive Statistics, ANOVA, Moving Average, Testing Hypothesis, Measuring Covariance and Correlation, Distribution, Regression, Graphs & Charts, Analysis Tool Pack, Solver, Histogram, Pareto, Water Fall, Import and Export data, Protect/Unprotect sheets/workbooks, Worksheet formatting and Print Display

Data Collection Method:-

With Data Quality, Collaboration & Security Like Share Your Workbook On Share Drive With Quality

Analysis Single/Multidimensional Analysis:-

Like Three Dimensional (3D) Tables, Sensitive Analysis Like Data Table, Manual What-If Analysis, Threshold Values, Goal Seek, One-Variable Data Table, Two-Variable Data Table

Advanced Dashboard in Excel:-

Overview of Chart types, Chart Formatting, Active X Form Controls, Principle of great dashboard design, Selecting Correct Chart to display data, Interactive Charts with Form Controls, Combo box, Check Box, Scroll Bar and Radio Button, Interactive Dashboard with Form Controls, Form Controls for reports automation, Data Models using Power Pivot

Two Live Report Development in Advanced Excel:-(Real World Data):-