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
-
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).
-
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.
-
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.
-
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)
- Open Excel and press Alt+F11 to open the VBA editor.
- Insert a new Module and paste the provided VBA code for the UDF and macros (examples below).
- Create a worksheet named “Units” and populate the units table (sample provided).
- Save the workbook as a macro-enabled file (.xlsm).
- 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 ⁄9; store Factor=⁄9, Offset=-32*⁄9 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.
’ 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.