Why Your Google Sheets Dashboard Shows Wrong Numbers (and How to Fix It with Live Data)

If your Google Sheets dashboard is showing wrong or stale numbers, the most likely causes are a frozen IMPORTRANGE connection, a formula that is not recalculating, or a stale API cache. Most issues can be resolved by forcing a recalculation (Ctrl+Shift+F9 on Windows, Cmd+Shift+F9 on Mac), reconnecting IMPORTRANGE, or replacing problematic formulas with a scheduled data connector.

The Problem: Stale Data Destroys Dashboard Trust

There is a specific moment that every data analyst knows. Your dashboard has been running smoothly for weeks. Then someone on the leadership team squints at a number, opens a browser tab, checks the source system, and says: "This doesn't match. Are these numbers even current?"

That question (once asked) is very hard to answer credibly. And once trust in a dashboard erodes, it rarely fully recovers.

As one data practitioner put it bluntly: "Nothing kills a finely tuned workbook faster than someone wondering whether the data is up to date."

The stakes are real. Stale data has been identified as one of the top three causes of lost trust in data platforms, alongside inconsistent definitions and poor documentation. A dashboard that shows last week's numbers as if they were today's does not just fail to inform. It actively misleads.

The Google Sheets community has quietly documented how pervasive this problem is. The Stack Overflow question titled "How do I force a Google Sheets formula to recalculate?" has accumulated over 255,000 views. That number tells you something important: this is not an edge case affecting a handful of power users. It is an endemic problem affecting anyone who builds dashboards in Sheets.

Here is a catalog of what actually goes wrong, and why:

IMPORTRANGE silently stops updating. IMPORTRANGE pulls data from another Google Sheet. It is supposed to stay live, but it can silently freeze when the source sheet is restructured, when access permissions change, when the source URL changes, or simply when Google's infrastructure decides to stop refreshing the connection. There is no error message, no warning, just stale numbers sitting in cells that look perfectly normal.

Volatile functions are not volatile when you think they are. Functions like NOW(), TODAY(), RAND(), and INDIRECT() are called volatile because they are supposed to recalculate every time the sheet recalculates. But Google Sheets does not recalculate on every action. It recalculates when a cell value changes, when you force a recalculation, or on a timer. If your dashboard sits open without any user interaction, volatile functions can go hours without updating.

The Sheets API returns cached values. If you or a script is reading data from your spreadsheet via the Google Sheets API, the API can return a cached version of the sheet rather than the current computed values. This is especially common when formulas have not recalculated recently. The API cache does not expire on a predictable schedule, which makes debugging maddening.

No "last updated" indicator means no way to verify freshness. Most Google Sheets dashboards have no mechanism to tell a viewer when the data was last refreshed. Viewers have to take it on faith that the numbers are current. That faith erodes quickly after even one incident of stale data.

The real cost of stale data. A marketing team making budget decisions based on last week's conversion data can misallocate meaningful spend. An operations team monitoring fulfillment rates on yesterday's numbers can miss a warehouse problem until it becomes a customer complaint. The spreadsheet does not raise its hand to say something is wrong. It just sits there, confidently displaying the wrong answer.

The Fixes: Six Methods to Get Your Dashboard Current

Fix 1: Force Recalculation Now

This is the fastest first step. It does not solve the underlying problem, but it tells you immediately whether the issue is a stale cache that a refresh can clear, or something structural.

On Windows: Press Ctrl + Shift + F9

On Mac: Press Cmd + Shift + F9

This keyboard shortcut forces Google Sheets to recalculate every formula in the spreadsheet, including volatile functions like NOW() and TODAY(), and including IMPORTRANGE references. After pressing it, wait 10–30 seconds for large sheets to finish processing before checking your numbers.

If your numbers update after forcing recalculation, you have confirmed that the formulas themselves are working. The problem is that they are not recalculating automatically. That points you toward Fix 5 or Fix 6 below.

If your numbers do not update, the problem is structural: either the source data is not changing, IMPORTRANGE has lost its connection, or there is a formula error somewhere upstream.

Alternative: Edit a cell. Another way to trigger recalculation is to double-click any cell and press Enter without changing anything. This signals to Sheets that the sheet has been modified and triggers a recalculation cycle. It is a manual version of the keyboard shortcut and works on mobile where the shortcut is not available.

Fix 2: Diagnose and Repair IMPORTRANGE

IMPORTRANGE is the source of many dashboard staleness issues. Use this diagnostic process to identify and fix the problem.

Step 1: Check for the error state. Click on one of your IMPORTRANGE cells. Look at the formula bar: does it still show the correct URL and range? If the cell shows #REF! or #ERROR!, that is your problem.

Step 2: Check access permissions. Open the source spreadsheet URL directly in your browser. Can you access it? If your access has changed, for example, if the source sheet was moved to a shared drive and permissions were restructured. IMPORTRANGE will silently fail.

Step 3: Re-authorize the connection. Even if IMPORTRANGE looks correct, its authorization can expire. Delete the IMPORTRANGE formula from the cell and retype it from scratch. When you confirm the formula, Sheets will prompt you to click "Allow Access" to re-establish the connection. Click it.

Step 4: Check that the source range still exists. If the source sheet was restructured (columns moved, rows inserted, sheets renamed) your IMPORTRANGE range reference may now point to empty cells or an entirely different range. Verify the range name and cell references against the current structure of the source spreadsheet.

Step 5: Verify the source sheet is not frozen. IMPORTRANGE can only pull from a source sheet that is itself calculating. If the source sheet has its own stale formulas or is very large and recalculates slowly, your IMPORTRANGE pull will reflect that delay.

For persistent IMPORTRANGE failures: Consider whether IMPORTRANGE is the right architecture for your use case. IMPORTRANGE is a point-in-time pull that depends on both sheets being live and accessible. For critical dashboards, a more reliable approach is to use a dedicated data connector that writes data directly into your sheet on a schedule (see Fix 5 and Fix 6).

Fix 3: Clear the Sheets Cache

Google Sheets caches computed values to improve performance. This cache is usually invisible, but it can cause the sheet to display old values even after a formula has technically been updated.

Browser-level cache clear:

Step 1. Close the Google Sheets tab.

Step 2. In Chrome, press Ctrl + Shift + Delete (Windows) or Cmd + Shift + Delete (Mac) to open the Clear Browsing Data panel.

Step 3. Select "Cached images and files" and set the time range to "Last hour" or "Last 24 hours."

Step 4. Click "Clear data," then reopen your spreadsheet.

For API-level caching: If you are reading spreadsheet values via the Google Sheets API (in a script or application), the API's spreadsheets.values.get endpoint returns cached cell values by default. To force the API to return freshly computed values, include the parameter valueRenderOption=FORMULA in your request, then evaluate the formulas client-side, or add a brief delay after triggering a recalculation before reading values.

The nuclear option: duplicate the sheet: If caching issues persist, create a new blank spreadsheet and use IMPORTRANGE or copy-paste-as-values to bring the data over. A fresh spreadsheet has no cache history. This is disruptive but can confirm whether cache is the actual culprit.

Fix 4: Add a "Last Refreshed" Timestamp

The best mitigation for the trust problem is transparency: show viewers exactly when the data was last updated so they can judge its freshness themselves.

Option A: A simple NOW() cell.

In an empty cell near the top of your dashboard, enter:

=NOW()

Format the cell as a date-time. This cell will update every time the sheet recalculates. If your sheet is not recalculating, the timestamp will freeze, which is actually useful diagnostic information. Pair this with a label: "Data as of [cell reference]".

Option B: An Apps Script timestamp that writes on trigger.

For more control, use Apps Script to write a static timestamp whenever a specific function runs:

function updateTimestamp() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  sheet.getRange('B2').setValue('Last refreshed: ' + new Date().toLocaleString());
}

Call updateTimestamp() at the end of any data-refresh function. Unlike NOW(), this timestamp will not change unless the refresh actually ran, so it accurately reflects the last successful data pull, not just the last time anyone opened the sheet.

Option C: Conditional formatting for staleness.

Create a staleness alert by applying conditional formatting to your timestamp cell. If the timestamp is more than 25 hours old (or whatever your acceptable refresh interval is), turn the cell background red. This gives viewers an immediate visual cue that the data may be stale, without them needing to do any mental math.

Fix 5: Replace Manual Formulas with a Live Data Connector

For dashboards that pull from external sources (databases, CRMs, marketing platforms, analytics tools) IMPORTRANGE and manual formulas are often the wrong architectural choice. They add fragility (broken connections, permission changes, formula errors) without adding reliability.

A dedicated data connector writes rows directly into your spreadsheet from the source system, on a schedule, without any formula dependencies. The data sits in cells as plain values, no formulas to break, no authorization to re-establish.

What this looks like in practice:

Instead of a chain like: Source System → IMPORTRANGE → VLOOKUP → Dashboard Cell

You get: Source System → Connector (scheduled sync) → Dashboard Cell

The connector handles authentication, querying, pagination, and error handling. If a sync fails, the connector logs the failure and you get an alert: rather than silently showing stale data.

When to make the switch:

• Your dashboard pulls from more than two external sources
• IMPORTRANGE failures have caused at least one "wrong numbers" incident
• Stakeholders have questioned the accuracy of your data
• Your current setup requires manual intervention (exports, copy-paste) more than once a week

Fix 6: Set Up Scheduled Auto-Refresh

If you want to keep using formula-based approaches but make them more reliable, scheduled refresh through Apps Script is the middle-ground solution.

Step 1. Open your Google Sheet. Click Extensions > Apps Script.

Step 2. Write a function that touches a cell value to trigger recalculation:

function forceRefresh() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  
  // Write current timestamp to a control cell
  const controlCell = sheet.getRange('A1');
  controlCell.setValue(new Date());
  
  // Flush to ensure writes are committed
  SpreadsheetApp.flush();
  
  // Update the last refreshed display
  sheet.getRange('B2').setValue('Last refreshed: ' + new Date().toLocaleString());
}

Step 3. Set up a time-based trigger. In the Apps Script editor, click the Triggers icon (clock symbol) in the left sidebar. Click Add Trigger.

Step 4. Configure the trigger:

• Function to run: forceRefresh
• Deployment: Head
• Event source: Time-driven
• Type: Hour timer (or Minutes timer for more frequent refresh)
• Interval: Every hour (or every 30 minutes)

Step 5. Click Save. The trigger will now run on schedule, updating the control cell and causing any formulas that depend on it to recalculate.

Important limitation: This approach forces recalculation within Sheets, but it does not pull new data from external systems. If your data source (a CRM, a database, a GA4 property) has new records, those records will not appear in your sheet unless a connector or IMPORTRANGE is pulling them. Scheduled refresh is most useful for sheets where the underlying data is already present but formulas need to recompute, not for sheets that need fresh data from external sources.

Comparison Table

Troubleshooting

IMPORTRANGE shows the right data but does not update when the source changes. Open the source spreadsheet and make any edit (even adding and immediately deleting a space in a cell). This triggers a recalculation in the source sheet, which IMPORTRANGE can then detect. If this fixes it temporarily, the root issue is that the source sheet itself is not recalculating: apply Fix 1 or Fix 6 to the source sheet, not the destination.

My NOW() timestamp updates but my data cells do not change. This means the recalculation trigger is working, but the underlying data is genuinely not changing. The source system (your CRM, your analytics platform, your database) either has not been updated, or the connection to it (via IMPORTRANGE or API) is fetching the same cached result. Verify that the source system actually has new data by checking it directly.

My Apps Script trigger shows successful execution but data is still stale. Check the Apps Script Executions log for warnings. A common cause is that the script ran but hit an error on a specific line that it handled gracefully (or logged but did not throw). Also verify that the sheet name in your script exactly matches the actual sheet tab name: sheet names are case-sensitive in Apps Script.

Stakeholders report wrong numbers even though my data is refreshing correctly. The issue may not be staleness. It may be a definition mismatch. Verify that the metrics your dashboard shows use the same filters, date ranges, and attribution settings as whatever your stakeholders are comparing against. A common example: your dashboard shows "all sessions" while a stakeholder is looking at "paid sessions only" in a different tool.

Google Sheets API is returning old values even after a refresh. After a programmatic recalculation trigger, wait at least 5–10 seconds before querying the API for values. The recalculation process is asynchronous and the API may read values mid-calculation. Alternatively, use the Sheets API's spreadsheets.get method with includeGridData: true and check cell effectiveValue rather than formattedValue to get the most recently computed result.

The Bottom Line

If your dashboard is showing stale data, start with the quick fixes: force recalculation, check IMPORTRANGE authorization, and add a timestamp so you and your stakeholders can see exactly how old the data is. These steps take minutes and will diagnose most common issues.

If the problems are recurring: if you are doing these fixes more than once a week, or if a staleness incident has already damaged stakeholder trust. That is a signal that your architecture needs to change. Formula-based dashboards built on IMPORTRANGE are inherently fragile for anything that needs to stay reliably current. They depend on multiple connections staying healthy simultaneously, and any one link breaking silently stales the whole thing.

The durable solution is to separate data delivery from data display: use a scheduled connector to write fresh data directly into your sheet as plain values, and build your dashboard formulas on top of that stable foundation. The formulas stay simple, the data stays current, and the "are these numbers right?" conversation stops happening.

Stop Wondering Whether Your Data Is Current

brooked.io writes live data from your databases and SaaS tools directly into Google Sheets on a schedule you control: hourly, every 15 minutes, or daily. No formulas to break, no IMPORTRANGE connections to babysit. Your sheet always has the current numbers, and your stakeholders can see exactly when it was last refreshed.

Troubleshooting quick reference

Frequently asked questions