Most practice owners fly blind. The schedule lives in one tab, the billing export lives in another, the front desk keeps a no-show list on a sticky note, and the answer to "how did we actually do last week?" takes someone an hour to assemble by hand. By the time the report exists, the week is over and the moment to act has passed.

A clinic dashboard fixes that. It is a single screen that reads from your raw data and answers the questions you ask every Monday morning: how many patients did we see, how much did we bill, how many slots went empty, and which provider is carrying the load. You do not need new software to build it. If your appointments and billing already live in spreadsheets, Google Sheets can turn them into a dashboard that updates itself.

This guide walks through how to create a dashboard in Google Sheets for a medical practice, with real formulas you can paste in. If you want to skip the build, you can start from our free clinic dashboard template and wire it to your own data.

What a clinic dashboard should show

A good healthcare dashboard in Google Sheets answers questions, it does not just display numbers. Before you touch a formula, decide which metrics earn a spot on the screen. For most small practices, the core set is short:

  • Visits this week: completed appointments in the current period, with last week for comparison
  • Revenue: amount billed and amount collected, so you can see the gap
  • No-show rate: no-shows and cancellations as a share of booked appointments
  • New vs returning patients: how much of your volume is growth versus your existing base
  • Accounts receivable (A/R): outstanding balances, ideally bucketed by how old they are
  • Provider utilization: visits or billed hours per provider against their available capacity

Resist the urge to add more. A dashboard that shows fifteen numbers shows nothing, because no one knows where to look. Pick six to eight, make them large, and leave the deep detail in the underlying tabs.

Step 1: structure your data

The single most important rule: raw data and the dashboard never share a tab. Keep your records on their own clean sheets, and let the dashboard read from them. A typical setup has two source tabs.

An Appointments tab, one row per appointment, with columns like Date, Patient ID, Provider, Status (Completed, No-Show, Cancelled), and Patient Type (New, Returning). A Billing tab, one row per charge, with columns like Date, Patient ID, Provider, Amount Billed, Amount Paid, and Status (Paid, Unpaid).

Separation matters for a few practical reasons. Your front desk can keep typing into the Appointments tab without ever risking a dashboard formula. You can rebuild or restyle the dashboard without touching a single record. And when something looks wrong, you can trace a number straight back to the rows that produced it. Treat the source tabs as the system of record and the dashboard as a read-only view.

If you find yourself typing a patient name directly onto the dashboard, stop. That number belongs on a data tab, and the dashboard should be pulling it.

One more habit that pays off: format your Date columns as real dates, not text, and keep status values consistent. "No-Show" and "no show" are different strings to a formula, and inconsistent labels are the most common reason a clinic dashboard quietly reports the wrong totals.

Step 2: build the KPI tiles

The headline numbers across the top of your dashboard are just well-aimed COUNTIFS and SUMIFS formulas. Each one counts or sums rows from a source tab that match a set of conditions. Put your reporting window in two cells, say B1 for the start date and B2 for the end date, and reference them so the whole dashboard shifts when you change the dates.

Visits completed in the window, billed and collected revenue, and the no-show rate all follow the same pattern:

KPI tiles: COUNTIFS and SUMIFS // Completed visits in the reporting window =COUNTIFS( Appointments!D:D, "Completed", Appointments!A:A, ">="&$B$1, Appointments!A:A, "<="&$B$2) // Revenue billed in the window =SUMIFS( Billing!D:D, Billing!A:A, ">="&$B$1, Billing!A:A, "<="&$B$2) // Revenue collected in the window =SUMIFS( Billing!E:E, Billing!A:A, ">="&$B$1, Billing!A:A, "<="&$B$2) // No-show rate = no-shows / booked appointments =IFERROR( COUNTIFS(Appointments!D:D, "No-Show", Appointments!A:A, ">="&$B$1, Appointments!A:A, "<="&$B$2) / COUNTIFS(Appointments!A:A, ">="&$B$1, Appointments!A:A, "<="&$B$2), 0)

The IFERROR wrapper on the no-show rate matters: in a week with zero appointments you would otherwise divide by zero and get an ugly error sitting on your dashboard. Format that cell as a percentage. New vs returning patients is the same idea, counting on the Patient Type column. Provider utilization starts as a per-provider visit count, which you can divide by that provider's available slots once you know their capacity.

Step 3: add trends

A single number tells you where you are. A trend tells you which way you are moving, and that is usually the more useful signal. Two tools cover almost everything: SPARKLINE for an inline mini-chart, and QUERY for a summary table that does the grouping for you.

A sparkline for weekly visits

A SPARKLINE draws a tiny chart inside one cell. Point it at a row or column of weekly totals and you get an at-a-glance trend line next to your headline number, no chart object to manage.

SPARKLINE: weekly visit trend // Assumes a helper row of the last 12 weekly visit // totals sits in F10:Q10 =SPARKLINE(F10:Q10, {"charttype","line"; "linewidth", 2; "color", "#0F9D58"}) // A bar-style version for weekly revenue in F11:Q11 =SPARKLINE(F11:Q11, {"charttype","column"; "color", "#0F9D58"; "empty", "zero"})

A QUERY summary table

To build the weekly totals those sparklines read from, or to show a provider breakdown, let QUERY do the grouping. It runs a SQL-like statement over your data and returns a small summary table. Here it groups billing by provider and sums what each one brought in, sorted from highest to lowest:

QUERY: revenue by provider =QUERY(Billing!A:F, "SELECT C, SUM(D), SUM(E) WHERE A >= date '"&TEXT($B$1,"yyyy-mm-dd")&"' AND A <= date '"&TEXT($B$2,"yyyy-mm-dd")&"' GROUP BY C ORDER BY SUM(D) DESC LABEL C 'Provider', SUM(D) 'Billed', SUM(E) 'Collected'", 1)

The TEXT($B$1,"yyyy-mm-dd") piece converts your date cells into the exact format QUERY expects after the date keyword. This one table gives you provider utilization and a billed-versus-collected gap in a single block that refreshes the moment new billing rows arrive.

Step 4: make it readable

The math can be perfect and the dashboard still useless if it is hard to read. A few finishing moves separate a real medical practice dashboard from a wall of numbers.

Conditional formatting for targets. Select your no-show rate cell, open Format then Conditional formatting, and add a rule: greater than 0.1 turns the cell red, otherwise green. Now a 10 percent no-show threshold is something you see, not something you have to calculate. Do the same for A/R buckets and collection rates.

Number formatting. Show revenue as currency, rates as percentages, and counts as plain integers. A KPI that reads $48,200 lands instantly; the same value as 48200.0 makes a reader stop and decode it.

A clean layout. Put the six to eight KPI tiles in a single row or a tidy grid across the top, trends in the middle, and the QUERY tables below. Use a little whitespace and one accent color, the clinic green works well, so the eye knows where to land first.

Freeze panes. If your dashboard scrolls, freeze the top rows with View then Freeze so the headline KPIs and your date controls stay visible while you scroll through provider detail.

Headline KPI row
Six to eight big numbers across the top: visits, billed, collected, no-show rate, new patients, A/R.
Trend strip
Sparklines for weekly visits and revenue, sitting right beside the numbers they summarize.
Provider table
A QUERY block ranking providers by billed and collected, so utilization is obvious at a glance.
Date controls
Two cells for start and end date that every formula references, so the whole view shifts at once.

Step 5: keep it live and shareable

The payoff of this structure is that the dashboard maintains itself. Because every tile and table reads from the Appointments and Billing tabs, the moment your front desk marks an appointment Completed or your biller logs a payment, the dashboard recalculates. There is no refresh button to remember and no weekly report to rebuild.

To share it safely, give viewers read-only access while you keep edit rights to yourself. Use the Share dialog and set people to Viewer, or build a dashboard-only spreadsheet that pulls from your protected source workbook with IMPORTRANGE so partners never see raw patient rows. If your records contain protected health information, keep all of this inside a Google Workspace account covered by a Business Associate Agreement and limit access tightly.

For a true hands-off setup, a short Apps Script function can email a snapshot on a schedule. A time-driven trigger running every Monday can capture the dashboard range as a PDF and send it to the owners, so the weekly numbers land in an inbox without anyone opening the file. That is the difference between a spreadsheet someone has to check and a dashboard that reports to you.

If you would rather start from something that already works, our free clinic dashboard template ships with the data tabs, KPI tiles, and QUERY summaries already wired up. Swap in your own appointments and billing, and you have a live medical practice dashboard the same afternoon.