Inserting views

You must be in design mode to insert a view. These rules apply:

  • Each workbook can have multiple sheets with multiple views embedded in each sheet.
  • You can only insert one instance of a view per workbook. The same view cannot be inserted in separate worksheets.
  • You cannot have more than 42 views in a workbook.

If you insert a view at the wrong cell, we recommend either deleting and then reinserting the view or using View Properties to change the positions of the off-grid dimensions and the data area. Do not delete empty rows or columns to reposition the view. Deleting rows or columns outside the view can cause the view definition to reload improperly.

Note: If you are inserting an ad hoc view into a workbook, we strongly recommend using the view definition formatting, since the data area will change as dimensions are rotated or pivoted and the number of members displayed changes. For this reason, it is also best not to have inserted rows or columns.

To insert a view in a workbook:

  1. With a workbook open, click Enter Design Mode for Views.
  2. Click Insert View.
  3. On the Data Area tab, select the view to include in the workbook.
  4. Click Next to go to the Layout tab. The Layout tab lets you specify the initial layout of the off-grid dimensions and the data area.
  5. Select a starting point for the off-grid dimension cells by entering the cell number or by using the cell selection icon. Make sure that the location you select does not cause existing materials on the worksheet to be overwritten.
  6. Select Across to display the off-grid dimensions by columns or select Down to display the off-grid dimensions by rows. Do not use this option if you manually select a starting location for the data area. Excel Services always uses Down as the default setting when you specify a starting location for the data area.
  7. Select whether the member name should be displayed to the right of or beneath the dimension name cell. The dimension names will only be displayed if the view definition specifies displayed dimension names.
  8. Select where you want the on-grid dimension data to appear. You can either place it relative to the off-grid dimension cells or you can start it at a specific cell. In either case, make sure that the location you select does not cause existing materials on the worksheet to be overwritten with the new data area. Data area settings override any conflicting off-grid dimension settings. Excel Services first tries to place the dimension names going down the worksheet, not across. If there is not enough room between the off-grid dimensions and the data area, Excel Services places the remaining dimensions/members across the worksheet.
    Option Description
    Rows between dimensions and grid Select this option to place the data area relative to the dimension cells. Select the option and specify the number of blank rows to place between the off-grid dimensions and the on-grid dimension data.
    Starting at cell Select this option to place the data area at a specific cell. Select the option and specify the cell location in the field. Or, click the cell selection button and select the cell from the worksheet.
  9. Click Next to go to the Format tab. The Format tab lets you specify different formatting options for the off-grid dimensions than for the on-grid dimension data.
  10. In the Off-Grid Dimensions group and the Data Area group, select Format as in view definition if you want the dimensions to retain the formatting defined for the view. If you select this option, any changes to the view definition automatically appear each time the workbook is opened. If you do not select this option, the existing formatting in the workbook is preserved; changes to the view definition are not reflected in the view unless the Reload View Definition command is selected.
    Select Autosize dimensions or Autosize data area to adjust the columns to fit the text after the dimensions are inserted. If you de-select this option, text that requires a larger space than the column size allows will overrun into the next cell (if it is empty) or it is truncated. This is standard Excel behavior. If you check both autosize options, first the columns for the data area are autosized, and then the columns for the off-grid dimensions are autosized.
  11. Click Finish.
  12. Save the workbook.