Description
This certificate course advanced Excel with VBA is designed o help you become a successful Data Analysts and is highly recommended for candidates who are just starting their career in Analytics. This powerful course will provide you how to make insightful dashboards.. It is an extremely popular and powerful tool that possesses the ability to rapidly develop professional-looking workbooks and dynamic reports to efficiently summarize and represent data. This course gives you the ability to create and share the basic employee resource schedules, a basic accounting program for check book ledger, to track product sales on a daily, weekly, monthly or quarterly basis. We will also learn about creating a custom command, custom toolbar button etc. After completing this course you will able to work with basic and advance calculations, graphical tools and using VBA you can create automated reports using procedures with respect to different events, understanding control structures, creating user forms, validating the data, and debugging and handling errors.
Syllabus
- 1.Working with Advanced Functions
- Working with Functions
- Using the IF Function
- Nesting Functions
- Using Multiple Conditions with the IF Function
- Using Table Lookup Functions (HLookup and VLookup)
- Using Text Functions
- 2. Analyzing Data
- Using Automatic Outlining
- Consolidating Information by Position or Category
- Inserting Subtotals
- Creating an Advanced Filter
- Using Database Functions
- 3. Working with Data
- Importing Data from a Text File
- Exporting Data
- Converting Text to Columns
- Connecting to Data in an Access Database
- Creating a Web Query
- Importing Web Data using a Saved Query
- Working with Hypertext Links
- 4.Create & Work with PivotTables
- Understanding PivotTables
- Creating a PivotTable using Worksheet Data
- Creating a PivotTable using an External Data Connections
- Laying out a PivotTable on a Worksheet
- Modifying PivotTable Fields
- Using a Report Filter
- Refreshing a PivotTable
- Formatting a PivotTable
- 5.Enhancing PivotTables
- Working with Summary Functions
- Sorting Items in a PivotTable
- Creating a Slicer
- Grouping Data
- Applying Label and Value Filters
- Creating a Calculated Field
- Creating a Calculated Item
- Creating Charts from PivotTables
- 6.Working with Data Analysis Tools
- Creating Scenarios
- Working with Scenarios
- Using Goal Seek
- Using One-Input Data Tables
- Using Two-Input Data Tables
- Understanding Solver
- Defining a Problem
- Solving a Problem
- Generating a Report of Results and Alternate Solutions
- 7.Working with VBA
- Recording and Running Macros in Excel
- Introduction to the Excel VBA Editor
- Creating Procedures and Functions
- Working with the Excel Object Model
- Controlling Program Flow
- 8.Excel VBA Advanced
- Advanced Procedures
- Advanced Variables
- Arrays
- VBA Controls
- Creating Custom User Forms
- Workbook and Worksheet Events
