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.

  1. Open the function below and copy it. It has no call at the end.
  2. On the Formulas tab, choose Name Manager, then New.
  3. Set the name to ConvertEnergy. Leave the scope as Workbook.
  4. Paste the function into Refers to.
  5. 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 FactorExcel code
J joule 1J
1E6 Btu[IT] million BTU 1055055852.62—
Btu[IT] British thermal unit 1055.05585262BTU
Btu[UK] United Kingdom British thermal unit 1055.05585257348—
Btu[th] thermochemical British thermal unit 1054.350264488889—
EJ exajoule 1000000000000000000EJ
Ecal[th] million million million calorie 4184000000000000000Ec
EeV exaelectronvolt 0.1602176487—
GJ gigajoule 1000000000GJ
GW.h gigawatt hour 3600000000000GWh
Gcal[th] thousand million calorie 4184000000Gc
GeV gigaelectronvolt 1.602176487E-10—
MJ megajoule 1000000MJ
MW.h megawatt hour 3600000000MWh
Mcal[th] million calorie 4184000Mc
MeV megaelectronvolt 1.602176487E-13—
TJ terajoule 1000000000000TJ
TW.h terrawatt hour 3600000000000000TWh
Tcal[th] million million calorie 4184000000000Tc
TeV teraelectronvolt 1.602176487E-7—
aJ attojoule 1E-18aJ
cJ centijoule 0.01cJ
cal[IT] calorie [International Table] 4.1868cal
cal[th] calorie 4.184c
ccal[th] hundredth of calorie 0.04184cc
ceV centielectronvolt 1.602176487E-21—
dJ decijoule 0.1dJ
dcal[th] tenth of calorie 0.4184dc
deV decielectronvolt 1.602176487E-20—
eV electronvolt 1.602176487E-19—
erg erg 1E-7e
fJ femtojoule 1E-15fJ
fcal[th] femtocalorie 4.184E-15fc
feV femtoelectronvolt 1.602176487E-34—
hp.h horsepower hour 2684519.537696173HPh
hp[metric].h metric-horsepower hour 2647795.5—
kJ kilojoule 1000kJ
kW.h kilowatt hour 3600000kWh
kcal[th] thousand calorie 4184kc
keV kiloelectronvolt 1.602176487E-16—
mJ millijoule 0.001mJ
mcal[th] thousandth of calorie 0.004184mc
meV millielectronvolt 1.602176487E-22—
nJ nanojoule 1E-9nJ
ncal[th] nanocalorie 4.184E-9nc
neV nanoelectronvolt 1.602176487E-28—
pJ picojoule 1E-12pJ
pcal[th] picocalorie 4.184E-12pc
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.000001uJ
ucal[th] millionth of calorie 0.000004184uc
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.

Related