Microsoft Excel Basic

Course overview

Delegates will learn the Excel basics: It will allow you to be able to perform basic calculations with confidence using formulas, be able to navigate around Excel much quicker and learn the shortcuts. It will show you how to create and format professional spreadsheets, build simple formulas, calculate totals and lots more.

Topics covered

Fundamentals

  • Starting Excel
  • What’s new in Excel
  • Understanding the Excel screen
  • Giving commands and using command shortcuts
  • Creating workbooks
  • Opening a workbook
  • Previewing and printing a worksheet
  • Saving workbooks
  • Closing a workbook
  • Using help
  • Exiting a workbook

Editing a worksheet

  • Editing cell contents
  • Copying and moving cells
  • Controlling how cells are moved or copied
  • Collecting items to move or copy
  • Checking spelling
  • Inserting cells, rows, and columns
  • Deleting cells, rows, and columns
  • Using undo and redo
  • Finding and replacing content
  • Adding comments to cells

Getting started with worksheets

  • Navigating worksheets
  • Entering labels and values
  • Selecting a cell range and entering data in a cell range
  • Overview of Formulas and cell references
  • Entering formulas including Sum, Average & Count / Formula error messages
  • Entering content automatically
  • Freeze Panes
  • Absolute Cell References
  • Hide / unhide worksheets

Formatting a worksheet

  • Formatting Text
  • Formatting Values
  • Basic sorting / Basic filtering
  • Merge / unmerge cells
  • Adjusting Row Height and Column Width
  • Working with Cell Alignment
  • Adding Cell Borders and Background Colours
  • Copying Formatting
  • Applying and Removing Cell Styles
  • Using Document Themes
  • Applying Conditional Formatting

Delivery methodology

(Face-to-face or virtual delivery available)

Small groups

Ideally 6-8 delegates to maximise engagement and interaction levels.

One-to-one

We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).

Tailored sessions

Talk to us about designing bespoke workshops for your team

Microsoft Excel Intermediate

Course overview

Once you know the basics of Excel this course aims to give you the knowledge to be able to use more advanced logical formulas including lookups to find, extract and compare data. You will be able to organise your data in tables, before learning how to evaluate it with advanced filtering techniques. You will also learn how to get started with Pivot Tables and present your data as charts.

Topics covered

Recap exercise on Absolute Cell Referencing

Working with tables

  • Creating a table
  • Adding and removing data
  • Working with the total row
  • Sorting a table
  • Filtering a table
  • Removing duplicate rows of data
  • Formatting the table
  • Using data validation
  • Converting to a range

Managing workbooks

  • Using workbook views
  • Selecting and switching between worksheets
  • Inserting and deleting worksheets
  • Creating headers and footers
  • Hiding rows, columns, worksheets, and windows
  • Setting the print area
  • Printing a selection, multiple worksheets, and workbooks
  • Creating a template
  • Protecting a workbook
  • Working with data ranges

Working with pivot tables

  • Creating a pivot table
  • Specifying pivot table data
  • Changing a pivot table’s calculation
  • Working with pivot table layout
  • Grouping pivot table items
  • Updating a pivot table
  • Creating a pivot chart
  • Using slicers

Getting started with worksheets

  • Choosing and selecting the source data
  • Choosing the right chart
  • Inserting a chart
  • Editing, adding, and removing chart data
  • Changing chart data
  • Using sparklines

More functions & formulas

  • Formulas with multiple operators
  • Inserting and editing a function
  • AutoCalculate and manual calculation
  • Defining names
  • Using and managing defined names
  • Displaying and tracing formulas
  • Understanding formula errors
  • Using logical and lookup functions such as IF and VLOOKUP

Delivery methodology

(Face-to-face or virtual delivery available)

Small groups

Ideally 6-8 delegates to maximise engagement and interaction levels.

One-to-one

We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).

Tailored sessions

Talk to us about designing bespoke workshops for your team

Microsoft Excel Advanced

Course overview

Our experts will show you how to transform disorganised data into useful information along with the skills to quickly make professional spreadsheets. You will learn how to automate tasks with macros, bring together multiple data sources, troubleshoot complex formulas and lots more.

Topics covered

Refresher on VLOOKUP’s and Tables

Text manipulation

  • Understanding text formulas
  • Format data using Text to Columns
  • Annotate Text from lists
  • Remove formatting within cells

Consolidation

  • Bringing multiple data sources together Connecting data to external sources including PowerBI

Working with the web and external data

  • Inserting a hyperlink
  • Importing data from an access database or text file
  • Importing data from the web and other sources
  • Working with existing data connections

Customising Excel

  • Customising the ribbon
  • Customising the quick access toolbar
  • Using and customising autocorrect
  • Changing Excel’s default options
  • Creating a custom autofill list
  • Creating a custom number format

Analysing data

  • Creating scenarios
  • Creating a scenario report
  • Working with data tables
  • Using goal seek
  • Using solver
  • Using text to columns
  • Grouping and outlining data
  • Using subtotals
  • Consolidating data by position or category
  • Consolidating data using formulas

Working with macros

  • Recording a macro
  • Playing and deleting a macro
  • Adding a macro to the quick access toolbar
  • Editing a macro’s visual basic code
  • Inserting copied code in a macro
  • Declaring variables and adding remarks to vba code
  • Prompting for user input
  • Using the if…then…else statement

Working with data ranges

  • Sorting by one column
  • Sorting by colours or icons
  • Sorting by multiple columns
  • Sorting by a custom list
  • Filtering data
  • Creating a custom autofilter
  • Using an advanced filter

Delivery methodology

(Face-to-face or virtual delivery available)

Small groups

Ideally 6-8 delegates to maximise engagement and interaction levels.

One-to-one

We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).

Tailored sessions

Talk to us about designing bespoke workshops for your team