III.1. Data Manipulation Advanced Excel VBA

Data Analysis and Manipulation with Advanced Excel and VBA

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.
  • Gain an understanding of the possibilities to use VBA macros and user-defined functions in data manipulation and analysis.
  • Develop advanced competence in VBA and macros.

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 · Using Lookup, Text, Data, Information and Logical Functions · Dynamic sorting · Advanced filters and data extraction 
  • Using VBA to Automate Data Manipulation and Analysis. · Adding or deleting worksheet categories. Determining the position and size of a data set. Reversing data sets by rows or columns · Consolidation of multiple worksheets · Reversing and splitting text items. User-defined functions for bespoke analysis.