VBA in MS Excel – Advanced Level - Data analysis

You will understand the mechanism supporting Data Analysis!

Full program VBA Data Analysis training

Training objectives

This training course is to empower participants’ expertise while doing their job, thus:

Understand the mechanism supporting data analysis (such as autofilter, pivot tables and date/time functions)
Understand chart designs created in spreadsheets
Enhance skills in creating reliable and effective algorithms
Develop a set of ready-to-use procedures – useful tools to apply in daily work.

Skills

After this training course, the participant will be able to do the following in an automated way:

analyze and model data using pivot tables
analyze data based on date/time
visualize the status based on the data collected in a worksheet (such as, “warning status” and “emergency status” of inventory, company resource loads, etc.)
analyze databases created in spreadsheets
present calculation results using diagrams

Audience profile

This training course is intended for individuals who, among other things:

analyze large amount of data collected in spreadsheets
are in charge of stock level control and logistics (using spreadsheets)
prepare extensive lists and reports based on pivot tables
It is indented, inter alia, for analysts, accountants, warehousemen, production support/organization employees

Requirements

very good practical knowledge of MS Excel (participants should use intensively MS Excel in daily work)
taken training course “VBA in MS Excel – Beginners Level. Basics of computer programming” and “VBA in MS Excel – Intermediate Level. Worksheets, workbooks, files” (or knowledge and background of the training material)

Training delivery method

Lecture + workshops (amount proportional to lecture) + workshop check (individual) + workshop discussion.

Duration

3 days, 24 training hours

Theoretical and practical knowledge

1. Start of the training course – organizational matters

2. Conditional cell formatting

Differences in conditional formatting between MS Excel versions
Conditional format operations
- How to remove cell formatting conditions
- How to add formatting conditions for cell
- Workshops
Ways to prevent/cope with conditional formatting errors

3. Autofilter

Using autofilter
- How to enable/disable autofilter
- How to filter by values in one column
- How to filter by values in two columns
- How to filter texts using text format
- How to show from-to number range
- How to use “Top 10” mechanism
- How to clear filter criteria
- Workshops

4. Pivot tables

How to insert pivot table into worksheet
- How to check the number of aggregates in a workbook
- How to create a pivot table
- How to check the number of pivot tables in a worksheet
- How to change the pivot table view (from “new” to “classic”)
- How to check data type in a field
- How to add fields to a pivot table
- How to count fields in pivot table areas
- How to list field names (from data range)
- How to remove selected grouping fields
- How to clear pivot table
- How to select and remove pivot table
- Workshops
Data field configuration
- How to enter the current name of the searched data field
- How to set the grouping/calculation function
- How to change field order
- How to arrange data fields by columns
- How to format field in data range
- How to show numbers as percentages
- How to remove field from data range
Configuration of grouping areas
- [optionally, that is, if time permits] How to show/hide details for selected grouping field
- [optionally] How to expand selected category from grouping field
- How to change page/filter (for texts)
- How to change page/filter (for dates)
- How to list components (available) of a page field
- How to list components of a selected pivot table field
- Workshops

5. Date/time processing (date/time functions)

Basic operations
- How to get current date/time
- How to get elements of specified date (year, month, day)
- How to get elements of specified time (hour, minute, second)
Calculations on dates/time
- How to add/subtract years/months/days from date
- How to add/subtract hours/minutes/seconds from date
- How to calculate time offset (by months, quarters, weeks, etc.)
- How to calculate time difference (in months, quarters, weeks, etc.)
- How to check period number for date (quarter, week, etc.)
Names of weekdays and months
- How to get weekday number from date
- How to get weekday name
- How to convert month number to month name
- [option] Timer – timing or program control (for instance, how to stop the program for a specified time)
Workshops

6. Charts

Creating charts
- How to insert chart (as a separate worksheet or in an existing worksheet)
- How to get data range and chart type
- How to insert chart title
- How to show/hide legend (and set its position)
- How to show data labels
- How to set data label position
- How to remove chart
- Workshops
Modifying common chart elements
- How to set text parameters for entire chart
- How to set chart background color
- How to set background color for plot area
- How to block automatic chart resizing (when hiding columns/rows of a worksheet)
- How to set chart size
- How to set chart position (on screen)
- How to set text parameters for chart title and data labels
- How to set data series color
- How to set number format for data labels
- How to set text parameters for legend
- Workshops
Modifying charts with axes
- How to add another data series to chart
- How to set titles for axes
- How to set text parameters for axis titles and descriptions
- How to change number formatting and scale for value axis
- How to show/hide gridlines for value axis
- How to resize gaps between bars or columns
- Workshops

7. End of the training course (test + discussion of the results, training evaluation)

Development path

Visual Basic for Applications in MS Excel - advanced level