Advanced Excel (Beginner)

Course Duration : 30 hrs
10,000

About Advance Excel

VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic with Microsoft Office applications – Microsoft Excel. By running VBA within the Microsoft Office applications, you can build customized solutions and programs to enhance the capabilities of those applications.


Course Overview

Here we will be learning to Work with:

  • VBA objects, properties, and methods
  • Run a sub procedure
  • Create a macro using the macro recorder
  • Create UserForm to Input Data (with proper validations).
  • Input/Output of Data with other Applications.

What we offer

Hands on experience with Visual Basic with Microsoft Excel.
Real time industry problems and solutions (Sample Projects)
Industry Experienced Faculties
Exclusive Study Material
State-of-Art Labs with latest Infrastructure

Chapter-1: Introduction to MS Excel & Understanding Basic Work with it

1.1 Features of Microsoft Excel
1.2 Improvements in the new version
1.3 Different components of Excel worksheet & workbook
1.4 Saving Excel File as .xlsx(.xls), .xlsm,.xlsb, .csv
1.5 Difference between Operators and Functions
1.6 Using Excel Shortcuts with Full List of Shortcuts -1
1.7 Copy, Cut, Paste, Hide, Unhide the Data in Rows, Columns and Sheet
1.8 Hyperlink

 

Chapter-2: Formatting Cell, Row, Column, Sheet – Merge Cells, Font, Color, Size, Style, Allignment,Wrap Text, Format Painter

2.1 HOME MENU -VARIANT OF AUTO, FILL & CLEAR FUNCTIONS
2.2 Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
2.3 Text Functions: LEFT, RIGHT, MID, LOWER, UPPER, PROPER, FIND, CONCATENATE, REPLACE, REPT,  TEXT, TRIM,SEARCH, SUBSTITUTE, TRUNC, CONVERT, DOLLAR etc
2.4 Using Excel Shortcuts with Full List of Shortcuts -2
2.5 Selection of visible cells & Paste Special Options

 

Chapter-3: Math and Trigonometry Functions: VARIANT OF RAND, ROUND, SUM, LOG function; CEILING, FLOOR, INT, LCM, MOD, EVEN/ODD, SUBTOTAL etc

3.1 Statistical Functions: VARIANT OF COUNT, MAX, MIN, AVERAGE, STDEV function; Forecast etc
3.2 Conditional Formatting -Single/ Multiple Cell Rules , Using Color Scales and Icon Sets in Conditional Formatting , 3.3 Creating New Rules and Managing Existing Rules
3.4 Using Excel Shortcuts with Full List of Shortcuts -3

 

Chapter-4: Data Sorting and Filtering -Single/Multiple level, Custom Sort & Filter

4.1 Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc
4.2 Sheet tab functions (Insert, delete,rename,move & copy,group & ungroup sheet tabs)
4.3 Page Layout and Printer Properties
4.4 Inserting Pictures and other objects in Worksheets

 

Chapter-5: Information Functions: CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE etc

5.1 Types of common errors in Excel -ERROR.TYPE
5.2 Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
5.3 Shared workbook – advantages & disadvantages
5.4 Text to column, Remove duplicates
5.5 Import data from other sources

 

Chapter-6: Relative & Absolute Reference

6.1 Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
6.2 Insert Table & charts – design/ switch between row & column/select data /change chart type, format axis, legends, secondary axis
6.6 Data validation

 

Chapter-7: Pivot table & Pivot chart, Comparison with Different functions

7.1 Slicer in Dynamic Dashboard
7.2 Sparkline charts
7.3 Formula Auditing – Evaluate formula
7.4 Insert/delete/edit/hide comments

 

Chapter-8: Array Functions, Comparison with Dbase functions

8.1 Database Functions: DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
8.2 Gridlines,Freeze Panes, Arrange Windows
8.3 Excel Options – Debugging problems, Excel Add-ins
8.4 Group/ungroup rows & Columns, subtotal

Advance VBA Macro Training Syllabus

  • Introduction of VBAMacros
  • RecordingsMacro, Working with VBAEditor
  • Editing, Writing VBA Code and Saving workbook with Macro Contents
  • VBA Programming Concept
  • VBA Syntax and Semantics
  • Variable Type and Declaration, working with variables & values
  • Procedures (Sub Procedure, Function Procedure)
  • Objects, Events , Modules, Operators & Functions
  • Repeating Actions Loops (For-next, Do loop, Do while, Do Until)
  • Array – One / Multi Dimensional Array
  • Textbox,Input Box, Message Box,Check Box,Buttons,User Forms, Active X control and GUI
  • Exporting files to different applications
  • Developing codes in VBA Macros
  • Debugging macro – Error handling

Case Study 1: Loss Making Categories

In this section, you want to identify the most profitable product sub-categories across each region.

Step – 1: Using the raw data, find the top 3 profitable Product Sub-Categories in each region.
Step – 2: Using PIVOT Tables, compare the profit for each of the top 3 profitable product subcategories by region:
A. Which subcategories are the profitable in most regions?
B. Sort the rows and columns by profit and apply Conditional Formatting. Does this throw up some exceptions?

 

Case Study 2: Loss Making Categories

Say you want to identify the least profitable (most loss-making) product subcategories and if needed, stop selling those product subcategories in the regions where they are the least profitable.

Step – 1: Find the two most loss-making Product Sub-Categories across all regions

Step – 2: For these subcategories, identify the regions where they are the least profitable

Step – 3: Articulate your observations and identify any anomalies that you observe

Food for thought: People mostly buy tables and chairs together; carefully analyse the business angle of these product categories and form hypotheses to explain your observations

Tanushree Bhattacharyya

Guest Faculty (Advanced Excel, R)

Tanushree, a post graduate in M.Sc(Econometrics & Statistics), having 8 yrs of experience in Analytics & Mkt Research.Currently working in a big MNC house, proficient in statistical tools like SAS, Advanced Excel, VBA, Access, SQL, SPSS, Quantum. She is highly skilled in data analysis and building statistical model, creating publication quality report and automation of the models with VBA/SAS/SPSS with an excellent track record of managing clients, projects and exceeding expectations. She is an expert in handling analytical projects involving various statistical techniques like demand forecasting , multivariate techniques, optimization, segmentation and reporting the insights to the management to fulfill the business requirements. She is involved with NIVT for over a year now and has an excellent track record of providing training to professionals on Excel,VBA & R Programming. On behalf NIVT she has conducted training in some corporate houses like Dynamic Level.