Microsoft Excel features more than 400 formulas that users enjoy when creating marvelous data models, calculators, and fun tools. Microsoft keeps adding new ones every now and then, but usually also keeps the existing ones for the sake of backwards compatibility. Naturally, even its power users rarely use all 400, and while some can be very handy to deal with complicated operations, some are very rarely used, if at all.
For a change, we’re going to take a look at the least useful Excel formulas and show you what you might be missing out on (or not)! Feel free to download our sample workbook from below.
BAHTTEXT
The BAHTTEXT function converts a number to Thai text format and adds a suffix "Baht." Although this formula probably is included for localization purposes, there is a quite popular urban legend about it.
It is rumored that some developers of Excel love Thai food and usually eat from a Thai restaurant. So, they coded this function to easily calculate the order costs.
ROMAN
The ROMAN function converts an Arabic numeral to Roman. Although Excel provides 5 levels of precision from classic to simplified, it has only aesthetical value and doesn’t see much use. Maybe once a year, when creating Super Bowl statistics?
This function also has another version that converts Roman numerals to Arabic (ARABIC). See our guide for more details on the ROMAN function: How to use ROMAN numbers
CONCATENATE
The CONCATENATE function can merge its parameters, and returns a string value. You can do the exact same thing by using an ampersand (&) character between strings or texts. The CONCATENATE function doesn't provide any advantage for the most part, unless you need to merge 100 strings and do not want to use ampersand (&) character 99 times. By the way, you still need to select each cell one-by-one because it doesn't support ranges.
Even Microsoft accepted the fact that it doesn’t see much use and has introduced a new function called CONCAT that supports ranges. At the time of writing this article, the CONCAT functions is only available to Office 365 users.
See our guide for more information about the CONCTENATE function: Transforming Data with the CONCATENATE function in Excel
GETPIVOTDATA
To be honest the GETPIVOTDATA function has its own niche and advantages. However; its static structure makes it more prone to errors and can make a workbook hard to maintain. The function retrieves summary data from a PivotTable, provided that the summary data is visible in the report.
It is fairly easy to use, just type in an equal sign (as you would for any formula), and then click a cell containing data in a Pivot Table. Excel will automatically generate the formula. However, this is where its user-friendliness ends.
If you have a relatively large Pivot Table, in addition to a very long formula that is hard to read, you will also get a single highlighted cell which really doesn’t explain anything.
The strings inside quotes (""") represent field names, in a static manner. This means that if update a field name, you will also need to update the formula manually to avoid #REF! errors.
Also see our How to Organize and Analyze Your Data Quickly with Excel's PivotTables article to learn more about how to master Pivot Tables.
