Jayne Wilkins & Associates Training Courses In Kent In London

01303 268357

jayne@jaynewilkins.co.uk

Microsoft Excel Courses - Intermediate

Microsoft Excel Courses

Microsoft Excel Intermediate

This Microsoft Excel Intermediate course is designed for students who have already attended the introductory course or who already have a good working knowledge of Excels basic features.

To attend this course you should be familiar with building basic formulae and confident in using AutoSum and adding, subtracting, multiplying and dividing. If you are not familiar with these please attend the Introductory course before coming on this course.

From: £325
Level: Intermediate
Duration: 1 Day

The Vital Bits (quick recap)

  • Excel's 3 key mouse shapes
  • Looking at the quickest way to add up large areas
  • The most efficient way to build & copy formulae

Formulae and Functions

  • Quickly adding up multiple areas in a sheet
  • Creating a grand total in a sheet
  • Carrying a number forward to another cell, sheet & book (linking)
  • Using brackets in formulae (order of calculation)
  • Using the $ in formulae (absolute & relative references)
  • Benefits of naming cells and ranges of cells
  • Using Insert Function to view and insert functions
  • Working with Functions to include:
    • Average, Min, Max, Count and CountA
    • CountIf, SumIF and IF
    • CountIFs, SumIFs and AverageIFs (from Excel 2007)
    • Nesting IF
    • Date functions
    • Text functions
    • Match and Vlookup functions
    • Nesting Functions

Multiple Worksheets

  • Inserting/Deleting/Moving/Copying Worksheets
  • Naming Worksheets
  • Working on multiple sheets at the same time
  • (Grouping and Ungrouping Sheets)
  • Summing across a group of worksheets (3 D Range)
  • Printing Multiple Worksheets and inserting Page Breaks

File Linking & Data Consolidation

  • Linking using Point and Click and Paste Special
  • Opening & Updating Linked Workbooks
  • Editing a Link
  • If time permits: Using Data Consolidation and 
  • Consolidating Data with Links

Working with Large Worksheets

  • Freezing Titles on the screen
  • Printing Titles on each page
  • Hiding and Showing columns, rows and worksheets
  • Freezing & Splitting Window Panes
  • Using Outline & Custom Views

Using Outline to hide and show columns

  • Using Outline to make different views of a worksheet
  • Adding and removing an Outline
  • Automatic Outline
  • Using Custom Views with Outline
  • Adding Custom Views to the Ribbon/Toolbar

Excel List or Database

  • List Rules
  • Sorting and Filtering a List
  • Finding and removing duplicate records
  • Using the Subtotal function with a filtered list
  • Using Custom Views with a filtered list

Using the Table feature

  • Formatting a range as a Table
  • Working with Dynamic Tables
  • Building formulae in Tables

PivotTables

  • A brief introduction to Data Analysis with Pivot Tables
  • What's a Pivot Table?
  • How to create a basic Pivot Table

Creating Graphs (if required)

  • Building a Graph
  • Modifying the Graph
  • Adding & Deleting Data Series to a Graph
  • Chart Types
  • Default Chart Format
  • Changing the Default Chart
  • Embedded Charts and Chart Sheets

Conditional Formatting

  • Applying in-built conditional formats
  • Creating conditional formats
  • Finding duplicates with conditional formats