How to Make a BMI Calculator in Excel With Categories

A BMI calculator in Excel takes two inputs, weight and height, and returns body mass index (BMI) with one formula: weight in kilograms divided by height in metres squared. Add ROUND to keep one decimal place and IFS to turn the number into a category such as Healthy weight or Overweight. This guide builds the calculator step by step, for anyone practising formulas or making a simple health tracker.

BMI is a screening number for adults. It does not measure body fat and it is not a diagnosis, so treat this sheet as a formula exercise, not medical advice.

What is the BMI formula?#

BMI is weight divided by the square of height. The metric version is:

BMI = weight (kg) / height (m)²

So a person who weighs 72 kg and is 1.75 m tall has a BMI of 72 / (1.75 × 1.75) = 72 / 3.0625 = 23.51.

There is also an imperial version for pounds and inches. Because the units are different, it multiplies by a conversion factor of 703:

BMI = 703 × weight (lb) / height (in)²

Both give the same answer for the same person, give or take a small rounding difference.

How to calculate BMI in Excel#

Put each input in its own cell and let the formula do the maths. This sheet uses weight in kilograms and height in centimetres, because that is how most people know their height:

In D2, type:

=B2/(C2/100)^2

Here is what each part does:

• C2/100 turns 175 cm into 1.75 m.
• ^2 squares it. The ^ key is the power operator; =POWER(C2/100,2) does the same thing.
• The brackets matter. Excel does powers before division, so without them it would square only the 100. Our guide to squares, square roots and powers in Excel explains the order.

The result is 23.5102.... Fill the formula down to D6 (Windows: Ctrl+D; Mac: ⌘D). If you are new to typing formulas, the first formulas lesson covers cell references and operators.

If your heights are already in metres, drop the /100: =B2/C2^2. For pounds and inches, use =703*B2/C2^2. For example, 160 lb and 67 in gives =703*160/67^2, which is about 25.06.

How to round BMI to one decimal place#

Wrap the formula in ROUND so the cell holds one decimal place, the way BMI is usually shown:

=ROUND(B2/(C2/100)^2,1)

The second argument, 1, is the number of decimal places. Filled down, the results are:

Why ROUND and not just the Decrease Decimal button? Formatting only changes what you see. The cell still holds 23.5102, and any formula that reads it uses the full value. ROUND changes the stored value. The ROUND function guide and the article on ROUND, ROUNDUP and ROUNDDOWN explain the difference.

How to add BMI categories with IFS#

IFS tests conditions in order and returns the result for the first one that is true. With the rounded BMI in D2, type this in E2:

=IFS(D2<18.5,"Underweight",D2<25,"Healthy weight",D2<30,"Overweight",TRUE,"Obesity")

These are the widely published adult cut-offs. The World Health Organization uses the same numbers, with slightly different names for some bands:

Order is the key. Excel stops at the first true test, so the checks go from the lowest cut-off to the highest. Ana's 23.5 fails D2<18.5, passes D2<25 and returns Healthy weight. Ben gets Overweight, Chloe Underweight, Dev Obesity and Ema Overweight. The final TRUE is a catch-all for everything 30 or above.

Base the category on the rounded BMI. A raw value of 24.96 shows as 25.0, so the label should say Overweight, not Healthy weight. Reading the rounded cell keeps the number and the label in agreement.

The IFS function guide and our IFS function article cover the syntax in more depth. Grouping ages into bands works the same way; see the age group formula.

BMI categories with nested IF (Excel 2016 and earlier)#

IFS needs Excel 2019, Excel 2021 or Microsoft 365. In older versions, nest IF functions so each one handles the next band:

=IF(D2<18.5,"Underweight",IF(D2<25,"Healthy weight",IF(D2<30,"Overweight","Obesity")))

It gives exactly the same results. The last IF needs no catch-all because its "false" part, "Obesity", already covers every remaining value. For more on choosing between the two, see how nested IF works. The nested IF and IFS lesson lets you practise both.

You can also use a small lookup table with the lower limit of each band (0, 18.5, 25, 30) and an approximate match. That keeps the cut-offs in cells, where they are easy to check and change, instead of inside a formula.

Make the calculator safer to use#

A few small changes stop the most common input mistakes:

1. Hide results for empty rows. Use =IF(OR(B2="",C2=""),"",ROUND(B2/(C2/100)^2,1)) so blank rows show nothing instead of #DIV/0!.
2. Check the unit. If someone types 1.75 in a centimetres column, BMI comes out in the hundreds of thousands. Add a data validation rule under Data > Data Validation that allows only decimals between, say, 100 and 250.
3. Label the units in the headers, such as "Height (cm)", so nobody has to guess.
4. Show a healthy weight range. The weights for a BMI of 18.5 to 24.9 are =ROUND(18.5*(C2/100)^2,1) and =ROUND(24.9*(C2/100)^2,1). For 175 cm that is 56.7 kg to 76.3 kg.

Limits of BMI#

BMI is quick to calculate, which is why it is popular, but it is a rough screening tool:

• It doesn't tell fat from muscle, so very muscular people can show as overweight.
• The adult categories above don't apply to children and teenagers. They are assessed with BMI-for-age charts.
• Some health bodies recommend lower cut-offs for some groups, for example people of some Asian backgrounds.
• It says nothing about where fat is stored, fitness or other health factors.

If a result worries you, talk to a doctor or other health professional rather than relying on a spreadsheet.

Practise it#

Try the exercise on this page: one formula that converts, squares, divides and rounds. Then practise writing formulas in the first formulas lesson, where the sheet checks each answer instantly, and practise IFS and nested IF in a live spreadsheet to build the category column yourself.