How to calculate price per square foot in Excel

Price per square foot for each listing

You're house hunting, or helping your parents compare places to downsize to, and the listings are all different sizes. Price per square foot puts them on the same scale. Put the price in C, the area in D, and =C2/D2 in E, then fill down. Format column E as currency.

If some listings don't show the area yet, the division gives #DIV/0!. =IF(D2="","",C2/D2) leaves the cell blank until you type the size.

Use the same kind of area for every row. Listings may quote finished living area, total area including a basement, or the lot. Mixing them makes the comparison meaningless.

Average per neighborhood: two answers, pick the right one

=AVERAGEIF(B2:B11,"Riverside",E2:E11) averages the four Riverside ratios and gives $319.43. That's the typical listing's price per square foot.

=SUMIF(B2:B11,"Riverside",C2:C11)/SUMIF(B2:B11,"Riverside",D2:D11) divides total price by total area and gives $326.28. That weights each home by its size, so big homes count more. Appraisers and market reports often use this pooled version.

Neither is wrong, but they differ, so say which one you used. In Northgate the gap goes the other way ($270.11 averaged vs $264.23 pooled) because the small Dogwood Ct home has the highest price per square foot.

Use the median when one listing is unusual

102 Grove Pl is a waterfront home at $480.77 per square foot. It drags the Riverside average up to $319.43, but the other three are all between $258 and $277. The median takes the middle value instead, so one outlier barely moves it: $269.24.

MEDIAN(IF()) keeps only the rows for one neighborhood and takes their median. It works as a normal formula in Excel 365; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. Without the waterfront home, the Riverside average would be $265.65, much closer to the median.

If the median and the average are far apart, look at what's pulling them. Sometimes it's a view or a big lot, sometimes a typo in the square footage.

Price per square metre

One square metre is about 10.7639 square feet. To get price per square metre, divide the price by the area in square metres: square feet / 10.7639. The factor sits in H2, so the formula is =C2/(D2/$H$2).

The shortcut is to multiply price per square foot by 10.7639. Going the other way, from a listing quoted in m², multiply the area by 10.7639 to get square feet.

Rent per square foot: per year or per month?

Commercial space in the US is usually quoted per square foot per year. A 1,200 sq ft shop at $1,800 a month is $1,800 x 12 / 1,200 = $18 per sq ft per year, which is $1.50 per month. Residential listings are more often compared per month. Always check which one a quote means before you compare.

Comparing two offers works the same way. A second space at $2,350 a month for 1,650 sq ft is $17.09 per sq ft per year: more rent overall, but cheaper per square foot. Whether the extra room is worth it is your call, but now it's a fair comparison.