About the Course
Its a 5 Sunday's workshop, 4 hrs each Sunday. This workshop will be conducted at a Microsoft Authorized Centre. The fee of the course includes a Microsoft Excel Specialist Exam voucher cost Rs. 1500.
This course is structured and comprehensive training program for learning Microsoft Excel in depth. It is full of real world examples. This is 100% PRACTICAL TRAINING PROGRAM, where Mr. Lokesh Lalwani (a Microsoft Certified trainer with 6+ years or training experience) will share a practical excel spreadsheet with you and you will learn all the excel formulas and advanced functions by doing it in your system.
The aim of this course is to make the participants become productive in Excel.
Topics CoveredA broad outline of the topics which will be covered are as follows:
- Excel Basics with Formula writing
(Excel Interface, File conversion, Customizing Ribbon, Name Box etc.)
- Using Text Functions
(Left, Right, Mid, Concatenate, Upper, Lower, Proper, Len, Rept, Find, Search, Substitute, Replace, Trim and Text to Column)
- Date & Time functions
(Today, Now, Day, Month, Year, Workday and Networkdays)
- Logical Functions
(If, And, Or, Not and performing multiple conditions with Nested If)
- Mathematical Functions
(ABS, Rand, Randbetween, Round, Roundup, Rounddown, Sum, Sumif and Sumifs)
- Statistical Functions
(Count, Counta, Countblank, Countif, Countifs, Average, Averageifs, Max, Min, Small and Large)
- Lookup functionality
(Lookup, Vlookup, Hlookup, Match and Index. Searching values on the basis of Exact and Appropriate relationship. Performing multiple Lookup value, Performing lookup in multiple fields, and many more)
- Financial Functions
(PV, FV, Rate, NPV, PPMT and IPMT)
- Naming Conventions
(Applying a Name to a cell or range of cells, Modifying, Deleting and Modifying the names)
- Array Formulas
(Introducing Array Formulas, Using Array Formulas in different situations)
- Playing with Data
(• Using Inbuilt Lists and creating custom lists
• Using Sort to Arrange data alphabetically or through customized order
• Using Sort to Arrange data either vertically or horizontally
• Extracting data by using Custom Filters
• Adding Multiple conditions to extract data using Advance Filters
• Performing calculations on one field depending upon the value of the second field of the database using Subtotals
• Importing the data from different application to Excel
• Removing duplicate records from the database
• Linking the sheets and workbook through Hyperlinks
• Representing the Data graphically using through various Chart Types such as Column Chart, Bar chart, Line Chart, Pie chart and Scatter chart.
• Using Keyboard Shortcuts)
- Advance Data Handling
(• Designing the Forms for the user using Data Validations
• Preparing dropdown lists to fill up a form
• Protecting and Sharing the Worksheets & Workbooks
• Consolidating the Data from different sheets using Data Consolidation)
- Analyzing Data by Data Analytical Tools
(• Performing What If Analysis by using Data tables and Scenarios Manager
• Using Pivot Tables & Pivot Charts- Creating and Modifying a Pivot Table Layout, Creating Groups, Inserting a Calculated Field and Representing data through Pivot Charts
• Using Goal Seek and Solver)
- Introduction to Macros
(• What is a Macro?
• Displaying the Developer Tab
• About Macro Security
• Saving Workbooks that contain macros
• Understanding different types of macros)
- Recording, Editing & Deleting of Macros
(• Understanding how to record VBA Macros
• Examples of recording macros
• Examining, Testing and Editing macros
• Absolute versus Relative referencing while recording macros
• Assigning macro to a keyboard shortcut or a button
• Macros In Depth
• Inset Form controls (Button, combo box, spin button, option button etc.)
• Insert ActiveX controls (Labels, Pictures, Toggle buttons, scroll bars etc.)
- Introduction to VBA Programming
(• Understanding VBA
• Know how of VBA coding
• Basics of writing VBA Code
• Debugging errors)
Who should attendAny Working Professionals & Students who wants to improve their Excel Skills or anyone who has a passion towards Excel.
Pre-requisitesMicrosoft Excel 2007/2010/2013
What you need to bringLaptop (If you don't have one, we will provide you the same.)"
Key Takeaways- An In-depth excel functionality
- From basics of writing a formula to complex functions
- Functions explained in most logical and simplest manner
- Suitable for beginners to advanced users. ideal for users who learn faster when shown.
- Learn different Functions from Microsoft Certified trainer from your own desk.
- You can post any question or query on the trainer's blog.
- Post training support (Trainer will share his ph. number, email id and his blog details so that during or after the training if you find any issue, you can just reach the trainer and he will solve your concern)