Calculate Hardness of Water using Excel Spreadsheet: Geochemistry Tips

Hard_water_and_drop Hard_water_and_drop


July 11, 2011 (Coal Geology) Hard water is water referes to the alkaline earth ion concentrations. The primary alkaline earth ions are Ca2+ and Mg2+. Calcium usually dominates over magnesium concentration.

Some important Points:

  • Hard water is generally not harmful to one’s health
  • pose serious problems in industrial settings, where water hardness is monitored to avoid costly breakdowns in boilers, cooling towers, and other equipment that handles water. In boilers, the deposits impair the flow of heat into water, reducing the heating efficiency and allowing the metal boiler components to overheat. In a pressurized system, this overheating can lead to failure of the boiler.
  • Chelation of organics within soap by the alkaline ions is the main reason of concern for hard water. When soap is chelated, it fails to properly react with the water.
  • Hard water also forms deposits that clog plumbing. These deposits, called “scale”, are composed mainly of calcium carbonate (CaCO3), magnesium hydroxide (Mg(OH)2), and calcium sulfate (CaSO4).[
  • Calcium and magnesium carbonates tend to be deposited as off-white solids on the surfaces of pipes and the surfaces of heat exchangers. This precipitation (formation of an insoluble solid) is principally caused by thermal decomposition of bi-carbonate ions but also happens to some extent even in the absence of such ions. The resulting build-up of scale restricts the flow of water in pipes.

Measurement Unit: "mg/L as of CaCO3"

How to convern both Ca+2 and Mg+2 concentration to "mg/L as of CaCO3" unit?

Formula for Hardness = {x(Mol. Wt. of CaCO3/Mol Wt. of Ca+2)}  + { x(Mol. Wt. of CaCO3/Mol Wt. of Mg+2)
= x(100.08/40.08)    +    x(100.08/24.31]

Use the attached excel spreadsheet to  Calculate hardness of water.

Hardness of Water Calculation Spreadsheet -CoalGeology.Com

Hardness Scale (mg/L of CaCO3):
0-60 – Soft Water
61-120 Moderately Hard Water
121-180 Hard Water
>180    Very Hard Water

Hard water is commonly associated with limestone/dolomite rocks such as in Florida.

Hardness in water is defined as concentration of multivalent cations. Multivalent cations are cations (positively charged metal complexes) with a charge greater than 1+. They mainly have the charge of 2+. These cations include Ca2+ and Mg2+. These ions enter a water supply by leaching from minerals within an aquifer. Common calcium-containing minerals are calcite and gypsum. A common magnesium mineral is dolomite (which also contains calcium). Rainwater and distilled water are soft, because they also contain few ions.

The following equilibrium reaction describes the dissolving/formation of calcium carbonate scales:

CaCO3 + CO2 + H2O ? Ca2+ + 2HCO3-


About Editor
Ankan Basu is a Certified Professional Geologist (CPG) with 10+ years of experience in the field of geology, hydrogeology and geochemistry.

1 Comment on Calculate Hardness of Water using Excel Spreadsheet: Geochemistry Tips

  1. Hard water may not be unhealthy but it makes water hard to suds, leaves skin feeling dry and flaky. However, removing hardness through water softening generally add salts which is not good for sodium-restricted diets. So there are tradeoffs either way.

Leave a comment

Your email address will not be published.