Course Content 

An Introduction to Excel

This course is an introduction to the Microsoft Excel application.

Course outline:

  • Entering data
  • Worksheets
  • Adding/removing
  • Moving
  • Navigating
  • Rows and columns
  • Simple cell Formatting
What's New in Excel

Course outline:

  • What’s new at first glance
  • Ideas
  • Charts
  • Great new functions
  • Filtering tables
Cell Formatting

This course will introduce you to the features of Cell Formatting within Microsoft Excel.

Course outline:

  • Basic cell formatting
  • Bold/Italic/Underline
  • Cell background colour
  • Alignment
  • Horizontal and Vertical
  • Text vs Numeric alignment
  • The Number format dropdown
  • Custom number formatting
  • Borders and shading
  • Text alignment
  • Percentages
Introduction to Formulae and Functions

This course is for those requiring an understanding of formulae construction.  The course also demonstrates the usage of the functions: SUM, AVERAGE, MAX, MIN, COUNT and TODAY.

Course outline:

  • How to construct a basic formula
  • Using Autofill
  • Understanding BODMAS
  • Absolute cell references
  • Creating a formula with a function
  • Mathematics with dates

A recording of the full course can be viewed by clicking this link:
Introduction to Formulae & Functions (length: 12 mins 32 secs)

Introduction to Logical Functions

This course is for those requiring an understanding of commonly used logical functions.

Course outline:

  • How to construct a basic IF function
  • Understanding relational operators
  • Using SUMIF and COUNTIF functions
  • Boolean logic with AND and OR functions
  • Using IFERROR to eliminate #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

A recording of the full course can be viewed by clicking this link:
Introduction to Logical Functions (length: 15 mins 53 secs)

A brief guide on how to use the IF function can be viewed by clicking this link:
Using the IF function (length: 6 mins 40 secs)

Advanced Formulae and Functions

This course will show you how to create advanced formulae and functions.

Course outline:

  • Calculations:
    • SUMIF
    • SUMIFS
    • COUNTIF
    • COUNTIFS
  • Dates
    • NETWORKDAYS
    • DATEDIF
    • EDATE
  • Logical
    • CHOOSE
    • IF (including Boolean logic)
      – AND
      – OR
    • IFS

A brief guide on how to use the IF function can be viewed by clicking this link:
Using the IF function (length: 6 mins 40 secs)

Introduction to Creating Charts

This course is for those requiring an understanding of the process behind creating charts to enhance their data.

Course outline:

  • Using a recommended chart
  • Modifying your chart design
  • Formatting chart elements
  • Selecting a specific chart type
  • Using keyboard shortcuts to create a quick chart
  • Awareness of pitfalls
  • Selecting the appropriate chart data
  • Working with pie charts

A recording of the full course can be viewed by clicking this link:
Introduction to Creating Charts (length: 15 mins 42 secs)

Introduction to Data Validation techniques

Data Validation is a feature in Excel used to control what a user can enter into a cell.  This course is for those requiring an understanding of how Data Validation can be applied.

Course outline:

  • Demonstrating Data Validation controls
  • Understanding the Data Validation dialog box
  • Modifying existing Data Validation controls
  • Creating Data Validation from scratch
  • Working with lists

A recording of the full course can be viewed by clicking this link:
Introduction to Data Validation techniques (length: 15 mins 27 secs)

Advanced Dropdowns

This course is to look at Advanced and dynamic dropdown lists using Data Validation techniques.
Please note, it is recommended that you have good knowledge of Data Validation and the use of Excel to attend this session.

Course outline:

  • Creating & formatting the data for your dynamic dropdown(s)
  • Creating a dynamic dropdown list
  • To allow users to add values to a lookup list and have this reflected in the data validation
  • Creating conditional dropdown lists
  • To allow users to select a value (e.g. a CCG) and have a second Dropdown linked only to that CCG (e.g. GP Practices etc.)
Conditional Formatting

Conditional Formatting is a great way to quickly visualise data in a spreadsheet.  This course is for those requiring an understanding of how Conditional Formatting can be applied.

Course outline:

  • What is Conditional Formatting?
  • Demonstrating preset highlighting rules (Highlight Cells Rules, Top/Bottom Rules, Data Bars, Colour Scales, Icon Sets)
  • Creating a Conditional Formatting rule using a formula
  • Clearing existing rules
  • Highlighting an entire record
Introduction to XLOOKUP

The XLOOKUP function (introduced to Excel in 2020) can be used in place of the functions VLOOKUP and HLOOKUP, both of which have their limitations.  This course is suited to those who have used the functions VLOOKUP and HLOOKUP previously.

Course outline:

  • Using XLOOKUP instead of VLOOKUP/HLOOKUP with IFERROR
  • Why using a Nested XLOOKUP could be useful
  • Limitations of VLOOKUP and HLOOKUP
  • Using XLOOKUP instead of INDEX and MATCH
  • Finding the nearest match using XLOOKUP
How to Cleanse Raw Data

Raw data often requires cleansing to make it meaningful, useful and usable.  This course is for those requiring an understanding of the techniques available to cleanse data.  The course also demonstrates the usage of the functions: CLEAN, TRIM, SUBSTITUTE, SEARCH, REPLACE, LEFT and MID.

This course is ideally suited to those who already have a good working knowledge of Excel.

Course outline:

  • Data cleaning functions
  • Text substitution functions
  • Using Flash Fill
  • Finding the correct text
  • De-duplication techniques
  • Using Text to Columns to clean data
  • Working with American formatted dates
  • Reformatting offset headings

A brief guide on how to use Flash Fill can be viewed by clicking this link:
Flash Fill in Excel 365 (length: 3 mins 55 secs)

Introduction to Pivot Tables

This course will introduce you to Pivot Tables and cover some of their basic features.

Course outline:

  • Inserting a simple PivotTable
  • Single Column/Row heading
  • Values
  • Formatting value
  • Sum/Count/Average values
  • Formatting
  • More advanced PivotTable
  • Multiple row/column headings
  • Filtering
  • Pivot Table Filters
  • Slicers
  • Refreshing Pivot Table data
  • Basic PivotTable options
Pivot Tables (Extended)

This course is an to show some advanced functionality when using Pivot Tables.
Please note, it is expected that you have prior knowledge of the use of Pivot Tables in Microsoft Excel before attending this session.

Course outline:

  • Calculated fields
  • Grouping
  • Included
  • Manual
  • Options
  • Sorting
  • Column widths
  • Linking pivot tables
  • Using Timelines
  • Slicers
Pivot Charts

Course outline:

  • Getting your raw data right
  • Adding a Pivot Chart
    – From raw data
    – From existing pivot table
  • PivotChart vs PivotTable fields
  • Filtering your PivotChart
  • Formatting your chart
Building Dashboards

This is an advanced Excel course aimed at showing you how you can build a dashboard using Pivot Tables and Charts.

Course outline:

  • What is a dashboard?
  • Planning for your dashboard
    – Formatting raw data
    – What do you want from the raw data?
  • Adding columns and rows to raw data
  • Adding pivot tables & charts from raw data
  • Formatting the pivots to show what you need
  • Creating the dashboard
  • Filtering using slicers
    – Linking slicers top multiple datasets
  • Tidying up the screen for final dashboard