Advanced Excel

Advance Features of Ms-Excel. Advanced Excel course also enables the student to utilize, the advance features taught in this course in an office environment with maximum efficiency. Extra Projects & Practical Training make you an expert.

Overview

Adv. EXCEL

Tpoics Covered:- Excel Advanced Formula, Excel Macro and Excel VBA

Our most popular course.You’ll learn how to create templates for commonly used worksheets. Learn how to apply filters and sort data into key elements. Getting Excel to make decisions using the ‘If’ function, Pivot Table and conditional formatting,. Existing Excel users whom have not had any formal training finds this course extremely useful, as it will bridge the gap in your knowledge of Excel.

Our Advanced Excel Training will really show you how to make Excel work for you. The course is aimed at fairly experienced Excel users whom need to learn more complex functions, nesting, data manipulation and protect data using the security features. You will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information.

Excel VBA course is suitable for advanced users of Microsoft Excel responsible for very large and variable amounts of data, or teams, who want to automate routine activities in Excel based on their requirements.


Benefits

Advanced Features of Ms-Excel. Advanced Excel course also enables the student to utilize, the advance features taught in this course in an office environment with maximum efficiency.

In this course you will learn to write macros in Excel using programming languageVisual Basic Application (VBA). Recorded macro can be edited to customize it & one can directly go for coding to accomplish the required tasks.


Prerequisites

Good proficiency in Microsoft Excel is required


Adv. Excel Training Duration

Regular classroom based training: 12 Weeks, 60 minutes of theory & practical session per day.

Modules

Course Content

Tpoics Covered:- Excel Advanced Formula, Excel Macro and Excel VBA

  1. Spreadsheet basics
  2. Creating, editing, saving and printing spreadsheets
  3. Working with functions & formulae
  4. Graphically representing data : Charts & Graphs
  5. Analyzing data : Data Menu, Subtotal, Filtering Data
  6. Working with functions & formulaeFormatting worksheets , Securing & Protecting spreadsheets
  1. Use the Function Wizard, Common functions
  2. (AVERAGE, MIN, MAX, COUNT,COUNTA, ROUND, INT)
  3. Nested functions , Name cells /ranges /constants
  4. Relative, Absolute, Mixed cell references : >,<,= operators
  5. Logical functions using IF, AND, OR, NOT
  6. The LOOKUP function , Date and time functions , Annotating formulas
  1. Sub Total Reports, Auto Filter
  2. Password Protecting Worksheets
  3. Linking Multiple Sheets
  4. Sheet Referencing
  5. Linking Between Word/Excel/Ppt
  6. What-if-analysis
  7. GOAL SEEK
  8. Reporting
  9. Consolidation of Data
  10. Data Validation
  11. Pivot Table and Chart
  1. NESTED IF
  2. Character Functions
  3. Date Functions
  4. Age Calculations
  5. LOOKUP, VLOOKUP, HLOOKUP, COUNTIF, SUMIF
  6. INDIRECT, MATCH, INDEX, AVERAGEIF, DATEDIF, NETWORKDAYS
  7. Financial FUNCTION, String FUNCTION...
  1. Performing complex calculations more efficiently,
  2. Using various Excel functions and executing in Dynamic projects
  3. Organizing and analysing large volumes of data
  4. Creating MIS reports
  5. Designing and using templ1ates
  6. Consolidating and managing data from multiple workbooks.
  7. Writing conditional expressions (using IF)
  8. Using logical functions (AND, OR, NOT)
  9. Using lookup and reference functions
  10. (VLOOKUP, HLOOKUP, MATCH, INDEX)
  11. VlookUP with Exact Match, Approximate Match
  12. Nested VlookUP with Exact Match
  13. VlookUP with Tables, Dynamic Ranges
  14. Using VLookUP to consolidate Data from Multiple Sheets
  15. Advanced Formatting: Using conditional formatting option for rows, columns and cells
  16. Formatting & customizing Pivot tables
  17. Using advanced options of Pivot tables, Pivot charts
  18. Consolidating data from multiple sheets
  19. Using external data sources
  20. Using data consolidation feature to consolidate data
  21. Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
  22. Viewing Subtotal under Pivot
  23. Advanced Date & Time Functions
  24. Data Validation in financial functions
  25. Advanced Shortcut Keyboard Commands
  26. Create Forms with Radio Buttons, Selection tools,
  27. other programming tools.
  28. Creating Slicers (2013 version)
  1. Create Functions & Procedures
  2. Design & Create reports
  3. Understanding Security Levels
  4. Security Levels & Meaning
  5. What is Function and Subroutine
  6. Writing Functions using VBA Editor
  7. Calling function in other workbook
  8. Relative & Absolute Macros
  9. Recording and running macro
  10. Storing macro in different books
  11. Creating buttons & assigning macros
  12. Creating My Menu
  13. Editing macros using VBA Editor
  14. Understanding the project explorer
  15. Exploring windows properties
  16. Modifying code & Debugging code
  17. Variables, Data types & Constants
  18. Arrays ,Object Variables
  19. Conditional structures (If Then,Select Case)
  20. Logical Operators / Looping constructs
  21. (For loops, Do loops, While)
  22. Built-in functions, Creating SubroutinesCreating forms, Form events
  23. Creating custom menus & toolbars
  24. Creating Add-ins
  1. What is VBA?
  2. Quick Review of Macros
  3. Using the Visual Basic Editor
  4. Uses of Record Macro
  5. Understanding and creating Modules
  6. How to create a Message Box
  7. Write program to update and retrieve information using Input Box
  8. Conditions (IF, AND, OR etc.)
  9. Understanding and using Select Case statement
  10. How do I define a Variables and Rules for defining a Variable Name and Type
  11. Creating And Using Variables
  12. Working with For …. Next procedure
  13. Working with Do While …. Loop procedure
  14. Calling Procedures from one procedure to another
  15. How to Save and Protect Modules
  1. Create or Add Single and Multiple Workbooks
  2. Workbooks Save and Save As with Password
  3. Open Single and Multiple Workbooks
  4. Close Specific and Multiple Workbooks
  5. Get Count of Workbooks
  6. To refer a particular Workbooks
  7. Activate from one Workbook to another Workbook
  8. Open Workbooks from Specific Path
  9. Get Workbooks Name and Path
  10. Hide and Unhide for Single and Multiple Workbooks
  11. Calculate entire Workbook
  12. Protect and Unprotect Worksheets
  1. Insert a Single and Multiple Worksheets
  2. Delete Specific and Multiple Worksheets
  3. Get Count of Worksheets
  4. Select a Specific and All Worksheets
  5. Get All Worksheets Name
  6. Hide and Unhide for Single and Multiple Worksheets
  7. Rename for Single and Multiple Worksheets
  8. Protect and Unprotect Worksheets
  9. Sort and Move Worksheets
  10. Calculate entire Worksheet
  1. Insert single and multiple Row, Column and Cells
  2. Delete single and multiple Row, Column and Cells
  3. Get Range or Address of Cell and Selection
  4. Navigate from one Cell to another Cell
  5. Select specific Range, Cell, Rows and Column
  6. Types of Selection and Offset method
  7. All the Options in Paste Special
  8. All the Options in Go To
  9. How to update the Formulas
  10. Cut, Copy and Paste Options
  1. Files Automations real time scenarios
  2. VBA Sample Project Report preparation
  1. What is Function?
  2. User Defined Function
  3. Create a Function using IF Condition and Select Case
  4. Create an Advanced IF Conditional Fuctions
  5. Create an Advanced Computation and Conditional Function
Post an Enquiry

Post an Enquiry

Address

1/583, ECR, KOTTIVAKKAM
CHENNAI / TAMIL NADU / INDIA

E-mail

astroinfo@astrotech.in
astroeq.com@gmail.com

Contact No.

+91 9710107874
(044) 438 55 773