Working with Excel tables

- [Instructor] Copilot in Excel unlocks a world of opportunities, especially if you're new to Excel. Here's the catch though. To fully leverage Copilot, you need to use Excel tables and structured references, which use table names and column headers instead of traditional cell addresses. Many enthusiasts eager to dive into Copilot often haven't used Excel tables before, so this video will guide you through them ensuring you feel confident using tables alongside Copilot. We'll use an exercise file named working-with-excel-tables for this demo, which includes a neat data set of book sales. Although it looks organized, we can enhance it by converting it into a table. To do this, select any cell within the data set. Navigate to Insert table or use the keyboard shortcuts Ctrl+T or Ctrl+L. (mouse clicks) A create table dialogue will appear asking you to confirm the data range and whether your data has headers. Confirm these details and click okay. (mouse clicks) Immediately you'll notice aesthetic upgrades like banded rows, simple yet effective improvements. But here's the key, table data requires a different analysis approach than usual. In tables, Excel doesn't use the standard cell references like A1 through B22. Instead, it references table names and specific rows or columns. This makes it crucial to have clear, understandable names for your columns and table. Usually, your first table is automatically named Table1, which isn't very descriptive. So let's change it to something more meaningful like book_sales. The table name should be a single word without spaces or dashes. (mouse clicks) (keyboard clacking) With a descriptive name, referencing becomes clear and more intuitive. Next, let's create some formulas using this table. You'll notice that formulas and tables look and function differently than those in regular cells. Let's add a calculated column first. Suppose we want to calculate total sales by multiplying quantity sold by price per unit. First, name the new column as sales in cell F1. As you enter this, Excel automatically extends the table format to this new column. For the calculation, instead of using cell references like D1 times E1, use structured references for better performance. To do this, use the left arrow key to navigate to the appropriate columns in the same row. Columns are referred to by their names with the @ symbol indicating the current row. (keyboard clacking) Now, what if we want to aggregate the data for the entire table? Instead of single cell references, the notation will change. For example, if you total up the sales column using either your mouse or keyboard, you'll start with basic references, but then we'll see the table and column names appear once completed. (keyboard clacking) So familiarize yourself with these table notations independently before integrating Copilot. This foundational knowledge allows you to fine tune and audit Copilot's outputs more effectively. I encourage you to start organizing your data into tables now, not only for their aesthetic appeal, but also for their compatibility with advanced tools like Copilot.