Traditionally, one cell meant one piece of data, but Excel data types add intelligent and richer meaning, creating a multidimensional view.
Back in 2019, Stocks and Geography were the first categories added to the ribbon, appearing in the Data tab. They were later joined by Currencies.
These three are ‘powered by Bing’, which acts as an index for scraping data from online sources such as Refiniti (for financial markets) and Wikipedia.
A cell with a data type contains a small icon. When clicked on, a pop-up card unveils containing numerous entries about that entity. It could be a person, country, movie, animal or yoga pose, but there are hundreds now thanks to Microsoft’s partnership with Wolfram — a computational technology company that offers ‘expertly curated’ data (https://lnkd.in/gs3Ftj-S).
Unfortunately, some data types are hampered by their lack of accuracy and reliability — especially Geography. For example, who knew the largest city in the United Kingdom is Pontarddulais? Yes, a small Welsh town with circa 10,000 inhabitants. 😬 Furthermore, the flags of Mexico, China, France and Sweden cannot be retrieved.
If you need geographic data, my advice is to use Wolfram’s Locations instead.
Perhaps indicative of their lukewarm popularity, Microsoft quietly announced that Wolfram data types will be removed from Excel on June 11, 2023 (https://lnkd.in/gipV2zpz). This won’t affect the other three, but it does mean you won’t be able to retrieve Wolfram data anymore.
Whilst data types have benefits compared to other methods of importing data, they should be used with caution.
Going forward, Microsoft will move people over to Power Query custom data types, which I will cover in a future post.
1. Select a cell(s) containing data.
2. Go to Data (tab) > Data Types (group) and click the dropdown to display the full set of data types.
3. Choose the most appropriate category for your data, or click Automatic to detect it.
4. Click the small in-cell icon to reveal the data card.
5. Click the Extract button next to a field to extract it to the first empty adjacent cell.
6. The formula generated separates the cell reference and field name with a full stop, eg. =𝙱𝟸.[𝙻𝚊𝚛𝚐𝚎𝚜𝚝 𝚌𝚒𝚝𝚢]. You are free to work with it like any other.