Excel 2007 Level 3

Short course

In Washington

£ 165 + VAT

Description

  • Type

    Short course

  • Location

    Washington

  • Duration

    1 Day

If you're already up to speed with Microsoft Excel but you would now like to automate some common tasks, apply advanced analysis techniques to more complex data sets. This course is aimed at someone desiring to gain the skills necessary to create macros, audit and analyse worksheet data, create PivotTables and PivotCharts, use lookup tables and import and export data.

Facilities

Location

Start date

Washington (Tyne and Wear)
See map
Suite 13, Vermont, House Concorde, NE37 2SQ

Start date

On request

About this course

Before starting this course, we recommended that you have attended the Excel 2007 Level 1 and 2 courses or have equivalent knowledge.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Overview

If you're already up to speed with Microsoft Excel but you would now like to automate some common tasks, apply advanced analysis techniques to more complex data sets. This course is aimed at someone desiring to gain the skills necessary to create macros, audit and analyse worksheet data, create PivotTables and PivotCharts, use lookup tables and import and export data.
Prerequisites
  • Before starting this course, we recommended that you have attended the Excel 2007 Level 1 and 2 courses or have equivalent knowledge.


Objectives


    Delegates will be able to :
  • Change the view of a spreadsheet to show a different layout without using any formatting tools, then change the data on the sheet to display different data values.
  • Create a PivotTable to manipulate and extract data, then create a chart to display the results.
  • Create a spreadsheet that will display multiple groups of records that can be updated from a single source.
  • Use the Goal Seek and Solver facilities to enable results to be predicted in a spreadsheet where data may be missing or unavailable.
  • Create a formula that will only add up or count records that match specific criteria.
  • Record a Macro that will automate a repetitive task and then assign it to a new button on a new toolbar.
Follow on Courses
  • VBA using Excel
Training Options
  • Private course at your company office throughout the UK, at our training room or at an independent location, convenient to you
  • Scheduled public course at one of our training venues
  • Bespoke course (a course written to your specific needs) at your company office throughout the UK, at our training venue or at an independent location, convenient to you
Whats Included
  • 1 delegate per computer
  • 12 Months telephone and email support
  • Comprehensive colour course manual and exercises
  • Use of a Modern Training suite with 19 inch flat screen monitors
  • Lunch (When at our training venue)
  • Refreshments (When at our training venue)
  • Relaxed refreshment area at our training room
Course Content
Organising Data Using Tables
  • Formatting tables
  • Modifying tables
  • Creating tables
Outlining
  • The outline symbol
  • Removing an outline from a worksheet
  • Ungrouping an outline manually
  • Creating an outline manually
  • Creating an outline automatically
Custom views
  • Displaying a different view
  • Deleting a view of a worksheet
  • Creating a view of a worksheet
Scenario
  • Removing all tracer arrows
  • Tracing precedents and dependents
  • Description of tracer arrows
  • Displaying the auditing toolbar
  • Using tracers to help audit a worksheet
  • Deleting a Scenario
  • Editing a scenario
  • Viewing a scenario
  • Setting up a scenario Adding a Comment to a cell Read a comment attached to a cell Print the comments with a Worksheet Edit a Comment on a cell Viewing comments all the time
Calculating Data with Advanced Formulas
  • Using VLOOKUP
  • Entering a workbook name and path into a cell
  • Round: rounding a number to decimal places
  • Functions, including Nested IF, SUMIF, COUNTIF and COUNTBLANK function
  • Printing formulas
  • Performing logical analysis
  • Calculating data with lookup & reference functions
  • Performing numeric calculations
  • Formatting text using formulas Tips using VLOOKUP HLOOKUP
Getting Started with Pivot Tables
  • Moving a pivot table from one sheet to another or around the same sheet
  • Creating a second (or more) pivot table on the same data
  • Number formatting of the data area
  • Rearranging fields in a pivot table
  • Dynamically building a pivot table
  • Data in the summary area
  • Fields to create column headings
  • Fields to create row headings
  • Creating your first pivot table Deleting a Pivot Table
Formatting the Pivot Table
  • Grouping and hiding multiple row and column groupings
  • Removing AutoFormat
  • Autoformat
Pivot Table Options
  • Turning off the summary rows and columns
  • Activating automatic refresh options
  • Refreshing the pivot table
  • Dealing with empty cells
Goal seek
  • Setting values based on a target cell
  • Using goal seek to find a required result
Macros
  • Setting a security option for your macros
  • Creating your own toolbar
  • Viewing the code behind a macro stored in the Personal workbook
  • Viewing the code behind a macro stored in the current workbook
  • Editing a Button on a Worksheet
  • Assigning a macro to a button on a worksheet
  • Removing a button from a toolbar
  • Assigning a macro to a toolbar button
  • Deleting a macro
  • Running a macro
  • Using elative references when recording a macro
  • Recording a macro Viewing the code behind a macro stored in the Personal workbook Creating your own toolbar Setting a security option for your macros

Excel 2007 Level 3

£ 165 + VAT