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.

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.

Preparation

Required 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)

• 3 days, 24 training hours

1. Start of the training course – organizational matters
2. Conditional cell formatting
   2.1 Differences in conditional formatting between MS Excel versions
   2.2 Conditional format operations
   2.3 Ways to prevent/cope with conditional formatting errors
3. Autofilter
   3.1 Using autofilter
   3.2 How to enable/disable autofilter
   3.3 How to filter by values in one column
   3.4 How to filter by values in two columns
   3.4 How to filter texts using text format
   3.5 How to show from-to number range
   3.5 How to use “Top 10” mechanism
   3.6 How to clear filter criteria
4. Pivot tables
   4.1 How to insert pivot table into worksheet
   4.2 How to check the number of aggregates in a workbook
   4.3 How to create a pivot table
   4.4 How to check the number of pivot tables in a worksheet
   4.5 How to change the pivot table view (from “new” to “classic”)
   4.6 How to check data type in a field
   4.7 How to add fields to a pivot table
   4.8 How to count fields in pivot table areas
   4.9 How to list field names (from data range)
   4.10 How to remove selected grouping fields
   4.11 How to clear pivot table
   4.12 How to select and remove pivot table
5. Data field configuration
   5.1 How to enter the current name of the searched data field
   5.2 How to set the grouping/calculation function
   5.3 How to change field order
   5.4 How to arrange data fields by columns
   5.5 How to format field in data range
   5.6 How to show numbers as percentages
   5.7 How to remove field from data range
6. Configuration of grouping areas
   6.1 How to show/hide details for selected grouping field [optionally, that is, if time permits]
   6.2 How to expand selected category from grouping field [optionally]
   6.3 How to change page/filter (for texts)
   6.4 How to change page/filter (for dates)
   6.5 How to list components (available) of a page field
   6.6 How to list components of a selected pivot table field
7. Date/time processing (date/time functions)
   7.1 Basic operations
   7.2 How to get current date/timea
   7.3 How to get elements of specified date (year, month, day)
   7.4 How to get elements of specified time (hour, minute, second)
   7.5 Calculations on dates/time
   7.6 How to add/subtract years/months/days from date
   7.7 How to add/subtract hours/minutes/seconds from date
   7.8 How to calculate time offset (by months, quarters, weeks, etc.)
   7.9 How to calculate time difference (in months, quarters, weeks, etc.)
   7.10 How to check period number for date (quarter, week, etc.)
8. Names of weekdays and months
   8.1 How to get weekday number from date
   8.2 How to get weekday name
   8.3 How to convert month number to month name
   8.4 Timer – timing or program control (for instance, how to stop the program for a specified time) [option]
9. Charts
   9.1 Creating charts
   9.2 Modifying common chart elements
   9.3 Modifying charts with axes

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

Training methods:

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

• Visual Basic for Applications in MS Excel - advanced level

For detailed information, please reach out to us at training@comarch.com.