Course Code: msexcelil
Duration: 14 hours
Prerequisites:

Basic MS Excel.

Course Outline:

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