The majority of e-commerce merchants start their analytics tracking with a spreadsheet. Excel or Google Sheets offer immediate accessibility, total flexibility and zero entry cost. It is therefore natural to build your first performance dashboards on these tools. But as the store grows, CSV exports pile up, formulas become more complex and data entry errors multiply. This article guides you step by step through creating an e-commerce dashboard in Excel, shows you which key indicators to track and explains when it becomes relevant to switch to a dedicated solution.
What you can track with Excel
Excel is a powerful tool for centralizing and visualizing e-commerce data, provided you have the time to feed and maintain it. Here is what can be tracked effectively with a well-structured spreadsheet.
- Daily, weekly and monthly revenue
- Number of orders and average order value
- Conversion rate by traffic source
- Customer acquisition cost (CAC) calculated manually
- Customer lifetime value (LTV) from exported history
- Product performance: sales, returns, margins
- Stock evolution and SKU turnover
- Revenue by channel: organic, paid, email, social
How to create an e-commerce dashboard with Excel
Structure the raw database
Create a dedicated tab for raw data. Each row represents an order, with the following columns: order ID, date, net amount, gross amount, status, acquisition channel, customer ID, country. Never modify this tab manually: it is the single source of truth.
Create an intermediate calculations tab
Isolate calculation formulas in a separate tab. Use named ranges to make formulas readable. Calculate average order value, conversion rate, CAC and revenue by period here. This makes updates easier and reduces errors.
Build pivot tables
Pivot tables are the analytics engine in Excel. Create one pivot table per analysis axis: sales by product, sales by channel, sales by country, sales by period. Connect each pivot table to the raw database for one-click updates.
Design the visual summary page
Create a single dashboard tab with charts from the pivot tables. Place key metrics at the top (KPI cards with conditional formatting), then trend charts, then detail tables. Use a consistent and clean color scheme.
Automate data imports
If your e-commerce platform offers scheduled CSV exports, configure automatic import via Power Query. This reduces manual entry but requires the export format to remain identical each time.
Set up an update schedule
Define a realistic update cadence: daily for gross revenue, weekly for performance analysis, monthly for cohort and retention analysis. Document the procedure in a dedicated tab to ensure continuity if someone else takes over.
KPIs to include in your Excel spreadsheet
| Metric | Excel Formula | Example Cell |
|---|---|---|
| Total revenue | =SUM(C2:C1000) | C1001 |
| Average order value | =AVERAGE(C2:C1000) | C1002 |
| Number of orders | =COUNTA(A2:A1000) | C1003 |
| Cancellation rate | =COUNTIF(D2:D1000,"cancelled")/COUNTA(A2:A1000) | C1004 |
| Revenue per unique customer | =SUM(C2:C1000)/COUNTA(UNIQUE(B2:B1000)) | C1005 |
| MoM revenue growth | =(current_month-previous_month)/previous_month | C1006 |
| Repeat customer rate | =COUNTIF(F2:F1000,">1")/COUNTA(B2:B1000) | C1007 |
| Average gross margin | =AVERAGE((C2:C1000-G2:G1000)/C2:C1000) | C1008 |
Limitations of Excel for e-commerce analytics
Critical limitations of Excel for e-commerce
Excel was not designed for real-time e-commerce analytics. Four critical limitations directly affect the reliability of your decisions: no real-time data (exports are always delayed), high risk of manual data entry or copy errors, inability to consolidate multiple stores in a single spreadsheet without complex development, and no native RFM segmentation (Recency, Frequency, Monetary) which requires advanced and unstable ARRAYFORMULA formulas.
When to move from Excel to a dedicated tool
Two hours of manual data entry per week represents more than one hundred hours per year spent feeding a spreadsheet rather than analyzing and deciding. This figure grows proportionally with order volume and the number of channels to consolidate. When you encounter any of the following signals, it is time to move to a dedicated tool: managing more than two stores or channels, volume exceeding 500 orders per month, needing fresh data more often than once a week, wanting automatic customer segmentation, or having experienced at least one data entry error that led to a poor business decision.
Fullmetrix: the automatic dashboard that replaces Excel
Fullmetrix is an e-commerce analytics SaaS solution designed to automate everything Excel does manually. Connecting to your WooCommerce or PrestaShop store automatically synchronizes all your orders, products and customer data. The dashboard updates continuously, without CSV exports, without formulas to maintain and without risk of human error. Unlike a spreadsheet, Fullmetrix automatically calculates your RFM segmentation, identifies customers at churn risk, analyzes retention cohorts and consolidates multiple stores in a unified view.
FAQ: e-commerce Excel dashboard
Can Excel be connected directly to WooCommerce or Shopify?
It is possible to connect Excel to WooCommerce via Power Query and the WooCommerce REST API, but this requires advanced technical configuration and regular maintenance. Most merchants opt for manual or semi-automated CSV exports. Shopify offers native exports but without a stable direct connection to Excel without a paid third-party connector.
What is Excel's row limit for e-commerce data?
Excel supports up to 1,048,576 rows per worksheet. For a merchant generating 100 orders per day, that represents approximately 28 years of data before hitting the limit. The constraint is not row volume but performance: beyond 100,000 rows with complex formulas, Excel becomes slow and unstable on most standard hardware configurations.
How do you calculate customer retention rate in Excel?
Retention rate is calculated by identifying customers who placed at least two orders in a given period. In Excel, this requires a column counting the number of orders per customer (COUNTIF on customer ID), then a ratio between customers with more than one order and total unique customers. This formula remains approximate as it does not account for the delay between orders or seasonality.
Excel or Google Sheets for an e-commerce dashboard?
Google Sheets has an advantage for collaborative work and integration with Google Analytics via native add-ons. Excel is superior for processing large data volumes thanks to Power Query and Power Pivot. Both tools share the same fundamental limitations for e-commerce analytics: manual entry, no real-time data and no automatic segmentation.
Replace your Excel spreadsheet with an automatic dashboard
Fullmetrix connects your e-commerce store and automatically generates all the KPIs you track manually in Excel, in real time and without data entry.
View Fullmetrix pricing
