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.

VBA macro automation

  • Introduction of VBA & macro.
  • Introduction of all types of windows (properties window, project window, code window, local window, watch window, immediate window)
  • Introduction of sub & function procedure
  • Write our first code.
  • Public procedure and private procedure
  • Naming of procedure

Working with variable & data types

  • Introduction of variable & data types.
  • Naming of variable
  • Storing data in variable
  • Introduction of public and private declaration.
  • Introduction of object variable.
  • Set the object variable – workbook object, worksheet object, and range object
  • Introduction of static declaration.

Working with workbook object.

  • Introduction of thisworkbook and activeworkbook
  • Add workbook
  • Rename workbook.
  • Save workbook in different format (.xlsx, .xlsm, .xls, .xlsb, .xltx, .xltm, .xlt, .pdf and many more)
  • Create workbook object

Working with worksheet object.

  • Add worksheet
  • Delete worksheet
  • Rename worksheet
  • Create worksheet object
  • Move or copy the worksheet to another workbook.
  • Activate worksheet.

Working with cells & range object.

  • Create range & cells object
  • Highlight cells & range
  • Copy & paste
  • Find the last column & row.
  • How to activate cells
  • Goto special.
  • Cell formatting & number formatting.
  • Sort & filter.
  • Find & select.
  • Basic & advanced filter.
  • Hyperlink.
  • Print excel sheet.

Condition logic

  • Using if, else if.
  • Use and, or, like
  • Use of goto statement.

Loop in VBA

  • For next loop
  • For each loop
  • Do loop/ do while/ do until
  • Nested loop
  • Exit from loop

Error in VBA.

  • All types of error in VBA (application-defined or object-defined error, object doesn’t support this property or method)
  • All types of error handler using in VBA ex. On error resume next, on error goto 0 and label.

Event

  • Worksheet event (activate, change, selection, deactivate and other event)
  • Workbook event (activate, open, close and other event)

Array

  • Static array.
  • Dynamic array.
  • Single and multi-dimension array.
  • Live array VBA projects.

Working with Function Procedure

  • Create Function Procedure in VBA.
  • Passing parameter in Function.
  • Using ByRef and ByVal in Function and Sub.

Udf (user defined function)

  • Create many types of udf function for calculation.
  • Create volatile & non-volatile function.
  • Use select case in function.
  • Use VBA Function - Replace, Instr, StrConv, Len, Left, Mid, Right
  • Use excel function in VBA.

User form

  • Create user form by using the VBA code.
  • Creating custom dialogue box in userform
  • Using all types of control in user form(combobox, listbox, checkbox, optionbutton.

Pivot table & pivot chart

  • Create a pivot table from VBA.
  • Create chart from VBA.

File folder management.

  • Using late binding & early binding methods in VBA projects.
  • Using Microsoft scripting runtime for manage folder and file.
  • Rename folder, create folder and delete folder.
  • Rename file, create file, delete file, and move file to one folder to another folder.
  • Looping over folder and file.
  • Using file dialog box.
  • Using file & folder pickers.
  • Extract file name from folder with using dir function

Excel Based Automation

  • Create Master data in excel from multiple sheet.
  • Create Master date in excel from multiple workbook.
  • Split data in multiple sheet/workbook.
  • Live excel based projects.
  • Data manipulation in excel.

Creating a word document

  • Referencing the word object library.
  • Using late binding and early binding method for creating a new instance of word.
  • Save word document in different format (.docx, .docm, doc, and pdf)
  • Copy data and chart into word
  • Writing and formatting text.

Creating outlook emails

  • Referencing the outlook object library.
  • Using late binding and early binding method for creating a new instance of outlook.
  • Opening an outlook application and creating a new mail.
  • Send email from outlook with a signature from VBA
  • Send emails from outlook from VBA with attachment.
  • Looping over sending emails.
  • Creating outlook event.

Creating a powerpoint presentation

  • Referencing the powerpoint object library.
  • Using late binding and early binding method for creating a new instance of powerpoint.
  • Creating presentation & slides.
  • Copying tables & charts into powerpoint.
  • Adding and formatting textboxes.

Working with MS access and excel

  • Referencing the MS access object library.
  • Using late binding and early binding method for creating a new instance of MS access.
  • Create table.
  • Import and export data in MS access to excel.
  • Using SQL query in VBA
  • Using some objects for MS access in excel.
  • Create for MS from wizard.
  • Using query in for MS.
  • Using VBA code in for MS.

ADODB method (Microsoft Activex data object 6.1)

  • Create a connection between SQL server and excel by using ADODB.
  • Using all types of SQL cursor in VBA.
  • Create table, drop table in SQL server using VBA.
  • Import & export data from SQL server.
  • Modifying data, inserting data.
  • Using SQL stored procedure in VBA.

Web crawling

  • Web crawling with browser internet explorer.
  • Creating html document.
  • Understanding the html tags and use them for crawling
  • Fetch the data from html document
  • Using get and post request in crawling
  • Using all types of web elements.
  • Use Chrome/Firefox for crawling by Selenium Library.

Add-ins and custom ribbons

  • Understanding of xml code
  • Creating add-ins and custom ribbon for excel.
  • Creating add-ins and custom ribbon for word.
  • Creating add-ins and custom ribbon for powerpoint.

VBA protection & security

  • Workbook, worksheet, range, and object protection
  • VBA project protection, and password breaker