INTERMEDIATE EXCEL FOR PROFESSIONALS
This Excel VBA training will show you how to make the most of the programming possibilities in Excel. It aims to give advanced excel uses a thorough introduction to Excel VBA programming.
The key topics that it covers include recorded macros, the macro editor, using and defining variables, creating custom forms and working with the various objects within Microsoft Excel.
Excel VBA is very widely used to automate repetitive tasks in Excel. Typical examples include creating charts from a standard data input or creating and formatting reports from a raw unformatted set of data, but given how widely Excel is used it is used in business it is used in a huge number of applications.
The course is instructor-led and involves working on a number of practical, hands-on examples and exercises in a workshop environment. Led by a highly experienced Excel VBA trainer with many years of experience our course ensures that you will leave with lots of practical Excel VBA experience as well as useful tips and tricks.
A foundation in basic Excel techniques is advised.
By the end of this course candidates will master the techniques of using the right excel tools to solve problems more efficiently. Also new features in the current Excel version will be highlighted.
Topics covered include Conditional Formatting wizard, Flash Fill, Name Manager, various functions (string, maths, logical, lookup), pivot tables, slicers and pivot charts to build a BI dashboard.
Having practiced all of these new skills extensively during the course you will be able to hit the ground running on your return to the office and should be able to complete your Excel work far more proficiently.
Attending delegates will be presented with a Certificate of Attendance upon completion of training.
We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.
Each delegate will be given a digital courseware manual to refer to after the course.
Apply Fill Tools, Conditional Formatting and Filtering
- Using advanced Fill Series options
- Flash Fill
- Apply advanced conditional formatting and filtering
- Creating filters
- Managing conditional formatting rules
- Conditional Formatting with Sorting & Filtering
Working with Data
- Getting External Data
- Restricting Cell Entries to Certain Data Types
- Data Validation Tools
- Removing Duplicate Rows from a Worksheet
- Sorting Data on a Single & Multiple Criteria
- Sorting Data Using Cell Attributes
- Using AutoFilter
- Filtering Data Using Cell Attributes
- Removing Duplicates in a Table
- Apply functions in formulas
- Functions Wizard
- Absolute vs Relative Referencing
- Using SUM, COUNT, SUBTOTAL
- Using nested functions
- Using the IF, AND, and OR functions
- Using the VLOOKUP function
- Using the NOW and TODAY functions
- Using text manipulation functions like LEFT, RIGHT, MID, TRIM, CONCATENATE
Data Analysis & and Manipulation using PivotTables & PivotCharts
- Create and manage PivotTables
- Modifying field selections and options
- Filters in PivotTables
- Creating slicers
- Create and manage PivotCharts
- Manipulating options in existing PivotCharts
- Multiple PivotTables on the same Sheet
- Applying styles to PivotCharts
- Building a BI Dashboard