Excel Intermediate

Category:

Introduction

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 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.

Product Documents

Or call us now on 01284 763040

Description

Who Should Attend?

  • 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.

Course Content

List Management

  • Excel Data List Rules
  • Use Data Forms
  • Custom Lists
  • Print Options
  • Set print titles and options
  • Headers & Footers
  • Page Breaks
  • Freeze & Split
  • Freeze and unfreeze rows and columns
  • Arrange windows of one or more workbooks
  • Worksheet management
  • Inserting, moving, naming, hiding, deleting worksheets
  • Copying Sheets to Other Workbooks
  • Working with Named Ranges
  • What are named ranges
  • Create, Define & Delete Named Ranges
  • Sorting & Filtering
  • Perform single and multilevel sorts
  • AutoFilter & Custom Filters
  • Outlining
  • Use grouping and outlines
  • Subtotalling
  • Use subtotalling
  • Data Subtotals versus =subtotal function
  • Data Validation
  • Applying Data Validation Criteria
  • Conditional Formatting
  • Apply Conditional Formatting
  • Cell Value
  • Formula Is
  • 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 with Autoformat
  • Display Grand Totals
  • Drill down in to the Data behind a number
  • Beyond Basic Functions – Optional if time
  • Standard Date Functions
  • If Functions
  • Example Text Function (Left, Right, Mid and Concatenate – &)
  • VLookup and HLookup
  • Course Duration: One Day