Using Excel's Transorm Data by Example Add-in

Using Excel's Transform Data by Example Add-in

Excel

Dating back to Excel 2010, Microsoft has been adding features to Excel to assist us in transforming our data so that it is more useful. Among the tools that Excel now offers are Power Query, Flash Fill, and the relatively unknown Transform Data by Example add-in – the focus of this discussion.

Getting Started with Transform Data by Example

As indicated above, Transform Data by Example is an add-in for Excel, meaning that you will need to download and install it before you can use it. You can download the tool from the Office Store or by visiting https://www.microsoft.com/en-us/garage/profiles/transform-data-example/. Once you download and install the add-in, you will see it visible near the right side of the Data tab of the Ribbon. Now you’re ready to put it to work!

Using Transform Data by Example

Transform Data by Example is pattern-recognition software that allows you to specify patterns and then it will transform columns of data based on those patterns. For instance, consider the telephone numbers shown in column A in Figure 1. Because of the wide variety of formatting options in column A, using Excel’s Flash Fill feature based on the patterns established in column B will provide a less-than-desirable set of results.

Transform Data by Example Figure 1
Figure 1 - Data for Transform Data by Example

With the patterns established in column B, click on any blank cell in column B and then clicking on the Transform Data by Example icon on the Data tab of the Ribbon opens the Transform Data by Example task pane shown in Figure 2. Further, clicking the Get Transformations button near the bottom of the pane launches this transformation tool, attempting to transform the data in column A based on the patterns established in column B.

Transform Data by Example Figure 2
Figure 2 - Transform Data by Example Task Pane

After a few seconds of analysis, Transform Data by Example presents several results options, as shown in Figure 3.

Transform Data by Example Figure 3
Figure 3 - Results Obtained from Transform Data by Example

Simply moving your mouse from one result option to another provides a live preview of how your data will appear if you accept that option. In the example presented, selecting the third option and click the Apply/Unapply button in the task pane produced the flawless set of results shown in Figure 4without rekeying any of the data!

Figure 4 - Results Obtained by Using Transform Data by Example
Summary

Excel’s data transformation tools are good and getting better every day; Transform Data by Example is indicative of this. Simply download the tool, install it, and you will have a very powerful feature at your disposal to help you clean up data in Excel.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

You can learn more about Transform Data by Example and countless other Excel features by participating in a K2 Enterprises training session. For more information, CLICK HERE to visit our training page.

Watch the following video to see Transform Data by Example in action: