Excel tables

Previous attendees have said…

• 20 previous attendees have left feedback
• 100% would recommend this session to a colleague
• 85% said that this session was pitched correctly

Three random comments from previous attendees

• learned a new way to make tables
• Really useful, I have been using excel for many years and I am self taught so really good to get some nuggets that I may have missed along the way and I now understand how important tables are! Off to change all my excel sheets to tables :)
• Tables came in after I’d learnt Excel & I’d used them occasionally but not fully understood & utilised. Now feel confident to make tables at the start of any Excel work.

Session materials

• all materials
• slides html / pdf

Welcome

• this session is 🌶: for Excel beginners
• you can find session materials at tinyurl.com/kindtrp

Where does this fit in?

KIND Excel beginner skill tree

• for this session, you’ll need to be familiar with the Excel basics (getting around in Excel, opening/saving/closing files, A1 referencing, and values)
• we’re going to dodge formatting and formulas as much as possible today

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

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
        

Add a table

1. make sure you have that date selected
2. In the ribbon menu, go to 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
   

A first table

• congratulations, you should now have a 1-cell table on your worksheet
  
• 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
   
3. Select that Table Design section. The most useful parts are on the far left:
   
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

4. Now expand your table to include that new data. You can drag the blue handle
   or use the Resize Table tool in the ribbon menu
   .
5. 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
   
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 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
  

  • 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