What Pivot Tables Actually Do (In Plain English)
You have 10,000 orders from Shopify. Each row is one order with: Order ID, Date, SKU, Quantity, Revenue, Customer, Channel.
Questions you want answered:
- Which SKUs made the most money?
- What's my revenue by month?
- Which customers bought the most?
- How does Shopify compare to Amazon revenue?
Without pivot tables: You filter, sort, use SUMIFS formulas, create charts manually. Takes hours. Breaks when data changes.
With pivot tables: Drag SKU to "Rows", drag Revenue to "Values", click OK. Done in 10 seconds. You have your top SKUs ranked by revenue.
Want revenue by month? Drag Date to Columns. Instant month-over-month comparison.
Want to filter to just one category? Add Category to Filters. Click the filter, select "Electronics". Now you see electronics SKUs only.
Pivot tables = Interactive data summarization. No formulas. Just drag and drop.
The Anatomy of a Pivot Table
Every pivot table has 4 areas you drag fields into:
1. Rows (What you want to see listed vertically)
- SKUs (product performance)
- Customers (customer ranking)
- Categories (category analysis)
- Dates (when grouped, shows trends over time)
2. Columns (What you want to see across the top)
- Months (month-over-month comparison)
- Channels (Shopify vs Amazon side-by-side)
- Product types (compare categories)
- Empty for simple lists
3. Values (What you want to calculate/sum)
- SUM of Revenue
- SUM of Profit
- COUNT of Orders
- AVERAGE of Order Value
4. Filters (What you want to filter by)
- Date range (show only Q4 2024)
- Category (show only "Apparel")
- Channel (show only "Shopify")
- Any field you want to slice by
Visual Example:
Your Raw Data (10,000 rows):
```
Order ID | Date | SKU | Revenue | Channel
1001 | 2024-01-05 | ABC | $50 | Shopify
1002 | 2024-01-05 | XYZ | $30 | Amazon
1003 | 2024-01-06 | ABC | $50 | Shopify
... 9,997 more rows
```
Your Pivot Table (3 rows):
```
SKU | Total Revenue
ABC | $125,000
XYZ | $98,500
DEF | $76,200
```
You just turned 10,000 rows into 3 meaningful rows in 10 seconds.
Create Your First E-commerce Pivot Table (5 Minutes)
-
3
Export Your Orders from Shopify/WooCommerce
Go to Orders → Export → All Orders → CSV. Save the file. You need at least: Order ID, Date, SKU, Revenue, Quantity columns.
-
3
Select Your Data Range
Click any cell in your data. Don't select anything - just click one cell. Excel auto-detects the range. (Or select all: Ctrl+A)
-
3
Insert Pivot Table
Go to: Insert tab → PivotTable button. Dialog appears. 'Select a table or range' should show your data. Click OK. New sheet opens with empty pivot table.
-
3
Drag SKU to Rows
On right side, you see 'PivotTable Fields' panel. Find 'SKU' field, drag it to 'Rows' box at bottom. Your pivot table now lists all unique SKUs.
-
3
Drag Revenue to Values
Find 'Revenue' field, drag it to 'Values' box. Excel automatically does SUM. Now you see each SKU with its total revenue.
-
3
Sort by Revenue (Highest First)
Click dropdown arrow next to 'Row Labels'. Choose 'More Sort Options' → Descending by 'Sum of Revenue'. Now top revenue SKUs appear first.
-
3
Add Filters (Optional)
Drag 'Date' to Filters box. A filter dropdown appears above pivot table. Click it, select date range. Pivot table instantly updates to that period only.
7 Pivot Tables Every E-commerce Seller Needs
1. Top Products by Revenue
Business Question: "What are my best-selling products?"
Pivot Table Setup:
- Rows: SKU or Product Name
- Values: SUM of Revenue
- Sort: Descending by Revenue
Result: Your products ranked from highest to lowest revenue
Add Quantity for Unit Sales:
- Drag Quantity to Values (below Revenue)
- Now you see both revenue AND units sold per SKU
Pro Tip: Add Profit instead of Revenue to see which SKUs make the MOST money (not just highest sales).
2. Revenue by Month (Trend Analysis)
Business Question: "How are my sales trending month-over-month?"
Pivot Table Setup:
- Rows: Date (Excel auto-groups by Month)
- Values: SUM of Revenue
Result: Each month listed with its total revenue. Visual trend of growth/decline.
If Dates Don't Group:
- Right-click any date in pivot table
- Select "Group"
- Choose "Months" and "Years"
- Click OK
Add Chart for Visual:
- Click anywhere in pivot table
- Insert → Chart → Line Chart
- Instant month-over-month trend visualization
3. Revenue by SKU by Month (Cross-Tab)
Business Question: "Which products are growing vs declining month-to-month?"
Pivot Table Setup:
- Rows: SKU
- Columns: Date (grouped by Month)
- Values: SUM of Revenue
Result: Grid with SKUs down left side, months across top. Each cell shows that SKU's revenue for that month.
Read the Table:
- Scan across a row to see one SKU's trend
- Scan down a column to see all SKUs for one month
- Empty cells = no sales that month
Add Conditional Formatting:
- Select all values in pivot table
- Home → Conditional Formatting → Color Scales
- Red = low, Green = high
- Instantly spot trends visually
4. Profit Analysis by Product (Needs COGS Data)
Business Question: "Which products are MOST profitable (not just highest revenue)?"
First, Add COGS to Your Data:
- Use VLOOKUP to add COGS column to orders: `=VLOOKUP(SKU, ProductCostTable, 2, FALSE)`
- Add Profit column: `=Revenue - (COGS × Quantity)`
Pivot Table Setup:
- Rows: SKU
- Values: SUM of Revenue, SUM of Profit
- Calculated Field for Margin %: (SUM of Profit / SUM of Revenue) × 100
Add Calculated Field:
- Click pivot table
- PivotTable Analyze tab → Fields, Items, & Sets → Calculated Field
- Name: "Margin %"
- Formula: `=Profit/Revenue*100`
- Click OK
Result: Each SKU with Revenue, Profit, AND Margin %. Sort by Profit to see most profitable products.
5. Customer Analysis (Top Customers by Spend)
Business Question: "Who are my top 20 customers?"
Pivot Table Setup:
- Rows: Customer Email or Customer Name
- Values: SUM of Revenue, COUNT of Orders
- Sort: Descending by Revenue
Result: Customers ranked by total spend. See both revenue and order count.
Add to Find VIPs:
- Filter to customers with > $500 spent
- Filter to customers with > 3 orders
- These are your repeat, high-value customers
Use This List For:
- VIP email campaigns
- Exclusive discount codes
- Early access to new products
- Personal thank-you messages
6. Channel Performance Comparison (Shopify vs Amazon)
Business Question: "Which sales channel is most profitable?"
Pivot Table Setup:
- Rows: Channel (Shopify, Amazon, eBay, etc.)
- Values: SUM of Revenue, SUM of Profit, COUNT of Orders
Add Calculated Fields:
- Average Order Value: Revenue / Order Count
- Profit Margin %: (Profit / Revenue) × 100
Result: Each channel side-by-side with all metrics. Instantly see which channel has best margins.
Common Findings:
- Shopify: Higher margins (lower fees)
- Amazon: Higher volume, lower margins (FBA fees)
- Own website: Highest margins, lowest volume
7. Product Category Performance Matrix
Business Question: "Which product categories should I invest in?"
Pivot Table Setup:
- Rows: Category
- Columns: Quarter or Month
- Values: SUM of Revenue
Add Growth Calculation:
- Right-click any value → Show Values As → % Difference From
- Base Field: Date, Base Item: Previous
- Now each cell shows % change from previous period
Result: Grid showing each category's growth rate over time. Spot trending categories vs declining ones.
Inventory Decision:
- Growing categories: Increase inventory, add more SKUs
- Declining categories: Clear out, reduce investment
- Stable categories: Maintain current levels
Advanced Pivot Table Techniques for E-commerce
Grouping Dates (Weekly, Monthly, Quarterly, Yearly)
By default, Excel lists every individual date. That's useless for trends.
Group Dates:
1. Right-click any date in Rows
2. Select "Group"
3. Check boxes: Months, Quarters, Years (uncheck Days)
4. Click OK
Now you have collapsible groups:
- Click "+" next to 2024 to expand into quarters
- Click "+" next to Q1 to expand into months
- Instant drill-down from year → quarter → month → day
Calculated Fields (Create New Metrics)
Example: Average Order Value
You have SUM of Revenue and COUNT of Orders. You want AOV.
1. Click pivot table → PivotTable Analyze tab
2. Fields, Items, & Sets → Calculated Field
3. Name: "AOV"
4. Formula: `=Revenue/Orders`
5. Click Add, then OK
New column appears: Average Order Value for each SKU/month/whatever.
Other Useful Calculated Fields:
- Margin %: `=Profit/Revenue*100`
- Return Rate: `=Refunded_Orders/Total_Orders*100`
- Units per Order: `=Total_Units/Order_Count`
Show Values As (Percentages, Ranks, Running Totals)
Right-click any value in pivot table → Show Values As:
% of Column Total:
- Shows each SKU as % of total revenue
- Answers: "What % of my revenue is this SKU?"
% of Row Total:
- Shows each month as % of that SKU's total
- Answers: "What % of this SKU's annual revenue was in January?"
Rank (Smallest to Largest or Largest to Smallest):
- Shows each SKU's rank (1st, 2nd, 3rd best seller)
Running Total:
- Shows cumulative revenue over time
- Answers: "How much revenue have I made year-to-date?"
% Difference From (Previous/Next):
- Shows month-over-month or year-over-year growth %
- Instant growth rate calculation
Pivot Table Slicers - Visual Filters for Non-Technical Users
The Problem:
Your manager/investor/partner isn't Excel-savvy. They don't know how to use filter dropdowns.
The Solution: Slicers
Add Slicers:
1. Click pivot table
2. PivotTable Analyze tab → Insert Slicer
3. Check boxes: Category, Channel, Quarter
4. Click OK
Three visual buttons appear:
- Category buttons: Electronics, Apparel, Home
- Channel buttons: Shopify, Amazon, eBay
- Quarter buttons: Q1, Q2, Q3, Q4
How They Work:
- Click "Electronics" button → Pivot table instantly filters to electronics only
- Click "Q4" button → Now showing Q4 electronics only
- Click again to deselect
- Multi-select: Ctrl+Click to select multiple categories at once
Why This Is Powerful:
Non-technical users can explore data themselves. They click buttons, pivot table updates. No Excel training needed.
Common Pivot Table Mistakes & Fixes
Mistake 1: "My dates won't group by month"
Cause: Dates are stored as text, not dates.
Fix:
1. Before creating pivot table, select date column
2. Home → Find & Replace (Ctrl+H)
3. Find: (your date format, e.g., "01/15/2024")
4. Use formula to convert: `=DATEVALUE(A2)`
5. Copy formula down, paste values, delete old column
Now dates are real dates. Pivot table will group them.
Mistake 2: "Sum of Revenue shows as COUNT of Revenue"
Cause: Revenue column has text values ("$50.00" instead of 50).
Fix:
1. Remove $ and commas from revenue column before pivot table
2. Formula: `=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))`
3. This converts "$1,234.56" to 1234.56
Mistake 3: "Pivot table shows wrong numbers after data update"
Cause: Pivot table cached old data. Needs refresh.
Fix:
Right-click pivot table → Refresh
Prevent Future:
PivotTable Analyze → Options → Check "Refresh data when opening the file"
Mistake 4: "My pivot table is too big, Excel crashes"
Cause: Too many unique values (100,000 unique SKUs, 10,000 unique customers).
Fix:
- Filter source data before pivot (e.g., last 90 days only)
- Use pivot table from external data source (Power Pivot)
- Summarize data first (daily totals instead of order-level)
- Use 64-bit Excel (handles more data)
Skip the manual work. Get automated Excel reports with pivot tables pre-built for top products, monthly trends, channel comparison, and more.
Get Pre-Built Pivot TablesAll pivot tables included, refresh with one click
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Not refreshing pivot table after updating source data
Solution: Always right-click pivot table → Refresh after adding new orders. Or set auto-refresh in options.
Putting too many fields in Rows, creating unreadable tables
Solution: Start with 1-2 fields in Rows. Add more only if needed. Keep it simple and readable.
Using pivot tables on unclean data (text instead of numbers, inconsistent SKUs)
Solution: Clean your data first: remove $, commas; trim spaces from SKUs; convert dates to actual dates.
Not sorting pivot table (shows data in random order)
Solution: Always sort by Values descending. You want top products first, not alphabetical order.
How to Verify Your Numbers
Ensure accuracy with these verification steps
Your top products pivot table shows SKUs ranked by revenue (highest first)
Your monthly trend pivot table shows dates grouped by month, not individual days
Your channel comparison pivot table shows SUM of Revenue, not COUNT
When you click Refresh, pivot table updates with any new data you added
You can add a slicer and clicking it filters the pivot table instantly
Frequently Asked Questions
What's the difference between pivot tables and regular formulas like SUMIFS?
Can I use pivot tables with 100,000+ orders?
Do pivot tables work in Google Sheets?
Can I make a pivot table update automatically when I add new rows?
How do I save multiple pivot tables for different analyses?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.