Course Content 

An introduction to Formulae and Functions

Course outline:

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

To view a recording of the whole course, please use this link: https://learning.necsu.nhs.uk/2021/04/29/introduction-to-formulae-functions-full-course/

Introduction to 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!
Introduction to Data Validation techniques

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
Conditional Formatting

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
Introduction to XLOOKUP in Excel 365

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

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
What's new in Excel

Course outline:

  • What’s new at first glance
  • Ideas
  • Charts
  • Great new functions
  • Filtering tables
Introduction to Microsoft 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
Microsoft Excel - 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.)
Microsoft Excel - 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
Microsoft Excel - Cell Formatting

This course will introduce you the features of Cell Formatting in MS 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
Microsoft Excel - 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
Microsoft Excel - 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 prior to attending this session.

Course outline:

  • Calculated fields
  • Grouping
  • Included
  • Manual
  • Options
  • Sorting
  • Column widths
  • Linking pivot tables
  • Using Timelines
  • Slicers
Advance 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
      • NOT
    • IFS