Excel Object Formatting

All standard Microsoft™ Excel formatting functionality is available in Excel objects in CDM. You can apply Excel formatting in any worksheet except the Database worksheet and any other worksheets that contain data queries.

You can use the Format Cells window to apply the following cell formatting to cells and worksheets:

• Leaders between words and values
• Number format
• Alignment of the cell or table in the Excel object (left, center, or right)
• Font format
• Borders around rows, columns, and cells
• Patterns in the cell
• Protection to prevent cells from being edited

Show and Suppress Rows and Columns in an Excel Object When the Report Is Generated

When you generate a report, CDM automatically suppresses any rows or columns that contain only zero values and shows all other rows and columns. If necessary, you can specify that you want to show or suppress particular rows or columns.

If an original value that was entered in the Excel object is a very small number that would be automatically rounded down to zero (for example, 0.00001), this number is treated as zero in the following procedures.

In the Zero Amount Shown As field in report properties, the administrator can specify a character such as a dash (-) to represent zeros in the report when the report is generated. If a character was specified in the Zero Amount Shown As field and you want zero values to appear in your report, then that character, rather than a zero, appears when the report is generated.

To show and suppress rows and columns in an Excel object when the report is generated:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary.
5. If you want to show content that would normally be suppressed in the generated report, or if you want to suppress content that would normally be shown, consult the following table:

   Content of row or column	Default behavior in the generated report	If you want to perform the following task	Complete these steps
   Row contains only zeros	Row is automatically suppressed. If the table contains only one row, and the row would normally be suppressed, the row header would be suppressed as well.	Show the contents of the row	Navigate to the end of the display range that contains the row you want to show, then in that row, type a plus sign (+) in the column that contains the ##RE variable.
   Column contains only zeros	Column is automatically suppressed. If the table contains only one column, and the column would normally be suppressed, the column header would be suppressed as well.	Show the contents of the column	Navigate to the end of the display range that contains the column you want to show, then in that column, type a plus sign (+) in the row that contains the ##RS variable.
   Row contains at least one non-zero value	Row is automatically shown.	Suppress the contents of the row	Navigate to the end of the display range that contains the row you want to suppress, then in that row, type a minus sign (-) in the column that contains the ##RE variable. Note: If a range extends to more than one page, the suppression is applied after the table is divided into multiple tables.
   Column contains at least one non-zero value	Column is automatically shown.	Suppress the contents of the column	Navigate to the end of the display range that contains the column you want to suppress, then in that column, type a minus sign (-) in the row that contains the ##RS variable. Note: If a range extends to more than one page, the suppression is applied after the table is divided into multiple tables.

   Restrictions:

   • The following cell formats are ignored by automatic suppression: empty cells, cells with text, cells containing numbers, and non-numeric formatting.
   • Columns that contain the following variables are ignored by automatic suppression, even if the content of the cells are non-zero: ##NL, ##NR, ##SL, and ##SR.
   • If a column is being suppressed as a result of automatic row or column suppression, any ##NL, ##NR, ##SR, and ##SL variables that follow the suppressed column are also suppressed.
   • When the Excel object contains merged cells, automatic row or column suppression occurs in the following manner:
     • If a row is merged across other columns, automatic row suppression applies if all values in the row are zeroes.
     • If a column is merged across other rows, automatic column suppression applies if all values in that column are zeroes.
     • If there is a mixture of merged rows and columns, automatic suppression is based on the entire table.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

When the report is generated, CDM deals with suppression items in the following order:

1. Plus and minus signs are applied.
2. Automatic suppression of zeros is applied.
3. Zero amounts shown as values are appended to the table.
4. Superscript or note columns are attached to the columns.

Apply Leaders Between Columns in an Excel Object

You can apply leaders between columns in an Excel object in CDM to make the text easier to read.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Type one of the following symbols in the column where you want leaders to appear:
   • @*. - To apply leader dots between two columns.
   • @*- - To apply leader dashes between two columns.
   • @*_ - To apply leader lines between two columns.
     
5. Save your changes to the Excel object.
6. Click the Default View Mode icon in the status bar.
7. Save your changes to the report and check in the Excel object.

Apply Font, Border, and Pattern Formats to an Excel Object

You can apply font, border and pattern formatting to cells in an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the cells that you want to format and right-click.
5. Click the tab containing the format controls that you want to use.
6. Apply the formatting.
7. Save your changes to the Excel object.
8. Click the Default View Mode icon in the status bar.
9. Save your changes to the report and check in the Excel object.

Enable Cell Merging in an Excel Object

By default, the merge cells setting is not enabled for any cells in a newly created Excel object in CDM. If you prefer, you can enable cell merging.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the cells that you want to format and right-click.
5. Find the alignment area and select the field for cell merging.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Enable Text Wrapping in an Excel Object

By default, the text wrapping setting is not enabled for any cells in a newly created Excel object in CDM. If you prefer, you can enable text wrapping.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the cell or range of cells.
5. Select the cells that you want to format and right-click.
6. Find the alignment area and select the field for text wrapping.
7. Save your changes to the Excel object.
8. Click the Default View Mode icon in the status bar.
9. Save your changes to the report and check in the Excel object.

Change Cell Alignment in an Excel Object

By default, an Excel object in CDM aligns text in a table to the lower left corner of a cell. If you prefer, you can change the alignment of text in a cell. You can change the vertical alignment (top, center, or bottom) and also the horizontal alignment (left, center, or right).

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Select the cells that you want to format and right-click.
5. Find the alignment area and select your preferred alignment.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Change the Height/Width of a Row/Column in an Excel Object

You can change the height of a row as well as the width of a column in an Excel object in CDM.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Rest the pointer on the row/column boundary that you want to move until it becomes a two-headed arrow.
5. Click and drag the boundary until the row/column is the height/width that you want.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Change the Size of All Rows and Columns in an Excel Object

You can change the size of all rows or all columns in a worksheet in an Excel object in CDM at the same time.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Move to the worksheet that you want to resize and select all rows and columns.
5. To resize all rows, move the pointer between rows until the row resize handle appears and double-click.
6. To resize all columns, move the pointer between columns until the column resize handle appears and double-click.
7. Save your changes to the Excel object.
8. Click the Default View Mode icon in the status bar.
9. Save your changes to the report and check in the Excel object.

Set Conditional Formatting in an Excel Object

You can use the conditional formatting feature in an Excel object in CDM to set rules for cell formatting based on the value in another cell. If the rules (conditions are met, then the formatting is applied.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. Click the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary.
5. Select the cell that you want to conditionally format.
6. Find the conditional formatting area.
7. Select the type of formatting you want (font, highlight cell, and so on) and enter the conditions that enable formatting for the cell.
8. To delete conditional formatting, select the cell or cells to which conditional formatting has been applied and clear the settings.
9. Save your changes to the Excel object.
10. Click the Default View Mode icon in the status bar.
11. Save your changes to the report and check in the Excel object.