Graphed LogoGraphed
Login

How to Create a SaaS Dashboard in Excel

Cody Schneider•

Building a dashboard for your SaaS company doesn't require expensive, complicated software right out of the gate. You can create a powerful, insightful dashboard using a tool you already know and own: Microsoft Excel. This guide will walk you through the essential SaaS metrics you should be tracking and then show you, step-by-step, how to build a dashboard in Excel to visualize them.

Why Start with an Excel Dashboard?

While dedicated business intelligence tools are powerful, Excel is often the most practical place to start for early-stage SaaS businesses, marketers, and founders. It’s familiar, flexible, and forces you to get hands-on with your data. The process of building a dashboard forces you to understand the fundamental calculations behind your metrics, giving you a much deeper understanding of your business's health.

The main goal is to create a single source of truth that helps you move from raw data to actionable insights without a steep learning curve or a hefty price tag.

Essential Metrics for a SaaS Dashboard

Before you build anything, you need to know what you’re going to measure. A dashboard is only as useful as the metrics it displays. For a SaaS business, your focus should be on growth, customer value, and retention.

Here are the core metrics every SaaS dashboard should include:

Monthly Recurring Revenue (MRR)

MRR is the lifeblood of any subscription business. It’s the total of all recurring revenue you expect to receive in a given month. It should include recurring charges from subscriptions but exclude one-time fees, setup costs, and usage-based charges.

  • Why it matters: MRR is your primary top-line metric for tracking growth and momentum.

Customer Lifetime Value (LTV)

LTV represents the total revenue you can expect to earn from a single customer account throughout their entire relationship with your company. A simple way to calculate it is by dividing your Average Revenue Per Account (ARPA) by your Customer Churn Rate.

  • Why it matters: LTV helps you understand how much you can afford to spend on acquiring new customers and informs decisions on pricing and retention strategies.

Customer Acquisition Cost (CAC)

CAC is the total cost of your sales and marketing efforts required to acquire a new customer. To calculate it, you sum up all your sales and marketing expenses over a given period and divide it by the number of new customers acquired during that same period.

  • Why it matters: Tracking CAC ensures you are acquiring customers profitably. If your CAC is higher than your LTV, your business model isn't sustainable.

LTV:CAC Ratio

This ratio compares a customer's lifetime value to the cost of acquiring them. It's a critical indicator of your sales and marketing ROI and overall business viability. A healthy ratio for a SaaS business is generally considered to be 3:1 or higher - meaning a customer's value is at least three times the cost to acquire them.

  • Why it matters: This single metric tells you if you have a profitable marketing and sales engine. A ratio below 1:1 means you're losing money on every new customer.

Customer Churn Rate

Customer Churn is the percentage of customers who cancel their subscriptions during a specific period. It's calculated by dividing the number of customers who churned in a period by the total number of customers at the start of that period.

  • Why it matters: High churn can silently kill a SaaS business. It indicates issues with your product, pricing, customer service, or overall value proposition.

Net MRR Churn

While Customer Churn tracks lost customers, Net MRR Churn tracks lost revenue. It is the MRR lost from churned customers minus any new revenue from existing customers (upgrades or cross-sells). It's possible to have a negative Net MRR Churn, which is a powerful sign of a healthy, growing business (this is called "net negative churn").

  • Why it matters: This metric gives a more complete picture of your revenue trajectory than customer churn alone. It shows whether expansion revenue from happy customers is offsetting the revenue lost from those who cancel.

Step-by-Step Guide to Building Your Dashboard in Excel

Now, let's get into the step-by-step process of building your dashboard. This guide assumes you can export your raw data from sources like Stripe, a CRM (like Salesforce), or your payment processor into a CSV or Excel file.

Step 1: Get Your Data Ready

Your dashboard can't exist without clean, well-structured data. Start by creating an Excel workbook with three tabs:

  1. Raw Data: This is where you'll paste your exported data. It should be in a tabular format.

  2. Calculations: This will be your "engine room." You'll use this tab to calculate all your key metrics.

  3. Dashboard: This is the final, visual front-end that everyone will see.

Your Raw Data tab should, at a minimum, include columns like:

  • Customer ID

  • Signup Date

  • Plan Type

  • Monthly Price (MRR per customer)

  • Customer Status (Active/Churned)

  • Churn Date (if applicable)

Keep this data raw. Don't add formulas or change formatting here. Each time you update your data, you'll simply replace the contents of this sheet.

Step 2: The 'Calculations' Tab

This is where the magic happens. On your Calculations tab, you'll summarize your raw data into the metrics you need. The goal is to create small summary tables that will feed the charts and KPI cards on your dashboard.

Let's use some common formulas to calculate our key metrics, assuming your raw data is in a table named CustomerData on the 'Raw Data' tab.

Calculating Total MRR

Find an empty cell on your 'Calculations' sheet and enter this simple SUMIF formula:

Calculating Total Active Customers

Similarly, use COUNTIF to get a count of all active customers:

Monthly Trend Table

To create trends over time, you need a summary table with months down the first column. Then, use SUMIFS and COUNTIFS to calculate metrics for each specific month.

For example, to calculate the MRR for January 2024 (assuming you have a 'Signup Date' column), the formula would look something like this:

Building out a table like this will fuel your line charts and monthly trend visualizations.

Step 3: Lay Out Your Dashboard

Switch over to your Dashboard tab. This sheet should be clean and easy to read. You can use cell fills and borders to create a visual structure. A common layout involves having key "KPI Cards" at the top and charts below.

For your KPI cards, you'll simply reference the cells from your Calculations tab. In a cell on your dashboard, type = and then click on the cell in the Calculations tab that holds your Total MRR value. Hit enter. Now, your dashboard will automatically update whenever the calculation changes.

A good starting layout includes:

  • A row of KPI Cards at the top for big numbers like Total MRR, Active Customers, and Net MRR Churn.

  • A larger area for monthly trend charts, like MRR Growth Over Time.

  • Sections for breakdown charts, like MRR by Plan Type.

Step 4: Create Data Visualizations (Charts)

Visuals make your data much easier to digest. Excel's charting capabilities are more than enough to get started.

Creating an 'MRR Over Time' Line Chart:

  1. Go to your Calculations tab and highlight the monthly trend table you created (the one with months and their corresponding MRR).

  2. Click on the Insert tab in the Excel ribbon.

  3. Choose a Line chart. A 2D line chart is usually best.

  4. Cut (Ctrl+X) the newly created chart and paste (Ctrl+V) it onto your Dashboard tab.

  5. Resize and position it as needed. You can use the Chart Design options to remove clutter like gridlines and customize the colors to match your brand.

Repeat this process for other visuals:

  • A bar chart to compare New Customers vs. Churned Customers month-over-month.

  • A pie or donut chart to show the breakdown of MRR by pricing plan.

  • A single gauge chart (or even just styled numbers) for your LTV:CAC ratio.

Step 5: Add Interactivity with Slicers

Slicers are a fantastic way to make your Excel dashboard interactive without any complex coding. They are essentially clickable filters for your charts and tables.

  1. First, make sure your table in the Raw Data tab is formatted as an official Excel Table (highlight the data and press Ctrl+T).

  2. Go to your Dashboard sheet. Select one of your charts.

  3. On the ribbon, go to PivotChart Analyze > Insert Slicer.

  4. A dialog box will appear showing all the columns from your CustomerData table. Check the box for a column you'd want to filter by, like 'Plan Type'.

  5. A slicer will appear on your dashboard. Now, whenever you have a chart selected and click a plan type on the slicer, the slicer will connect only to that chart.

  6. To connect a slicer to multiple charts, right-click the slicer, go to Report Connections, and check the boxes for all the PivotTables/PivotCharts you want it to control.

Tips for an Effective Excel Dashboard

  • Keep it Simple: Don't try to cram every possible metric onto one screen. Focus on the core metrics that drive your business. A cluttered dashboard is an ignored dashboard.

  • Define Your Update Cadence: The biggest drawback of an Excel dashboard is manual updates. You must be disciplined about exporting fresh data and pasting it into your 'Raw Data' tab weekly or monthly.

  • Protect Your Structure: Once your dashboard is built, protect the 'Calculations' and 'Dashboard' sheets to prevent accidental edits. You can do this under the 'Review' tab.

Final Thoughts

Building a powerful SaaS dashboard in Excel is a perfectly achievable goal that gives you a deep understanding of your business's core performance drivers. It’s an effective and accessible way to track your most important metrics by organizing your data, calculating KPIs, and turning them into clear, actionable charts.

Eventually, the manual process of downloading CSVs and updating your Excel sheets will become a bottleneck. When that happens, you may find that you spend more time updating reports than analyzing them. We built Graphed to solve exactly this problem. Instead of manual exports, we connect directly to your data sources like Shopify, Google Analytics, Salesforce, and Stripe. You can create always-on, real-time dashboards just by describing what you want to see in simple language, letting you focus on strategy instead of spreadsheet management.