Insert hardcoded values

Use this dialog to insert hardcoded values instead of formulas in the active workbook or in a newly generated copy of the active workbook.

Best practice

Hardcoded values may be a good choice if a workbook or selected worksheets are sent to external recipients. It is recommended to always create a new copy of the workbook with hardcoded values and keep the original file. Alternatively you can create a backup of the original file before replacing all formulas with hardcoded values.

The Insert hardcoded values feature can be found in the smartRush ribbon in Excel in the Workbook group:

Insert hardcoded values

If the button in the ribbon is pressed the following dialog will open:

Insert hardcoded values

The following settings are available

  1. Scope of operation
    • Active worksheet: This option will replace all formulas with hardcoded values on the active worksheet. Note: This option is only capable of a single worksheet, if more than one worksheet should be processed option 4 can be selected.
    • Selection: This option will replace all formulas within the selected range on the active worksheet with hardcoded values. This option is the correct choice if only a part of a worksheet should be replaced.
    • Full workbook: This option replaces all formulas on all worksheets in the active workbook with hardcoded values
    • Selected sheets: This option replaces all formulas on the checked sheets in the list with hardcoded values.
  2. Target of operation
    • Insert hardcoded values in current workbook: If this option is selected the formulas will be replaced by hardcoded values directly within the active workbook. CAUTION: Please make sure to create a backup before applying this action.
    • Copy sheets to new workbook: If this option is selected the worksheets will be copied to a new workbook before formulas are replaced. Formulas in the active workbook will remain without any changes.
      • Transfer all formats: If this option is checked all formats like number formats, font color, font size, background color etc. will be transferred to the new workbook
      • Transfer number formats only: If this option is selected only number formats will be transferred to the new workbook. All other formats will be ignored. Note that this option is only available if not all formats should be transferred.
      • Transfer comments and notes: If this option is checked all comments and notes within cells will be transferred to the new workbook. If a workbook is sent to a third party recipient it may be a good choice not to include comments and notes.
      • Transfer validation: If this option is checked all data validations (like number ranges, lists etc.) will be transferred to the new workbook. Usually it has very limited sense to transfer validations to hardcoded workbooks.
  3. Recalculate workbook before hardcoded values are inserted: If this option is checked the workbook will be recalculated before formulas are replaced by hardcoded values.