Why E-commerce Sellers Need Different Excel Skills
You're not building financial models for Fortune 500 companies. You need Excel to answer specific questions:
- "What's my profit on SKU-12345 after Shopify fees?"
- "Which orders from last month haven't been paid yet?"
- "How much inventory do I have tied up in products that haven't sold in 60 days?"
- "What's my true margin on discounted orders?"
Generic Excel tutorials teach you to make pretty charts. This guide teaches you the 10 formulas that solve real e-commerce problems.
These are the same formulas used in our automated reports for payout reconciliation, margin analysis, and inventory tracking.
Formula 1: SUMIFS - Revenue/Profit by Any Criteria
The Problem It Solves:
"What's my total revenue for SKU-ABC in January from Shopify channel?"
Without SUMIFS, you manually filter and sum. With 10,000 orders, that's impossible.
The Formula:
```excel
=SUMIFS(revenue_column, sku_column, "SKU-ABC", date_column, ">=2025-01-01", date_column, "<=2025-01-31", channel_column, "Shopify")
```
Real E-commerce Example:
Your Data:
| Order ID | SKU | Revenue | Date | Channel |
|----------|-----|---------|------|---------|
| 1001 | SKU-ABC | $50 | 2025-01-05 | Shopify |
| 1002 | SKU-XYZ | $30 | 2025-01-06 | Amazon |
| 1003 | SKU-ABC | $50 | 2025-01-07 | Shopify |
| 1004 | SKU-ABC | $50 | 2025-02-01 | Shopify |
Formula:
```excel
=SUMIFS(C:C, B:B, "SKU-ABC", D:D, ">=2025-01-01", D:D, "<=2025-01-31", E:E, "Shopify")
```
Result: $100 (rows 1 and 3 only)
Common Use Cases:
- Total sales by product
- Revenue by channel (Shopify vs Amazon)
- Sales by date range
- Discounted orders total
- Refunds by month
Pro Tip:
Use cell references instead of hardcoding:
```excel
=SUMIFS(C:C, B:B, F2, D:D, ">="&G2, D:D, "<="&H2)
```
Now change F2, G2, H2 to filter different SKU/dates without rewriting formula.
Formula 2: VLOOKUP - Map SKU to Cost/Fee/Anything
The Problem It Solves:
You have 500 orders. Each order has a SKU. You need to add the COGS for each SKU to calculate profit.
Without VLOOKUP, you manually look up each SKU's cost and type it in. That's 500 manual lookups.
The Formula:
```excel
=VLOOKUP(lookup_value, lookup_table, column_number, FALSE)
```
Real E-commerce Example:
Your Product Cost Table (Sheet2):
| SKU | COGS | Supplier |
|-----|------|----------|
| SKU-ABC | $20 | Acme Co. |
| SKU-XYZ | $15 | Beta Inc. |
| SKU-123 | $30 | Gamma LLC |
Your Order Data (Sheet1):
| Order ID | SKU | Revenue | COGS | Profit |
|----------|-----|---------|------|--------|
| 1001 | SKU-ABC | $50 | =VLOOKUP(B2,Sheet2!A:C,2,FALSE) | =C2-D2 |
| 1002 | SKU-XYZ | $30 | =VLOOKUP(B3,Sheet2!A:C,2,FALSE) | =C3-D3 |
Result:
- Row 2 COGS: $20 (looked up SKU-ABC)
- Row 2 Profit: $30 ($50 - $20)
- Row 3 COGS: $15 (looked up SKU-XYZ)
- Row 3 Profit: $15 ($30 - $15)
What Each Part Means:
- `B2` = The SKU you're looking up
- `Sheet2!A:C` = Your lookup table (SKU in column A, COGS in column B)
- `2` = Return value from column 2 of the table (COGS)
- `FALSE` = Exact match only (critical for SKUs)
Common Use Cases:
- Map SKU → COGS
- Map SKU → Supplier name
- Map SKU → Category
- Map Order ID → Payout ID
- Map Discount Code → Discount %
The #N/A Error Fix:
If you see #N/A, the SKU doesn't exist in your lookup table. Wrap in IFERROR:
```excel
=IFERROR(VLOOKUP(B2,Sheet2!A:C,2,FALSE), "SKU Not Found")
```
Formula 3: IF Statements - Flag Profit Warnings
The Problem It Solves:
You have 500 SKUs. Which ones have margin below 20%? Which orders lost money?
Manual scanning is impossible. IF statements flag problems automatically.
The Formula:
```excel
=IF(condition, value_if_true, value_if_false)
```
Real E-commerce Example:
Flag Low-Margin SKUs:
| SKU | Revenue | COGS | Margin % | Warning |
|-----|---------|------|----------|---------|
| SKU-ABC | $50 | $20 | 60% | =IF(D2<20%, "LOW MARGIN", "OK") |
| SKU-XYZ | $30 | $28 | 7% | =IF(D3<20%, "LOW MARGIN", "OK") |
Result:
- Row 2: "OK" (60% > 20%)
- Row 3: "LOW MARGIN" (7% < 20%)
Nested IF for Multiple Conditions:
```excel
=IF(D2<0, "LOSING MONEY", IF(D2<20%, "LOW MARGIN", IF(D2<40%, "OK", "EXCELLENT")))
```
Result Categories:
- Negative margin: "LOSING MONEY"
- 0-20%: "LOW MARGIN"
- 20-40%: "OK"
- 40%+: "EXCELLENT"
Common Use Cases:
- Flag unprofitable products
- Identify overdue reorders (days to stockout < 7)
- Mark high-return SKUs (refund rate > 10%)
- Categorize customers (1 order = "One-time", 2+ = "Repeat")
- Alert on fee overcharges (actual > expected)
Formula 4: COUNTIFS - Count Orders by Criteria
The Problem It Solves:
"How many orders used discount code SAVE20 in January?"
"How many SKU-ABC units sold on Shopify last week?"
The Formula:
```excel
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
```
Real Example:
```excel
=COUNTIFS(DiscountCode_Column, "SAVE20", Date_Column, ">=2025-01-01", Date_Column, "<=2025-01-31")
```
Use Cases:
- Orders per discount code
- Units sold per SKU
- Refunds per month
- New customers acquired (first order = TRUE)
- High-value orders (order value > $200)
Formula 5: AVERAGEIFS - Average Order Value, Margin, Anything
The Problem It Solves:
"What's my average order value for Facebook customers?"
"What's average margin on electronics category?"
The Formula:
```excel
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2)
```
Real Example - AOV by Channel:
```excel
=AVERAGEIFS(OrderValue_Column, Channel_Column, "Facebook")
```
Real Example - Average Margin by Category:
```excel
=AVERAGEIFS(Margin_Column, Category_Column, "Electronics", Margin_Column, ">0")
```
(Second criteria excludes negative margins from average)
Use Cases:
- Average order value by channel
- Average profit margin by category
- Average days to stockout
- Average customer lifetime value by cohort
- Average refund amount
Formula 6: Text Formulas - Clean Messy Data
The Problem It Solves:
Shopify exports have messy data:
- SKUs with spaces: " SKU-ABC "
- Prices with currency symbols: "$19.99"
- Dates in wrong format: "01/03/2025"
TRIM - Remove Extra Spaces:
```excel
=TRIM(A2)
```
" SKU-ABC " → "SKU-ABC"
SUBSTITUTE - Remove Currency Symbols:
```excel
=VALUE(SUBSTITUTE(A2,"$",""))
```
"$19.99" → 19.99 (as number)
TEXT - Format Dates Consistently:
```excel
=TEXT(A2, "YYYY-MM-DD")
```
1/3/2025 → 2025-01-03
CONCATENATE/TEXTJOIN - Combine Fields:
```excel
=CONCATENATE(FirstName, " ", LastName, " - ", Email)
```
John + Smith + john@email.com → "John Smith - john@email.com"
LEFT/RIGHT/MID - Extract Parts:
```excel
=LEFT(A2, 3) // First 3 characters
=RIGHT(A2, 4) // Last 4 characters (last 4 of card)
=MID(A2, 5, 3) // 3 characters starting at position 5
```
Real Use Case - Extract Variant from SKU:
SKU format: "SHIRT-RED-MEDIUM"
```excel
Color: =MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2)-1)
```
Result: "RED"
Formula 7: Date Formulas - Handle Order Timing
The Problem It Solves:
E-commerce is all about timing:
- Which month/quarter was this order?
- How many days between order and payout?
- Which orders are in this week's payout batch?
MONTH/YEAR - Extract Month/Year:
```excel
=MONTH(A2) // Returns 1-12
=YEAR(A2) // Returns 2025
=TEXT(A2, "MMM YYYY") // Returns "Jan 2025"
```
TODAY/NOW - Current Date:
```excel
=TODAY() // Returns today's date
=NOW() // Returns current date and time
```
Date Arithmetic:
```excel
=A2 - B2 // Days between two dates
=A2 + 7 // Date 7 days from A2
```
Real E-commerce Example - Days Since Last Sale:
```excel
=TODAY() - LastSaleDate
```
Real Example - Which Payout Batch (Shopify T+2):
```excel
=OrderDate + 2 // Expected payout date for Shopify daily payouts
```
Real Example - Month-Over-Month Comparison:
```excel
This Month: =SUMIFS(Revenue, OrderDate, ">=2025-01-01", OrderDate, "<=2025-01-31")
Last Month: =SUMIFS(Revenue, OrderDate, ">=2024-12-01", OrderDate, "<=2024-12-31")
% Change: =(ThisMonth - LastMonth) / LastMonth
```
Building Your First E-commerce Excel Dashboard
-
9
Export Your Orders Data
From Shopify/WooCommerce, export orders with: Order ID, Date, SKU, Quantity, Revenue, Discount, Shipping, Status. Save as CSV.
-
9
Add VLOOKUP for COGS
In orders sheet, add COGS column: =VLOOKUP(SKU_cell, Sheet2!A:C, 2, FALSE). Drag down to all rows. Every order now has its cost.
-
9
Calculate Fees per Order
Add fee column: =Revenue × 0.029 + 0.30 (for Shopify Payments Basic plan). Adjust % based on your plan: Advanced 2.5%, Plus negotiated.
-
9
Calculate Net Profit per Order
Profit = Revenue - COGS - Fees - Shipping Cost. Formula: =Revenue - COGS - Fees - ShippingCost. This is your true profit per transaction.
-
9
Add Conditional Formatting Alerts
Select profit column → Conditional Formatting → Color Scales (red for negative, green for high). Instantly see which orders lost money.
-
9
Create Summary Dashboard with SUMIFS
Create summary table: Total Revenue (SUM), Total Profit (SUM), Avg Margin (AVERAGE), Orders Count (COUNT), Avg Order Value (AVERAGE). Use SUMIFS for by-month breakdown.
-
9
Build Pivot Table for SKU Analysis
Select all data → Insert → PivotTable. Rows: SKU, Values: SUM of Revenue, SUM of Profit, COUNT of Orders. Sort by profit descending to see winners/losers.
Formula 8: INDEX-MATCH - VLOOKUP's Smarter Cousin
Why INDEX-MATCH Beats VLOOKUP:
VLOOKUP limitations:
- Can only look right (column 2, 3, 4 must be right of column 1)
- Breaks when you insert columns
- Slow on huge datasets
INDEX-MATCH solves all three.
The Formula:
```excel
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
```
Real E-commerce Example:
Product Table:
| SKU | Product Name | COGS | Supplier |
|-----|--------------|------|----------|
| ABC | Red Shirt | $20 | Acme |
| XYZ | Blue Pants | $25 | Beta |
Look Up COGS (VLOOKUP Way):
```excel
=VLOOKUP("ABC", A:D, 3, FALSE)
```
Look Up COGS (INDEX-MATCH Way):
```excel
=INDEX(C:C, MATCH("ABC", A:A, 0))
```
Look Up Supplier (LEFT of SKU column) - VLOOKUP CAN'T DO THIS:
```excel
=INDEX(D:D, MATCH("ABC", A:A, 0))
```
When to Use INDEX-MATCH:
- Looking up values to the LEFT
- Large datasets (faster than VLOOKUP)
- When columns might be inserted/moved
- Complex lookups with multiple criteria
Formula 9: Array Formulas - Multiple Criteria Lookups
The Problem It Solves:
"What's the COGS for SKU-ABC when ordered from Supplier-1 in January?"
VLOOKUP can't handle multiple criteria. Array formulas can.
The Formula (Legacy Excel):
```excel
=INDEX(return_range, MATCH(1, (criteria_range1=criteria1)*(criteria_range2=criteria2), 0))
```
Press Ctrl+Shift+Enter to make it an array formula.
The Formula (Modern Excel - XLOOKUP):
```excel
=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found")
```
Real Example - Two-Criteria Lookup:
Cost Table:
| SKU | Supplier | COGS |
|-----|----------|------|
| ABC | Acme | $20 |
| ABC | Beta | $18 |
| XYZ | Acme | $25 |
Find COGS for ABC from Beta:
```excel
=INDEX(C:C, MATCH(1, (A:A="ABC")*(B:B="Beta"), 0))
```
Result: $18
Modern Alternative (Excel 365):
```excel
=FILTER(C:C, (A:A="ABC")*(B:B="Beta"))
```
Formula 10: Conditional Formatting Rules - Visual Alerts
The Problem It Solves:
You don't want to manually scan 500 rows for problems. Let Excel highlight them automatically.
E-commerce Alert Scenarios:
#### Alert 1: Negative Profit (Losing Money)
- Select profit column
- Conditional Formatting → New Rule → Format cells where value < 0
- Format: Red fill, bold
#### Alert 2: Low Inventory (< 7 Days to Stockout)
- Select "Days to Stockout" column
- Conditional Formatting → Color Scale (Red at 0, Yellow at 7, Green at 30)
#### Alert 3: High Refund Rate (> 8%)
- Select "Refund Rate %" column
- Conditional Formatting → Icon Sets → Red flag if > 8%
#### Alert 4: Fee Overcharge (Actual > Expected)
- Select "Fee Variance" column
- Format cells where value > 0 in red (you were overcharged)
Formula-Based Formatting:
Highlight Entire Row if Unprofitable:
1. Select entire data range (A2:Z500)
2. Conditional Formatting → New Rule → Use formula
3. Formula: `=$H2<0` (assuming profit in column H)
4. Format: Light red fill
Now entire row turns red when profit is negative.
Putting It All Together: Payout Reconciliation Example
The Business Problem:
Your Shopify payout is $9,347.23. Your dashboard shows $10,000 in sales. Where did $652.77 go?
The Excel Solution:
Step 1: Calculate Expected Payout
```excel
=SUMIFS(OrderTotal, PayoutID, "PO-12345") - SUMIFS(Fees, PayoutID, "PO-12345") - SUMIFS(Refunds, PayoutID, "PO-12345")
```
Step 2: Compare to Actual Payout
```excel
=ExpectedPayout - ActualPayout
```
Step 3: Flag Mismatches
```excel
=IF(ABS(Variance) > 0.10, "MISMATCH", "OK")
```
Step 4: Breakdown by Fee Type
```excel
Transaction Fees: =SUMIFS(Fees, FeeType, "Transaction", PayoutID, "PO-12345")
Payment Fees: =SUMIFS(Fees, FeeType, "Payment", PayoutID, "PO-12345")
Shopify Fees: =SUMIFS(Fees, FeeType, "Shopify", PayoutID, "PO-12345")
```
Now you can see exactly where the $652.77 went:
- Transaction fees: $290.00
- Payment fees: $87.00
- Shopify subscription: $29.00
- Refund from last week: $246.77
- Total: $652.77 ✓
Skip the formulas entirely. Get automated Excel reports with all calculations built in - just open and analyze.
Get Pre-Built Excel ReportsAll formulas included, fully documented
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Using SUM instead of SUMIFS and filtering manually
Solution: SUMIFS is faster and less error-prone. Filter once in formula, not by hiding rows.
Not using $ signs for absolute references in VLOOKUP table range
Solution: Use =VLOOKUP(B2, $A$2:$C$500, 2, FALSE) so table range doesn't shift when you copy formula down.
Using TRUE in VLOOKUP for SKU lookups
Solution: Always use FALSE for exact match with SKUs. TRUE does approximate match and returns wrong data.
Not wrapping VLOOKUP in IFERROR to handle missing SKUs
Solution: =IFERROR(VLOOKUP(...), 'Not Found') prevents #N/A errors breaking your sheet.
Hardcoding dates instead of using cell references
Solution: Put dates in cells, reference them in formulas. Makes it easy to change date ranges without rewriting formulas.
How to Verify Your Numbers
Ensure accuracy with these verification steps
Your SUMIFS formula returns the same total as manually filtering and summing
Your VLOOKUP correctly maps all SKUs to costs (no #N/A errors)
Your profit calculation matches: Revenue - COGS - Fees = Profit
Your conditional formatting highlights all negative profits in red
You can change a date range in one cell and all formulas update automatically
Frequently Asked Questions
What's the difference between SUMIF and SUMIFS?
Why does my VLOOKUP return #N/A?
Can I use these formulas in Google Sheets?
What if I have Excel 2010 or older?
How do I learn which columns to reference in my specific data?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.