VBA in MS Excel - Data analysis

Advanced Level
    Duration
    Start hour
    Level

      VBA in Microsoft Excel – Advanced Level. Data analysis.

      You will understand the mechanism supporting Data Analysis!

      For Whom?

      VBA in MS Excel - Data analysis training course is indented, inter alia, for analysts, accountants, warehousemen, production support/organization employees.

      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

      Similar trainings

      Consultant:

      Sylwester Woźniak
      Sylwester Woźniak

      sylwester.wozniak@comarch.com

      +48 12 687 78 11

      +48 608 646 465