VBA in MS Excel – Advanced Level Data analysis
Remote and stationary training options
View the training programme
- What will you gain from the 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.
- Is this training for you?
Profile of participants
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.
PreparationRequired knowledge:
- 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)
- Duration
Duration:
- 3 days, 24 training hours
- Detailed training program
Detailed training program
Start of the training course – organizational matters
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
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
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
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
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
End of the training course (test + discussion of the results, training evaluation)
Training mathods
Lecture + workshops (amount proportional to lecture) + workshops check (individual) + workshops discussion.
- Development path
- VBA language in MS Excel