Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.17 Real Estate: Units Sold and Price per Square Foot

By Last updated:

Example first. Zoya tracks fictional residential projects in five cities and wants sales progress and price per square foot.

Real Estate project — in 30 seconds

What: A Real Estate tracker: one Table tblUnits, the calculated column PricePerSqft, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Real Estate report: Units Sold %, Sold Value (₹ lakh), Average Price per sq ft are the numbers a manager reads first.

Tiny example: From 6 rows: Units Sold % = 66.7%, Sold Value (₹ lakh) = 261.4, Average Price per sq ft = ₹7,191.

First look at the data (before → after):

Real Estate project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

UnitID PricePerSqft
U-601 ₹9,388
U-602 ₹10,259
U-603 ₹6,500
U-604 ₹5,500
U-605 ₹5,500
U-606 ₹6,000

Expected result — KPI cells (expected results)

KPI Formula Result
Units Sold % =COUNTIF(tblUnits[Status],"Sold")/ROWS(tblUnits[Status]) 66.7%
Sold Value (₹ lakh) =SUMIFS(tblUnits[PriceLakh],tblUnits[Status],"Sold") 261.4
Average Price per sq ft =AVERAGE(tblUnits[PricePerSqft]) ₹7,191
Before table and formula as text

Before — Table tblUnits (fictional sample rows)

UnitID City Type AreaSqft PriceLakh Status
U-601 Pune 2BHK 980 92.0 Sold
U-602 Pune 3BHK 1,350 138.5 Unsold
U-603 Nashik 2BHK 900 58.5 Sold
U-604 Nagpur 1BHK 620 34.1 Sold
U-605 Kolhapur 2BHK 940 51.7 Unsold
U-606 Sambhaji Nagar 3BHK 1,280 76.8 Sold

Formula / click path

PricePerSqft: =[@PriceLakh]*100000/[@AreaSqft]
Units Sold %: =COUNTIF(tblUnits[Status],"Sold")/ROWS(tblUnits[Status])
Sold Value (₹ lakh): =SUMIFS(tblUnits[PriceLakh],tblUnits[Status],"Sold")
Average Price per sq ft: =AVERAGE(tblUnits[PricePerSqft])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblUnits in Table Design › Properties › Table Name.
  2. Add the column PricePerSqft with =[@PriceLakh]*100000/[@AreaSqft]; the Table fills it down.
  3. On a KPI sheet type =COUNTIF(tblUnits[Status],"Sold")/ROWS(tblUnits[Status]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and PriceLakh in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Status.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build an inventory sheet: price per sq ft per unit (shown without decimals), units sold %, sold value in ₹ lakh, average price per sq ft, value by city, a chart and a Status slicer.

Calculated column formulas:

Column Formula
PricePerSqft =[@PriceLakh]*100000/[@AreaSqft]

KPI cells (check your numbers):

KPI Formula Result
Units Sold % =COUNTIF(tblUnits[Status],"Sold")/ROWS(tblUnits[Status]) 66.7%
Sold Value (₹ lakh) =SUMIFS(tblUnits[PriceLakh],tblUnits[Status],"Sold") 261.4
Average Price per sq ft =AVERAGE(tblUnits[PricePerSqft]) ₹7,191

PivotTable (expected):

City Sum of PriceLakh
Kolhapur 51.7
Nagpur 34.1
Nashik 58.5
Pune 230.5
Sambhaji Nagar 76.8
Grand Total 451.6

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Status and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Real_Estate_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Real Estate dashboard guide and its workbook Real_Estate_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of PriceLakh?
  2. Filter the Table on City = Pune (Data › Filter): what is Units Sold % for Pune alone?
  3. Add the Clustered Column PivotChart and the Status slicer to your Dashboard sheet.
Answer

Pune (230.5). For Pune only (2 rows): Units Sold % = 50.0%. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Check Units Sold % by hand for two rows before you trust the dashboard. If your hand answer and the cell differ, the formula range is usually wrong.

Loading practice tasks...

  1. Total area sq ft (Easy): Add up Area sq ft in D2:D11.
  2. Average price per sq ft (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Tower 1 (Easy): How many rows have Tower "Tower 1"?
  4. Largest area sq ft (Easy): The largest value in D2:D11.
  5. Area sq ft for Tower 1 (Medium): Total of D2:D11 where B is "Tower 1".
  6. Tower 1 in Delhi (Medium): Total of D2:D11 where B is "Tower 1" and C is "Delhi".
  7. Best in Tower 1 (Medium): Largest E2:E11 value where B is "Tower 1".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Chloe Dubois (Medium): The Area sq ft for "Chloe Dubois" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Tower 1 (Hard): Share of D2:D11 that is "Tower 1", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →