Posey's Tips & Tricks
Expanded Data Types: Microsoft Excel's Most Significant New Feature in Years
Beyond just crunching budgets, Microsoft's recent Excel overhaul will give it the intelligence to recognize over 100 new data categories and process information in ways that were previously unimagined.
A few months ago, I speculated on what a then-rumored Office 365 "Life Edition" might end up looking like. Ultimately, Microsoft chose to abandon the "Life Edition"
moniker in favor of much more generic-sounding names -- Microsoft 365 Personal and Microsoft 365 Family. These new Office 365 editions introduced a number of new features, some of which are also going to be available in business editions of Office 365.
With a couple of exceptions, I have not yet had the opportunity to try out the new features for myself. Even so, there were a couple of announcements regarding Excel that really caught my attention.
One of the things I mentioned in that earlier column was that Microsoft was working on integrating some new finance tools into Excel. At that time, I didn't have any information on what those tools might consist of, but I speculated that Microsoft might try to integrate some of the functionality from a long-retired product called Microsoft Money into Excel. While I don't have any way to know if Microsoft did indeed integrate Microsoft Money into Excel, I find it really interesting that Microsoft's new personal finance feature is called Money in Excel.
Money in Excel allows you to tie Excel to your bank accounts and credit cards. This allows Excel to import transactions and analyze your spending. For example, Excel is able to show you how your spending has changed from one month to the next, as well as what types of things you are spending the most money on. Excel will also be able to alert you to things like price changes for recurring payments, account overdrafts and banking fees.
While I think Money in Excel has the potential to be very useful, the thing that really intrigues me is Excel's support for new data types. With a few exceptions, Excel has historically supported some pretty generic data types. These include things like floating point numbers, integers, string data, date and time stamps, and so on. However, Microsoft is now extending Excel to allow it to support far more data types than ever before.
I don't blame you if your eyes started to glaze over as you read that last paragraph. Normally, Excel data types is a really dry subject to talk about. As I said before, though, I am intrigued by what Microsoft is doing with these new data types. In fact, I think the introduction of these new data types could be a total game-changer for Excel (and not in a boring data-analytics sort of way).
The best way I can think of to convey the significance of Excel's new data types is to ask you to think of all of the ways you use Excel that have nothing to do with work, and how those use cases could be improved with additional intelligence.
I often find myself using Excel as a tool for keeping track of my travels. For instance, I might have a spreadsheet line that lists a flight number, the date of the flight, how much I paid for the ticket, a link to my reservation and so on. Other lines in the spreadsheet might include similar information for hotels or rental cars.
When I have created these types of spreadsheets in the past, Excel has been oblivious to most of the information in the spreadsheet. It might be able to keep a tally of the trip's costs, and it might understand that I am entering dates into certain fields, but the text entries are meaningless to Excel. It doesn't understand the difference between a flight number and the name of a rental car company.
But what if Excel could actually make sense of all that information and augment the data on its own? What if Excel could recognize that the last piece of data entered was a flight number and could automatically create cells for things like the flight status, baggage fees or the odds of getting an upgrade?
Microsoft hasn't said anything about making Excel recognize airline reservations, but the example I just gave is representative of the type of functionality that we will soon begin to see in Excel. According to Microsoft's announcement of the Family and Personal subscriptions, Excel is going to become aware of over 100 new topics including things like food, places, chemistry and movies.
While Microsoft hasn't said exactly how Excel will support these topics, a demo showed someone typing in the name of a food item and Excel automatically displaying the nutritional information for that item (calories, fats, carbs and more).
Even though I haven't had a chance to try out Excel's new capabilities for myself, I suspect that the new data types are going to allow Excel to be used in ways that were previously unimagined. I think Excel's newfound ability to recognize new data types and to provide supplementary data is easily the most significant feature to be added to Excel in the last 20 years.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.