Excel foundations 1 (intermediate Excel session 1)

excel
intermediate
Excel skill-builder
Published

November 4, 2025

This is a session in our intermediate-level Excel skill builder course. This consists of five practical training sessions, designed to be taken together, that are aimed at helping users with some prior Excel experience build and consolidate their skills. The sessions are:

Session materials

Previous attendees have said…

  • 132 previous attendees have left feedback
  • 97% would recommend this session to a colleague
  • 91% said that this session was pitched correctly

Three random comments from previous attendees
  • Good session with relevant key function points conveyed. Perhaps obligatory intro (session not recorded and reasons etc.) could be part of event description for new attendees and / or verbally shortened to leave more time to impart more knowledge.
  • I’m dyslexic and getting access to the step by step guide prior to the training was great. If I hadn’t went over the training first I think I would have been a bit confused. I’m used to corporate/very direct training, this is the task, this is how you do the task, you try the task and questions at the end. This training felt more casual and I was a bit distracted by emoji’s on the screen and the chat window (I know this was other participants way of joining in). I did find the trainer funny and engaging, which was nice.
  • I think it was an excellent session and I really appreciated the fact that was interactive and one could ask questions and learn from the other participants. Brendan and his helper were marvellous. Thank you.
Forthcoming session(s)
Booking link Date
Excel foundations 1 (intermediate Excel session 1) 10:00-12:00 Wed 14th January 2026
Excel foundations 1 (intermediate Excel session 1) 14:00-16:00 Thu 30th April 2026

Session outline

  • most Excel questions can be broken down into these five areas
    • Cells and formatting
    • Ranges and tables
    • References
    • Formulas
    • Functions
  • we’ll look at cells, ranges, and references in this session

Tasks

  • there are tasks throughout the training session
  • they’re described in the green boxes
  • please follow along with the tasks
Task
  1. this is the first step of the task
  2. this is the next step

Getting started

  • this course is written for M365 Excel (desktop)
  • other versions of Excel are available, and will work in a similar way
  • files for today:
    • s01_exercises.xlsx is a starting-point for the exercises today
    • s01_exercises_final.xlsx is the end-point for the exercises today - it’s there to help if you get stuck or lost

A first task

Task
  1. open the sample spreadsheet s01_exercises.xlsx and have a look around
  2. find the service_use worksheet

Terminology

Excel interface
  • worksheet / workbook distinction
  • Ctrl + F1 to hide the ribbon

Keyboard shortcuts

  • Excel is rich in keyboard shortcuts
  • these shortcuts are a great way to build speed and fluency
Task
  1. Press Alt
  2. Try exploring the ribbon using the keyboard shortcuts

Cells and formatting

  1. Excel sheets are made up of cells
  2. each cell can contain either a value or a formula
  3. cells can be formatted
    1. sometimes that’s just text formatting, like size, colour, etc
    2. sometimes that’s number format, which changes the value in the cell
  4. formatting can be conditional, where formatting changes based on cell values. We talk about this in a later session

Rows and columns

  1. cells are arranged in rows and columns Rows and columns
  2. columns usually have letters, and rows have numbers

Date task

Task
  1. look at the values in the first 15 rows of the date column 1900 dates
  2. Excel generally stores dates using the 1900 date system, which is the number of days since Jan 1st 1900 - so 44570 stands for the 9th of January 2022
  3. we can format these into something human-readable. Please select all the cells in that date column and change the number format to Short date Change number format to Short Date 1. or use Alt, H, N, S 1. or Ctrl + 1 and select from the dropdown

Ranges and tables

  • any group of cells is called a range
  • we can convert ranges to tables
  • tables have lots of useful features
    • automatic formatting of headings
    • auto filters
    • flash fill
    • …

Table exercise

  • let’s turn a range into a table
Task
  1. the first 15 rows and 5 columns of our exercise worksheet contain a range
  2. please select all of those cells
  3. then select Format as Table from the ribbon (or Ctrl + T) Format as table
  • you can also convert a table back into a range using the Convert to Range tool Convert to range

Selecting tables

  • within a table press Ctrl + A to select all values
  • press Ctrl + A again to include the headers in the selection
  • and press Ctrl + A a third time to include the entire worksheet

References

  • references are the ‘address’ of a particular cell
  • references are usually made up of a letter and a number (like A2, B56, …)
    • the letter stands for the column a cell is in
    • the number stands for the row
  • we can use those references in formulas
    • =A1 will copy the values from A1 into the current cell

Reference task

Task
  1. using A1 reference, can you populate the first three rows of the summary table? Populate the summary table
    1. Start date
    2. End date
    3. First duty manager
  2. you might need to switch off Calculated Columns from the floatie Switch off calculated columns

Useful tools for references

  • select a range of cells using : - so =A1:C3 will select the nine cells at the top left-hand corner of the sheet
  • to select everything in a column(s), you can use a column letter and the range operator - so =SUM(D:D) would be a quick way of summing every value in the D column
  • you can also use . to trim blank values from this range. More info on the Microsoft learn pages.

Absolute vs relative references

  • by default, A1 references are relative
    • if the formula moves, the reference will change
  • as you’ve seen, relative references can cause problems
  • you can change relative references to absolute (=fixed) references using $
    • $A$1 refers to A1 no matter what
      • use this if you absolutely always want to refer to the same cell
Task
  1. update your relative references in the summary table to absolute references

Working with absolute references

  • there’s a bit more to say about absolute references
    • $A1 will be absolute in the column, but relative in the rows
    • A$1 will be relative in the row, but absolute in the column
Note
  • if you click in the formula bar, F4 toggles absolute/relative
    • relative > absolute > abs row > abs col
Task
  1. try changing your 3 absolute references to abs col references (like $A2)
  2. now try copying the manager’s name down the column
  3. now try copying the manager’s name along the row
  4. does this behave as you’d expect?
  5. (please remove any of this copied data now - we won’t use it again)

Working with names

  • we can name cells in Excel
  • this is done using the name box The name box
Task
  1. select B18
  2. in the name box, please type a useful short name and confirm with Ctrl + Enter
  3. please repeat for the other two value cells in the summary table

Why bother naming things?

  • we can use those names in formulas
Task
  1. in a random cell, use TEXT() to find the days of the week from each of your start and end dates
  2. for example, my start date is named start_date, so =TEXT(start_date, "dddd") gives me Saturday
  • they have semantic content: it’s easier to understand what start_date means compared to B17
  • naming important values helps lower the complexity of formulas by:
    • making it obvious what is being referred to
    • allowing you to change the location of a cell - as long as the name is retained, the formulas will continue to work
  • we can name lots of other parts of Excel sheets too

Naming

  • most objects in Excel can be named - including cells, tables, and ranges
    • special case - Table[Column] shorthand for columns. This is known as structured referencing
  • those names will work like variable names
  • you can see names in the name box to the left of the formula bar

Adding names

  • the easiest way to name a cell is to click in that cell, then edit using the name box (to the left of the formula bar)
  • there are a few name rules
    • don’t make names that look like references
    • don’t include spaces
    • don’t rely on case (Excel in general is case insensitive)
    • keep them shortish (less than 255 chars)
    • avoid numbers at the start of names

The name manager

  • you can review names using the name manager via Ctrl + F3
    • note reference, scope, and value
    • Ctrl + J for new lines in name manager
    • F2 switches between edit and enter mode

The Go To menu

  • you can use names to navigate using the Go To menu via F5
    • gives you an overview of the names in play
    • quick navigation shortcut

Thank yous

I’m grateful to Jennifer Watt, John Mackintosh, Duncan Sage, David Coigach, Michael Robb, Angela Godfrey, Spela Oberstar, Andrew Christopherson, and other members of the KIND network for their valuable suggestions and corrections to these training materials