How to Create an E-commerce Dashboard in Excel

Running an ecommerce store often feels like you're juggling a dozen different browser tabs just to understand your business health. You've got Shopify open for sales, Google Analytics for traffic, and your ads manager for campaign performance. This article will show you how to bring your most important metrics together by building a simple, powerful ecommerce dashboard right in Microsoft Excel.

Why Bother with an Excel Dashboard?

Before we get into the nuts and bolts, let's talk about why this is worth your time. A well-built dashboard isn't just a pretty report, it's a command center for your business. It allows you to:

Centralize Your Key Metrics. Instead of bouncing between platforms, you get a single snapshot of your most important data - sales, analytics, marketing spend, and more, all in one place.
Spot Trends Instantly. Is this month's revenue up or down compared to last month? Which marketing channel is actually driving a return? A dashboard makes these trends obvious at a glance.
Make Data-Driven Decisions. A clear view of your performance helps you move beyond guesswork. You can confidently decide where to invest your marketing budget or which products to promote.
Communicate Performance. Whether you're reporting to a boss, a client, or just keeping your team in the loop, a dashboard provides a clear, concise way to share business performance.

Excel is a great starting point for this because it's a tool most people already have and are somewhat familiar with. Let's get started.

Building Your Dashboard: A Step-by-Step Guide

We'll break down the process into four manageable steps: defining your metrics, gathering your data, performing the analysis, and finally, building the visuals.

Step 1: Define Your Key Metrics (KPIs)

You can track hundreds of metrics, but a dashboard that shows everything shows nothing. The goal is to focus on the Key Performance Indicators (KPIs) that are most critical to your ecommerce success. Don't overcomplicate it at first. Start with a handful of essential metrics across different areas of your business.

Here are a few classic examples to consider:

Sales & Revenue KPIs

Total Revenue: The total amount of money generated from sales.
Number of Orders: The raw count of transactions.
Average Order Value (AOV): The average amount customers spend per order. (Total Revenue / Number of Orders)
Top 5 Selling Products: Which items are your biggest moneymakers?

Marketing & Traffic KPIs

Sessions by Source: Where is your website traffic coming from? (e.g., Google Organic, Facebook Ads, Email)
Conversion Rate: The percentage of website visitors who complete a purchase. (Number of Orders / Total Sessions)
Customer Acquisition Cost (CAC): How much does it cost you to acquire a new customer? (Total Marketing Spend / New Customers Acquired)

Choose 5-7 of these to start. Picking the right KPIs is about answering the question: "What numbers do I need to look at every morning to know if my business is healthy?"

Step 2: Gather and Organize Your Data

This is where the manual work begins. You'll need to export data from your primary sources, usually as CSV (Comma Separated Values) files, which Excel opens perfectly.

Export Your Data:
- From Shopify (or your ecommerce platform): Export your sales report for a specific date range (e.g., the last 90 days). Make sure to include columns for Order Date, Order ID, Revenue, Product, etc.
- From Google Analytics: Export a traffic acquisition report for the same date range. This should show you sessions broken down by source/medium.
Create Your Excel Workbook:
- Start a new Excel file. Create two tabs and name them "Raw Data" and "Dashboard".
- On the "Raw Data" tab, paste your exported data. It's best practice to consolidate your key data points into a single, clean table. For example, your table might have columns like: Date, Orders, Revenue, Sessions and Traffic Source. You might have to do some manual VLOOKUPs or copy-pasting to combine sales and traffic data by date.
- Once your data is in a single table, click anywhere inside it and press Ctrl+T (or on Mac, Cmd+T) to format it as an official Excel Table. Give it a name like "PrimaryData" in the "Table Design" tab that appears. This step is huge, as it allows your formulas and charts to update automatically when you add new data later on.

Your "Raw Data" tab now acts as the single source of truth for your dashboard.

Step 3: Create a Calculation/Analysis Tab

To keep things tidy, it's best to perform calculations on a separate tab rather than directly on your raw data sheet or your final dashboard. Create a new tab called "PivotTables".

PivotTables are Excel's most powerful feature for summarizing large datasets. They are the engine of your dashboard, doing all the heavy lifting in the background.

Click anywhere in your data table on the "Raw Data" tab.
Go to the Insert tab in the ribbon and click PivotTable.
Excel will automatically select your data table. In the dialogue box that pops up, choose to place the PivotTable in your existing "PivotTables" sheet.
Now, you can start summarizing! For example, to summarize Revenue by Date:
- Drag the Date field into the Rows area.
- Drag the Revenue field into the Values area.

Voilà! You have a summary of total revenue for each day. Create a few different PivotTables on this sheet, one for each KPI or chart you plan to build. For example, you might create another PivotTable to summarize 'Sessions by Traffic Source' to power that chart.

For standalone metrics like Total Revenue or AOV, you can create a simple summary area. In a few cells, use formulas that reference your PivotTables or your raw data table. For example:

=GETPIVOTDATA("Sum of Revenue", A3) — Pulls the grand total from your pivot table

=B1/C1 — (Where B1 is Total Revenue and C1 is Total Orders to calculate AOV)

Step 4: Design the Dashboard Tab

This is the fun part where everything comes together visually. Click over to your empty "Dashboard" tab.

Create KPI Cards

For your main numbers like Total Revenue and AOV, you don't always need a chart. Simple "cards" work great. Just select a cell, increase the font size way up, and make it reference your calculations on the "PivotTables" tab. For example, in cell B2, type = and then click the cell on your PivotTables tab that contains your total revenue figure. Add a text label above it, apply some cell borders and background color, and you've got a great-looking KPI card.

Build Your Charts

Now, let’s add some visuals. For each chart, click on the PivotTable that summarizes the data you want to visualize.

Go to the PivotTable Analyze tab and click on PivotChart.
Choose the best chart type for your data:
- Line Chart: Perfect for showing trends over time, like daily revenue or sessions.
- Bar Chart: Great for comparing categories, such as sessions by traffic source or sales by product.
- Pie Chart: Use sparingly! It's okay for showing parts of a whole if you have fewer than five categories (e.g., breakdown of revenue by top 3 product categories).
Once a chart is created, clean it up. Give it a clear title, remove unnecessary labels (called "field buttons") by right-clicking them and selecting "Hide," and adjust the colors to match your brand.
Cut (Ctrl+X) the finished chart from the "PivotTables" sheet and Paste (Ctrl+V) it onto your "Dashboard" sheet. Arrange your charts and KPI cards neatly on the grid.

Make it Interactive with Slicers

Slicers are basically user-friendly filter buttons that let you (or your team) drill down into the data without needing to be an Excel wizard.

Click on any of your PivotCharts on the dashboard.
Go to the PivotTable Analyze tab and click Insert Slicer.
Check the box for the field you want to filter by, like 'Date' or 'Traffic Source'.
Now you have a clickable slicer! To connect it to your other charts, right-click the slicer, go to Report Connections, and check the boxes for all the PivotTables you want it to control. Now, clicking a date range on the slicer will update all of your charts at once.

The Obvious Limitations of Excel

An Excel dashboard is a fantastic achievement, but it's important to be realistic about its drawbacks, especially as your business grows.

It's Still Manual: This dashboard is not live. To update it, you have to repeat the entire process: re-export all your CSVs, copy-paste them into your "Raw Data" tab, and then right-click on your PivotTables and hit "Refresh." This weekly reporting chore - downloading on Monday, analyzing Tuesday, fixing it Wednesday - eats up valuable time.
It's Prone to Errors: A simple copy-paste mistake, a deleted cell, or a broken formula can wreck your entire dashboard, leading you to make decisions based on bad data.
Data Silos Remain: While this dashboard combines some data, it's still hard to connect performance across the entire customer journey (e.g., seeing which specific ad campaign led to a sale of a specific product).
It Can Be Slow: As your order volume and traffic grow, your Excel file can become massive and slow, making even simple refreshes a frustrating experience.

Final Thoughts

Building an ecommerce dashboard in Excel is an empowering first step toward becoming a more data-driven store owner. It forces you to define your core metrics and gives you a centralized place to monitor performance, pulling you out of the weeds of day-to-day platform management.

Once you get tired of that manual refresh routine, you'll probably look for a more automated solution. That's actually why we built Graphed. We automate the entire process by connecting directly to your tools like Shopify, Google Analytics, and Facebook Ads. We pull your data into a single place automatically, ensuring your dashboards are always live and up-to-date. You can even build dashboards and ask questions using plain English, skipping the PivotTables and formulas entirely so you can get an immediate answer and get back to growing your business.