Build Your E-commerce Product Catalog with an Excel Template
# E-commerce Product Catalog: Master Your Inventory with Excel Managing a product catalog is one of your most critical responsibilities as an E-commerce Manager. Every product detail—from SKU codes and pricing to inventory levels and specifications—directly impacts your sales performance, customer satisfaction, and operational efficiency. A disorganized catalog leads to costly mistakes: duplicate listings, pricing inconsistencies, missed inventory updates, and frustrated customers encountering outdated information. Conversely, a well-structured product sheet becomes your competitive advantage, enabling you to scale your business confidently while maintaining data accuracy across all sales channels. Excel offers a powerful, cost-effective solution for centralizing your product information. Whether you're managing dozens or thousands of SKUs, a properly designed spreadsheet streamlines your workflow, reduces manual errors, and provides instant visibility into your entire product ecosystem. This guide walks you through building and optimizing a professional product catalog in Excel. You'll learn how to structure your data for maximum efficiency, automate routine tasks, and create a system that grows with your business. Ready to take control of your product data? We've included a free, ready-to-use Excel template that you can customize immediately for your specific needs.
The Problem
E-commerce managers juggle dozens of product sheets daily, yet struggle with critical inefficiencies. Managing SKU data across multiple sales channels becomes chaotic—product descriptions, prices, and inventory levels constantly fall out of sync. Manual updates across platforms consume hours, creating costly errors: wrong prices published, outdated stock quantities, or missing product attributes that hurt conversion rates. Tracking performance metrics scattered across spreadsheets drains time better spent on strategy. Calculating profit margins, comparing competitor pricing, or analyzing seasonal trends requires piecing together fragmented data. When inventory updates arrive late or inventory counts mismatch, overselling happens, damaging customer trust. Collaborating with suppliers and warehouse teams through email attachments creates version control nightmares. You're never certain which product sheet is current. These daily frustrations prevent you from focusing on growth initiatives and strategic decision-making—the work that actually drives revenue.
Benefits
Save 5-8 hours weekly by bulk-uploading product data to multiple sales channels (Amazon, Shopify, eBay) using Excel templates instead of manual entry.
Reduce pricing errors by 95% with dynamic pricing formulas that automatically adjust markups based on cost, competitor data, and inventory levels.
Track inventory discrepancies in real-time by linking product sheets to stock counts, preventing overselling and reducing return rates by up to 20%.
Cut product listing creation time from 30 minutes to 5 minutes per item using Excel templates with pre-formatted descriptions, SEO keywords, and image links.
Gain instant profitability insights by calculating unit economics (COGS, margin %, ROI) across your entire catalog, identifying underperforming products within minutes instead of days.
Step-by-Step Tutorial
Create the table structure
Create a new Excel workbook and define the main columns for your product sheet. Include columns for Product ID, Product Name, Category, SKU, Unit Price, Stock Quantity, Supplier, Status, and Notes. This structure will serve as the foundation for all your e-commerce product management tasks.
Use Ctrl+T to convert your data range into a structured table, which enables automatic formula expansion and easier filtering
Set up header row formatting
Format the header row with bold text, background color, and borders to make it visually distinct. This improves readability and helps team members quickly identify column purposes. Apply consistent font styling across all headers.
Use the Format as Table feature (Home > Format as Table) to automatically apply professional styling with alternating row colors
Add product information columns
Populate the basic product data columns with realistic examples: Product ID (P001, P002), Product Name (Wireless Headphones, USB-C Cable), Category (Electronics, Accessories), SKU (WH-BLK-001), Unit Price (49.99, 12.99), and Stock Quantity (150, 320). This represents your core product inventory data.
Use data validation for Category column to ensure consistency—go to Data > Data Validation and create a dropdown list of approved categories
Create a Status column with IF formula
Add a Status column that automatically displays 'In Stock', 'Low Stock', or 'Out of Stock' based on the Stock Quantity value. This helps you quickly identify inventory issues without manual review. Use nested IF statements to create three conditions.
=IF(D2=0,"Out of Stock",IF(D2<=50,"Low Stock","In Stock"))Copy this formula down the entire column by selecting the cell and using Ctrl+D to fill down. Adjust the '50' threshold based on your business needs
Add supplier lookup with VLOOKUP
Create a separate Supplier Reference table on another sheet containing Supplier IDs and Supplier Names. Then use VLOOKUP in your main sheet to automatically populate the Supplier column based on a Supplier ID. This eliminates manual data entry and reduces errors.
=VLOOKUP(C2,SupplierRef!A:B,2,FALSE)Create the reference table on a sheet named 'SupplierRef' with columns: Supplier ID (S001, S002) and Supplier Name (ABC Electronics, XYZ Distributors). Use FALSE for exact match
Calculate total inventory value
Add a new column called 'Total Value' that multiplies Unit Price by Stock Quantity to show the monetary value of each product's inventory. This metric helps e-commerce managers understand capital tied up in inventory and identify high-value products.
=E2*F2Format this column as Currency (right-click > Format Cells > Currency) to display values as $49.99 for better readability
Create a reorder indicator with TEXT function
Add a 'Reorder Flag' column that combines TEXT function with IF logic to display formatted reorder recommendations. When stock falls below a minimum threshold, it displays a formatted alert message with the product name and recommended quantity.
=IF(F2<=50,TEXT(E2*10,"Reorder "&A2&" - Qty: 0"),"OK")Adjust the reorder threshold (50) and multiplier (10) based on your average sales velocity and supplier lead times
Add conditional formatting for quick visual scanning
Apply conditional formatting rules to highlight cells based on their values. For example, highlight 'Out of Stock' status in red, 'Low Stock' in yellow, and 'In Stock' in green. This enables managers to spot inventory issues at a glance without reading every cell.
Select the Status column, go to Home > Conditional Formatting > New Rule, and use formula =D2="Out of Stock" with red fill color
Create summary statistics section
Add a summary section below your product table that calculates key metrics: Total Products, Total Inventory Value, Low Stock Items, and Out of Stock Items. Use COUNTA, SUMIF, and COUNTIF functions to automatically update these metrics as your data changes.
=COUNTIF(D:D,"Low Stock")Place summary statistics in a separate area (like rows 30-35) with clear labels. Use SUMPRODUCT for complex calculations: =SUMPRODUCT((F2:F100<=50)*(E2:E100*F2:F100))
Set up data validation and protection
Protect your template by locking formula cells and allowing only specific columns for manual entry. This prevents accidental deletion of formulas while enabling team members to update product information. Use Sheet Protection to enforce this structure.
Go to Review > Protect Sheet, check 'Protect worksheet and contents of locked cells', and allow users to 'Select unlocked cells only'. First, unlock data entry columns by right-clicking > Format Cells > Protection tab
Template Features
Dynamic inventory tracking
Automatically calculates stock levels by deducting sales from initial inventory, triggering alerts when stock falls below reorder points
=IF(B2-C2<=D2,"⚠ REORDER",B2-C2)Profit margin calculator
Instantly computes gross profit and margin percentage for each product to identify your most profitable items
=(E2-F2)/E2*100Sales performance dashboard
Aggregates revenue by product category and tracks month-over-month growth with automatic percentage calculations
=SUMIF($A$2:$A$100,G2,$H$2:$H$100)SKU and variant management
Organizes multiple product variants (size, color, weight) with linked inventory and pricing to prevent overselling
Conditional stock alerts
Cells highlight in red when inventory drops below safety stock level, enabling proactive purchasing decisions
=IF(B2<C2,"CRITICAL",IF(B2<C2*1.5,"LOW","OK"))Multi-channel price synchronization
Maintains consistent pricing across platforms (Amazon, eBay, Shopify) with automatic markup calculations and bulk update capability
=F2*(1+$J$1)Concrete Examples
Inventory Management & Stock-Out Prevention
Alex, an e-commerce manager at an online fashion retailer, needs to monitor stock levels across 150+ SKUs to prevent stockouts during peak seasons. The Product Sheet template helps track current inventory, reorder points, and supplier lead times.
Product: 'Cotton T-Shirt Blue M' | Current Stock: 45 units | Reorder Point: 30 units | Lead Time: 14 days | Monthly Velocity: 60 units | Supplier: TextileCo | Last Order Date: 2024-01-15
Result: Automated alerts identifying 23 products below reorder point, projected stockout dates for each SKU, and a prioritized purchase order list to submit to suppliers within 48 hours
Profit Margin Analysis by Product Category
Jordan, an e-commerce operations manager, needs to identify underperforming products dragging down overall profitability. Using the Product Sheet, she tracks cost structure and margin metrics across electronics, home goods, and accessories.
Product: 'Wireless Headphones' | COGS: $25 | Selling Price: $79.99 | Marketplace Fee: 12% | Shipping Cost: $3.50 | Monthly Units Sold: 340 | Current Margin: 31%
Result: A ranked list showing that Wireless Headphones ranks #3 by margin (31%) but #1 by volume contribution; identification that 12 low-margin products should be discontinued or repriced; recommendation to increase price on 8 items by 8-12% to reach 35% target margin
Multi-Channel Performance Comparison
Sam manages product listings across Amazon, Shopify, and eBay simultaneously. The Product Sheet template consolidates performance metrics to identify which channels drive the highest ROI for each product.
Product: 'Stainless Steel Water Bottle' | Amazon: 450 units/month, $18.99 price, 8% commission | Shopify: 120 units/month, $21.99 price, 2.9% fee | eBay: 85 units/month, $19.99 price, 12.9% commission | Return Rate: 2.1% (Amazon), 0.8% (Shopify), 3.5% (eBay)
Result: Dashboard showing Shopify generates highest profit margin (78%) despite lower volume; Amazon provides volume scale (65% of total sales) but with higher returns; recommendation to increase Shopify marketing investment by $500/month and implement stricter quality controls for eBay listings
Pro Tips
Dynamic Inventory Alerts with Conditional Formatting + Formulas
Create automatic stock level warnings by combining conditional formatting with a formula that flags products below reorder points. Use Ctrl+Shift+X to quickly apply conditional formatting rules. Set up a formula like =AND(B2<C2,B2>0) to highlight items needing urgent reordering, helping you avoid stockouts without manual monitoring.
=AND(B2<C2,B2>0)Bulk Price Updates with Find & Replace + Formulas
Instead of manually updating prices, use Ctrl+H (Find & Replace) combined with a helper column formula to apply percentage increases across categories. Create a formula that multiplies base price by a category-specific multiplier, then copy values back. This scales to hundreds of SKUs in seconds and maintains audit trails.
=B2*(1+VLOOKUP(A2,CategoryMarkup,2,FALSE))Real-Time Profitability Dashboard with Pivot Tables
Build a pivot table from your product sheet (Ctrl+Shift+L for quick table formatting) to instantly see profit margins by category, supplier, or sales channel. Add slicers (Insert > Slicer) to filter by date range or product status. This transforms raw data into actionable insights for promotional decisions in under 60 seconds.
Automated SKU Generation & Duplicate Detection
Use a formula to auto-generate unique SKU codes combining category codes + sequence numbers, then apply conditional formatting to highlight duplicates. This prevents costly inventory errors and ensures data integrity as your catalog grows. Use Ctrl+Alt+D after creating a helper column to identify duplicates instantly.
=CONCATENATE(LEFT(A2,3),"-",TEXT(COUNTIF($A$2:A2,A2),"0000"))