Data Manipulation with Advanced Excel

Data Analysis and Manipulation with Advanced Excel

OBJECTIVES

  • Learn the functionality and functions available in Excel to conduct data analysis, as well as to clean and manipulate data sets in preparation for such analysis.
  • Develop and reinforce knowledge of a wide range of specialised Excel functions.
  • Develop some knowledge of the limits of native Excel, and where automation with macros, or other tools may be needed.

DESCRIPTION

  • Overview. This course covers aspects of analysing and manipulating data using advanced Excel and VBA. The first part concerns methods, functions and tools to analyse datasets and databases (that are assumed to be clean and in the correct format). The second part concerns methods to clean and manipulate raw data sets into the required form. .
  • Practical Work and Exercises. Readers are expected to build simple examples for themselves as they follow the text. The course also contains downloadable data sets or simple models that allow a reader to do this practical work without having to enter large sets of data or repeat previous steps.

KEY TOPICS

  • Data Cleaning and Manipulation with Excel. Cleaning Data · Filters, searching and sorting · Removing duplicates and errors · Splitting and combining text fields · Creating unique keys · Creating flat tables · Multiple data sets .Advanced filters and data extraction.
  • Advanced Excel Functions and Their Applications. Using Lookup, Text, Data, Information and Logical Functions · Dynamic sorting · Filters and data extraction.