Skip to main content

Transform Data By Example—a Microsoft Garage project for Excel

A common problem facing Excel users is data wrangling—transforming raw data into a different format so that it can be used for further analysis. For many common data domains—such as date-time, addresses, phone numbers and URLs—sophisticated transformation functions and services exist but are locked up in code repositories such as Stack Overflow, .NET and GitHub, or services such as Bing Maps. For Excel users attempting to perform a specific data transformation task, these functions are difficult to find and use.

Transform Data By Example—auto-magic? You decide

Transform Data By Example is an Excel add-in that makes it easy to quickly find relevant transformation functions for a specific data transformation task. All you need to do is provide a few examples of the desired output, and Transform Data By Example automatically returns a ranked list of relevant data transformation functions from the large repository of functions it has crawled and indexed. Simply click one of the returned functions to see the results of applying that function on the data.

In the example below, the user has a collection of addresses in the United States in various formats and needs to extract city, state and zip code information for aggregate analysis. The user only needs to provide a few output examples in the column next to the input and then click Get Transformations! Within a few seconds, Transform Data By Example finds relevant transformation functions from a large collection of functions it indexes, and then automatically uses these functions to compose and generate new code that can perform the exact transformation the user specified. All the heavy lifting happens under the cover and is transparent to users. There is no need to write code or leave the familiar Excel interface.

Transform Data By Example 1

Transform Data By Example 2

Extensible engine for transformations

In addition to the large collection of data transformation functions that Transform Data By Example already indexes, users can add their own data transformation code to the collection through an easy extensibility interface below. Transform Data By Example makes the new transformation functionality instantly searchable.

Transform Data By Example 3

Like a web search engine, Transform Data By Example continuously improves its quality by leveraging implicit user feedback. The more you use it, the better it gets. At Microsoft Research, we’re constantly developing and improving Transform Data By Example, so be sure to give it a try and let us know what you think at our UserVoice forum. We greatly value your direct feedback to help us get better at helping you wrangle that data!

Learn more

—The Microsoft Research team

You may also like these articles

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…

Image for: A woman sitting on a couch using her PC laptop.

Introducing Money in Excel, an easier way to manage your finances

Today, with the launch of Money in Excel, we are embarking on a journey to simplify your day-to-day money decisions to help you stay on track of your longer-term financial goals.