I like to say that good data, much like money, doesn’t grow on trees. It takes work to obtain. Often, this work involves retrieving and combining data from multiple sources using techniques like lookup functions, Power Query joins, and so forth.
Copilot is emerging as an incredible tool for helping Excel users avoid getting stuck on the technical side of Excel, allowing them to focus on understanding and acting on their data. However, as a tool based on a large language model, it has been somewhat slow to incorporate certain features. Initially, it was more or less limited to working with a single table at a time, leaving those seeking help with, for example, writing an XLOOKUP() function in the dark.
I’m excited to show you that this is no longer the case and that Copilot can now work with multiple tables at a time, with still varying success.
To follow along with this demo, download the exercise file below:
In this case, we have the population by borough of New York city for each decade categorized by borough_id instead of the borough’s name. While utilizing unique IDs like this is often beneficial from a computational perspective, it can become confusing when working practically with the data. The focus is more on understanding the underlying category rather than the arbitrary primary ID.
This is where lookup tables and similar techniques come into play. For new analysts or even seasoned ones feeling fatigued, setting up these structures can be a hassle. Sometimes, all you need is a quick solution that utilizes multiple tables without the need to construct an elaborate lookup system.
Copilot can assist us in such situations in the short term, and over time, it can serve as a guide to help us navigate through working with multiple tables effectively. Let’s give it a try.
Ensure that you have followed all the steps to load the data into Copilot. I would also suggest following this exercise with Excel Online as I have found Copilot more reliable on the online version.
Next, select the population table located to the left of the worksheet and start Copilot.
To begin, I’ll ask Copilot plainly for the population of Brooklyn in 1990. Without any further steps required from us, Copilot promptly provides the answer:
Imagine the immense utility of being able to swiftly retrieve data during a meeting or just before signing off for the day in this manner.
Next, let’s explore a more comprehensive approach beyond retrieving a single datapoint. Specifically, I’ll ask for Copilot’s help in writing an XLOOKUP() function to extract information from the boroughs table.
My prompt will be highly specific, outlining the tables to utilize and the formula to employ. Keep in mind, this assumes a foundational understanding of XLOOKUP() and its functionality. This scenario underscores the significance of possessing prior Excel proficiency before delving into Copilot’s capabilities.
Once Copilot derives a function, you have the option to copy the formula and insert it directly into the table, among other actions. However, I would advise that you thoroughly inspect your data afterward to ensure accuracy.
For instance, consider the scenario where an unexpected borough ID of 6 appears in the data. This could result in an #N/A error, which may be undesirable for presentation or calculation purposes.
To address this, I propose modifying the Copilot prompt to instruct it to fill in nonmatching values with Other. Again, to achieve this successfully, precision in language and a clear understanding of the desired outcome in your workbook are crucial.
Last but not least, what if you wished to utilize relational joins in Power Query instead of using XLOOKUP() to consolidate these data sources? While Copilot currently cannot automate the steps required to do this, it can provide you with fairly accurate guidance to accomplish it yourself.
The crucial aspect here is once again to be very precise about your desired outcome, including naming the tables, specifying the fields for merging, and so forth. From there, Copilot will provide you with the instructions necessary to complete the merge.
What questions do you have about working with multiple tables in Copilot for Excel, specifically, or Copilot in Excel more generally? Alternatively, what other use cases for working with multiple tables in Copilot would you like to explore or see me cover? Let me know in the comments.
If you’d like to delve even deeper into Copilot in Excel, be sure to explore my comprehensive course on LinkedIn Learning covering this topic.
Leave a Reply