Microsoft Excel Intermediate

Microsoft Excel Intermediate

Duration:
1 Day
Aims:

This course is aimed at regular users of Excel who are familiar with the basics and who need to broaden their knowledge of Excel.

Objectives:

On completion of this course, attendees will be able to: create and apply formulas that include statistical, logical, lookup and date functions; apply formatting based on conditions; work across multiple sheets; write formulas to link sheets and workbooks; use a variety of tools to manage and extract data from lists; create a basic PivotTable to summarise data; create and modify charts.

Pre-Requistites:

Attendees should have attended our Excel Foundation course or have equivalent knowledge. Before attending this course, attendees must be confident with creating basic formulas (+-/*).

Versions

Courses are delivered in 2007, 2010, 2013, 2016.

What will I learn?
Introduction to Named Ranges
  • - Recap on absolute cell references (the use of the $ sign in formulas)
  • - Define Named Cell Ranges
  • - Edit and Delete Named Ranges
  • - Use Named Ranges in Formulae
Further Formulae and Functions
  • - Introduction to the IF function
  • - SUMIF, COUNTIF, COUNTBLANK & AVERAGEIF functions
  • - Introduction to the VLOOKUP function
  • - Calculate dates with NOW and TODAY functions
Work with Multiple Sheets and Workbooks
  • - Group and ungroup worksheets
  • - Organise windows
  • - Link formulas across worksheets and workbooks
  • - Edit links
Work with Data Lists
  • - Multi Criteria Sort and filter
  • - Use Find and Replace
  • - Work with the "Format as a table" feature
  • - Create automatic Subtotals
Introduction to Conditional Formatting
  • - Cell Rules
  • - Apply Top-Bottom Rules
  • - Apply Data bars, Gradient Fills, Icon Sets
Introduction to PivotTables
  • - Create a PivotTable from an Excel data list
  • - Add/remove/reorganise data fields
  • - View data in more detail
  • - Change how values are summarised
  • - Refresh data/change data source
  • - Filter PivotTable with Slicers
  • - Create and modify a Pivot Chart
Charts
  • - Create and modify charts such as Pie, Column & Line
  • - Format charts to enhance their presentation
  • - Print charts
Working with Sparklines
  • - Create, format and delete Sparklines
Related courses
Run this
course at
your offices
Request a quote
which course
Which level do you need?
Take our online skills assessment
Find out more
Speak to a friendly advisor

call

0333 344 6630

or visit our

Online Chat
Trustpilot