Microsoft Excel Intermediate

Designed for Excel 365, 2019, 2016, 2013, 2010

Who is this course for?

These intermediate Microsoft Excel courses are designed for delegates who have used Excel before and want to expand their knowledge. Improving formatting, organising data, highlighting key information and creating formulas/links between sheets.

You may also wish to know we also deliver advanced Excel training courses.

Prerequisites

A working knowledge of creating/formatting simple spreadsheets, basic formulas and functions e.g. AutoSum. Or our Excel Introduction course.

Benefits

Quickly summarise multiple sheets of data into one
Turn long lists and reports into easy to read tables
Formulas to check whether cells pass or fail your rules
Create links between cells so that they all update automatically
Highlight targets, trends, duplicates and errors with Conditional Formatting
Formatting and layout consistency by saving as a Template

• Course content

  Working with Large Worksheets

  Viewing options
  – Adjusting zoom
  – Using Freeze Panes
  – Using Split Window
  Hiding and displaying data
  – Hiding rows and columns
  – Creating an outline
  Printing large worksheets
  – Page Break preview
  – Working with page breaks
  – Printing multiple sheets

  Working with Multiple Worksheets and Workbooks

  Using multiple worksheets
  – Navigating between sheets
  – Moving and copying sheets
  Linking worksheets
  – Creating 3-D formulas
  – Consolidating data
  Linking workbooks
  – Creating a formula to link workbooks
  – Using the Paste Link option
  – Maintaining links

  Working with dates

  Entering date functions
  – TODAY function
  – NOW function
  Date and time formats
  Using dates in formulas

  Conditional formulas and formatting

  The IF function
  Conditional formatting
  – Based on a cell value
  – Based on a formula
  – Identifying cells with conditional formatting
  – Copying conditional formats

  List Management

  Working with lists
  – Structure of a list
  Sorting and filtering lists
  – Simple sorting
  – Sorting by multiple columns
  – Using AutoFilter
  Advanced filtering
  – Using custom filter
  – Using Advanced Filter
  Adding subtotals to a list

  Documenting and Auditing

  Comments
  – Viewing, adding, editing and deleting comments
  Auditing features
  – Tracing formula errors
  Protection
  – Protecting a worksheet
  – Protecting cell ranges

  Using Templates

  Built-in templates
  – Accessing and using Excel’s templates
  Creating and managing templates
  – Creating, using and modifying a custom template file