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
- How to Defined Named Range?
- Scope of Named Range.
- Dynamic Named Range
- Use in Formula
- 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
- 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
- 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
- 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.
- 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.
- 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
MS excel power BI tools
- An overview of power bi tools.
- Introduction of power query.
- Introduction of power pivot
- Installing a power query.
- Creating a power query.
- Options of loading data.
- Creating data transforms.
- Getting the initial data.
- Initial transforms to prepare the data.
- Splitting columns, merging columns.
- Creating custom columns.
- Indexing rows.
- Grouping and pivoting data
- 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
- 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.
- Print excel sheet.
- 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.
- Worksheet event (activate, change, selection, deactivate and other event)
- Workbook event (activate, open, close and other event)
- Static array.
- Dynamic array.
- Single and multi-dimension array.
- Live array VBA projects.
Working with Dictionary
- Creating Dictionary object by Late Binding and Early Binding.
- Adding Key and Item in Dictionary.
- Accessing Key and Item from Dictionary.
- Looping on keys or items.
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.
- 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 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