Your supplier sends weights in kilograms but your system needs pounds. International invoices arrive in euros but your reports are in dollars. Product dimensions are in centimeters but your shipping tool expects inches. Excel’s CONVERT function handles dozens of unit types, and for currencies, a simple rate table does the job. Here’s how to convert anything in bulk without manual calculation.
The CONVERT Function
Syntax
=CONVERT(number, from_unit, to_unit)
Converts a number from one measurement unit to another. Supports weight, distance, time, temperature, volume, area, speed, and more.
Common Conversions
| A | B | C | |
|---|---|---|---|
| 1 | Value | Formula | Result |
| 2 | 50 kg | =CONVERT(50,”kg”,”lbm”) | 110.23 lbs |
| 3 | 100 cm | =CONVERT(100,”cm”,”in”) | 39.37 in |
| 4 | 72°F | =CONVERT(72,”F”,”C”) | 22.22°C |
| 5 | 5 miles | =CONVERT(5,”mi”,”km”) | 8.05 km |
| 6 | 3.5 liters | =CONVERT(3.5,”l”,”gal”) | 0.92 gal |
Finding unit codes
The unit codes aren’t always obvious. Common ones: “kg” (kilograms), “lbm” (pounds), “cm” (centimeters), “in” (inches), “mi” (miles), “km” (kilometers), “l” (liters), “gal” (gallons), “C” (Celsius), “F” (Fahrenheit), “m” (meters), “ft” (feet). Search “Excel CONVERT function units” for the full list.
Bulk Conversion with a Rate Table
For conversions CONVERT doesn’t handle (like currencies, or custom business units like “pallets to units”), build a rate table:
| A | B | |
|---|---|---|
| 1 | Currency | Rate to USD |
| 2 | EUR | 1.08 |
| 3 | GBP | 1.27 |
| 4 | JPY | 0.0067 |
| 5 | CAD | 0.74 |
Then convert any amount using VLOOKUP against the rate table:
=C2 * VLOOKUP(D2, RateTable, 2, FALSE)
Where C2 is the amount and D2 is the currency code. Multiplies the amount by the exchange rate to get USD.
Dynamic Currency Conversion
For a spreadsheet where users select the source and target currencies:
=C2 * VLOOKUP(D2, Rates, 2, FALSE) / VLOOKUP(E2, Rates, 2, FALSE)
Converts to USD first (multiply by source rate), then to target currency (divide by target rate). Handles any currency-to-currency conversion through a single USD-based rate table.
Text-Based Format Conversion
Converting number formats (not units) — like phone numbers, dates, or ID codes:
Format phone number: 5551234567 → (555) 123-4567
="("&LEFT(A2,3)&") "&MID(A2,4,3)&"-"&RIGHT(A2,4)
Pad with leading zeros: 47 → 00047
=TEXT(A2, "00000")
Convert number to words (simple)
=TEXT(A2, "#,##0.00")
Formats 1234567.8 as “1,234,567.80”. For converting numbers to written words (“One Thousand Two Hundred”), you need VBA — no formula can do this.
Exchange rates change daily
A static rate table is only accurate for the day you set it. For financial reporting that needs current rates, you need a way to update the table — either manually or through an API connection. Excel’s built-in “Stocks” data type (365 only) can pull some currency data, but VBA with a web API is more reliable for automated rate updates.
When Formulas Aren’t Enough
Simple conversions with CONVERT and rate tables work for standard tasks. But business data conversion often involves more:
- Mixed units in one column — some rows are in kg, others in lbs, and the unit is embedded in the text
- Live exchange rates — pulling current rates from an API and applying them automatically
- Custom conversion rules — “1 pallet = 48 units” or “1 case = 12 bottles” with product-specific mappings
- Bulk file processing — converting units across hundreds of files from different suppliers
- Audit trail — logging which conversions were applied and what rates were used
A custom VBA conversion tool detects units, applies the right conversion, pulls live rates, handles product-specific mappings, and processes files in bulk.
“Excellent in every way. Saved me so much time with his automation of my spreadsheets!”
Karyn M. · Coventry · PeoplePerHour
Need Automated Data Conversion Across Files?
Tell us about your conversion needs and we’ll build a tool that handles it all.
Start a Project