Copilot in Excel offers a wealth of opportunities, particularly for new users, to streamline and automate their work. However, there’s a somewhat paradoxical requirement that this work must be conducted using Excel tables and structured references. Based on my experience as an Excel trainer, many people interested in using Copilot may not have even worked with tables before! Let’s address these concepts one at a time. In this post, I’ll guide you through the essentials of working with tables, ensuring you’re well-prepared to interpret and modify the output that Copilot provides in table format.
You can follow along with the exercise file provided below:
Here we have a small dataset on book sales. The first step is to convert this range of data into a table. To do this, position your mouse cursor in any cell within the dataset, and then you have several options. You can navigate to Insert > Table or use the keyboard shortcuts Ctrl + T or Ctrl + L.
You will then see the Create Table menu, which asks you to confirm where the source data is located and whether it includes headers. Once you have verified these details, click OK to proceed:
Assuming this workbook is saved on OneDrive or SharePoint, and that AutoSave is enabled (along with a valid license, of course!), this should be all you need to get started with Copilot. However, you’ll likely find it beneficial to understand some of the underlying behaviors of Excel tables before diving too deeply into Copilot.
Giving the table a name
Because this data is presented in a table, Copilot formulas won’t use the standard alphanumeric format like cells A1:B22 or column D. Instead, they will reference the table by name and specify particular rows or columns.
To make these references easier to read and understand, it’s a good idea to give our table a custom name. To do this, click inside any cell in the table and navigate to the Table Design tab that appears on the ribbon. Toward the left, you’ll find a Table Name box. By default, if not named, the first table created is simply called Table1. This isn’t very descriptive, so let’s rename it to something like book_sales (The table name needs to be one word, without dashes.).
Giving the table a descriptive name will enhance readability and clarity for any subsequent references. Let’s now proceed with creating formulas based on this table. As you will see, they function and appear somewhat differently from what many Excel users might be accustomed to.
Creating a calculated column in an Excel table
Let’s begin by creating a calculated column. For instance, we might want to multiply Quantity Sold by Price per Unit to calculate Sales in Column F. First, label cell F1 as Sales. You will notice that this column is automatically added to the table and formatted accordingly.
Next, we’ll create the calculated column. This step can be a bit challenging for those who are new to it. While it’s possible to use alphanumeric references such as D1 * E1 in this column, you will achieve better performance by using structured table references. This involves using the left arrow key on your keyboard to navigate to the relevant column in the same row.
You’ll see that the column is referred to by its name, not by its location, and the @ symbol is used to indicate that you are referring to the corresponding column in the same row.
Your Sales column should include a formula similar to the one below, and it should have been constructed as shown in the following animation:
=[@[Quantity Sold]] * [@[Price per Unit]]
It’s important to be comfortable working with these notations, as many of your results in Copilot will utilize them.
Creating a table aggregation
How about if, instead of creating a calculated column, we want to aggregate the entire table? This approach will involve a different notation than alphanumeric references. Specifically, I am going to calculate total sales. If you begin to total up that column using your mouse or keyboard, you’ll notice it starts with basic alphanumeric references. However, once completed, you should see the name of the table and the column being used, like so:
=SUM(book_sales[Sales])
If you experiment with adding or removing rows from the table or rearranging column positions, you’ll notice that using this structured reference is much more robust than the typical alphanumeric reference. Although you might not encounter this format frequently in Copilot, as it often suggests aggregation results in PivotTables, understanding this fundamental Excel table reference type is crucial.
Getting table help in Copilot
Now that you’re familiar with using structured references, let’s explore why it’s important to understand them. For instance, when we ask Copilot to help create a sales tax column, you’ll notice that it uses the @ notation to construct it:
On the other hand, requesting Copilot to create a summary or aggregation formula can be more challenging. Instead, you might consider simply asking it to calculate the sum directly, without specifying that it’s a formula. As mentioned earlier, this often results in the generation of a PivotTable.
Currently, Copilot appears to have difficulties constructing formulas or other tools designed to function outside of the table itself, such as aggregations. However, keep an eye out for improvements—I anticipate changes in this area soon.
Learning more about tables
There are many more things to explore regarding Excel tables, structured references, and related topics. For a comprehensive and in-depth resource, I highly recommend my book, Modern Data Analytics in Excel. However, for a quicker option to get started, why not ask Copilot directly about what we should know when working with tables? Indeed, we receive insightful and practical responses in return.
What questions do you have about working with tables, or are there other topics you need help with to get started with Copilot? Please share them in the comments below.
Leave a Reply