Posey's Tips & Tricks
The New Copilot Function Vastly Improves Microsoft Excel, Part 1
Excel’s newest Copilot integration finally delivers the data intelligence Microsoft promised years ago, transforming how users clean, organize and understand information within their spreadsheets.
Way back in 2020, I wrote an article for this site discussing the significance of new data types within Excel. At the time, Microsoft had announced that it would be adding native support for over a hundred new data types that hadn't historically been associated with Excel. For example, these new data types could be used to allow Excel to recognize the names of geographic locations. Microsoft also created a data type related to foods, and speculation at the time () was that entering a word such as Pizza might cause a spreadsheet to automatically display things like calorie counts or the number of carbs in the crust.
When I wrote my 2020 blog post, I described the introduction of the new data types as being "Microsoft Excel's most significant new feature in years." To demonstrate the significance of the new data types, I mentioned that if you were to create a spreadsheet containing information related to your travel information, Excel wouldn't normally understand the difference between a flight number and the name of a rental car company. However, in the article, I posed the questions, "what if Excel could actually make sense of all that information and augment the data on its own? What if Excel could recognize that the last piece of data entered was a flight number and could automatically create cells for things like the flight status, baggage fees or the odds of getting an upgrade?"
At the time, it seemed that Microsoft was working to build some sort of data intelligence into Excel, but I wasn't really sure what that data intelligence would look like. However, Microsoft has recently released an update for insiders that uses Copilot to provide that data intelligence. This new functionality, which should eventually be available to anyone who has a Copilot license, is completely game changing. Although I had previously described the new data types as being Microsoft's most significant new Excel feature in years, the data types offer little benefit without some sort of intelligence that can take advantage of the data types. The newly introduced Copilot functionality, paired with Excel's ability to recognize various data types is going to completely change the way that Excel users work. I would even go so far as to say that for Excel, this update changes everything.
So this raises the question of what you can actually do with the new Excel update. So far, the best demo that I have seen pertaining to Excel's capabilities was in a YouTube video found here.
While this video demonstrates several different capabilities associated with the new update, the demos that caught my attention the most were those related to a rather messy address list. This address list crammed entire addresses into a single column of data, so that one cell would contain the street address, city, state and zip code. Additionally, these addresses were formatted inconsistently. Some addresses for example, placed the zip code at the beginning rather than at the end of the address. Even so, the person who created the video was able to effortlessly make Excel extract the zip codes from the addresses.
Think about that one for a moment… In order to do that, Excel had to know more than just that a particular column contained address data. Excel had to understand the components that make up an address, how those components differ from one another and what each component looks like. In fact, with a simple prompt, the person who created the video was able to rewrite the addresses so that they were correctly formatted.
Many years ago, I owned an ecommerce site. It's kind of a long story, but I inherited an extremely messy customer list that reminded me a lot of the address list shown in the video. I had to manually reformat the 30,000+ entries in my customer list. It was a grueling and error prone process that took me a couple of weeks to complete. The person who created the video did the exact same thing (albeit with a lot less data) in a matter of seconds.
In case you are wondering, Excel's new data recognition capabilities are not limited to basic data types such as addresses. In another demo, the previously mentioned YouTuber got Excel to automatically categorize a list of expenses. They performed yet another demo pertaining to professional sports teams. Similarly, Microsoft has created a simple demo showing off Excel's ability to understand airport codes.
I could go on and on about all of the new capabilities that Copilot brings to Excel, but I would rather show you how to use these new capabilities. I will give you a crash course in Part 2.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.