Advanced excel & mis

• Introduction of MIS.
• Introduction of ad-hoc reporting & routine reporting.
• Basic & advance excel training.
• Dashboard training.
• Interview tips.
• Pdf file & videos.

Basic & advance excel

• Introduction of excel.
• Introduction of different excel version and features.
• Save file in different format (.xlsx, .xlsm, .xls, .xlsb, .xltx, .xltm, .xlt, pdf)
• Paste special – paste all, paste values, paste formula, paste format, paste data validation, paste comments and others.
• Goto special – select all comments, select constant data, select blank
• Fill series – create number series, create date series
• Conditional formatting (inbuilt & custom) - color duplicate data, color number as condition.
• Cell formatting (inbuilt & custom) - number formatting, date formatting, time formatting, and custom formatting.
• Sort – data sorting by single and multiple column, and custom sorting
• Filter – text filter, number filter, custom filter, and custom filter
• Find & select– find data in sheet and workbook by formula, values, and comments
• Replace – replace data in sheet and workbook by formula, values, and comment.
• Basic and advanced filter – filter data by single and multiples criteria, and filter unique data.
• Hyperlink (wizard & function) – existing file or web page, place in this document, create a document, and email – address.
• Flash fill – get the separate word
• Text to column (delimited & fixed width) – space, tab, comma, and others.
• Remove duplicate 
• Data validation (inbuilt & custom function) – list, date, time, text length, and custom validation.
• Subtotal – sum, count, counta, min, max.
• Print excel sheet – print title, header & footer, page margin.
• Slicer filter – use in pivot table for filter.
• Import & export data from excel to other application (sql server, ms access, xml data).
• Goal seek – find the right input.
• Data table – see the multiple inputs at the same time.
• Customize excel – application user name, disable and enable vertical and horizontal scroll-bar.
• And many more basic tricks.

Protection & security

• Cells & range protection
• Content & formula protection
• Worksheet protection
• Workbook protection
• Track changes
• Share workbook

Named Range

• How to Defined Named Range?
• Scope of Named Range.
• Dynamic Named Range
• Use in Formula

Cell Reference

• What is Cell Reference?
• All Types of Cells Reference.
• Use of Cell Reference in Formula.

Introduction of Function

• What is Function and Formula?
• Components of Function
• Types of Function
• Evaluate Function
• What is Criteria?
• Convert text number as number
• Execute Formula by Function, Control Enter, Control Shift Enter

Error in excel

• Handling all types of error in excel like (#n/a, #name, #ref, #values, #num, #null, #div)
• Circular reference.
• Iserr – return as true and false
• Isna – return as true and false
• Iserror – return as true and false
• Iferror – return as custom message if we get an error

Basic Function

• Min – get the lowest value in excel and also as per criteria.
• Max – get the max value in excel and also as per criteria.
• Small – get the k-^th lowest value from data-set.
• Large – get the k-^th largest value from data-set.
• Row, Rows, Column, Columns
• Indirect – Returns the reference specified by a text string
• Address – Returns a cell address as given specified a row and column
• Char, Code
• Round, Roundup, RoundDown, Mround, Int, Value, Mod

Logical function

• If and nested if – get the value if condition is true and false.
• Or – apply multiple logics and condition and return as true and false
• And - apply multiple logics and condition and return as true and false.
• True, false, and not

Date & Time function

• Date – returns a date combination of year, month and day.
• Time – returns a time combination of hour, minute, and seconds.
• Edate – returns as date of the previous and up-coming date.
• Eomonth – returns as last date of month.
• Month – returns number of month from date.
• Weekday – returns a number of 1 to 7 by identifying the day of the week.
• Weeknum – returns the current week number in year.
• Networkdays – returns the number of whole workdays between two dates.
• Datedif – returns the difference between two dates in year, month, and day.
• Datevalue & timevalue – convert the string date and time as data and time format
• Make a calendar by using the above function

Text function

• Left – returns the specified numbers of character from the string/text from beginning of text
• Right - returns the specified numbers of character from the string/text from right side of the text
• Mid - returns the specified numbers of character from the string/text from middle of the text
• Substitute – replace a character in text by other character(substitute is case sensitive)
• Replace – replace a character in text by other character.(replace is non-case sensitive)
• Find – returns a position number of any character or text from the another text(find is case sensitive)
• Search - returns a position number of any character or text from the another text(search is case sensitive)
• Len – Returns a total count of characters from text
• Rept – Repeat the characters as giver number of time.
• Text – Converts a value in text as given specified format.

Mathematical function

• Count – count the numbers in excel from multiple ranges.
• Counta – count the non-blank cells in excel from multiple ranges.
• Countblank – count the blank cells in excel.
• Countif – count the data in excel as per given criteria. (15 types example)
• Countifs - count the data in excel as per given multiples criteria. (10 types example)
• Sum – sum the numbers in excel from multiple ranges and sum by multiple criteria(5 types example)
• Sumif – sum the number in excel as per given criteria(10 types example)
• Sumifs - sum the number in excel as per given multiples criteria(10 types example)
• Sumproduct – sum and count the data in excel(10 types example)
• Average – get the average value in excel.
• Averageif – get the average value in excel as given criteria.
• Averageifs - get the average value in excel as given multiples criteria.
• Aggregate – sum, count, min, max, average by ignoring the error and hidden value.
• Rank – returns the rank of numbers in excel.
• Frequency – get the number of repetition of data.

lookup function

• Choose – Returns a value from list of values, based on number.
• Match – Returns a relative position of any value from the ranges and array.
• Hyperlink – Create a hyperlink between two source.
• Vlookup – Looks for a value in the leftmost column of table and returns a value from same row a column you specify
• Hlookup - Looks for a value in the top row of table and returns a value from same row a column you specify
• Lookup – Looks up a value either from one-row or one-column range or from an array.
• Index – Returns a value from table by given row and column number.
• Offset – Returns a reference of cell or range by given number of rows and column.
• Array function – Combination of multiple formula.

Data analysis tools

• Pivot table – Returns a summarize data from large data.
• All pivot table function & features.
• Pivot table from multiple worksheets & multiple workbooks using sql query at a time.
• Pivot table from ms access.
• Pivot table from the sql server.
• Pivot chart – Returns a summarize data in chart.

Dynamic chart & dashboard

• Dynamic chart
• Dashboard

MS excel power BI tools

• An overview of power bi tools.
• Introduction of power query.
• Introduction of power pivot

Power query

• Installing a power query.
• Creating a power query.
• Options of loading data.
• Creating data transforms.

Editing queries.

• Getting the initial data.
• Initial transforms to prepare the data.
• Splitting columns, merging columns.
• Creating custom columns.
• Indexing rows.
• Grouping and pivoting data

Power pivot

• Importing sql server tables
• Importing access and excel data
• Using filter into rows/columns
• Using data and diagram view
• Hiding from the client view
• Create relationships
• Creating a pivot table from power pivot.
• Adding to data models.
• Using the function in the pivot table
• Creating hierarchies

MS Access

• Introduction of MS Access database.
• Introduction of DBMS & RDBMS.
• Import Export Data from other source
• Copy data in MS Access as Table
• Introduction of Table
• Introduction of data types and uses.
• Create a Table from copy paste method.
• Create a Table from the wizard.
• Introduction of Query
• Make all types of Query from QBD (Query by Design) method Ex. Select, Make Table, Append, Update, Crosstab, Delete.
• Using inbuilt Macro in Access.
• Create Forms from Wizard.
• Make Report from Wizard.