Data management in Excel - Workshop 2017

The management and reporting of data is an important feature of the modern business organisation. Business data is often available in the form of tables or data lists, a convenient format for Excel's very considerable collection of very powerful analytical and organisational tools.

The use of drop-down lists and combo boxes, when combined with database and lookup functions, extends the range of features available to extract various types of data from data bases or data lists. These features provide a convenient and flexible method of displaying key information using a simple mouse-click.

Key topics:

  • Auto and advanced filters to display relevant records in a data set
  • Dynamic range names for use with lookup and reference functions
  • Importing and organising data including cleaning imported data
  • Using Excel Tables including their power in updating summary tables when new data becomes available
  • Building and using PivotTables supported by dynamic range names and Excel Tables
  • Using Lookup and Reference functions to obtain summary measures, including Form controls, such as spinners and combo boxes
  • Techniques for consolidating business data
  • Data validation and group mode

Learning manual provided.

Offered in Excel 2013 and compatible with Excel versions 2007, 2010

Learning objectives

  • Select a range of common statistical and database functions
  • Use Excel’s lookup and reference functions in a variety of ways
  • Extract summary information from databases using conditional functions
  • Create summary reports using array formulae
  • Employ dynamic range names effectively with conditional functions
  • Practice using the auto and advanced filters
  • Extract information from Excel Tables using structured referencing
  • Apply dynamic range names to various data sources
  • Practice using form tools, such as combo boxes, to selectively display information
  • Create summary reports to present the salient features of business data
  • Use data validation techniques
  • Apply various conditional formatting techniques
  • Consolidate data across many workbooks with the consolidation tool

Audience

This workshop is designed specifically for finance and accounting professionals who use Excel in their day to day roles.

Please note enrolments close 2 working days prior to the workshop date.

Looking for a customised learning solution for your organisation or team? Contact us today to discuss in-house learning options.

1 day

loading...