
Less familiar Excel functions can handle everyday spreadsheet tasks that might otherwise require manual checks or extra calculations. CONVERT, for example, changes 174 miles to about 280 kilometers using the unit codes “mi” and “km”; those codes are case sensitive. The example comes from a freight worksheet demonstrated by My Online Training Hub.
Use FORMULATEXT and TYPE to identify overwritten formulas and numbers stored as text, then examine how CELL and SHEET can keep workbook details current. The guide also shows how DATEDIF calculates complete years and remaining months between dates, including the need to place the earlier date first and avoid the unreliable MD unit.
Convert Measurements in a Table
TL;DR Key Takeaways :
- CONVERT changes measurements between units and can fill an entire table column automatically. Unit codes are case sensitive, and CONVERT does not handle currency.
- FORMULATEXT can expose hard-coded values in place of formulas; IFNA and conditional formatting can flag them. TYPE identifies numbers stored as text, which can otherwise be missed by calculations such as SUBTOTAL.
- CELL, TEXTAFTER, SHEET and SHEETS can keep workbook and sheet details current in headers. N can add a note inside a numeric formula without changing its result.
- ROMAN and ARABIC convert numbers to and from Roman numerals. TRANSLATE and DETECTLANGUAGE help with languages; TRANSLATE requires Microsoft 365 and an internet connection, and its wording should be checked.
- DATEDIF calculates time between dates, including complete years and remaining months. Microsoft warns that its MD unit can produce negative, zero or inaccurate results for some date combinations.
CONVERT changes measurements between units, such as miles and kilometers or Fahrenheit and Celsius. In Excel, you can use it in a table to convert an entire column automatically.
On the route sheet of Harbor Lane Logistics, a practice freight business in Portland, a formula converts 174 miles to 280 kilometers for a new Canadian customer. It takes the distance as its number, “mi” as the from-unit and “km” as the to-unit. Excel copies the formula down the table automatically.
A second table references values and unit codes in columns K and L to convert pallet weight, warehouse temperature, fuel, transit time and container length. You can change the units in their cells, but unit codes are case sensitive: a lowercase “c” or a misspelled code produces an N/A error.
Excel’s IntelliSense displays a complete list of CONVERT units, although the unordered list can be tricky to search. CONVERT does not handle currency; Excel puts currency conversions in the currencies data type on the Data tab.
Find Hard-Coded and Misread Values
FORMULATEXT displays a cell’s underlying formula as text, helping you spot an overwritten calculation. Because it can return only a formula, it produces an N/A error in Harbor Lane’s third-quarter report where someone typed 5% into cell D10. The figure matches the expected result, but is likely to be wrong when actuals change the following month, without a warning.
Wrapping FORMULATEXT in IFNA replaces the error with “hard coded.” You can then use conditional formatting to mark cells containing that text with a light red fill and dark red text.
TYPE helps you find values that look numeric but are stored as text. In September orders copied from an ordering system, SUBTOTAL reports 19,565 instead of 32,705 because it ignores three text amounts. TYPE returns 2 for those entries in rows 8, 11 and 13. After you convert them to numbers, every TYPE result is 1 and the total is correct.
TYPE also returns 4 for a logical value, 16 for an error, 64 for an array and 128 for compound data. ISNUMBER instead returns TRUE for numbers and FALSE otherwise, so TYPE gives you more detail when examining an unfamiliar file from another system.
If warning flags are missing, check that background error checking is on under Error Checking in the Formulas options, then use Reset Ignored Errors if needed. A flagged cell offers an option to convert its contents to a number.
Expand your understanding of Excel functions and tips with additional resources from our extensive library of articles.
- 10 New Excel Functions to take your formulas to the next level in 2024
- 15 MS Excel Functions to Boost Your Data Analysis and Automation
- 15 Modern Excel Functions Worth Knowing in 2026 That Outclass Clunky Workflows
- 15 Excel Tips and Tricks to Save Hours in 2025
- Advanced Excel Tips & Tricks to improve your data analysis in 2024
Document Workbooks and Formulas
CELL can return a workbook’s path using any cell on the sheet as a reference. The result includes the file name in square brackets and the sheet name at the end. This can help you keep headers current: Harbor Lane’s manually typed report headers contain a file path, sheet name and sheet position, but could be out of date within a week.
TEXTAFTER extracts the sheet name using the first closing bracket as its delimiter. Outside Microsoft 365, you can instead use MID and FIND to locate that bracket and return the following characters.
SHEET and SHEETS provide the current sheet’s position and the workbook’s sheet count. In the example, a header changes from “sheet four of eight” to “sheet five of nine” after a sheet is added and the current one is moved. SHEET can also take a tab name: the “type” sheet is third in the example.
The N function lets you put a note inside a formula. Harbor Lane’s example records that its 4% commission rate was agreed with finance in August and is due for review in January. N turns the quoted note into zero, leaving the calculation unchanged. The note is limited to 255 characters, and the technique is really suitable only for formulas that return a number.
Keeping the note inside the formula makes it less likely to become separated from the calculation than a cell comment or note. N can also convert dates to serial numbers and TRUE to 1.
Work With Roman Numerals and Languages
ROMAN and ARABIC convert numbers in opposite directions. ROMAN turns 14 into XIV for a document cover and handles values up to 3999. ARABIC converts scanned contract clause numbers back into numbers, so you can sort the example list by that column.
BAHTTEXT has written numbers as Thai words for more than 20 years. Combined with TRANSLATE, it can turn those words into another supported language. In the invoice example, the language codes TH and EN produce English text, although the result still contains Baht and Satang.
Two nested SUBSTITUTE calls replace those terms with dollars and cents, without VBA. TRANSLATE requires Microsoft 365 and an internet connection, and you should check its machine-translated wording before use.
TRANSLATE also converts product descriptions from English to Spanish for a customer in Mexico, with the same Microsoft 365, internet and machine-translation conditions. DETECTLANGUAGE can identify the language of text in a supplier spreadsheet when it is unknown.
Calculate Service Length With DATEDIF
DATEDIF calculates the time between dates, even though Excel offers no autocomplete or tooltip for it and does not show it in the function wizard or insert function dialogue. Excel retains the function for compatibility with Lotus 1-2-3 workbooks.
In the HR example, start dates are in column D and the report date is in F4. The Y unit returns complete years; YM returns the remaining months; and YD returns days left after complete years. MD returns days left after months, but Microsoft warns it can produce a negative number, zero or an inaccurate result for some date combinations.
Combining Y and YM with text gives a years-and-months figure for service, ages, contract lengths or overdue invoices. The start date must come before the end date, or DATEDIF returns an error. For the days calculation, My Online Training Hub would avoid MD given Microsoft’s warning about its results for some date combinations.
Media Credit: MyOnlineTrainingHub
Disclosure: Some of our articles include affiliate links. If you buy something through one of these links, Geeky Gadgets may earn an affiliate commission. Learn about our Disclosure Policy.