You can setup any view to export with values only or with formulas. The way this is setup depends on what you want managers to do offline in the export Excel file. If you want users to be able to export from SecureSheet with formulas included in the export file, consider the following when choosing to enable this functionality:


  • When a view exports with formulas from SecureSheet, the tabs that support calculations must also be exported so the calculations work properly in the exported file.
    • You identify which tabs are required for an exported SecureSheet to work offline.
    • If there are any columns that you do not want any values to be seen in the exported file, identify columns to be cleared in the export.
    • If there are formulas in columns that you want replaced with values, identify formula-based columns that need to export as values-only.


  • If you want to enable a view to export with values only - no formulas - follow the steps to setup a view to export with values.



Note:        Even though SecureSheet exports with formulas to Excel with passwords and allows administrators to set a second-layer password, there are ways to hack into an Excel password-protected file that someone who chooses to hack in can pursue. SecureSheet cannot guarantee the security of an Excel file. SecureSheet can guarantee security in SecureSheet, but once data leaves SecureSheet, SecureSheet has no control.

Note:        If you allow users to export a view with formulas and you want to stop any manipulation of columns in the exported excel file (i.e., columns cannot be reordered, additional columns cannot be added, etc.), set a sheet password that freezes the column structure on a view that exports with formulas. This can be set per SecureSheet.

Set Up a View to Export with Formulas

  1. Edit an existing View. 
  2. Set the Summary Calcs: box to A1=THIS;Z1="ExportViewFormulas:Data" where "Data" is the name of the tab with your data validation values or the values being cross-referenced in calculations on your main tab.

NOTE:  If you have more than one supporting tab that needs to export, use a comma in between tab names, e.g., Data,BudgetMatrix.

  1. Turn on the Export Link checkbox.
  2. Enter an E in the Export Options: box.
  3. Click Save View.


Additional Options when Exporting with Formulas

You may have a scenario where you do not want all the formulas or values in the columns to be exported. You can control the export where formulas are replaced with values and value columns are cleared out, and you can define these settings by view.


  • The standard export with formulas includes all columns, by design, as the hidden columns might influence the formulas.
  • There is an option to remove data from columns as part of the export.
    • Provide a list of columns (A,B,C,etc...) and they can be set up such that when they export, those columns are empty (this applies to both value-only and formula driven columns).
    • You have to determine what columns impact the formulas so that it does not mess up managers as they are trying to export and work offline.
  • There is an option to replace the formula with the value in the column on export.
    • Provide a list of columns (A,B,C,etc...) and they can be set up such that when they export, those columns only have values and not the formulas.
  • Consider keeping data validations in an export and not clearing them as data validations can be helpful offline.
Syntax and Examples:

*rcs* - clear the VALUES out of these columns on export with formulas (NOTE:  SecureSheet cannot control the hiding or unhiding of columns in an exported file)

    • This would be used for columns that do not impact formulas AT ALL or columns that you do not want users to see
    • Syntax example in Summary Calcs:

DA1="ExportViewValues*rcs*A,B,C,D,E,F,G,J,K,L,M,N,O,P,Q,R,V,W,X,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AO,AT,AU,AV,AW,AX,AY,BC,BD,BE,BF,BG,BS,BT,BU,BV,BW,BX,BY,BZ,CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ*rce*"

 

*rcfs* - replace the FORMULA with VALUE on export with formulas

    • Syntax examples in Summary Calcs: A1=THIS;Z1="ExportViewFormulas*rcfs*AS,CA,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ,EA,EE,FN,FM,FO,FP*rcfe*"

On a Manager view:  A1=THIS;Z1="ExportViewFormulas:Data*rcs*A,B,C,D,E,I,K,M,P,R,T,AK,BD,BF,BG,BH*rce*"

On an HRBP view:  A1=THIS;Z1="ExportViewFormulas:Data*rcs*A,B,C,D,E,I,K,M,P,R,T,AK,BD,BH*rce*"


Combining clear values and replace formulas with values:

    • Summary Calcs: syntax example:
      • A1=THIS;Z1="ExportViewFormulas:*rcfs*A,B*rcfe**rcs*C,D*rce*"
        • Where columns A and B are replaced with values and columns C and D are cleared out