Customize crosstab formatting styles


After completing this lesson, you will be able to:

  • Customize crosstab formatting styles

Analysis Style Sets

A style set is a collection of Microsoft Excel cell styles that is applied by Analysis to the entire workbook to format the cells of crosstabs. They are a great method for ensuring all the formatting in your workbook has a consistent look, perhaps conforming to your corporate branding guidelines. There are four style sets that come with Analysis for Office. The SAP style set SAP SAP Blue is used as default.

Four Style Sets: SAP Blue, SAP Blue Crystal, SAP Black & White, SAP Tradeshow Plus

Whenever you insert a new crosstab in a workbook, the styles in the current default style set are used to format the crosstab cells. You can change the applied style set in your analysis by choosing StylesApply Style Set . . . and then choosing from the available styles. Select Set as default if you want the selected style set to be your new default style set.

The Styles Apply Style Set option from the Analysis Design ribbon

You can also create and share your own style sets by modifying the cell styles of the preconfigured style sets. You will see the specific cell styles on the standard Excel Home ribbon. Simply right-click the SAP cell you want to change and choose Modify. Make any formatting changes you wish, then back on the Analysis Design ribbon, choose StylesSave Style Set . . . and name your new style set.

We go into more detail on changing styles in the Analysis styles topic.
Save a new style set

To share a style set with other users, choose Export Style Set . . . or to use another user's style set, choose Import Style Set . . .. You can save a style set to a network drive so that other users can easily access it.

Analysis Styles

Cross tabs have standard styles for each component of the result set such as dimension header and member cells, data cells, and totals.

The crosstab areas affected by styles: Dimension Header cells, Dimension Members cells, Total cells, Data cells

These standard styles are based on the style set applied but can be changed using standard Microsoft Excel functionality from the Home ribbon. Choose the More drop-down to expose all of the styles, including the SAP styles of the style set.

SAP Styles on the Excel Home ribbon

You can also use Excel's styles to create new styles to add to the style set. SAP recommends establishing and following a standard naming convention for any new styles you create, to distinguish them from the standard styles.

The SAP standard styles affect the formatting as follows:

Style NameDescription
SAPDimensionCellFormat for dimension header cells
SAPMemberCellFormat for member cells (non-hierarchical dimensions)
SAPHierarchyCellFormat for even-numbered hierarchical member cells and 0
SAPHierarchyOddCellFormat for odd-numbered hierarchical member cells
SAPMemberTotalCellFormat for member total cells
SAPDataCellFormat for data cells
SAPDataRemovedFormat for empty crosstab cells whose data was removed by selecting the workbook property Remove Data Before Saving
SAPDataTotalCellFormat for data total cells
SAPExceptionLevel1-9Format for highlighted cells due to conditional formats (rule priorities 1-9)
SAPEmphasizedFormat for highlighted data cells (as per query definition)
SAPEmphasizedTotalFormat for highlighted total cells (as per query definition)
SAPBorderFormat for borders around a crosstab and between header/member and data cells (format for left border is taken); any changes do not take effect until you Refresh All in the Analysis ribbon
SAPMessageTextFormat for the message that is displayed whenever a crosstab cannot be rendered and displayed in the sheet
SAPFormulaFormat for inserted single-cell formulas but not for array formulas (such as Info Field for Effective Filters) or formulas inserted directly into the sheet (manual or as MS Excel function)
SAPGroupingFillCellFormat for header fill cells when grouping crosstabs
SAPEditableDataCellFormat for input-ready data cells in planning change mode
SAPEditableDataTotalCellFormat for input-ready total cells in planning change mode
SAPReadonlyDataCellFormat for read-only data cells in planning change mode
SAPReadonlyDataTotalCellFormat for read-only total cells in planning change mode
SAPLockedDataCellFormat for locked data cells in planning change mode
SAPLockedDataTotalCellFormat for locked total cells in planning change mode

Finally, you can change the default format style of a particular cell to use a different style. Select the cell you want to change and on the Analysis Design ribbon, choose Format CellsNew Format for <label>.

Format Cells on the Analysis Design ribbon

Design Panel and Design Rules Tab

Table Design encompasses the customized elements you can change about a crosstab, such as adding new lines or changing the cell format. These changes persist after navigation steps that force a rebuild of the crosstab, like a refresh or swaping axes. The specific Table Design options are:

  • Adding new lines (rows and columns)
  • Applying formats to single cells, rows and columns
  • Adding formulas in new member, data, and input-enabled planning data cells
  • Adding texts in new member cells

On the Design Rules tab of the Design Panel, you can display and maintain rules for these Table Design elements. The rules are displayed for each data source and rule type. You can select the data source in the Rule for field. If a workbook contains only one data source, then this data source is preselected. In the Rule type field, you can select a rule type. The following rule types are available:

  • New Lines
  • Format
  • Formula
  • Texts
The Design Rules tab of the Design Panel

You can select a rule and Deactivate it if you do not want the rule to be applied at the moment. If you deactivate a rule, the icon for the rule in the list changes. You can also select a rule and delete it completely.

You can also remove all design rules manually by choosing Reset Data Source. If Table Design rules are available for the data source, a pop-up will ask if you want to keep the rules. With Yes, the rules will be available after the reset. With No, the rules will be removed.

Enhance a Crosstab

As the company analyst, you are required to change the appearance of the crosstab to make it more readable.

Task 1:

Log in to track your progress & complete quizzes