Energy Unit Converter in Excel
1 kilojoule = 0.9478171203 British thermal unit
Formula for this pair
These units are a straight multiple of each other. Put the starting value in cell A1.
=A1*0.9478171203
Once you have saved the function below, type this in a cell
=ConvertEnergy(1, "kJ", "Btu[IT]")
CONVERT
Paste this into a cell. Excel's CONVERT function knows both of these units.
=CONVERT(1, "kJ", "BTU")
How to save as a reusable Excel function
Microsoft 365. No VBA and no add-in.
- Open the function below and copy it. It has no call at the end.
- On the Formulas tab, choose Name Manager, then New.
- Set the name to ConvertEnergy. Leave the scope as Workbook.
- Paste the function into Refers to.
- In a cell, type the call shown above.
ConvertEnergy
Show the full function
=LAMBDA(value,from_unit,to_unit,LET(fromFactor,SWITCH(from_unit,"J",1,"1E6 Btu[IT]",1055055852.62,"Btu[IT]",1055.05585262,"Btu[UK]",1055.05585257348,"Btu[th]",1054.350264488889,"EJ",1000000000000000000,"Ecal[th]",4184000000000000000,"EeV",0.1602176487,"GJ",1000000000,"GW.h",3600000000000,"Gcal[th]",4184000000,"GeV",1.602176487E-10,"MJ",1000000,"MW.h",3600000000,"Mcal[th]",4184000,"MeV",1.602176487E-13,"TJ",1000000000000,"TW.h",3600000000000000,"Tcal[th]",4184000000000,"TeV",1.602176487E-7,"aJ",1E-18,"cJ",0.01,"cal[IT]",4.1868,"cal[th]",4.184,"ccal[th]",0.04184,"ceV",1.602176487E-21,"dJ",0.1,"dcal[th]",0.4184,"deV",1.602176487E-20,"eV",1.602176487E-19,"erg",1E-7,"fJ",1E-15,"fcal[th]",4.184E-15,"feV",1.602176487E-34,"hp.h",2684519.537696173,"hp[metric].h",2647795.5,"kJ",1000,"kW.h",3600000,"kcal[th]",4184,"keV",1.602176487E-16,"mJ",0.001,"mcal[th]",0.004184,"meV",1.602176487E-22,"nJ",1E-9,"ncal[th]",4.184E-9,"neV",1.602176487E-28,"pJ",1E-12,"pcal[th]",4.184E-12,"peV",1.602176487E-31,"quad",1055055852620000000,"therm[EC]",105506000,"therm[UK]",105505585.257348,"therm[US]",105480400,"uJ",0.000001,"ucal[th]",0.000004184,"ueV",1.602176487E-25),fromShift,SWITCH(from_unit,"J",0,"1E6 Btu[IT]",0,"Btu[IT]",0,"Btu[UK]",0,"Btu[th]",0,"EJ",0,"Ecal[th]",0,"EeV",0,"GJ",0,"GW.h",0,"Gcal[th]",0,"GeV",0,"MJ",0,"MW.h",0,"Mcal[th]",0,"MeV",0,"TJ",0,"TW.h",0,"Tcal[th]",0,"TeV",0,"aJ",0,"cJ",0,"cal[IT]",0,"cal[th]",0,"ccal[th]",0,"ceV",0,"dJ",0,"dcal[th]",0,"deV",0,"eV",0,"erg",0,"fJ",0,"fcal[th]",0,"feV",0,"hp.h",0,"hp[metric].h",0,"kJ",0,"kW.h",0,"kcal[th]",0,"keV",0,"mJ",0,"mcal[th]",0,"meV",0,"nJ",0,"ncal[th]",0,"neV",0,"pJ",0,"pcal[th]",0,"peV",0,"quad",0,"therm[EC]",0,"therm[UK]",0,"therm[US]",0,"uJ",0,"ucal[th]",0,"ueV",0),toFactor,SWITCH(to_unit,"J",1,"1E6 Btu[IT]",1055055852.62,"Btu[IT]",1055.05585262,"Btu[UK]",1055.05585257348,"Btu[th]",1054.350264488889,"EJ",1000000000000000000,"Ecal[th]",4184000000000000000,"EeV",0.1602176487,"GJ",1000000000,"GW.h",3600000000000,"Gcal[th]",4184000000,"GeV",1.602176487E-10,"MJ",1000000,"MW.h",3600000000,"Mcal[th]",4184000,"MeV",1.602176487E-13,"TJ",1000000000000,"TW.h",3600000000000000,"Tcal[th]",4184000000000,"TeV",1.602176487E-7,"aJ",1E-18,"cJ",0.01,"cal[IT]",4.1868,"cal[th]",4.184,"ccal[th]",0.04184,"ceV",1.602176487E-21,"dJ",0.1,"dcal[th]",0.4184,"deV",1.602176487E-20,"eV",1.602176487E-19,"erg",1E-7,"fJ",1E-15,"fcal[th]",4.184E-15,"feV",1.602176487E-34,"hp.h",2684519.537696173,"hp[metric].h",2647795.5,"kJ",1000,"kW.h",3600000,"kcal[th]",4184,"keV",1.602176487E-16,"mJ",0.001,"mcal[th]",0.004184,"meV",1.602176487E-22,"nJ",1E-9,"ncal[th]",4.184E-9,"neV",1.602176487E-28,"pJ",1E-12,"pcal[th]",4.184E-12,"peV",1.602176487E-31,"quad",1055055852620000000,"therm[EC]",105506000,"therm[UK]",105505585.257348,"therm[US]",105480400,"uJ",0.000001,"ucal[th]",0.000004184,"ueV",1.602176487E-25),toShift,SWITCH(to_unit,"J",0,"1E6 Btu[IT]",0,"Btu[IT]",0,"Btu[UK]",0,"Btu[th]",0,"EJ",0,"Ecal[th]",0,"EeV",0,"GJ",0,"GW.h",0,"Gcal[th]",0,"GeV",0,"MJ",0,"MW.h",0,"Mcal[th]",0,"MeV",0,"TJ",0,"TW.h",0,"Tcal[th]",0,"TeV",0,"aJ",0,"cJ",0,"cal[IT]",0,"cal[th]",0,"ccal[th]",0,"ceV",0,"dJ",0,"dcal[th]",0,"deV",0,"eV",0,"erg",0,"fJ",0,"fcal[th]",0,"feV",0,"hp.h",0,"hp[metric].h",0,"kJ",0,"kW.h",0,"kcal[th]",0,"keV",0,"mJ",0,"mcal[th]",0,"meV",0,"nJ",0,"ncal[th]",0,"neV",0,"pJ",0,"pcal[th]",0,"peV",0,"quad",0,"therm[EC]",0,"therm[UK]",0,"therm[US]",0,"uJ",0,"ucal[th]",0,"ueV",0),(value*fromFactor+fromShift-toShift)/toFactor))
Conversion factors
The factors this converter uses. Copy them into Excel as tab-separated values.
| Symbol | Name | Factor | Excel code |
|---|---|---|---|
| J | joule | 1 | J |
| 1E6 Btu[IT] | million BTU | 1055055852.62 | — |
| Btu[IT] | British thermal unit | 1055.05585262 | BTU |
| Btu[UK] | United Kingdom British thermal unit | 1055.05585257348 | — |
| Btu[th] | thermochemical British thermal unit | 1054.350264488889 | — |
| EJ | exajoule | 1000000000000000000 | EJ |
| Ecal[th] | million million million calorie | 4184000000000000000 | Ec |
| EeV | exaelectronvolt | 0.1602176487 | — |
| GJ | gigajoule | 1000000000 | GJ |
| GW.h | gigawatt hour | 3600000000000 | GWh |
| Gcal[th] | thousand million calorie | 4184000000 | Gc |
| GeV | gigaelectronvolt | 1.602176487E-10 | — |
| MJ | megajoule | 1000000 | MJ |
| MW.h | megawatt hour | 3600000000 | MWh |
| Mcal[th] | million calorie | 4184000 | Mc |
| MeV | megaelectronvolt | 1.602176487E-13 | — |
| TJ | terajoule | 1000000000000 | TJ |
| TW.h | terrawatt hour | 3600000000000000 | TWh |
| Tcal[th] | million million calorie | 4184000000000 | Tc |
| TeV | teraelectronvolt | 1.602176487E-7 | — |
| aJ | attojoule | 1E-18 | aJ |
| cJ | centijoule | 0.01 | cJ |
| cal[IT] | calorie [International Table] | 4.1868 | cal |
| cal[th] | calorie | 4.184 | c |
| ccal[th] | hundredth of calorie | 0.04184 | cc |
| ceV | centielectronvolt | 1.602176487E-21 | — |
| dJ | decijoule | 0.1 | dJ |
| dcal[th] | tenth of calorie | 0.4184 | dc |
| deV | decielectronvolt | 1.602176487E-20 | — |
| eV | electronvolt | 1.602176487E-19 | — |
| erg | erg | 1E-7 | e |
| fJ | femtojoule | 1E-15 | fJ |
| fcal[th] | femtocalorie | 4.184E-15 | fc |
| feV | femtoelectronvolt | 1.602176487E-34 | — |
| hp.h | horsepower hour | 2684519.537696173 | HPh |
| hp[metric].h | metric-horsepower hour | 2647795.5 | — |
| kJ | kilojoule | 1000 | kJ |
| kW.h | kilowatt hour | 3600000 | kWh |
| kcal[th] | thousand calorie | 4184 | kc |
| keV | kiloelectronvolt | 1.602176487E-16 | — |
| mJ | millijoule | 0.001 | mJ |
| mcal[th] | thousandth of calorie | 0.004184 | mc |
| meV | millielectronvolt | 1.602176487E-22 | — |
| nJ | nanojoule | 1E-9 | nJ |
| ncal[th] | nanocalorie | 4.184E-9 | nc |
| neV | nanoelectronvolt | 1.602176487E-28 | — |
| pJ | picojoule | 1E-12 | pJ |
| pcal[th] | picocalorie | 4.184E-12 | pc |
| peV | picoelectronvolt | 1.602176487E-31 | — |
| quad | quad | 1055055852620000000 | — |
| therm[EC] | European Community therm | 105506000 | — |
| therm[UK] | United Kingdom therm | 105505585.257348 | — |
| therm[US] | United States therm | 105480400 | — |
| uJ | microjoule | 0.000001 | uJ |
| ucal[th] | millionth of calorie | 0.000004184 | uc |
| ueV | microelectronvolt | 1.602176487E-25 | — |
Notes
Microsoft's c is the thermodynamic calorie. In this catalogue that is cal[th], 4.184 joules. Microsoft's cal is the IT calorie, cal[IT], 4.1868 joules. A prefix sticks to that code, so kcal[th] is kc, not kcal. Electronvolts are left unmapped, because this catalogue's constant may not match Excel's. Horsepower-hour (hp.h) uses HPh.
Google Sheets
Google Sheets has CONVERT and many of the same codes, and it has LAMBDA. Named functions are created under Data > Named functions, not Formulas > Name Manager. A code that works in Excel can return #N/A in Sheets, so try a known value before you rely on it.