- Tel : +91 974 974 9596
- Email : info@flairtechnologies.in
Microsoft Excel & VBA Training
An Microsoft Excel course offers comprehensive instruction on one of the most widely used spreadsheet software globally. Participants learn essential skills such as data entry, formatting, and formula creation, as well as advanced features like pivot tables, charts, and data analysis. This course is invaluable for professionals in various fields, helping them improve productivity, data management, and decision-making abilities.
Why Chose Flair Technologies for Microsoft Excel & VBA Training?
MICROSOFT EXCEL (VBA-MACROS):
About Microsoft Excel:
Microsoft Excel is a spreadsheet program used to store, organize, and analyze data. It is used by businesses, organizations, and individuals to store and analyze data, create charts and graphs, and perform calculations. Excel is used for a variety of tasks, such as budgeting, forecasting, data analysis, and more. Major vendors for Microsoft Excel include Microsoft, Apple, and Google. Microsoft offers Excel as part of its Office suite, while Apple and Google offer their own versions of the program. All three vendors offer a variety of features and tools to help users get the most out of their data.
Microsoft Excel & VBA Course Outline:
Microsoft Excel:
Introduction to Excel:
- Short Keys
- Formatting (Applying Borders, Colors, and Font Styles)
- Conversion of Formats
- Merging and Wrapping the Text
- Conditional Formatting
- Format as Table
- Inserting and Deletion of Rows, Columns, and Sheets
- Row Height and Column Width Formula-Based
- Conditional Formatting
Tables, Illustrations and Charts:
- Hide and Unhide of Rows, Columns, and Sheet Protecting Sheet and Workbook
- Move or Copy, Rename Sheet and Tab Color Filling Series of Numbers and Dates Sorting and Filtering Pivot Tables
- Usage of Formulas in Pivot Tables
- Inserting Pictures, Clip Art, Text Box, Shapes, and Smart Art Usage of Charts (Column, Pie, Bar, Line) Usage of Dynamic Ranges in Charts.
Proofing, Comments and Changes:
- Inserting Hyperlinks
- Linking Sheets, Cells, Workbook, Range, and Mail
- Header-Footer, Word Art, and Signature Line
- Inserting Objects
- Protect Sheet
- Protect Workbook
- Hiding Formulas
- Sharing Workbook
- Track Changes (Highlight, Accept, and Reject Changes)
- Inserting and Editing Comments
Page Setup, Scale to Fit and Arrange:
- Inserting Comments and Spell Check
- Freeze Panes (Rows and Columns)
- Save Workspace, Switch Windows, and Split Window
- Arranging the Window (Vertically and Horizontally) Page Layout, Gridlines, and Formula Bar Paper Margins, Gridlines, and Paper Size
- Page Breaks, Applying Background, and Print Titles Row Repeat at Top and Print Preview
Connections and Data Tools:
- Data Importing (From Access, Web and Text)
- Other Sources (SQL server and XML)
- Advance Sorting and Filtering
- Text to Columns
- Removing Duplicates
- Data Validation
- List box, Formula based restrictions
- Customization of error alert and Input box
- Types of Alerts (Stop, Warning and information)
- Highlighting the Invalid data
Data Tools and Outline:
- Data Consolidation
- Scenario Manager
- Goal Seek
- Data Table
- Group and Ungroup
- Adding subtotals to the list
- Defining name to the range
- Name manger editing
- Trace precedents/Trace Dependents
- Evaluate Formulas
Text, Arithmetical Functions:
- UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, MID
- FIND, TRIM, CLEAN, CHAR, CODE
- CONCATENATE, SUBSTITUTE, EXACT, REPT
- REPLACE, SEARCH, VALUE and TEXT.
- ABS, ROUND, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and RANDBETWEEN
- Activities based on the above functions
Date & Time and Logical Functions:
- DATEDIF, DATE, TODAY, NOW, WEEKDAY, MONTH, YEAR, YEARFRAC, NETWORKDAYS, DAYS360, MINUTE, HOUR, SECOND, WEEKNUM, EDATE and EOMONTH.
- IF, AND, OR, NOT, TRUE, FALSE, IFERROR and Nested Functions. Activities based on the above functions.
Statistical and Information Functions:
- AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA
- COUNTBLANK, COUNTIF, COUNTIFS, LARGE, SMALL, MAX, MIN, RANK, ROWS, ROW, COLUMN and COLUMNS
- ISBLANK, ISERROR, ISNUMBER, ISTEXT and ISNA Activities based on the above functions
Lookup and References:
- VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, CHOOSE and INDIRECT.
- VLOOKUP with MATCH, IFERROR, WILDCARD and CHOOSE.
- Combination of Formulas
- Array Formulas
- VLOOKUP and COLUMN
- VLOOKUP with IF and VLOOKUP with AND Activities based on the above functions
Activities:
- SUM and CHOOSE
- Sorting the data using formula
- Multiple VLOOKUP
- Multiple INDEX
- Leave Tracker using conditional formatting
- Using formula in conditional formatting to highlight second repeated value
Summarizing of Excel:
- Activities based on Real Time
- Scenarios Summarizing Of Excel
SQL :
SQL :
- Table Creation, Insert, Update, Delete data, Select
- Clause Operations (Where, Group by, Having, Order by)
- Operators (IN, LIKE, ISNULL, Union)
- Joins
- Sub Queries (Inner Query & Outer Query)
- Data Modeling using SQL
- Views Creation
- Analytical Functions (Rank, Dense_Rank, Partition by)
VBA Macros:
Introduction to Macros:
- Introduction to Macros
- Introduction to VBA
- Importance of Developer Tab and VB Window Path to Get into VB Window
- Introduction to different windows in VB Enabling the Macros to run the program Macro workbook
- saving Introduction to cell reference
Introduction to Properties:
- Introduction to object model
- Introduction to property and methods
- Importance of arguments
- Usage of different property and methods with cells Introduction to sheet reference
- Usage of different property and methods with sheets
Declaration of Variables:
- Introduction to workbook reference
- Usage of different property and methods with workbook
- Introduction to Variable Declaration
- Importance of Different Data Types
- Different Levels of Declaration of Variables
- Different Ways of Declaration of Variables
Loop Statements:
- Introduction to Message Box
- Introduction to Input Box
- Introduction to Different Loop Statements
- FOR_NEXTLOOP
- Activity of FOR_NEXTLOOP
Conditional Statements:
- Introduction to Conditional Statement
- Introduction to Different Conditional Statements
- Activity on Different Conditional Statements
- Activity on Different Conditional Statements with LOOP
Go To Command:
- Select Case Decision Structure
- Activity on Select Case Structure
- Introduction to GOTO Label Command
- Activity on GOTO Label Command
Loops:
- Introduction to TO_DOLOOP
- DO_WHILE LOOP
- Activity on DO_WHILE LOOP
- DO_UNTIL LOOP
- Activity on DO_UNTIL LOOP
- Introduction to FOR EACH NEXT LOOP
- Usage of SET statement
- Activity on FOR EACH NEXT LOOP
Data Segregation:
- Segregation of Data in Static Way
- Introduction to Rows Count and Columns Count
- Count Segregation of data in dynamic way
User Defined Functions:
- Introduction to USER DEFINED
- Function Activities on USER DEFINED
- Function Activity on File Browser
Recording of Macros:
- Record Macro
- Running a Macro
- Running a Macro from the Macros Dialog Box
- Creating a Short Key to Run Macro Running a Macro with a Short Key
- Assigning a Macro to a menu or tool bar Editing a Macro with VB
File System Objects and File Directories:
- Designing and Creating Objects (Files, Folders)
- Performing Different Operations on Objects (Insert, Update, Delete)
- Looping the Objects
Active Directory Objects (ADO’s):
- Basic Active Directory Queries
- Working with Active Directory Objects
- Advanced Active Directory Queries
- Different Types of Connection Strings
- Working with Sql queries, joins
- Working with different sql and access db objects
User Forms:
- Designing and Creating Forms
- Working with Controls
- Creating Custom Dialog Boxes
- User Forms Properties, Methods, and Event
Error Handling:
- On Error Go to Zero
- On Error Go to Resume
- On Error Go to Label
User Defined Functions:
- UDF on Extracting Numbers
- UDF on Extracting Data
- UDF on Number to Word
- Segregation of data from one cell to different columns Real time activities
Activities:
- Working on Dynamic Outlook Mails (Email Automation)
- Working on PPT Automation
- Working on Word Automation
Remember that this is a detailed outline and can be adjusted based on the duration of the course, the depth of coverage desired for each topic, and the level of expertise of the participants. Additionally, as technology evolves, new tools and practices might emerge, so it’s essential to keep the course content up-to-date to reflect the latest trends and best practices.
Can You Get a Job with a Certification ?
Microsoft Excel training and certification courses are designed to help individuals learn how to use the powerful spreadsheet software. These courses cover topics such as creating and managing worksheets, using formulas and functions, and creating charts and graphs. Upon completion of the course, individuals can earn a certification that demonstrates their proficiency in Excel. Expands career opportunities: with Excel being widely used in industries such as Finance, Marketing, Sales, Operations and Human Resources a certification opens up a range of career opportunities. 82% of jobs require Excel skills and/or experience with productivity software. Certified Excel skills can increase your earning potential.
What you will Learn in Excel Course ?
Explore our Excel courses for beginners and gain knowledge in a diverse range of topics. Learn the basics of Excel, including data entry, formatting, and formulas. Discover data analysis techniques such as sorting, filtering, pivot tables, and charts. Master formatting options and conditional formatting to improve spreadsheet readability. Understand data validation to ensure accurate data entry. Learn to work with multiple worksheets, link data, and consolidate information. Explore printing and sharing features to present your work professionally. Whether you’re new to Excel or looking to expand your skills, our beginner-friendly courses provide a solid foundation to enhance your understanding of Excel concepts.
Who can Take this Course ?
Choose The Best
College Students those are interested to pursue their career as a Data Analyst can take this program. This Program will help you get hired with attractive salary. package after completing your college. This is the right time for you to utilize & learn as many skills that will help you in hiring as well as to get attractive salary.
This program will help you switch from your current domain to a Microsoft Excel. We had seen a huge number of working professional took this program & got amazing hikes, switched from boring jobs to a Microsoft Excel & even got 100% hike from their current salary.