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)
