How Do You Build a Medical Practice Dashboard in Google Sheets With AI?

How Do You Build a Medical Practice Dashboard in Google Sheets With AI?

Most practice managers already have the numbers. What they do not have is one screen that shows them.

The data sits in three places at once: appointment counts in the scheduling system, collections in the billing platform, and provider productivity in whatever spreadsheet someone built in 2019.

Google Sheets is an unglamorous answer to that problem, and a surprisingly good one. It is already in most practices, it costs nothing extra, and since 2026 it has an AI assistant that will build the charts for you.

There is one hard constraint, and it shapes everything below: a spreadsheet prompt is not the place for patient data. Build the dashboard on de-identified aggregates and it works. Build it on a patient list and you have created a compliance problem instead of a report.

The Short Answer

You build it in three layers. A raw tab holding exported aggregate counts, a calculation tab holding the metrics, and a dashboard tab holding the charts.

Then you use the Ask Gemini side panel in Google Sheets to write the formulas, build the pivot tables and generate the charts from plain-language prompts.

Gemini never sees a patient name, because the raw tab only ever contains totals: appointments by week, no-shows by provider, days in accounts receivable, collections by payer class.

That restriction is not a workaround. It is also the thing that makes the dashboard useful, because a practice owner wants the trend, not the chart review.

Is your practice website showing up when patients search? Get a free SEO audit of your site and see exactly which local searches you are missing. Request your free audit.

What Does Gemini in Google Sheets Actually Do?

Google calls the feature Gemini in Sheets, and you reach it through the Ask Gemini button in the top right of the spreadsheet.

It opens a side panel that takes natural-language instructions and acts on the sheet directly rather than just answering questions about it.

The Parts That Matter for a Dashboard

Four of its capabilities do almost all the work on a project like this one.

It writes formulas from a description, which saves you the QUERY and SUMIFS syntax that most practice managers never learn.

It builds pivot tables on request, which is how you turn a flat export of appointment rows into counts by provider and month.

It generates charts from a prompt, so "show no-show rate by provider as a column chart" produces a chart instead of a tutorial.

And it applies sheet-level actions: conditional formatting, dropdowns, sorting, filtering, number formats and frozen header rows.

Who Can Actually Use It

This is the part worth checking before you plan a project around it. Gemini in Sheets requires an eligible Google Workspace or Google AI plan.

Google's April 2026 rollout of the build-and-edit-whole-spreadsheets capability listed Business Standard and Plus, Enterprise Standard and Plus, the AI add-ons, and consumer Google AI Pro and Ultra.

Google also noted at that launch that the feature was available in the US in English only, with language support expanding afterwards.

If your practice runs on the free consumer version of Google Sheets, the side panel will not be there, and the rest of this guide still works manually.

How Do You Build It, Step by Step

The build takes about an hour the first time and twenty minutes a month afterwards.

1. Export aggregates, not records. In your practice management system, run the summary reports rather than the patient-level ones. You want counts and totals by week, provider and payer.
2. Create three tabs. Name them Raw, Calc and Dashboard. Keeping them separate means a messy monthly paste never breaks a chart.
3. Paste the export into Raw. Put each report in its own block of columns with a clear header row. Resist the urge to tidy it by hand.
4. Ask Gemini to build the calculation layer. Open the side panel and describe the metric in words: "on the Calc tab, calculate the no-show rate per provider per month from the Raw tab."
5. Check every formula it writes. Click the cell and read the formula. Gemini is good at SUMIFS and occasionally wrong about which column you meant.
6. Ask for the charts one at a time. A prompt per chart gives you cleaner output than one prompt asking for a whole dashboard, and it is easier to correct.
7. Move the charts onto the Dashboard tab. Gemini inserts a generated chart on a new tab with its own copy of the data, so you will be relocating them.
8. Add a date note and freeze the top row. A dashboard with no "data through" date gets misread within a month. Ask Gemini to apply the formatting.
9. Share it as view-only. Give the practice owner and the physicians a view link. Edit access on a reporting file is how formulas quietly die.

Want more patients finding you before they find the practice down the road? A free audit shows you where your local visibility is leaking. Get your free audit.

Which Metrics Belong on a Practice Dashboard?

Keep it to eight or fewer. A dashboard that shows everything gets looked at once.

These are the ones practice owners actually act on, and all of them are aggregates that need no patient-level data at all.

Appointment volume by week, split by new versus established patients, which is the clearest early signal of growth or decline.

No-show and cancellation rate by provider and by day of week, because the pattern is almost always schedule design rather than patients.

Days in accounts receivable, the single best measure of whether your billing is working, tracked as a trend rather than a snapshot.

Collections against charges by payer class, which exposes the contract that is quietly underpaying you.

Provider productivity as encounters per clinical session, normalised so a part-time provider does not look like a problem.

Chair or room utilisation, the percentage of available slots that were booked, which tells you whether to hire or to market.

Claim denial rate with the top three denial reasons, since three reasons usually explain most of the total.

New patient source mix, if your intake captures it, because that is the number that connects marketing spend to revenue.

The Rule That Keeps This Out of Trouble

This section matters more than the chart formatting, so it is worth being blunt about it.

Google's own help documentation for Gemini in Sheets tells users not to enter personal, confidential or sensitive information in prompts. Take that at face value.

Separately, Google's HIPAA guidance for Workspace is explicit that administrators must review and accept a Business Associate Agreement before protected health information is used in Google services at all.

The same guidance states that customers who have not signed a BAA with Google must not use PHI in Google Workspace services, and that third-party applications and add-ons fall outside the agreement's coverage.

So the safe pattern is simple. The spreadsheet holds counts, rates, totals and averages. It never holds names, dates of birth, medical record numbers, diagnoses or anything that identifies a person.

If a cell could be traced to one patient, it belongs in your practice management system, not in a reporting spreadsheet and certainly not in a prompt.

Small cell counts deserve a second thought too. A single no-show in a one-provider clinic on a known day is arguably identifying even without a name attached.

The practical test: would you be comfortable showing this tab on a screen in your waiting room? If not, aggregate it further.

Your compliance is sorted. Is your search visibility? Find out what patients see when they search for your specialty in your city. Run a free audit.

Limitations Worth Knowing Before You Commit

Google Sheets is the right tool for a practice of two to twenty providers. It has real limits, and knowing them up front saves rework.

Generated charts do not stay linked to your source data. Google documents this directly: a chart Gemini produces does not respond to changes in the data set used to create it.

That is the single biggest gotcha. It means a dashboard built entirely from AI-generated charts will not refresh when you paste next month's numbers.

The fix is to have Gemini build the chart once, then rebuild it as a native Sheets chart pointing at your Calc range, so it updates on every paste.

The conversation is not persistent. Reload the browser or reopen the file and the side-panel history can disappear, so insert output into the sheet rather than leaving it in the chat.

Nothing is automated. There is no connector pulling from your practice management system, so someone still exports and pastes once a month.

It does not scale past a few thousand rows comfortably. If you are tracking encounter-level data across multiple locations, you have outgrown this approach.

It cannot be your source of truth. A spreadsheet dashboard is a reporting layer over the real system, and should never become the place a number is first recorded.

AI output needs review every time. Google's own interface warns that generated content may be inaccurate, and a wrong denominator in a rate calculation is easy to miss.

Google Sheets With AI vs the Alternatives

Four options are realistic for a practice this size, and the right answer depends more on your staffing than your data.

Google Sheets with Gemini wins on cost and speed. You already own it, nobody needs training, and you can have something usable this afternoon.

Your practice management system's built-in reporting wins on accuracy, because the numbers come from the source and refresh automatically. It usually loses on flexibility.

Looker Studio is the natural next step once the spreadsheet starts creaking. It connects to Sheets directly, refreshes on its own, and is free.

Power BI or Tableau make sense at multi-location scale or when you need row-level security across departments. Below that, the licensing and the learning curve are hard to justify.

A reasonable path is to build in Sheets, prove which eight metrics people actually use, then rebuild just those in Looker Studio once the manual paste becomes annoying.

Most practices never need to make that move. A one-page Sheets dashboard that gets looked at every Monday beats a sophisticated one that nobody opens.

One free audit, one clear list of fixes. See how your practice site performs in local and AI search. Claim your free SEO audit.

Final Thoughts

A practice dashboard is not a data project. It is a habit, and the tooling only matters in so far as it makes the habit easy to keep.

Google Sheets with Gemini is good at exactly that. It removes the formula writing and the chart fiddling, which are the two reasons these projects usually stall after month two.

Keep it to aggregates, keep it to eight metrics, rebuild the AI-generated charts as native ones so they refresh, and put a date on the page.

If you are weighing this against a heavier tool, our guide to building a dynamic dashboard in Power BI with AI covers the same ideas at the other end of the scale.

And once the numbers are visible, the next question is usually where new patients are coming from, which is a search visibility problem rather than a spreadsheet one.