Certificate Authentication

1. Excel Advanced

Prerequisites: Ability to work with a spreadsheet, knowledge of Windows.

Overview: Participants will be able to excel themselves, in the most optimal way to create, consolidate and manage data. A number of tools designed to streamline the work often significantly reduces the time to activities carried out so far and can help you design an application that could perform new tasks.

Course Outline:

Adjustment of the working environment

• Keyboard shortcuts, facilities
• Create and modify toolbars
• Excel Options (autosave, input, etc.).
• Options paste special (transpose)
• Formatting (style, format painter)
• Tool go to

Organization of information

• Management sheets (naming, copying, color change)
• Assign and manage the names of cells and ranges
• Protect worksheet and workbook
• Secure and encrypt files
• Collaboration and track changes, comments
• Inspection sheet
• Create your own templates, charts, worksheets, workbooks

Data analysis

• Logic
• Basic functions
• Advanced functions
• Scenarios
• Search by
• Solver
• Charts
• Graphics support (shadows, charts, AutoShapes)

Database management (list)

• Data consolidation
• Grouping and outlining data
• Sorting data (over 4 columns)
• Advanced data filtering
• Database Functions
• Subtotal (partial)
• Tables and Pivot Charts

Cooperation with other applications

• Get External Data (CSV, TXT)
• OLE (static and link)
• Web Queries
• Publication sheets on site (static and dynamic)
• Publication PivotTables

Automation of work

• Conditional Formatting
• Create your own formats
• Check the correctness
• Recording and editing macros

Visual Basic for Applications

• Create your own functions
• Results in VBA
• VBA Forms

Topics Customer wants to deal with:

• VBA
  • Understand how the Visual Basic editor works in Excel.
  • Learn to work with the VBA object model.
  • Learn to work with VBA object variables.
  • Learn to work with string variables.
  • Learn to work with date variables.
  • Learn to work with numeric variables.
  • Learn to use the selection structure.
  • Learn to use the Select Case statement and the MsgBox function.
  • Learn to work with the repetition structure.
  • Learn to work with option button, check box, and list box controls.
  • Learn to use Automation.
  • Learn to trap errors
• Macro Recorder
• Forms
• Loops
• User Defined Functions

2. Excel Intermediate Level

Adjusting Excel Environment and Automation of Work in Excel

•  Hotkeys, facilities
•  Create and modify toolbars
•  Options excel (auto save, input, etc.)
•  Options paste special (transpose)
•  Formatting (style, format painter)
•  Tool ‐ go to
•  Fast text‐to‐column
•  Delete duplicate data
•  Forcing input the correct data ‐ how to ensure that data were specific format
•  Sorting multi‐level ‐ the rules and the proper sorting options
•  Macros. Running and recording a macro (modifying the code/ editing – no?), where to store it;
•  Conditional Formatting. Create a conditional formula
•  Validating cell entries (Data Validation)
•  Using lookup functions
•  Tracking error performance formulas

Automating work with External Data

•  Combining data from other Windows programs
•  Access to external data, create a Web Query
•  Using queries to selectively retrieve information from external databases

Pivot Tables Advanced

• Overview
•  Basic information about PivotTables
•  Overview of PivotTable fields.
•  Create a PivotTable report with a list of data
•  Change the layout of a PivotTable
•  Creating a PivotChart
•  Change the layout of a PivotChart

Information Filtering

•  Filtering fields
•  Changing the type of calculation data fields
•  Grouping PivotTable data elements
•  Entering additional calculations to the data area
•  Add a calculated field
•  Adding a calculated
•  Get values from a PivotTable report
•  Performing the pivot of information from external database

Pivot Chart

•  Changing a PivotChart
•  Filtering fields PivotChart
•  Grouping elements PivotChart field
•  Add a table of data to the PivotChart
•  Visualize data using pivot chart
•  Calculated Item and Calculated Field

Analysing Data in Excel

Part I Lookups and data tables practical information:

• Using MATCH and INDEX
• Advanced list management
• Exploring database functions

Part II Advanced Functions

• Logical functions
• Math and statistical functions
• Financial functions

Topics Customer wants to deal with:

• Pivot tables (only touched them briefly in the basic course)
• Easy macros (e.g. macro recorder)
• Advanced functions
• Automation
• Security (Macros)