Setting Up Your First Dashboard in Google Sheets

By

Why Google Sheets Is the Right Starting Point

If you have been putting off building a business dashboard because you assume it requires expensive software or a developer, a well-structured Google Sheets file will change that assumption fast. This article walks you through setting up your first ROI dashboard from scratch—one that is practical enough to use every week and flexible enough to grow with your business.

Google Sheets earns its place here for a few straightforward reasons. It is free, it lives in the cloud so anyone on your team can access it, it connects to other Google products without extra configuration, and it is transparent—you can see every formula and understand exactly what the numbers mean. That transparency matters more than most people realize when you are trying to build trust in your own data.

Before You Open a Blank Sheet: Decide What You Are Actually Measuring

The most common mistake people make when building a dashboard is opening a blank spreadsheet and starting to type. Resist that impulse. Spend fifteen minutes answering three questions first:

  • What decisions will this dashboard inform? Pricing changes, ad spend adjustments, hiring decisions, product cuts—pick two or three specific decisions you make regularly.
  • What data do you already have? Bank statements, a POS system, an email platform, an ad account. List what exists and where it lives.
  • How often will you update it? Weekly is the practical sweet spot for most small businesses. Daily is usually too noisy; monthly is too slow to act on.

Your answers narrow the dashboard to what is genuinely useful. A marketing-focused business might track cost per lead, revenue by channel, and email conversion rate. A service business might track billable hours, average project margin, and client acquisition cost. There is no universal template that fits every situation, which is exactly why building your own beats downloading someone else’s.

Setting Up the File Structure

Create a new Google Sheets file and name it something unambiguous: Business ROI Dashboard — [Your Business Name]. Vague file names create confusion when you are sharing with a bookkeeper or a business partner six months from now.

Use multiple tabs, each with a clear role. A structure that works well for most small businesses looks like this:

  • Dashboard — the summary view you look at every week. Charts, key numbers, nothing else.
  • Revenue Data — raw revenue entries by date, source, and amount.
  • Cost Data — expenses broken into categories (marketing, operations, payroll, software, etc.).
  • Channel Performance — if you run paid ads or have multiple sales channels, this tab holds that breakdown.
  • Calculations — a working tab where your formulas live, separate from both the raw data and the display layer.

Keeping raw data, calculations, and display separate sounds like extra work, but it saves enormous debugging time later. When a number looks wrong, you know exactly which tab to check.

Building the Revenue and Cost Tabs

Start with the Revenue Data tab. Set up columns with consistent headers: Date, Source, Description, Amount, Category. The Source column is important—it is how you will eventually answer questions like “Which marketing channel brings in the most revenue?” Common sources include Direct, Referral, Google Ads, Organic Search, and Social Media. Use a dropdown list (Data → Data Validation → Dropdown) to enforce consistent entries. Inconsistent labels in this column are the single biggest cause of messy dashboards.

Do the same for Cost Data: Date, Vendor, Description, Amount, Category. Your cost categories should map roughly to how you think about your business. A simple starting set: Marketing, Software, Payroll, Rent/Facilities, Professional Services, Miscellaneous. Again, use a dropdown for Category.

Enter at least three months of historical data before you consider your dashboard live. With less than that, the numbers are too volatile to act on. If pulling historical figures manually feels overwhelming, start with the current month and one prior month, then fill in the rest as you have time.

Writing the Core Formulas

In your Calculations tab, build the metrics that will drive your Dashboard display. Here are the essential ones with concrete examples.

Total Revenue by Period

Use SUMIFS to pull revenue for a specific date range. For example, if your Revenue Data tab has dates in column A and amounts in column D, a formula like =SUMIFS('Revenue Data'!D:D,'Revenue Data'!A:A,">="&StartDate,'Revenue Data'!A:A,"<="&EndDate) will sum everything in the period. Define StartDate and EndDate as named ranges or simple cell references in your Calculations tab so you can change the period in one place and have every formula update automatically.

Total Costs by Period

The same SUMIFS logic applies to your Cost Data tab. Build one formula for total costs and additional formulas that filter by Category so you can see, for example, exactly how much went to marketing versus operations.

Gross Margin

Gross margin in its simplest form is (Revenue − Cost of Goods Sold) ÷ Revenue, expressed as a percentage. If you are a service business without direct product costs, you might substitute total costs to get a net margin figure instead. Either way, the formula in Sheets is straightforward: =(B2-B3)/B2 where B2 is revenue and B3 is the relevant cost figure. Format the cell as a percentage.

Return on Ad Spend (ROAS)

If you run any paid advertising, ROAS is worth tracking separately. It is simply Revenue attributed to ads ÷ Ad spend. A result of 3 means you earned three dollars for every dollar spent. Calculate this in your Calculations tab by pulling ad-sourced revenue from your Revenue Data (using SUMIFS filtered by Source = "Google Ads" or whichever channel) and dividing by the matching ad spend from Cost Data.

Customer Acquisition Cost (CAC)

Total marketing and sales spend divided by the number of new customers acquired in the same period. Track new customer counts in a simple column on your Revenue Data tab or in a separate small table. CAC is one of those metrics that looks fine in isolation and alarming in context—which is exactly why the dashboard format, where you see it alongside revenue and margin, is so valuable.

Building the Dashboard Tab

The Dashboard tab should be readable in under thirty seconds. That is your design constraint. Pull your calculated figures into a clean summary table at the top: current period revenue, costs, gross margin percentage, ROAS, and CAC. Below that, add two or three charts.

The most useful charts for an early-stage dashboard are:

  • Revenue over time — a simple line chart showing monthly or weekly revenue. Visual trend lines are faster to read than columns of numbers.
  • Revenue by channel — a bar or pie chart showing which sources are actually driving revenue. This one tends to produce the most immediate decisions.
  • Costs by category — a bar chart that shows where money is going. Most business owners are surprised by at least one category when they see this for the first time.

To insert a chart, select the relevant data range, go to Insert → Chart, and choose your chart type. Google Sheets will generate a reasonable default. Spend a few minutes in the chart editor to clean up labels and remove visual clutter—legends, axis titles, and a plain color scheme make charts easier to read than the defaults.

Use conditional formatting on your key metric cells to give yourself at-a-glance signals. For example, format the margin cell red if it falls below a threshold you define, and green if it is above a healthy target. These thresholds are personal to your business—there is no universal "good margin" that applies across industries.

Keeping It Maintained Without Letting It Become a Burden

A dashboard only has value if you actually update it. The most reliable system is a fixed weekly appointment—thirty minutes on the same day each week to enter new data and review the numbers. Some business owners tie this to their bookkeeping routine; others do it as a Monday morning ritual before the week gets busy. The specific timing matters less than the consistency.

Automate what you can. Google Sheets can connect to external services through tools like Zapier or Make, which can push data from your ad platforms, CRM, or payment processor directly into your spreadsheet without manual entry. Start manual so you understand the data, then automate the repetitive parts once the structure is stable.

Review the dashboard structure itself every quarter. As your business changes, the metrics that matter change too. A dashboard built when you had one revenue channel needs to evolve when you add a second. Treat the file as a living document, not a finished product.

Your Practical Starting Point

Create the file today with the tab structure described above. Enter this month's revenue and costs, write the three core formulas—total revenue, total costs, and gross margin—and build one chart. A simple, working dashboard is worth more than a perfect one you never finish. Once the basic version is running and you are looking at real numbers weekly, you will know exactly what to add next.

Related reading

Similar Posts