Excel Unit Converter Template: Instant Metric–Imperial Conversions

Free Unit Converter for Excel: Customizable Functions and Shortcuts

Working with different measurement systems inside Excel can be slow and error-prone if you keep switching tabs, copying values into external tools, or writing ad-hoc formulas. A free, customizable unit converter for Excel brings conversions into the spreadsheet where you need them: reproducible, programmable, and fast. This article shows what a lightweight converter can do, how to add customizable functions and keyboard shortcuts, and provides ready-to-use examples.

Why use an in-sheet unit converter?

• Speed: Convert values directly in cells without switching apps.
• Accuracy: Use consistent conversion factors and avoid manual mistakes.
• Reproducibility: Store conversion logic in the workbook for auditing and reuse.
• Automation: Convert whole ranges, tables, or pivot outputs with formulas or macros.
• Customization: Add your own units, aliases, and rounding rules.

What the free converter provides

• Custom Excel functions (UDFs) to convert between units using intuitive syntax.
• A lookup table you can edit to add or update units and aliases.
• Example macros to convert selected cells or entire columns.
• Optional keyboard shortcuts to run common conversions quickly.
• Clear handling of ranges, arrays, and empty/non-numeric cells.

How it works — core components

1. Units table: a sheet (e.g., “Units”) with columns: UnitKey, UnitType, Factor, Offset, Aliases.

   • UnitKey: canonical name (e.g., meter).
   • UnitType: group (length, mass, temperature).
   • Factor: multiplicative factor to convert to base unit.
   • Offset: additive term for affine conversions (used for temperature).
   • Aliases: comma-separated alternate names/abbreviations (m, metre).

2. UDF ConvertUnit(value, fromUnit, toUnit, decimals)

   • Looks up UnitType for both units and ensures they match.
   • Applies Factor/Offset to normalize to base unit, then converts to target.
   • Returns rounded result when decimals provided.

3. Macro ConvertSelection(fromUnit, toUnit, decimals)

   • Iterates selected cells and replaces numeric values with converted results (preserving non-numeric cells).
   • Optionally writes results to adjacent column instead of overwriting.

4. Shortcut bindings

   • Assign keyboard shortcuts (e.g., Ctrl+Shift+U) to macros for common tasks like “Convert selection from imperial to metric”.

Installing the converter (summary)

1. Open Excel and press Alt+F11 to open the VBA editor.
2. Insert a new Module and paste the provided VBA code for the UDF and macros (examples below).
3. Create a worksheet named “Units” and populate the units table (sample provided).
4. Save the workbook as a macro-enabled file (.xlsm).
5. Optionally set macro shortcut keys in the VBA editor (Tools → Macros → Options).

Example Units table (minimal)

• UnitKey | UnitType | Factor | Offset | Aliases
• meter | length | 1 | 0 | m, metre
• kilometer | length | 1000 | 0 | km, kilometre
• foot | length | 0.3048 | 0 | ft, feet
• pound | mass | 0.45359237 | 0 | lb, lbs
• kilogram | mass | 1 | 0 | kg, kilo
• celsius | temperature | 1 | 0 | C, °C
• fahrenheit | temperature | 0.5555555556 | -17.7777777778 | F, °F
  (Temperature offsets: to convert F→C normalize with offset -32 then multiply by ⁄₉; store Factor=⁄₉, Offset=-32*⁄₉ or implement affine logic in code.)

VBA: UDF and conversion macro (compact)

Paste into a Module. This is a concise example; expand the units table and error handling for production.

Code
Copy CodeCopied
’ ConvertUnit UDF Function ConvertUnit(val As Variant, fromU As String, toU As String, Optional dec As Variant) As VariantIf Not IsNumeric(val) Then ConvertUnit = CVErr(xlErrValue): Exit Function

 
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Units") Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Dim i As Long, fromRow As Long, toRow As Long fromRow = 0: toRow = 0 For i = 2 To lastRow     If LCase(ws.Cells(i, 1).Value) = LCase(fromU) Or InStr(1, "," & LCase(ws.Cells(i, 5).Value) & ",", "," & LCase(fromU) & ",") > 0 Then fromRow = i     If LCase(ws.Cells(i, 1).Value) = LCase(toU) Or InStr(1, "," & LCase(ws.Cells(i, 5).Value) & ",", "," & LCase(toU) & ",") > 0 Then toRow = i Next i If fromRow = 0 Or toRow = 0 Then ConvertUnit = CVErr(xlErrRef): Exit Function If LCase(ws.Cells(fromRow, 2).Value) <> LCase(ws.Cells(toRow, 2).Value) Then ConvertUnit = CVErr(xlErrNA): Exit Function Dim valBase As Double, factorFrom As Double, offsetFrom As Double, factorTo As Double, offsetTo As Double factorFrom = CDbl(ws.Cells(fromRow, 3).Value): offsetFrom = CDbl(ws.Cells(fromRow, 4).Value) factorTo = CDbl(ws.Cells(toRow, 3).Value): offsetTo = CDbl(ws.Cells(toRow, 4).Value) valBase = (CDbl(val) + offsetFrom) * factorFrom Dim result As Double: result = (valBase / factorTo) - offsetTo If IsMissing(dec) Then ConvertUnit = result Else ConvertUnit = Round(result, CLng(dec)) 
End Function
’ ConvertSelection macro Sub ConvertSelectionMacro()
Dim fromU As String, toU As String, dec As Long fromU = InputBox("From unit (e.g., ft):", "Convert Selection") If fromU = "" Then Exit Sub toU = InputBox("To unit (e.g., m):", "Convert Selection") If toU = "" Then Exit Sub dec = Val(InputBox("Decimals (leave blank for full precision):", "Convert Selection")) Dim rng As Range, c As Range Set rng = Selection For Each c In rng.Cells     If IsNumeric(c.Value) Then         On Error Resume Next         c.Value = Application.WorksheetFunction.Convert(c.Value, fromU, toU) ' try built-in first (if available)         If Err.Number <> 0 Then             Err.Clear             c.Value = ConvertUnit(c.Value, fromU, toU, IIf(dec = 0, , dec))         End If         On Error GoTo 0     End If Next c 
End Sub 

Note: Excel has a built-in CONVERT function, but it covers only a fixed set of units and names. The UDF above uses a custom table so you can add units, aliases, and affine conversions (temperatures) reliably.
Example formulas

Single value: =ConvertUnit(A2,“ft”,“m”,2) — converts A2 from feet to meters, rounded to 2 decimals.
Array (spill): =MAP(A2:A10, LAMBDA(x, ConvertUnit(x,“lb”,“kg”,2))) — converts a column of pounds to kilograms (Excel 365).

Shortcuts and productivity tips

Assign a macro shortcut (e.g., Ctrl+Shift+U) to the ConvertSelectionMacro for quick conversions.
Create quick buttons on the ribbon (Customize Ribbon → New Group → Assign macro) for common conversions like ft→m or lb→kg.
Keep a “Unit presets” sheet with named ranges for common conversions; macros can reference those presets without prompting.
Validate your Units table with a test sheet that compares UDF outputs to trusted conversions.

Testing and validation

Add unit tests in a hidden sheet comparing UDF results against known values (e.g., 1 inch = 0.0254 meter).
Include edge-case checks: zero, negative values, extremely large numbers, and non-numeric cells.

Extending the converter

Add compound unit support (e.g., mph, N·m) by parsing unit expressions and combining base factors.
Add currency conversion by fetching exchange rates (requires web queries and careful refresh/logging of sources).
Build an add-in (XLA/XLAM) so the converter can be installed across workbooks.

Conclusion
A free, customizable unit converter for Excel saves time and reduces errors by keeping conversion logic inside your workbook. With a simple units table, a flexible UDF, and a few macros and shortcuts, you can convert single values or entire ranges, add your own units and aliases, and integrate conversions into automated reports. Ready-to-use code and a unit table let you start converting immediately — expand them as your needs grow.