Preserving range properties

Preserve Range Properties preserves selected properties for the data area of the view (including the on-grid member cells) when refreshing the view, reloading the view definition, moving the data area (using View Properties), or making other changes to the view. It does not preserve properties for the off-grid dimension cells (to protect off-grid dimension cells, use the Format tab on the View Properties dialog box). This command retains any Excel formatting, locking, comments, or validation created whenever a view that uses the formatting of the worksheet or view definition is refreshed (i.e., the Format as in view definition option is selected on the Format tab of either the Insert View dialog box or the View Properties dialog box). You do not need to use this command if you do not use either of the Format as in view definition options.

These rules apply:

  • Each range that is preserved applies only to the current view. It does not apply across worksheets or views.
  • By default, any cell value derived from an Excel formula is preserved. If you enter Excel formulas in the data area, they are preserved without use of the Preserve Range Properties command. For example, you could enter a custom column header such as Total by using ="Total" over an existing column heading. Another example would be to enter an Excel formula over a database formula line, such as a downfoot.
  • Inserting a view row or column within a preserved range that has been formatted will cause a loss of formatting to one or more rows or columns. Therefore, be sure to insert view rows or columns first, before preserving and formatting a range.

Creating many ranges with the Preserve Range Properties command could degrade performance, since the settings are saved and then reapplied during every refresh. Preserving range formatting is only necessary if the Format as in view definition options are checked on the Format tab of the Insert View or View Properties dialog boxes.

To preserve range properties:

  1. Select the range of cells on the worksheet that you want to protect.
  2. Select the Preserve Range Properties command.
  3. The selected range displays in the For field of the Preserve box. Click Add to add the range to the Range list. Adding the range to the list enables these options:
    1. The Format option preserves Excel formatting.
    2. The Locked option preserves Excel protection settings.
    3. The Comments option preserves Excel comments.
    4. The Validation option preserves Excel validation settings.
  4. To define other ranges, click Apply to set the current range, click Add, and then click the icon in the For field to select a new range.
  5. Click OK.