🛒
What You're BuildingA complete retail sales analysis sheet for FreshMart's March data — featuring automatic total calculations via ARRAYFORMULA, business metrics in a summary panel, order classification using nested IF, professional formatting, and a category-wise sales chart.
Day1
| Order ID | Product | Category | Qty | Price | Total Sale | Value Band |
|---|---|---|---|---|---|---|
| ORD001 | Basmati Rice | Grains | 12 | ₹85 | =D3*E3 | Low |
| ORD002 | Sunflower Oil | Oils | 5 | ₹210 | =D4*E4 | Medium |
| ORD009 | Paneer | Dairy | 15 | ₹245 | =D10*E10 | High Value |
| … 12 more rows … | ||||||
🪜
Step-by-Step Guide1
Build Your Dataset (15 rows)
Create a tab named Day1. Add column headers in row 2: Order ID, Product Name, Category, Qty Sold, Unit Price, Total Sale, Date of Sale, Value Band. Enter 15 rows of realistic grocery data. Categories should include: Grains, Oils, Dairy, Snacks. Dates should all be in March 2025.
Making Realistic DataMix high and low-value orders. Include some totals above ₹1000 (e.g., 15 units × ₹245 = ₹3,675), some medium (₹500–₹1000), and some low (<₹500). This makes your IF classification more interesting.
2
ARRAYFORMULA for Total Sale (Column F)
Instead of entering =D3*E3 and dragging down, put this single formula in F2:
Formula — Cell F2 only
=ARRAYFORMULA(D3:D17 * E3:E17)
ImportantDo NOT put any other formulas in F3 through F17. ARRAYFORMULA fills them all automatically. If you put a formula in F3, it will cause a conflict error.
3
Summary Metrics Panel (Column I)
In column I (rows 3–8), build this summary panel:
I3: Total Revenue → =SUM(F3:F17)
I4: Highest Sale → =MAX(F3:F17)
I5: Lowest Sale → =MIN(F3:F17)
I6: Average Order → =AVERAGE(F3:F17)
I7: Orders > ₹1000 → =COUNTIF(F3:F17, ">1000")
I8: Categories → =COUNTA(UNIQUE(C3:C17))
Label each metric in column H, and apply a yellow background fill to the entire I3:J8 range so it stands out visually.
4
Nested IF for Value Band (Column H)
In H2, write an ARRAYFORMULA with nested IF to classify every order at once:
Formula — Cell H2 only
=ARRAYFORMULA(
IF(F3:F17>1000, "High Value",
IF(F3:F17>=500, "Medium",
IF(F3:F17>0, "Low", ""))))
IF(F>1000, "High Value", IF(F>=500, "Medium", "Low"))
F>1000 → "High Value" — check the biggest threshold FIRST
F>=500 → "Medium" — only reached if first IF was false
"Low" → everything that falls through the previous two checks
5
Professional Formatting
Apply these formatting steps in order:
1. Select row 2 → Bold → Background: dark blue → Font: white
2. View → Freeze → 1 row
3. Select A2:I17 → Format → Alternating colours
4. Select E3:F17 → Format → Number → Custom: ₹#,##0
5. Resize columns: double-click each column border to auto-fit
2. View → Freeze → 1 row
3. Select A2:I17 → Format → Alternating colours
4. Select E3:F17 → Format → Number → Custom: ₹#,##0
5. Resize columns: double-click each column border to auto-fit
6
Insert Category Sales Chart
Select column C (Category) and column F (Total Sale) using Ctrl+Click. Go to Insert → Chart. Choose Column chart. Set: Title = "March Sales by Category", X-axis = Category, Y-axis = Total Sale (₹). Apply a clean colour theme from Chart Style settings.
If Chart Shows Wrong DataClick the chart → Edit chart → Data range. Manually set X-axis to column C and Series to column F. Google sometimes guesses incorrectly.