You can convert all the cells of a crosstab into formulas in one step. This conversion deletes the crosstab object and defines every row in the table as a Microsoft Excel formula. The result values called from the server with the formula are still displayed in the table. The formula of the selected cell is displayed in the formula bar. In formula mode, you can edit the analysis table using Microsoft Excel formatting and formula functions and make further calculations using the existing data.
Having all formulas for the crosstab instead of a single crosstab object will probably decrease the performance of the entire workbook since every cell represents a call to the data source system. However, you will have much more control over the layout and calculation of your data. You need to decide if the trade-off is worthwhile to you and your organization.
A few advantages to converting the crosstab to all formulas include:
- You can create a different table layout than the standard Rows/Columns layout.
- You can insert spaces to make the display easier to read.
- You can copy parts of the table or individual cells to another position in the workbook in order to compare particular values.
- You can re-use cells.
- You can overwrite a members with another one, or add one in order to call data that you need from the Business Intelligence (BI) server. If member "3.2024" is used to read the sales revenue for March 2024, for example, you can replace the 3 with a 4, thus using member "4.2024" to obtain the sales revenue for April 2024.
- You can calculate additional subtotals.
You must, however, keep in mind these restrictions:
- Navigation using drag and drop or the Design Panel is not possible.
- The context menu is not available.
- Workbook performance may be slower since more calls to the data source must be made.
To convert a crosstab to formulas, choose Convert to Formula in the Tools area of the Analysis Design ribbon.