Option to show formulas in cells

Posted by: ivanchenkodenis on 27 December 2017, 8:53 pm EST

  • Posted 27 December 2017, 8:53 pm EST

    Does spreadsheets have function (option/switch) to view formulas in cells.
    Like sheet.showFormulas(true) and all formulas in cells are appeared as tex, not as resulting value?
  • Replied 28 December 2017, 7:48 pm EST


    You can get the formulas of all cells using getArray() method and set these formulas a cell value using setArray. Please refer to the sample application to know the implementation.

    Deepak SharmaSpreadJSGetFormulaSetValue.zip
  • Replied 29 December 2017, 6:41 am EST

    I understand, what you're proposing (still this example not working https://monosnap.com/file/WxDlJCzwqSyYVx6WPj45Jp9rruemMU)
    But I think, after setting this formulas as text, we wont be able to edit them with coloring and helpers. Am I right?
  • Marked as Answer

    Replied 2 January 2018, 1:02 am EST


    Here is the working code:

    $("#Button2").click(function () {
    var rcount = activeSheet.getRowCount() - 1;
    var ccount = activeSheet.getColumnCount() - 1;
    var formulaArray = activeSheet.getArray(0, 0, rcount, ccount, true);
    activeSheet.getRange(0, 0, rcount, ccount, GC.Spread.Sheets.SheetArea.viewport).clear(GC.Spread.Sheets.StorageType.data);
    activeSheet.setArray(0, 0, formulaArray);

    You are right once we set the value of cells by overriding the formulas all formulas will be removed. You will still have the formulas in the formula array.
    The cell value will not be edited like formulas(with helpers and colors).

    Deepak Sharma
  • Replied 16 October 2019, 3:33 am EST

    I was too looking for something like this. What we need is something like Formula Audit mode in excel. Is it possible to do something like that in Spread.Sheets ?
  • Replied 16 October 2019, 5:11 pm EST

    Hi Harsha,

    Could you please confirm if you need just "ShowFormulas" feature of auditing or other features like Formula trace too?
    Auditing features are not supported in the current version of SpreadJS but they are already in our backlog to be supported in future releases.
    The formula tracing feature is expected to be released in the V13.
    For show formula, we are sorry but we do not have an ETA, I've added you as correspondence for the case(Internal tracking ID: SJS-2138) and will let you know about any updates regarding the same.

  • Replied 28 February 2020, 10:06 am EST

    Upvote for formula auditing, like the show formulas feature in Excel. We currently have to export to Excel & do our show formulas in native excel but it would be very very nice & convenient if we didn't need to do that.
  • Replied 1 March 2020, 4:01 pm EST


    We have added you as correspondence for the case. I'll let you know when this gets implemented.

  • Replied 23 April 2020, 10:44 pm EST


    This feature is now available in the latest build(13.1.0). In the latest build, we added an option 'showFormulas' to show formulas like the in MS Excel(using ctrl + `(backtick) shortcut). When we set the showFormula to true, the view changes to formula view, and then copy from the sheet causes the formulas to get copied on the clipboard instead of the selected values. You may refer to the following code snippet and the sample demonstrating the same:
    sheet.options.showFormulas = true;


    Please update to the latest version and let us know if the issue persists for you. You may get the latest build from npm or download using the following link:

  • Replied 24 April 2020, 12:28 am EST

    When will this be added as a button to the Designer like Excel? Thank you.
  • Replied 24 April 2020, 1:25 am EST

    This is already added to the latest designer 13.1.0 too. You may check out the demo here:
    Use ctrl+` shortcut or enable formulas view using the Formula->Formula Auding->Show Formulas option.
  • Replied 24 April 2020, 1:26 am EST

    Excellent! Thank you very much!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels