Your Excel file is the source for the structure that becomes your SecureSheet. All the properties in Excel, like values, formulas, formats, data validations, conditional formats, named ranges, and merged cells, are decided and maintained by you in your compensation spreadsheet.


To get started, focus on the structure of your main worksheet, i.e., all the columns you need are identified in the sheet. Recognizing the data will be dynamic through the process, you may start working with last year’s data or a sample set of data.


To download an example spreadsheet modeled in Excel with SecureSheet setup considerations, click here.


Video Resources:

Setting Up Excel File for SecureSheet

Before the SecureSheet Support Team creates a SecureSheet from your Excel file, complete a few steps to prepare your Excel file for SecureSheet. 


Complete these steps before uploading your Excel file to File Attachments:


  1. Insert 5 columns to the left of your main worksheet.
    • SecureSheet “reserves” these columns for implementing statements and setting up export links for individual statements, for example.
    • SecureSheet uses Column A for Unique ID. This is used to keep audit history for each row in the sheet. It is also used in the data management process for importing values only to the sheet.
      • Column A is generally set to equal the EEID column. There are rules that apply if leading zeros exist in the EEIDs:
        • Column A should always be formatted as "General".
        • Column A should never be formatted "Text".
        • Typically, the Unique ID is the EEID column, and Column A is setup as a formula, e.g.,  =F11*1 to remove any leading zeros in the EEIDs.
          • If removing leading zeros from EEIDs does not make Column A unique, concatentate columns from your data to make Column A a unique ID.
          • There can be no duplicate values in Column A.
  1. Go 25 columns to the right of your last data column and type "End".  This sets up additional columns that may be used for security or statement setup.
    • SecureSheet “reserves” these columns for implementing more advanced filtering and locking logic if needed in the views.
    • When your SecureSheet is initially created, the last three columns will be system-generated columns that audit row changes. They will always be at the end of the data set after the last column in the sheet.
  1. In the first row of any extra column to the right of your data, enter this syntax:  vexit/recalc/Recalc. This will display as a Recalc hyperlink in the cell.
    • When you import to SecureSheet, SecureSheet recalculates based on what you imported. However, on occasion, the SecureSheet Support Team may ask that you recalc and this is an easy way to do that.

Excel Set Up Considerations

For security setup and data management:

  • If not already present in your spreadsheet, add an Employee Status column to track employee status changes.
    • The employee status column allows you to manage status changes after go-live and preserve the audit trail throughout your process.
    • After go-live, you do not want to delete employee rows and reimport data or you will lose the cell history of the SecureSheet. 
      • The number of rows must stay the same after go-live. Row order must also stay the same unless you request that a resort of data and reimport (still same number of rows, just resorted) tracks cell history tied to the Unique ID.


  • Add a column to track employee eligibility for each compensation component you are planning in your model. 
    • Add an eligiblity column (yes/no) at the start of each section, e.g., the merit section, the bonus section, and the equity section. This allows users to easily filter, and logic may be setup to lock the input column(s) for the section(s) that don't apply to each employee.
      • For example, an employee is eligible for merit and neither bonus nor equity. In your SecureSheet setup, the merit planning input columns will be unlocked for the employee and bonus and equity planning input columns will be locked.


  • Is your organization hierarchy direct between employee and manager from first level to highest level leader?
    • With a direct hierarchy, SecureSheet can infer the organization hierarchy as long as each manager also has an employee row in the data (even if at the highest level if you desire, they are just identified as an employee with a manager with no compensation data included).
      • Even if your hierarchy may be inferred, build out the hierarchy by level so your end users may filter by level in their view(s).
    • With an indirect hierarchy, or a more complicated hierarchical setup in your organization:
      • Security may be controlled by a user's EEID being found on a row of data. 
      • The hierarchy should include an EEID column next to a name column for each level in the hierarchy (e.g., Level 2 EEID in one column and Level 2 Name in an adjacent column). It is a better practice to setup security using EEIDs than names.
    • If the hierarchy skips levels, make it realistic, i.e., do not fill in the same name in levels 1 through 5 (or up to top of chain); where the hierarchy skips, leave the level blank.


  • How do your human resource team members access data?
    • Security may be set to cross reference a value in a particular column or any combination of values in any of the columns in your main tab (e.g., by location and functional area).
    • If you identify your human resource team members on each employee row, include a column with their EEID as well (e.g., HR EEID in one column and HR Name in an adjacent column).
    • You may have a hierarchy of HR team members or more than one HR team member who needs access to the same data. If more than one human resource team member needs to be identified on each row, add as many sets of HR EEID and HR Name columns as needed. 
      • If this is your scenario, also add a column that concatenates all of the HR EEIDs. This concatenated HR EEID column will be used as the security cross reference to show the appropriate rows to the human resource team members. (Note: this approach is not limited to human resources; it can be used for any combination of manager/leader IDs if needed).


For process tracking:

  • If you want managers to acknowledge they have completed their recommendations, consider adding a "Submitted" column to your Excel file, e.g., "Planner Submitted" with a dropdown value "Submitted".
  • If you want approving managers to acknowledge their approval, consider adding an "Approved" column to your Excel file for each level of approval in your process, e.g., "First Level Approved", "Second Level Approved", etc., each column with a dropdown value "Approved".


For data entry:

  • Do you allow planners to enter a percentage OR an amount?


  • Do you have employees in multiple countries?
    • If you show the base pay/amount(s) awarded in local currency, build in the following columns:
      • Country
      • Currency
      • Pay information in local currency
      • Pay information in budgeted/planning currency


  • If you have a dependent data validation (i.e., the drop down values in one column are based on the value in another column or series of columns), be sure to set up the dependent data validation following the SecureSheet instructions for setting up a dependent dropdown.


For summary data:

  • Summary calculations should be in the header rows at the top of your sheet (e.g., budget runners or counters if you want them).
  • If you have more extensive summary information that you would like to include in SecureSheet, note that SecureSheet does not support pivot tables; however, any pivot table can be translated into formulas. 
    • You may build out summary data tables as formulas as needed, either on your main tab in header rows, or on a supporting tab, and import them into SecureSheet. The SecureSheet Support Team will help you build views of summary data based on user security as needed.
    • If you have charts, build them in Excel as you would want to see them in SecureSheet.


For less troubleshooting and consistency when your Excel file becomes a SecureSheet, consider the following:

  • Move all summary calculations to header rows at the top of your main sheet. SecureSheet will dynamically calculate summary totals in a view for each user based on the rows displayed for the user in the view. 
    • Header rows can be hidden per view in SecureSheet.
    • Place summary calculations that need to be visible to end users above columns that will not be hidden in the end user views.
    • Extend header formula ranges beyond the last row of data to accommodate adding additional rows of data through the process (e.g., if the total rows in a sheet is 100 and the last row referenced in a discrete range is 1000, SecureSheet will automatically ignore rows 101:1000).
  • When building VLOOKUPs that reference a supporting tab, use absolute ranges in your lookup formula, e.g., C5:E60 vs C:E.
  • If you use Named Ranges in your Excel model, give them absolute cell references, e.g., G10:J500, not relative like G:J.
  • Copy formulas all the way down columns in your data.
    • As a best practice, use formulas consistently on all data rows; do not have formulas on some rows and values in other rows in the same column.
  • Check column data types to make sure they are consistently applied to the entire column.

NOTE: TEXT formatted columns cannot be used in calculations in SecureSheet.

  • SecureSheet does not import any data formatted as a table in excel (pivot or otherwise). Remove or rearchitect table formatting/references.
  • Make fonts consistent (name and size).
  • Align number and date columns to the right or left consistently.
  • Manage column widths (adjust for length of data values in the column; wrap headers if necessary).
  • Shade user input columns for visual cue (e.g., light yellow).
  • Unhide all supporting tabs.
  • Unhide all columns and rows.
  • UnGroup any grouped data.
  • Remove cell borders from main data rows.
    • If desired, border summary data in the header rows.
    • Grid Lines is a property in SecureSheet that can be used to delineate cells.


NOTE: There are 250 maximum columns allowed in a SecureSheet (the last three columns are reserved as the SecureSheet audit columns).

Action