Pro User
  • All of Microsoft
  • My Role
    • All in My Role
    • Pro User
    • Administrator
  • Focus
    • All in Focus
    • Microsoft Copilot
    • All about AI
    • M365 Admin
    • What's Hot
    • M365 Release
Timespan
explore our new search
Excel: use overlooked functions for everyday tasks
Excel

Excel: use overlooked functions for everyday tasks

by HubSite 365 about Mynda Treacy (MyOnlineTrainingHub) [MVP]

Use Excel functions to convert units, inspect formulas, flag text-formatted numbers, document workbooks and calculate service periods.

Key insights

  • CONVERT uses unit codes such as "mi" and "km"; codes are case-sensitive and the function does not convert currencies.
  • FORMULATEXT displays a cell's formula, while IFNA can replace its error for cells that contain no formula.
  • TYPE returns 2 for text, which can help find number-like text that SUBTOTAL ignores.
  • N can add a short text note to a numeric formula; the note is limited to 255 characters.
  • DATEDIF calculates complete years and remaining months, but its start date must be earlier than its end date.

Excel's lesser-known functions can handle unit conversions, formula inspection, data checks and date calculations without extra tools. They are useful for analysts, report authors and spreadsheet users who troubleshoot inherited workbooks or prepare files for colleagues. Mynda Treacy (MyOnlineTrainingHub) [MVP] demonstrates these techniques using logistics, reporting and HR examples.

What it does

These functions address practical gaps in everyday workbooks: converting measurements, making formulas visible, finding values stored as text, and generating workbook details. Some are also useful for documenting the reasoning behind a calculation or converting number formats for reports. They do different jobs, so the best starting point is the specific problem in the worksheet.

For example, CONVERT handles measurements such as miles to kilometers, while FORMULATEXT displays a formula as text. TYPE reports what kind of value a cell contains, and CELL, SHEET and SHEETS can generate workbook-identifying details. Other examples include Roman numeral conversion and DATEDIF for elapsed time.

Who it is for and what you need

These techniques are aimed at people who build or maintain Excel workbooks, particularly reports shared with colleagues and spreadsheets imported from other systems. The examples use worksheet formulas and ordinary cell references. The video also uses Excel tables, where a formula entered in a column copies down automatically.

Some functions have specific considerations. The demonstration notes that TRANSLATE is available in Microsoft 365 and requires an internet connection. It also uses TEXTAFTER in one example, with MID and FIND as an alternative for users who are not on Microsoft 365. No special add-in or VBA code is needed for the formula techniques described.

How it works

For a measurement conversion, enter the value, the starting unit code and the target unit code in . A formula such as converts the value in A2. You can also put the unit codes in cells and refer to them, making the conversion easier to change for different rows. Check spelling and capitalization: invalid or incorrectly cased codes can return .

To inspect formulas in a report, use with the cell to check. If the cell has a typed value rather than a formula, the function returns an error. Wrapping it in lets you display a label such as “Hand-coded” instead, which can then be highlighted with conditional formatting. That helps reviewers find cells where a value may no longer update with the rest of the workbook.

For imported data, apply to the values that should be numeric. A result of 2 indicates text; in the demonstrated order report, those text entries were causing to omit amounts. Excel's error-checking flag can offer “Convert to Number.” The video also describes checking that background error checking is enabled under File > Options > Formulas if the flags do not appear.

For automatic workbook labels, returns a path containing the file name and sheet name. can extract the sheet name after the closing square bracket; returns a sheet's position and returns the total sheet count. Together, these formulas can produce a label such as “Sheet 4 of 8” that updates when sheets are added or moved.

To keep a short explanation with a numeric calculation, add to the formula. Because N converts text to zero, adding it does not change a numeric result. For elapsed service time, uses a start date, an end date and a unit: “Y” returns complete years, while “YM” returns remaining months after complete years. The formula can combine those results into a readable years-and-months label.

Limitations and alternatives

These functions do not remove the need to check the result. Unit codes in are case-sensitive, and it does not convert currencies; the demonstration points to Excel's Currencies data type on the Data tab for currency conversions. only returns formulas, so a manually entered value needs separate handling if you want a friendly label.

The note is limited to 255 characters and is best suited to formulas that return numbers. converts numbers only up to 3999; larger values return . For dates, the start date in must precede the end date or the formula returns . The video cautions against using the “MD” unit because some date combinations can produce negative, zero or inaccurate results.

spells numbers in Thai. Combined with , it can provide number wording in another supported language, but the output may retain Thai currency terms such as “baht” and “satang.” The demonstrated formula uses to replace those terms, and the translated wording should be reviewed before it appears on an invoice because machine translation may need correction.

Sources

Frequently asked questions

How do I convert units in Excel?

Use CONVERT with a value, a starting unit code and a target unit code, such as =CONVERT(A2,"mi","km"). Unit codes are case-sensitive.

How can I tell whether an Excel cell contains a formula?

Use FORMULATEXT with the cell reference. If the cell contains a typed value rather than a formula, use IFNA around FORMULATEXT to show a message such as “Hand-coded.”

How do I calculate years and remaining months with DATEDIF?

Use DATEDIF with the start date, end date and "Y" for complete years; use "YM" to return remaining months after complete years. The start date must be earlier than the end date.

Does Excel's TRANSLATE function need an internet connection?

Yes. The demonstration notes that TRANSLATE is available in Microsoft 365 and requires an internet connection.