Microsoft Excel - some experiential Tips & Tricks

[IainB]

2018-01-17 0904hrs: Every now and then, someone in the DC Forum posts a question about Excel, and sometimes there's a good reply and sometimes we learn something new, but the isolated thread rather seems to get buried, over time - becoming a sort of "gradually lost knowledge".
After starting the "Microsoft OneNote - some experiential Tips & Tricks" thread, I have been wondering for some time whether something similar might not be useful for Excel - things discovered, problems encountered and needing a solution, problems solved; that sort of thing. With some kind of index at the front - if that is workable (we need a wiki...).

Anyway, in my wanderings, I today stumbled upon a brilliant Microsoft Garage Project (Excel Add-on: Transform Data by Example) that had been released 2017-05-17 and that I simply had not known about.
So, I installed the Add-on and (as is my wont) "captured" the knowledge about  it in the form of some detailed notes about it in my OneNote journal - i.e., what I call my "21st century Zettelkasten PIM". That's one of my main methods for knowledge curation.
At the same time, I would like to share the knowledge in this thread, and perhaps other DCF users could contribute their Excel problems, solutions, tips and tricks as well.
If people find useful Excel posts in the forum, please point me to them or make a quote post to them, and I shall then be able to index it.

[IainB]

Excel Add-on: Transform Data by Example
Apparently launched 2017-05-17, this Add-on has the potential to save the Excel user hundreds of hours of mucking about with "data wrangling" - sorting mixed/mangled data strings into some kind of usable, normalized form. Works like a charm. Highly recommended. Ruddy brilliant. And it "learns". Seems to currently be constrained to max 100 rows at a time.

• What it is: Excel Add-on that links to an AI search engine for indexed data transform templates.
• Source: <https://www.microsoft.com/en-us/garage/profiles/transform-data-example/>
• Description: "Example-driven search engine to find functions for your data wrangling tasks"
• A Microsoft Garage project.
• Video: <https://youtu.be/cl7RXSHdsyo> (is excellent).

Transform Data by Example: (as at 2017-05-17)
   • Transform, clean and standardize diverse data sets
   • Excel Add-In (available from the Office Store)
   • Discover and reuse rich set of existing data transformations
   • Supports common domains like date, name, address, phone number
   • Easy to use: provide only a few input-output examples
   • Extend with your custom data transformations
   • <https://aka.ms/transform-data-by-example>