Cellwise Start free

Excel guide · 3 min read

How to calculate sales per square foot in Excel

Quick answerSales per square foot = sales for a period / selling area in square feet. In Excel, with yearly sales in D2 and selling area in B2: =D2/B2. The Downtown store sold $760,000 on 2,400 sq ft of selling space, about $316.67 per square foot.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHIJKL
1StoreSelling sq ftTotal sq ftSales (year)Sales per selling sq ftQuarters openStoreQuarterSalesSetting
2Downtown24003100760000316.666666674DowntownQ1168000Downtown shelf (linear ft)
3Mall38004600970000255.263157894DowntownQ2175000
4Outlet6000750047000078.3333333332DowntownQ3181000
5DowntownQ4236000
6MallQ1214000
7MallQ2221000
8MallQ3230000
9MallQ4305000
10OutletQ3196000
11OutletQ4274000

Download this sheet as an Excel practice file (.xlsx)

Step 1: Yearly sales per store with SUMIFS

H:J is the sales log by store and quarter. Your log is probably monthly; the formula is the same, the example is just shorter. In D2, copied down: =SUMIFS($J$2:$J$11,$H$2:$H$11,A2). Downtown $760,000, Mall $970,000, Outlet $470,000.

Use net sales after returns, and leave out sales tax. Online orders picked up in store are a judgement call: include them or not, but do it the same way for every store.

=SUMIFS($J$2:$J$11,$H$2:$H$11,A2)

Result: 760000 shows as $760,000 with Currency format

760000: Downtown's sales for the year.

Step 2: Sales per selling square foot

Divide sales by selling area: =D2/B2 in E2. Downtown $316.67, Mall $255.26.

Selling area is the floor where customers shop. Total area adds the stockroom, offices and staff areas. Both are used, so label which one you mean. On total area Downtown drops to =D2/C2, about $245.16, and the Mall to about $210.87.

Selling area is better for judging how hard the floor works. Total area is closer to what you pay rent on. If you compare against figures someone else publishes, find out which area they used first.

=D2/B2

Result: 316.66666667 shows as $316.67 with Currency format

About 316.67: Downtown's yearly sales per square foot of selling space.

Comparing stores of different sizes

The Mall sells more in total but less per square foot than Downtown. That's common: a bigger floor rarely fills with proportionally more shoppers. Sales per square foot lets you compare them on the same scale, which totals can't.

It doesn't say which store is more profitable. Rent per square foot, staffing and margin can all differ. Put rent per square foot next to it for a fairer picture.

A store open for part of the year

The Outlet opened in July. Its full-year figure, =D4/B4, is $78.33, but that's six months of sales over a full store. Comparing it with Downtown's year is unfair.

The simple fix is to annualize: divide by the quarters open (F4 counts them with =COUNTIFS($H$2:$H$11,A4)) and multiply by 4. =D4/F4*4/B4 gives about $156.67. The catch: the Outlet's six months include the holiday quarter, so doubling them overstates a normal year.

A fairer test is the same months for everyone. Downtown's second half: =(SUMIFS($J$2:$J$11,$H$2:$H$11,A2,$I$2:$I$11,"Q3")+SUMIFS($J$2:$J$11,$H$2:$H$11,A2,$I$2:$I$11,"Q4"))/B2, about $173.75, against the Outlet's $78.33 for the same two quarters. If you want a full-year estimate, scale by an older store's seasonal share instead of doubling: Downtown made 417 of its 760 thousand in Q3 and Q4, which puts the Outlet at about $142.77.

=(SUMIFS($J$2:$J$11,$H$2:$H$11,A2,$I$2:$I$11,"Q3")+SUMIFS($J$2:$J$11,$H$2:$H$11,A2,$I$2:$I$11,"Q4"))/B2

Result: 173.75 shows as $173.75 with Currency format

About 173.75: Downtown's July to December sales per selling square foot.

Sales per linear foot of shelf

For a single department or a grocery aisle, floor space is too coarse. Measure shelf length instead and divide by that. With Downtown's 420 linear feet of shelving in M2: =D2/M2, about $1,809.52 per linear foot.

Use it to compare categories competing for the same shelving, such as which brands earn their space. Measure shelf the same way everywhere (one shelf level or every level), or the figures won't compare.

=D2/M2

Result: 1809.5238095 shows as $1,809.52 with Currency format

About 1809.52: Downtown's yearly sales per linear foot of shelf.

Common mistakes

Mixing selling and total area across stores

Use one kind of area for every store, and say which in the header.

Comparing a part-year store with full-year stores

Compare the same months, or annualize with a seasonal share rather than a straight multiple.

Including sales tax

Use net sales before tax, after returns.

Treating it as a profit measure

High sales per square foot with high rent can still lose money. Look at it next to rent and margin.

FAQ

What is the formula for sales per square foot?

Sales for the period divided by the store's selling area in square feet. In Excel: =D2/B2. Use a full year of sales unless you state a different period.

Should I use gross or net square footage?

Selling area (where customers shop) shows how well the floor sells. Total area is closer to what you rent. Use one consistently and label it.

How do I calculate sales per square foot for a new store?

Compare the same months across stores, or annualize its sales. If it opened before a busy season, scale by an existing store's seasonal share rather than doubling.

How do I calculate sales per square foot by department?

Add a department column to the sales log and use SUMIFS with both store and department, then divide by each department's floor area.

Functions used

Related guides

Updated