Excel - PivotTables, Macros & Introduction To VBA

Excel - PivotTables, Macros & Introduction To VBA

Overview

PivotTables offer the user the ability to number-crunch large amounts of data in order to produce concise reports and charts which can be automatically updated at the touch of a button. Learn the basics of Macros and how they are recorded through Excel to help perform the same task over and over in a few seconds. Macros are ideal to repetitive work which take a long time to perform each time. Gain an introductory look at Visual Basic for Applications.

Participants who attend this course should be very efficient in the use of Excel and should have attended or be proficient in all items covered in our Advanced level course. The course is aimed at anyone who is required to create complex reports from Excel data or who finds that they spend too much time trying to manipulate data in order to get the results needed for reporting. The course will give a brief introduction to Visual Basic for Applications (VBA) – the programming code behind Microsoft Office products. While VBA is a specialised topic outside the remit of this course, we will explore some of its features. This course is for non-programmers.

Syllabus
  • Import data from other sources such as txt or CSV files.
  • Work with text features of Excel to clean imported data. Or Summary Reports.
  • Understand how to construct an effective database within Excel.
  • Create Tables to future-proof data entry.
  • Creating Formulas in Pivot Tables.
  • Rearranging/filtering Pivot Tables.
  • Grouping data in Pivot Tables.
  • Slicers and timelines.
  • Summarising data and presenting numbers effectively.
  • Updating and Refreshing options.
  • Working with totals and subtotals.
  • Control Pivot Table options and report layout.
  • Drill down to find the data behind the results.
  • Create Pivot Charts and use them in the design of mini dashboards.
  • Record and store macro files.
  • Understand the security implications of Macros.
  • Learn the difference between Relative and Absolute Macros.
  • Assign a macro to an onscreen button or a new menu.
  • Take a first look at VBA and learn about IF and Loop Statements.