Microsoft Excel: Charts, Pivot Tables & Macros (1 day)

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.

Microsoft is an increasing popular career choice. This Microsoft training course will help accelerate your job prospects.

Related Courses

• Microsoft Excel: Introduction for Beginners (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (1 day)
• Microsoft Excel: Visual Basic For Applications (VBA) Programming (2 day)
• Microsoft Excel: Financial Modelling using Excel
• Microsoft Excel: Formulae & Functions (1 day)
• Microsoft Office: Word, Excel & PowerPoint: Introduction for Beginners (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (2 day)
• Microsoft Excel: Visual Basic for Applications (VBA) Programming (4 day)
• Microsoft Excel: Financial Modelling (2 day)
• Microsoft Excel: Business Modelling for Telecoms (2 day)

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 and Removing items from the Row and Column areas
• Adding Filters with Pages
• Summarising numbers in the Data Area
• Pivot the Row and Column Headings
• Refreshing the Pivot Table
• Reducing disk space
• Selecting different functions
• Format the data and Autoformat
• Show data options
• Display Grand Totals
• Change the sort order of the 'Rows', 'Columns' or data
• Grouping
• Multiple Row and Column Items
• Drill down in to the Data behind a number
• Use Hide and 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 - and )
• HLookup and VLookup