Excel Business Intelligence (BI)

MODULE 1: Importing and Linking Data
Learning outcomes: Import, sort and filter data from a number of different sources into an Excel spreadsheet
Topics covered:

• Importing Data from a text file
• Importing Data from an XML file
• Linking to an External Data Source
• Using Microsoft Query to Control Linked Data
• Modify and Update Connections

MODULE 2: Data Manipulation with Pivot Tables
Learning outcomes: Summarise information from large data sets. Group, filter and perform calculations on summarised data
Topics covered:

• Creating Basic Pivot Tables
• Grouping Pivot Data
• Calculated Fields in Pivot Tables
• Filtering Pivot Data
• Changing the Pivot Table Display Methods

MODULE 3: Advanced Reporting Functions

Learning outcomes: Use a variety of advanced functions to interrogate and manipulate spreadsheet data
Topics covered:

• Using the GetPivotData Function
• Using the Index Function
• Using the Match Function
• Using Index and Match Together
• Using the Choose Function
• Using the Frequency Function

MODULE 4: Automating Reports with Controls
Learning outcomes: Automate the appearance of charts and data using a variety of built in controls and macros
Topics covered:

• Using Form Controls
• Record and Run Simple Macros
• Assign Macros to Form Controls

MODULE 5: Advanced Charting Features
Learning outcomes: Explore advanced charting options and techniques to display complex data in a professional way
Topics covered:

• Creating Combination Charts
• Displaying a Secondary Axis
• Modifying Axis Options
• Display Data Trends
• Charting an Acceptable Range
• Charting Targets