To convert units in bulk in Excel, use =CONVERT(number, from_unit, to_unit), which covers weight, distance, temperature and volume, as in =CONVERT(50,"kg","lbm"). CONVERT does not handle currency, because exchange rates change: for that, put rates in a small lookup table and multiply against it, so one rate edit updates every converted row.
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 |
Name the rates first: select A2:B5, type RateTable in the Name Box to the left of the formula bar, and press Enter. 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, RateTable, 2, FALSE) / VLOOKUP(E2, RateTable, 2, FALSE)
With the source currency in D2 and the target in E2, it 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")
Thousands separators: 1234567.8 → 1,234,567.80
=TEXT(A2, "#,##0.00")
Formats 1234567.8 as “1,234,567.80”. For spelling a number out in words (“One Thousand Two Hundred”), VBA is the practical way.
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 Currencies data type (Microsoft 365) can pull rates online, but VBA with a web API is more reliable for automated rate updates.
Common questions
Can CONVERT handle currencies?
No. CONVERT works on physical units only, because exchange rates change daily and Excel has no built-in rate. Use a rate table you control and update.
Can Excel fetch live exchange rates?
On Microsoft 365, the Currencies data type in the Data tab pulls rates online. They refresh on demand, not continuously, so a stored rate table is still better for anything auditable such as an invoice.
Why does CONVERT return #N/A?
The unit code is wrong or the two units are not compatible. CONVERT will not convert weight into distance, and the codes are case sensitive.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.