# 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:

- Create the following column headers on an Excel worksheet:
**Apartment Area****Cost****Price**/**sqft**

- 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.

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

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

`=B5/A5`

- 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.

- Hit the
`Enter`key and you get the price per square foot. - 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:

- Populate the property area and cost data according to the image shown above.
- 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)`

- 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:

- Choose a data set within your Excel sheet; click
**Data**and then choose**From Table**/**Range**inside the**Get & Transform Data**block. - 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. - Whether you choose local data on the existing Excel worksheet or import data from an online location, the
**Power Query**editor will open.

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

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

`=[Price]/[Square Footage]`

- Click
**OK**to close the column editor in Power Query.

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

- 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:

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

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

- There, click
**Insert**and then choose**Module**to add a new one. - 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
```

- 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. - 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. - Remember, the script will be dividing price values by property area values.
- Click the
**Save**button and close the**VBA Editor**.

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

- 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:

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

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

- 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);
}
```

- Click
**Save Script**and then hit the**Run**button. - 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!