How to create a Google Sheets dashboard that updates automatically

To create a Google Sheets dashboard, keep raw records on a data tab, calculate each metric on a second tab with SUMIFS, QUERY or a pivot table, and build charts and scorecards from those results on a third tab. Because every number is a formula, the dashboard updates itself whenever the data changes.

This guide is for whoever keeps the numbers at a small or mid-size business and wants one page that shows how things are going, without hiring a developer. It covers the build in eight steps, how to keep new data flowing in without copy and paste, the limits you’ll run into, and when a dashboard app with sign-in is the better tool.

Reviewed October 2026 against Google’s own help pages.

What is a Google Sheets dashboard?

A Google Sheets dashboard is a tab that turns raw records into the few numbers and charts people check every week: revenue this month, open orders, overdue tasks, spend against budget. It reads from the other tabs in the file, or from other files, so nobody has to rebuild it by hand before Monday’s meeting.

You can create a dashboard in Google Sheets with nothing but built-in tools. Anyone with a Google Account can use Sheets, and everything in the eight steps below comes with it.

Dashboards that hold up over time use three tabs:

Tab	What goes on it	Who edits it
Data	One record per line, one column per detail, no totals	The people, forms or imports that add records
Summary	Formulas and pivot tables that calculate each metric	You
Dashboard	Charts, scorecards and filters that read from Summary	Nobody, because it’s protected

Keeping the three apart is what makes the dashboard trustworthy. People can add records without touching a formula, and you can change a chart without touching the data.

Data One record per line, no totals

Rep Status Amount

Ana Paid 1,200

Ben Open 860

Ana Late 430

People, forms and imports add records

Summary Formulas add up each status

Status Amount

Paid 1,200

Open 860

Late 430

Only you edit the formulas

Dashboard Charts and scorecards read from Summary

Paid revenue 1,200 Amount by status

Paid 1,200

Open 860

Late 430

Protected, so nobody edits it

One naming trap: Google Sheets also has an Activity dashboard, under Tools > Activity dashboard. That one shows who has viewed the file, not your business data.

Google Sheets dashboard examples

Four common examples, with the formula that does most of the work:

Dashboard	Scorecards	Charts	Formula doing the work
Sales	Revenue vs target, win rate	Deals by stage, monthly revenue	SUMIFS by stage and month
Orders	Open, late, days to ship	Open orders by rep, 10 oldest (table chart)	COUNTIFS on Status; QUERY
Budget	Spend vs budget	Spend by category	SUMIFS by category and month
Projects	Overdue, due this week	Tasks by status per person	COUNTIFS on Status and due date

For the columns to start from, see the Google Sheets dashboard template.

How to create a dashboard in Google Sheets step by step

The steps below show how to make a dashboard in Google Sheets for a simple orders tracker. The same steps work for a sales pipeline, a project tracker or a budget.

1. Decide which questions the dashboard answers

Start with questions, not charts. Write down the three to five questions someone should be able to answer within seconds of opening the dashboard, such as “Are we on track this month?” or “What’s late?”, then the number that answers each one.

Give every metric a one-line definition that everyone agrees on. “Revenue” can mean invoiced or paid, and a dashboard that mixes the two loses people’s trust fast. If a metric needs a column your data doesn’t have yet, add that column before you build anything else.

2. Put the raw data on its own tab

Name a tab “Data” and keep it plain: one record per line, one column per detail, headers in the first line, no merged cells, no blank lines and no subtotals mixed in. Enter dates as real dates, not text that looks like a date. Messy source data is a common reason a dashboard shows a wrong number without any error.

For the orders example, the Data tab looks like this:

Order date	Region	Rep	Status	Amount
2026-10-01	West	Ana	Paid	1,200
2026-10-02	East	Ben	Open	860
2026-10-03	West	Ana	Late	430

Next, select the data and click Format > Convert to table. A Google Sheets table lets you give each column a type, such as Date, Currency or Dropdown, and Sheets flags entries that don’t match. Making Status a dropdown column keeps typos like “Piad” out of your totals.

If your records come in through Google Forms, this part is done for you: Sheets puts form responses into a table automatically.

3. Build summary tables with SUMIFS, QUERY and pivot tables

Add a tab called “Summary”. Every metric gets calculated here, so no chart ever points at raw records. Anything people filter with a slicer (step 5) is the exception.

For headline numbers, use SUMIFS and COUNTIFS. They add up or count the records that meet one or more conditions:

Revenue from paid orders:
=SUMIFS(Data!E:E, Data!D:D, "Paid")

Revenue this month:
=SUMIFS(Data!E:E, Data!D:D, "Paid", Data!A:A, ">="&EOMONTH(TODAY(), -1)+1)

Open orders in the West:
=COUNTIFS(Data!D:D, "Open", Data!B:B, "West")

Whole-column references such as Data!E:E pick up new records automatically. If you converted the data to a table named Orders, you can write the first formula as =SUMIFS(Orders[Amount], Orders[Status], "Paid"). Table references update when you add or remove records, and they’re easier to read six months later.

For a breakdown, use QUERY. It filters, groups and sorts in one formula, using a short query language:

=QUERY(Data!A:E, "select B, sum(E) where D = 'Paid' group by B order by sum(E) desc label sum(E) 'Revenue'", 1)

That returns paid revenue by region, largest first. The 1 at the end tells QUERY your data has one line of headers.

For trends over time, a pivot table is usually easier than a long formula. Select columns A to E on the Data tab, click Insert > Pivot table, add Order date under “Rows” and Amount under “Values”, and add a filter that keeps only Paid orders. Then right-click a date in the pivot table, choose Create pivot date group, and pick the period you want. The pivot table refreshes whenever its source data changes.

Use formulas when another formula or a chart needs the result. Use a pivot table when you want a summary people can rearrange, or one that responds to slicers (step 5).

4. Turn the summaries into charts and scorecards

Add a tab called “Dashboard”. For each chart, select the summary cells, click Insert > Chart, and pick the type under Setup in the chart editor. A handful of chart types cover almost every dashboard:

• Scorecard charts for headline numbers. Choose Scorecard chart (under “Other”), point “Key value” at a Summary cell, and add a “Baseline value” such as last month’s figure to show the change, as a number or a percentage (how scorecards work).
• Column or bar charts to compare categories, like revenue by region.
• Line charts for trends over time, like monthly revenue from your pivot table.
• Pie or doughnut charts only for parts of a whole, and only with a few slices.
• Table charts for a short list people can sort, such as the ten largest open orders.

For a trend inside a single cell, SPARKLINE draws a tiny chart. If Summary!B2:B13 holds monthly revenue, =SPARKLINE(Summary!B2:B13) next to the revenue scorecard shows the last 12 months at a glance.

Lay the tab out the way people read: headline numbers across the top, trends in the middle, detail at the bottom. Use the same color for the same thing everywhere, such as one color per region.

If a summary can grow, set the chart range to whole columns (Summary!A:B). Before sharing, add a test record and check every number picks it up.

5. Make it interactive with slicers and dropdowns

Slicers are filter controls that sit on the dashboard. Click a chart or pivot table, choose Data > Add a slicer, and pick a column such as Region or Rep. Three things to know:

• A slicer filters every chart and pivot table on its tab that uses the same data. It doesn’t change formulas, so put the pivot tables and charts people should slice on the Dashboard tab, built from the Data tab.
• Anyone with access to the file, including viewers, can use a slicer. Their selections stay private unless you click More > Set current filters as default.
• Each slicer filters one column. Add a second slicer for a second filter.

For numbers built with formulas, add a dropdown instead. Click an empty cell, such as B1 on the Dashboard tab, choose Insert > Dropdown, enter your regions, and reference that cell in your formulas:

=SUMIFS(Data!E:E, Data!D:D, "Paid", Data!B:B, Dashboard!B1)

Changing a dropdown counts as an edit, so only people with edit access can use it. For viewers who want to dig into a detail table, a filter view works: anyone who can view the file can create a temporary filter view that only they see.

6. Highlight what needs attention

Color should mean something. Use conditional formatting (Format > Conditional formatting) to turn a cell red when a number misses its target, or a color scale to shade a table from low to high. Keep everything else neutral so the red cell is the first thing people notice.

Then add a short note at the top of the dashboard: who owns it, where the data comes from and how often it updates. Someone other than you should be able to trace any chart back to its source.

7. Protect the tabs that hold formulas

Click Data > Protect sheets and ranges, choose Sheet, and protect the Summary and Dashboard tabs. Under Set permissions, choose “Restrict who can edit this range” and set it to only you. If you trust your editors and only want a speed bump, choose “Show a warning when editing this range” instead. If the dashboard has a dropdown, check “Except certain cells” so that cell stays editable.

Protection prevents accidents. It isn’t access control: Google says it “shouldn’t be used as a security measure,” because people can still copy and export a protected spreadsheet. If a formula does get broken, open version history and restore an earlier version.

8. Share it with the right access

Click Share and give most people Viewer access. Viewers see the live numbers and can still use slicers and temporary filter views. Give Editor access only to people who maintain the data, and Commenter access to people who should flag problems without changing anything.

Check two settings before you send the link:

• Viewers can download, print and copy the file by default. The owner can turn that off in the sharing settings.
• To show the dashboard to people who don’t have access to the file, File > Share > Publish to web can publish a single tab as a read-only page that updates when your data changes, usually within a few minutes. Depending on your account settings, that page can be visible to anyone on the web, and a published chart also exposes the data behind it.

Can Gemini build a Google Sheets dashboard for you?

Partly. If your plan includes Gemini in Sheets, Insert > Create a canvas turns a tab into an interactive dashboard from a prompt. Google’s canvas help page lists the limits: a canvas reads one tab, anyone who can open the file can open it, viewers can’t change it, and it needs an eligible Workspace or Google AI plan. Gemini-generated charts don’t follow later data changes. Use either for a first draft; keep formulas behind numbers you report weekly.

How do you make a Google Sheets dashboard update automatically from live data?

A Google Sheets dashboard updates itself when two things are true: every number on it is a formula that traces back to the Data tab, and new records reach the Data tab without anyone pasting them in. If you followed the steps above, the first part is done. The second depends on where your records come from.

It’s also worth checking File > Settings > Calculation. In Automatic mode, Sheets recalculates as soon as anything changes. In Manual mode, it only recalculates when someone clicks Calculate, which makes a shared dashboard look stuck (calculation settings).

Collect new records with Google Forms

If records come from people, such as orders, requests or inspections, give them a Google Form instead of the spreadsheet. In the form, click Responses, then More > Select destination for responses, and pick your spreadsheet. Responses land on their own tab. Use that tab as your Data tab, and every formula that reads it updates as responses arrive.

Pull from other spreadsheets with IMPORTRANGE

When records live in another file, such as each team’s own tracker, IMPORTRANGE brings them into your Data tab:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Orders!A:E")

The first time, the cell shows a #REF! error with an Allow access button. Click it to connect the two files. If you don’t own the source file, its owner has to grant you access first. After that, changes in the source reach your dashboard after a delay (longer if imports are chained), and Sheets rechecks at least once an hour while the file is open.

Google’s own tips keep IMPORTRANGE fast: import only the ranges you need, do the summarizing in the source file when you can, and avoid chains where one file imports from another that imports from a third. Each request is capped at 10MB of data.

Import CSV files from a link with IMPORTDATA

If a tool can publish a report as a CSV link, IMPORTDATA pulls that .csv or .tsv file into a tab, and Sheets recalculates it every hour.

Refresh on a schedule with Connected Sheets or Apps Script

If your data lives in BigQuery, Connected Sheets can refresh it on a schedule, as long as you have BigQuery access and a Google Cloud project with billing.

For everything else, Google Apps Script can fetch or tidy data on a timer. Open Extensions > Apps Script, write one small function that does one job, and run it by hand until the output is right. Then add a time-driven trigger under Triggers > Add Trigger; triggers can run as often as every minute or as rarely as once a month. Log the time and result of every run, make reruns safe so a retry doesn’t add records twice, give the script only the access it needs, and alert yourself when a run fails.

Third-party connector add-ons can also pull data from ad platforms and CRMs on a schedule. They add a subscription to manage, and they can break when a source renames a column. If you’d rather avoid code, our guide to automating Google Sheets without scripts covers the no-code options.

Combine data from several systems

Give each source its own tab: IMPORTRANGE for spreadsheets, a connector add-on or Apps Script for other apps. Keep one shared column, such as order ID, so SUMIFS or XLOOKUP can match records.

How often does a live Google Sheets dashboard refresh?

“Live” depends on where the data comes from:

Where the data comes from	When the dashboard catches up
Someone edits the Data tab	Right away, in Automatic calculation mode
A Google Forms response	As each response arrives
IMPORTRANGE from another spreadsheet	After a delay, plus a recheck at least hourly while the file is open
IMPORTDATA, IMPORTHTML, IMPORTXML or IMPORTFEED	Every hour
Connected Sheets (BigQuery)	When someone refreshes it, or on a schedule you set
Apps Script with a time-driven trigger	On your schedule, from every minute to once a month
A Data Studio report built on the sheet	Every 15 minutes by default, or every 1, 4 or 12 hours (data freshness)

What can’t a Google Sheets dashboard do?

For plenty of teams, a spreadsheet dashboard is the right tool. These are the limits people usually hit first:

• Everyone who can open the file can see all of it. Hidden and protected tabs aren’t private, so you can’t show each rep only their own numbers inside one shared file.
• Updating a record means editing the spreadsheet. If a manager needs to change a status or approve an order, they need edit access to the whole file. A form is good for adding records but awkward for changing existing ones.
• Files have a size ceiling. A Google Sheets file holds up to 20 million cells or 100MB, and big imports or long IMPORTRANGE chains slow it down well before that.
• Sharing has limits. One file can be shared with up to 600 individual email addresses, and only 100 open tabs or devices can edit it at once.
• Formula chains are fragile. A renamed column or a changed date format can break a formula several steps away, often without an obvious error.

None of this matters much for a three-person team. It matters a lot once the whole company checks the dashboard.

Shared file Everyone with the file sees every rep's orders.

Data

Region Rep Status Amount

2 West Ana Paid 1,200

3 East Ben Open 860

4 West Ana Late 430

Dashboard app with sign-in Ana signs in and sees only her own orders.

Orders dashboard A Ana

My orders 2 orders

Oct 1, 2026 · West Paid 1,200

Oct 3, 2026 · West Late 430

The app reads and writes the same Google Sheet, so nothing is copied or moved.

Should you use Google Sheets, Data Studio or a dashboard app?

Four things decide it: where the data lives, who needs to sign in, whether those people need to change anything, and what it costs as more people use it.

	Google Sheets dashboard	Data Studio report	GoodTaco dashboard app (our product)
Best for	A small team that already works in the sheet	Polished reports that combine Google Ads, Analytics, Sheets and databases	Dashboards people sign in to, with forms to update records
Where your data lives	In the spreadsheet	Stays in each source	Your Google Sheet, Airtable base or a built-in database
Who signs in and sees what	Everyone with file access sees every tab	People you invite to view or edit the report	Private apps require sign-in, limited to the users or roles you choose
Updating records	Editors type into the spreadsheet	Reporting only, so you change data at the source	Forms and screens that write back to the same sheet
How fresh	Recalculates on every edit, imports run on Google’s schedule	Sheets data every 15 minutes by default	Reads and writes the same sheet
Price to start	Comes with a Google Account	No cost (Data Studio Pro is paid)	Free plan; paid from $9/month

GoodTaco is priced per workspace, not per app user.

• Stay in Google Sheets if a few people use the dashboard, everyone can see everything, and the data already lives in the sheet. It’s the fastest option and costs nothing extra. A canvas feels like an app, but everyone with file access sees everything.
• Choose Data Studio (called Looker Studio until April 2026) for presentation-quality reports that blend marketing sources, or a report many people read but never change. For pure reporting, it’s the better pick.
• Choose a dashboard app when people need their own sign-in, when not everyone should see the whole file, or when the dashboard needs forms that update records.

How do you turn a Google Sheets dashboard into an app with sign-in?

GoodTaco (our product) is an AI app builder for business apps. You connect the Google Sheet behind your dashboard, describe what you need in plain language, and GoodTaco’s AI builds a working web app with screens, forms, search, dashboards and sign-in in minutes. The sheet stays the source of truth: the app reads and writes that same sheet, so there’s nothing to migrate.

Here’s how that works with the dashboard you just built:

1. Connect the sheet. Sign in with Google and pick the spreadsheet in GoodTaco’s Google Sheets app builder. The connection uses OAuth, so you never share a password.
2. Describe the dashboard. Start with one specific view, see how it looks, then ask for changes.
3. Choose who gets in. Private apps ask people to sign in with Google or an email link before they see any data, and you decide which users or roles have access. You share the app, not the spreadsheet.
4. Publish. Hosting is included, so the app goes live at a GoodTaco link, and you can use a custom domain. Every change is saved in version history, so you can roll back.

Two first prompts that work well:

Build a dashboard from the Orders tab with revenue by region and by month, open orders by rep, and a form to change an order’s status.

Every morning, email me the late orders, grouped by rep, from my Gmail.

Apps can send email from your Gmail and run scheduled jobs like that daily reminder. GoodTaco also connects to Airtable, Jira, Smartsheet, Zendesk, Zoho CRM, QuickBooks and Supabase, so a dashboard app doesn’t have to start in a spreadsheet.

You can also see how the Google Sheets dashboard builder works, or read the full walkthrough on turning a Google Sheet into an app.

If your numbers live in Excel, upload the workbook or a CSV file and GoodTaco imports the data into a built-in database and rebuilds your formulas in the app. Charts and pivot tables aren’t imported, so the app builds its own charts from your data.

The free plan needs no credit card, so you can see your dashboard as an app before you decide anything. Start free

Frequently asked questions

How do I create a dashboard in Google Sheets?

Put raw records on one tab, calculations on a second tab, and the dashboard on a third. Use QUERY, pivot tables, and conditional formulas to create stable metrics, then add charts that point to dynamic ranges. Test the dashboard with new rows before sharing it.

How do I make a dashboard in Google Sheets easy to maintain?

Keep manually entered values out of the presentation tab. Give every source column a stable header, avoid merged cells in raw data, use named ranges where they make formulas clearer, and document any import or Apps Script trigger. Someone other than the original author should be able to trace a chart back to its source.

How do I create a Google Sheets dashboard that updates automatically?

Connect the dashboard to a live source with a supported Sheets function, a connector, or a scheduled Apps Script job. Build summaries from the connected tab and point charts at ranges that expand with new records. The dashboard is only automatic when both the data import and the calculations update without copy-paste.

How do I build a dashboard when the data lives in several systems?

You can import each source into Sheets, but the formulas and sync jobs become harder to operate as sources multiply. A connected GoodTaco app is a better fit when the team needs one live view across supported sources, role-based access, or a workflow that writes changes back to the source systems.

Can you create a dashboard in Google Sheets?

Yes. Anyone with a Google Account can build one with tools that come with Sheets: formulas such as SUMIFS and QUERY, pivot tables, charts, slicers and conditional formatting. An add-on or a script only becomes useful when data has to come in from other apps on a schedule.

Can a Google Sheets dashboard update in real time?

Edits and form responses show up right away, as long as calculation is set to Automatic under File > Settings. Imported data follows Google's schedule: IMPORTDATA, IMPORTHTML and IMPORTXML recalculate every hour, and a Data Studio report built on a sheet refreshes every 15 minutes by default.

Where is the activity dashboard in Google Sheets?

Open the file and click Tools > Activity dashboard. It shows who the file is shared with, who in your organization has viewed it and a viewer trend, not your business data. You need edit access, and it only works on files owned by a work or school account.