Working with multiple tables in Copilot

- [Speaker] I often say that good data, much like money doesn't just appear out of nowhere. It takes real effort to gather, and a big part of this in data involves pulling together data from different sources. Combining data from different places can be a daunting task that shakes the confidence of many Excel users. The complex mazes of VLOOKUP, and now XLOOKUP can be confusing, but here's where Copilot steps in to change the game. Let's take it for a spin with the exercise file, copilo-multiple-tables. In this example, we're looking at New York City's population by borough for each decade. The catch is that it's sorted by borough ID and not by the name. While using IDs like this can be computationally useful, it can get tricky when you're trying to work with the data in a practical sense. This is where Copilot can really shine both as a short-term helper and also as a long-term guide to efficiently manage multiple tables. Let's put it to the test. First, I'm going to select the population table on the left of the worksheet and then open Copilot. To start, I'm going to ask for the population of Brooklyn in 1990. Without the borough IDs properly aligned here, this could be tough to figure out on your own. But with Copilot, we get our answer in a flash. Think about how useful this could be, pulling up data in a snap before a meeting, or just before you wrap up for the day. Next, let's dive deeper than just fetching a single data point. I'm going to ask Copilot to draft an XLOOKUP function to pull details from the borough's table. I will be specific about the tables and the formula to use. This does assume you're somewhat familiar with how XLOOKUP works. It really highlights the to have a solid Excel foundation in place before diving into what Copilot can do for you. Once Copilot crafts a function, you can copy the formula into your worksheet or insert it into your table. For instance, if an unexpected borough ID like six pops up as it does in our data here, you'll receive an NA error, which is not something you might want in a report or during calculations. So to fix this, I suggest tweaking the Copilot prompt to handle unmatched values by filling them in with "Other." I will insert the improved formula into our workbook. Getting troubleshooting like this right means being precise with your instructions and really understanding what you need from your data and how Excel can help you. Lastly, what if you wanted to use relational joins and Power Query instead of XLOOKUP to merge these data sources and wanted Copilot's help? While Copilot can't automate every step of Power Query yet, it can guide you through the process with quite a bit of accuracy. Again, the key here is clarity. Specify your tables, the fields to merge and more. Copilot will then steer you through the necessary steps to accomplish the merge. Visibility to work with multiple tables marks a significant leap in what Copilot can do. While it's mainly been limited to working within single tables, it's now savvy enough to handle multiple tables simultaneously, bringing it closer to the everyday realities of any analysts who rarely have all of their data neatly in one place.