Microsoft Excel: Charts, Pivot Tables & Macros

Course Overview

This course is aimed at delegates who are using Excel to maintain lists of data and carry out some form of analysis on them. It is designed for people who need to know how to fully understand and manipulate lists to maximum effect. This will be done by covering data manipulation and filters, the more advanced mathematical and statistical analysis of data and ultimately the use of Pivot Tables, Macros and Charts within Excel.

• For delegates who use Excel to store and analyse data instead of Microsoft Access.
• To create and maintain a list of data that makes lists work for you.
• Use Filters to quickly select sets of data within lists.
• Manipulate data into useful elements e.g. separate data from a single cell.
• See the magic of Pivot Tables and the speed of which they analyse and present statistical information.

Audience

• Users needing to manipulate and analyse data stored in the form of Excel lists.
• Users required to produce in-depth reports that analyse data through the use of complex formulae and/or pivot tables.
• Anyone who has to deal with financial (e.g. sales, budgets) or statistical data on a regular basis in their job role.

Skills Gained

By the end of the course the delegate will be able to:

• Be able to create lists that Excel recognises
• Print large lists of data with repeating titles
• Use Pivot Tables to analyse information within the list
• Create Basic Macros and edit macros using VBA
• Build a range of Charts and manipulate

Prerequisites

• Delegates must be familiar with working in a Microsoft Windows environment i.e. mouse skills, working with multiple windows and screen elements such as scroll bars, menu bars and toolbars
• Delegates are required to have a good working knowledge of Excel - the ability to easily create professional looking workbooks from scratch

Course Outline

List Management

• Data Forms
• AutoFilter
• Sorting Data

Creating Sub-Totals

• Creating and Removing

Pivot Tables

• Creating a Pivot Table with the Wizard
• Adding & Removing items from the Row & Column areas
• Adding Filters with Pages
• Summarising numbers in the Data Area
• Pivot the Row & Column Headings
• Refreshing the Pivot Table
• Reducing disk space
• Selecting different functions
• Format the data & Autoformat
• Show data options
• Display Grand Totals
• Change the sort order of the 'Rows', 'Columns' or data
• Grouping
• Multiple Row & Column Items
• Drill down in to the Data behind a number
• Use Hide & Show Detail

Macros (Recorded)

• Record macros
• Personal Macro Workbooks
• Run macros
• Deleting macros
• Assign a macro to a command button or a menu item
• View VBA Code
• Edit a recorded macro

Creating Charts

• Chart Wizard
• Chart Types
• Customising a Chart
• Formatting

Advanced Charts

• Error Bars and Trend lines
• Embedding
• Dual Y-axis

Beyond Basic Functions - Optional if time

• Basic If Functions
• Example Text Function (Left, Right, Mid and Concatenate - &)
• HLookup and VLookup

Follow On Courses

• Excel Advanced
• Excel Formulae and Functions

Microsoft Excel: Charts, Pivot Tables & Macros / MEX-CPT