Excel tables

excel
beginner
Excel for beginners
Published

February 17, 2025

This session is part of our Excel for beginners course. That’s a series of six linked sessions, delivered on Teams, that give an introduction to Excel for people working in health and social care. The sessions are:

Together, they aim to help you develop an appropriate set of Excel skills to help your work. This session covers tables in Excel:

KIND Excel beginner skill tree

Previous attendees have said…

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

Three random comments from previous attendees
  • Simple explanation of using Excel Tables
  • Just brilliant as ever. would recommend these to everyone!
  • good introduction
Session materials

Session outline

  • introduction
  • add and remove tables
  • grow and shrink tables
  • autofill and friends
  • sort and filter
  • names and reference
  • pass data to other tools

Introduction

  • this is an easy session to sum up: use tables in Excel
  • most people don’t use Excel tables
  • they’re probably the best Excel skill investment there is
  • this is a practical session, so you’ll need some kind of Excel to follow along

A way of thinking about tables

  • imagine you have several columns of data
  • you can think of a table as a container for that data:
    Table lunchbox
  • putting data in a table helps present your data neatly
  • tables also protect your data, and make it easier to move from place to place

Some data

  • tables are potentially useful throughout the life-cycle of an Excel file
  • let’s start at the beginning:
    1. open Excel
    2. in a new worksheet, please enter today’s date
      1. DD/MM/YYYY format is best, but we’ll park that for the formatting session
        Enter a date in a cell

Add a table

  1. make sure you have that date selected
  2. In the ribbon menu, go to Insert > Table
    Insert > Table
    1. or you can use Ctrl + t
  3. you’ll be asked to create a table - you should be able just to select OK
    Create a table

A first table

  • congratulations, you should now have a 1-cell table on your worksheet
    a 1-cell table
  • think about this as a container for your data
    • you could call it a data structure instead if you wanted to be more fancy

Removing your table

  1. Tables aren’t formatting - so you can’t clear them like formatting
  2. Click inside your table, and you should see a new section of the ribbon menu - Design or Table Design depending on your version
    Design or Table Design menu
  3. Select that Table Design section. The most useful parts are on the far left:
    Design or Table Design menu
  4. Select Convert to Range - your table should now return to being an ordinary cell
  5. Finally, please re-table your cell

Grow and shrink tables

Let’s add some new data:

  1. Please add three or four more dates, going back into last week, in the rows below your date
  2. Please add a word describing your main breakfast item in the next column
  3. Please add a third column, scoring that breakfast out of ten

Grow and shrink tables

  1. Now expand your table to include that new data. You can drag the blue handle
    Expand your table with the blue handle or use the Resize Table tool in the ribbon menu
    Resize Table tool in the ribbon menu.
  2. You can use the same methods to shrink a table

Autofill and friends

  1. drag down to add two more empty rows
  2. select your three dates, and drag the green fill handle down into the empty cells
    drag the green fill handle down into the empty cells
  3. this should populate the correct values - although you’ll need to check carefully that everything has worked

Filter and sort

Each of your column headers has a dropdown menu dropdown menu that will allow you to sort and filter your table

  • sort allows you to order your table by that column
  • filters shows and hides rows of the table
  • you’ll have different filtering options for the different types of columns in your table

Names and reference

  • you can name each column. These work best as single descriptive words - date rather than breakfast date

    • you might find it helpful to write longer names in snake case (breakfast_date) or camel case (breakfastDate)

    • please now name your columns

  • you can also name the table as a whole. Again, that’s done from the left-hand side of the Table menu
    name tables from the left-hand side of the Table menu

    • please give your table an appropriate name

Passing data demo

  • Your table can now be saved, developed further, or passed to other data tools
  • Tables have major advantages on this front:
    • they’re portable, so you can move a table around
    • you can use structured referencing - like =data[score] - to select an entire column by name, which helps make Excel more robust
      • see the intermediate Excel session on referencing
    • you can send the data easily and safely to PivotTables, PowerQuery, PowerPivot, and other fancier data tools

CONCLUSION: USE TABLES