Buffer preparation calculator (Henderson-Hasselbalch)

Acid and base form masses for a buffer at a target pH, from pKa values, with a titration route.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C
1	Buffer preparation calculator (Henderson-Hasselbalch)
2	Example data — replace the blue input cells with your own.
3
4	BASE : ACID RATIO	GRAMS OF ACID FORM (G)	GRAMS OF BASE FORM (G)
5	1.585	4.64	8.70
6
7	Buffer inputs
8	Buffer name	Phosphate buffer, pH 7.40	Label only
9	Buffer system	Phosphate (pKa2)	Type a name from the Systems tab, column A
10	Target pH	7.40
11	Total buffer concentration (mol/L)	0.100	Acid form plus base form, after dilution
12	Final volume (mL)	1,000
13	Temperature (°C)	25	The pKa values are for 25 °C
14	Acid form purity	100.0%	Fraction of the weighed solid that is the acid form
15	Base form purity	100.0%	Fraction of the weighed solid that is the base form
16

Showing the first 16 of 49 rows and 3 of 3 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H
1	Buffer systems: pKa and molar masses of the acid and base forms
2	Typical literature pKa values at 25 °C and molar masses of the anhydrous forms, in g/mol.
3
4	System	pKa (25 °C)	Acid form	Acid form molar mass (g/mol)	Base form	Base form molar mass (g/mol)	Useful pH from (pKa - 1)	Useful pH to (pKa + 1)
5	Acetate	4.76	Acetic acid	60.05	Sodium acetate (anhydrous)	82.03	3.76	5.76
6	Phosphate (pKa2)	7.20	Sodium dihydrogen phosphate (anhydrous)	119.98	Disodium hydrogen phosphate (anhydrous)	141.96	6.20	8.20
7	Tris	8.07	Tris hydrochloride	157.60	Tris base	121.14	7.07	9.07
8	Ammonium	9.25	Ammonium chloride	53.49	Ammonia	17.03	8.25	10.25
9	Carbonate (pKa2)	10.33	Sodium bicarbonate	84.01	Sodium carbonate (anhydrous)	105.99	9.33	11.33
10	Formate	3.75	Formic acid	46.03	Sodium formate	68.01	2.75	4.75
11
12	pKa values are typical literature values at 25 °C. They shift with temperature and ionic strength.
13	Tris pKa falls as temperature rises (about -0.028 per °C). The other values vary less.
14	Pick a system by its name in column A of this table. The Buffer tab reads the row with an exact lookup.

Showing the first 14 of 14 rows and 8 of 8 columns. Cells with formulas show the formula on hover.

	A
1	Buffer preparation calculator (Henderson-Hasselbalch)
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Calculates how much of the acid form and the base form to weigh for a buffer at a target pH, from a named buffer system and a total concentration.
6	Also shows how to make the same buffer by titrating the acid form with a strong base.
7
8	How to use it
9	1. On the Buffer tab, type a system name from the Systems tab, for example Phosphate (pKa2).
10	2. Enter the target pH, total buffer concentration, final volume and temperature.
11	3. Enter the purity of the acid and base solids if they are below 100%.
12	4. Read the base:acid ratio and the grams of each form in the tiles and in the calculated block.
13	5. For titration, enter the NaOH stock concentration and read the NaOH volume in block 3.
14
15	Formulas and method
16	Henderson-Hasselbalch: pH = pKa + log10(base / acid). Ratio = base / acid = 10^(pH - pKa), computed with POWER(10, pH - pKa).

Showing the first 16 of 33 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

This workbook prepares a buffer at a target pH using the Henderson-Hasselbalch equation. Choose a buffer system from the Systems table, then enter the target pH, the total buffer concentration and the final volume. The sheet computes the base to acid ratio as 10 raised to the power of pH minus pKa, the fraction of each form, and the grams of acid form and base form to weigh.

A second block shows how to make the same buffer by titrating the acid form with a strong base. It gives the moles of sodium hydroxide needed, which equal the moles of base form, and the volume of the NaOH stock. The Systems tab lists six buffer systems with pKa values at 25 °C and molar masses of the anhydrous acid and base forms.

A warning appears when the target pH is more than one pH unit from the pKa. The example is a 0.100 M phosphate buffer at pH 7.40 in 1000 mL, using the second pKa of phosphate. Real pKa values shift with temperature and ionic strength, so confirm the final pH with a calibrated meter.

What’s inside

Base to acid ratio from POWER(10, pH minus pKa), with the fraction of each form
Systems table of six buffers with pKa values at 25 °C and molar masses
Grams of each form from moles, molar mass and purity
Warning when the target pH is more than one unit from the pKa
Titration block gives the moles and volume of strong base to add

Which tabs does the workbook have?

Tab	What it holds
Buffer	Buffer inputs, base to acid ratio, grams of each form, pH check and the titration route.
Systems	pKa at 25 °C and molar masses of the acid and base forms for six buffer systems.
Notes	What the workbook does, the method, assumptions and limits.

What formulas does this template use?

This template holds 41 formulas in 198 cells across 3 tabs, so 21% of its cells calculate. They use 8 distinct functions; the longest formula is 128 characters and 6 of them read from another tab.

Function	Uses	What it does
`IF`	36	one result when a test is true, another when false
`ISNUMBER`	30	true for a number
`AND`	8	true when every test is true
`VLOOKUP`	6	looks a value up in a column
`ABS`	3	absolute value
`IFERROR`	3	swaps an error for a fallback value
`OR`	1	true when any test is true
`POWER`	1	raises to a power

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On the Buffer tab, type a system name from column A of the Systems tab.
Enter the target pH, total buffer concentration, final volume and temperature.
Enter the purity of the acid and base solids if either is below 100%.
Read the base to acid ratio and the grams of each form in the tiles and the calculated block.
For titration, enter the NaOH stock concentration and read the volume to add.

What is it good for?

Weighing phosphate salts for a pH 7.4 buffer
Preparing a Tris buffer for gel or protein work
Checking a published buffer recipe against the equation
Teaching the Henderson-Hasselbalch equation with worked numbers

Questions about this sheet

Why does the buffer warning appear?

The target pH is more than one pH unit from the pKa of the chosen system. Outside that range a buffer has little capacity, so choose a different system.

Are the pKa values exact at my temperature?

They are typical literature values at 25 °C. Tris pKa falls as temperature rises, and the temperature check flags any other temperature.

Why are the molar masses for the anhydrous salts?

The Systems tab lists the anhydrous forms. For a hydrated salt, edit the molar mass in the Systems table to match the reagent you weigh.