How to Calculate Price Per Square Foot in Excel: 5 Easy Ways

Are you tired of manually calculating the price per square foot of properties, products, or services? Do you want to streamline your data analysis process and make informed decisions? Today I show you how to calculate the price per square foot in Excel using various methods!

Excel is a powerful calculator tool that can help you perform complex calculations and manipulate data with ease and visualize them as well. One great use of it is to calculate the price per square foot in real estate, construction, retail, etc., sector to find costs of lands, houses, home decor services, and more.

There are different ways to get to the cost per square foot. Depending on your situation take your pick from below.

Reasons to To Calculate Price Per Square Foot in Excel

The primary reason is to know how much you pay or earn when the unit of service or transaction is per square foot. Here are some examples where this calculation will help you:

• You’re a real estate agent and you set the price to sell, buy, or invest in real estate projects by evaluating the cost per square foot data.
• Being a construction project lead, engineer, or homeowner, you can find out a lump sum money needed to build the property by finding out the price per square foot.
• In the retail sector, the price per square foot calculation helps you to decide the profitability of your retail shop and how much you should charge for product promotion or advertising from a brand.
• By knowing the exact price per square foot of your rental properties, you can list an appropriate rent when advertising so that you can make a profit after paying for taxes, maintenance, ad-hoc repairs, etc.

Calculate Price Per Square Foot Using Division

One of the easiest ways to calculate the price per square foot in Excel is using the mathematical operator Division. If you got the total amount of real estate and its total cost, then simply divide the cost by area to get the price per square foot. Here’s how it’s done:

1. Create the following column headers on an Excel worksheet:
   • Apartment Area
   • Cost
   • Price/sqft

2. Select cells below the column B and C headers and choose Accounting from the Number drop-down list in the Home tab on the Excel ribbon menu. Doing so will add common formatting like 1000 separators, decimal digits, etc.

3. Now, enter the respective data under their relevant column headers except for the Price/sqft column because here you’ll calculate the value.

4. Now, copy and paste the following formula into the first cell under the Price/sqft column:

=B5/A5

5. In the above formula, B5 represents the total price of a real estate and A5 represents the area of it. Adjust the formula according to your own Excel worksheet data.

6. Hit the Enter key and you get the price per square foot.
7. Copy the formula in other cells nearer to the Apartment Area and Cost columns to get their Price/sqft values.

Calculate Price Per Square Foot Using SUMPRODUCT

Got a large list of properties for which you need a more efficient way to compute the per square foot price of real estate or other services that are calculated in square foot units? You can try the SUMPRODUCT formula in Excel. Here’s how it works:

1. Populate the property area and cost data according to the image shown above.
2. Now, copy and paste the following formula inside the first cell under the Square Footage (price/sqft) column and hit Enter.

=SUMPRODUCT(B10:B12,1/A10:A12)

3. This should immediately fetch the price per square foot of the selected property area.

The SUMPRODUCT function multiplies each value in the Price column by the corresponding value in the 1/Square Footage column (which is the reciprocal of the square footage for each property). Then, it adds up the results. This gives you the total cost per square foot for all the properties on your list.

Calculate Price Per Square Foot in Power Query

If you’re importing a large dataset of properties and prices, then you can apply the price per square foot formula in the Power Query tool. You can do this when cleaning and organizing the imported real estate data for further analysis.

In Power Query, you can create a custom column for the Price/sqft and apply a division formula to get the necessary unit price values. Thus, you directly get the price per square foot values in the destination Excel worksheet. Here are the steps you can follow:

1. Choose a data set within your Excel sheet; click Data and then choose From Table/Range inside the Get & Transform Data block.
2. Alternatively, you can click Get Data in the above command block and choose from many options like From File (From TSV, From CSV, From JSON, etc.), From Database, From Azure, and so on.
3. Whether you choose local data on the existing Excel worksheet or import data from an online location, the Power Query editor will open.

4. Now, click Add Column on the Power Query toolbar and then hit the Custom Column button.

5. On the Custom Column dialog box, enter a column name like Price/sqft.
6. Then, Inside the Custom column formula box, copy and paste the following code:

=[Price]/[Square Footage]

7. Click OK to close the column editor in Power Query.

8. You should already see the new Price/sqft column inside the Power Query editor along with the values for individual properties.
9. Now, simply click the File tab on the Power Query toolbar and select Close & Load.

10. This will create a new worksheet inside the destination Excel sheet and add all three columns as an Excel table.

Calculate Price Per Square Foot Using a VBA Macro

When analyzing property square footage and pricing data in bulk, it could be time-consuming if you manually create a division or SUMPRODUCT formula to get the price per square foot data. Instead, you can use the following VBA script with minor adjustments. Here’s how to use the VBA script on your Excel worksheet:

1. Go to the Excel worksheet where you’ve got property area and pricing data.
2. Click File on the ribbon and then select Save As.

3. Click Browse and then select Save as type drop-down list.
4. There, click on the Excel Macro-Enabled Workbook option.
5. Click Save to close the Save As dialog box.
6. Now, hit Alt + F11 to open the VBA Editor.

7. There, click Insert and then choose Module to add a new one.
8. On the new Module, copy and paste the following VBA script:

Sub PricePerSqFt()
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row 'find the last row with data
    
    Dim i As Long
    For i = 2 To lastRow 'loop through each row of data
        If Range("A" & i).Value <> "" And Range("B" & i).Value <> "" Then 'only calculate if both area and price values exist
            Range("C" & i).Value = Range("B" & i).Value / Range("A" & i).Value 'calculate price per square foot
            Range("C" & i).NumberFormat = "$#,##0.00" 'format as currency
        End If
    Next i
End Sub

9. In the above script, Range A is for the property area, Range B is for the price of the respective property, and Range C is the destination of Price/sqft values. So, if your property area and pricing data are in different columns, you need to change the ranges in the above script.
10. For instance, if your property area values are in column D, pricing data are in column E, and you need the Price/sqft in column F, change A to D, B to E, and C to F everywhere in the above script.
11. Remember, the script will be dividing price values by property area values.
12. Click the Save button and close the VBA Editor.

13. Now, hit Alt + F8 to bring up the Macro dialog box.
14. There, select the PricePerSqFt macro and hit Run.

15. Excel will populate the designated column with Price/sqft values.

Calculate Price Per Square Foot Using Office Scripts

If you’re using the latest Excel 365 desktop app or accessing the Excel web app using your Microsoft 365 subscription, you can automate the whole process using a simple Office Script code. Here’s how it works:

1. Open your property area and prices table on an Excel worksheet either online or on Excel 365 desktop app.
2. Let’s consider, the property area values are in column A and prices are in column B.

3. Now, click Automate on the ribbon and select New Script in the Scripting Tools block.

4. Copy and paste the following script inside the Code Editor on the right-side navigation pane.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=B2/A2");
	// Paste to range C3:C10 on selectedSheet from range C2 on selectedSheet
	selectedSheet.getRange("C3:C10").copyFrom(selectedSheet.getRange("C2"), ExcelScript.RangeCopyType.all, false, false);
}

5. Click Save Script and then hit the Run button.
6. In the cell range C2:C10, you should see the price per square foot values.

If the property area and prices data are in different columns, say column D and E, modify the setFormulaLocal("=B2/A2") code element to setFormulaLocal("=D2/E2"). Also, don’t forget to change all the occurrances of column C in the above code to the destination column like column F.

Conclusions

Knowing how to compute the price per square foot in Excel is indispensable if you work as a data analyst in real estate and related sectors. Also, the skill will help you as an individual purchasing a home, or apartment, or hiring a carpenter to fix wooden fixtures.

The article explained five different ways to calculate price per square foot. The SUMPRODUCT and division method is good for small datasets. Contrarily, if your dataset is enormous, you can go for the VBA, Power Query, and Office Scripts-based methods. Don’t forget to write down your experience in the comment box below when trying out these Excel tips!