Using Excel's Transform Data by Example Add-in
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.
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.
After a few seconds of analysis, Transform Data by Example presents several results options, as shown in Figure 3.
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 4 – without rekeying any of the data!
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.
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.