Speed Challenge Day 2
Tables
Description
Tables are a way to store data. This is an alternative to storing data in ordinary cell ranges (like we've done for many years). Tables auto-expand, use structured references, fill formulas down automatically, and provide a total row.
Christian's Opportunities
Learning how to use Tables and Excel in general, helps you save time of course. But, it also helps you get better opportunities at work. Instead of grinding away at the repetitive tedious manual tasks, you are able to work on more interesting projects and are more sought after. This is exactly what happened to my student Christian. Check it out here.
How to Insert a Table
- Windows: Insert > Table (or Ctrl T)
- Mac: Insert > Table (or ⌘ T)
File
If you'd like to give it a try after watching the video, download the Excel file: ChallengeTables.xlsx.
Try It
Exercise 1
- Insert a Table.
- Select any cell in the Amount data range.
- Insert > Table.
- Use the Table Name in a formula.
- Select any cell in the Table.
- Click the Table tab and view the Table Name field. Use this Table Name in the following formula.
- In the Total cell, write a formula that sums the table. For example: =SUM(Table1)
- Note: you can change the table name using the Table name field by entering your new name and pressing Enter. Avoid spaces and funky characters.
- Auto-expand.
- Type a number into the cell immediately under the Table.
- The Table should automatically expand its dimensions to include the new value.
- The Total formula should update its result accordingly to include the new value.
- Note: The Table will only auto-expand if the new value is entered immediately under the table (no blank rows). Tables auto-expand down and right, but not left or up.
Exercise 2
- Convert the data range into a Table.
- Write a formula to compute the Total of the Extended column.
- For example, if your table was named Table2, the formula would be: =SUM(Table2[Extended]).
- The syntax for a structured table reference is generally: TableName[ColumnName].
Exercise 3
- Convert the data range into a Table.
- Write a formula in the first table row to compute the Extended amount (Amount * Quantity).
- When you press Enter, Excel should fill that formula down throughout the remainder of the table column.
- As you are writing the formula, Excel will automatically insert the structured references for the Amount and Qty values. For example: =[@Amount]*[@Qty].
- Note: depending on your version of Excel, it may or may not include the @ in the table references.
- Write a formula in the first table row to compute the Total amount (Extended + Shipping).
- For example: =[@Extended]+[@Shipping].
- Add a Total Row.
- Select any cell in the Table to make it active.
- Table > Total Row.
- Note: Excel will automatically add the total to the rightmost numeric column. If you want to add totals to other columns, simply select the Drop Down in the corresponding Total Row column to reveal numerous options.
- Auto-expand.
- Select any numeric cell in the Table.
- Note that when you press the Tab key on your keyboard, the active cell moves one cell to the right.
- Once you get to the last column of the Table, pressing Tab will loop back to the first column but on the next row.
- We can use this as an easy way to insert a new Table row. Select the cell in the rightmost column in the last data row of the Table and press Tab.
- Excel will insert a new data row and automatically fill table formulas down.
Exercise 4
- Convert the data range to a Table.
- Select any cell in the Table to make it active.
- To remove the Table formatting, click the Table tab and pick the None format from the Table Style gallery (upper left).
- To remove the Table: Table > Convert to Range.
Conclusion
Tables are a great way to store data in your workbooks because they auto-expand. They help to improve efficiency and minimize errors in formulas that use the corresponding structured references.
We'll be using Tables in the next couple of Challenge videos as well 🙂
See you in the video!