ModelMonkey Logo
Data Analysis

Google Sheets View Only: How to See Formulas

Marc SeanJune 18, 20265 min read

For sheets you own or have edit access to, the options are much richer - and if you're doing any serious model auditing, FORMULATEXT and a short Apps Script will get you further than Ctrl+` alone.

What Actually Works in View-Only Mode

Click any cell and the formula bar shows the full formula - including cross-tab references like =SUMIFS('Revenue'!D:D,'Revenue'!B:B,">="&Assumptions!$B$3). You can read it, you just can't interact with it.

There's no keyboard shortcut to toggle formula view across the whole sheet in view-only mode. You're reading one cell at a time.

If the file owner didn't check "Disable options to download, print, and copy" in the share settings, you have two exits:

  • File > Download > Microsoft Excel (.xlsx): downloads a fully editable copy with all formulas intact. Open it in Excel or re-upload to Sheets, then do whatever you want.
  • File > Make a copy: works if copy permissions are enabled. Creates your own editable copy in Drive.

If download and copy are both blocked, you're stuck reading cell by cell through the formula bar. At that point, request edit access or ask for a copy. There's no workaround for a file that's been locked down properly.

Showing Formulas in Your Own Model

For sheets you control, you have 3 practical options depending on what you're trying to do.

Ctrl+` (the backtick, top-left of most keyboards) toggles formula view on the active sheet. Column widths go sideways because formulas run long, but it's fast for a quick visual scan. Hit it again to go back to values. The same toggle lives at View > Show > Formulas.

This is useful for a quick gut-check. It's not useful for auditing formula consistency across 8 tabs, or for documenting a model before a handoff.

FORMULATEXT() is the better tool for real audit work. It returns a cell's formula as a text string, which means you can build a reference column in a separate tab:

=FORMULATEXT('DCF'!C15)

Returns something like =NPV(Assumptions!$C$4,CashFlow!D8:H8)+CashFlow!D8/(Assumptions!$C$4-Assumptions!$C$5).

For auditing an entire row or column:

=ARRAYFORMULA(IFERROR(FORMULATEXT('P&L'!C5:C52),""))

This drops every formula in P&L column C into your audit sheet. Where a cell contains a hardcoded value instead of a formula, FORMULATEXT returns #N/A - the IFERROR swaps that for a blank. Scanning for blank cells in your audit column flags hardcoded numbers sitting inside formula ranges, which is one of the more common errors in multi-year models built by multiple people.

As of June 2026, FORMULATEXT works correctly on named ranges and structured references, but it won't resolve the output of IMPORTRANGE - it returns the IMPORTRANGE formula itself, not the underlying formula from the source file.

FORMULATEXT for Cross-Tab Consistency Checks

The real use case in a financial model is checking that formula structure is consistent across columns. Say your DCF has discount factors in row 8 across 5 projection years:

=ARRAYFORMULA(IFERROR(FORMULATEXT('DCF'!C8:G8),""))

If every cell uses the same formula pattern (reference shifting one column each year), the output should look almost identical across the row. A hardcoded number, a wrong anchor, or a copy-paste that drifted - those jump out visually.

A practical example from a quarterly board pack workflow: EBITDA margin in the P&L should always pull from the same Assumptions tab cell. Dumping =ARRAYFORMULA(FORMULATEXT('P&L'!D10:H10)) takes 5 seconds and catches the cell where someone typed 38.5% directly instead of referencing Assumptions!$C$7.

Apps Script Formula Dump

If you want a complete formula audit across your whole model, this runs in under 30 seconds:

function dumpFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('P&L'); // change to your sheet
  var formulas = source.getDataRange().getFormulas();

  // Create or clear the audit tab
  var audit = ss.getSheetByName('Formula Audit') || ss.insertSheet('Formula Audit');
  audit.clearContents();

  // Write formula text; empty string where cells have no formula
  audit.getRange(1, 1, formulas.length, formulas[0].length).setValues(formulas);
}

Run it from Extensions > Apps Script. The getFormulas() method returns a 2D array of strings - formula text for formula cells, empty string for hardcoded values. You get a clean grid of formula text for the entire sheet.

For a multi-tab model, loop over the sheets you care about:

function dumpAllFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var audit = ss.getSheetByName('Formula Audit') || ss.insertSheet('Formula Audit');
  audit.clearContents();

  var tabs = ['P&L', 'Balance Sheet', 'Cash Flow', 'DCF', 'Returns Analysis'];
  var row = 1;

  tabs.forEach(function(name) {
    var sheet = ss.getSheetByName(name);
    if (!sheet) return;
    var formulas = sheet.getDataRange().getFormulas();

    audit.getRange(row, 1).setValue('=== ' + name + ' ===');
    row++;
    audit.getRange(row, 1, formulas.length, formulas[0].length).setValues(formulas);
    row += formulas.length + 2; // gap between sections
  });
}

The output is a single tab with formula text from every sheet, grouped by tab name. Useful before a bank syndicate review, model handoff, or any time you're signing off on someone else's work.

Where ModelMonkey Fits

When you're working through a complex model and want to spot formula inconsistencies without manually inspecting each cell, ModelMonkey's AI assistant in the sidebar can read ranges and flag cells where formulas break from the expected pattern - hardcoded values inside formula columns, references that don't match the established structure, that kind of thing. It's faster than scanning a FORMULATEXT dump manually when the model has 2,000+ formula cells across tabs.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.

Frequently Asked Questions