You can use various value formatting options to change how your spreadsheet displays your data.
Change value formatting
You can select a value formatting type for a cell to automatically use that format each time you enter data. Cells are set to Auto by default, but entering a currency symbol or comma separator changes the format to Accounting.
To apply value formatting:
- Select a cell or group of cells.
- From the Edit toolbar, click the Value Formatting dropdown.
- Select a value format to apply to the selected cell.
Advanced value formats
You can also edit value formatting settings from the Formats panel.
To open the Formats panel:
- Select a cell.
- From the Edit toolbar, click the Value Formatting dropdown.
- Select More Formatting Options from the menu to open the panel.
You can also open this panel by clicking the gear icon in the right panel.
The Formats panel contains different settings depending on the selected cell’s format. You can use this panel to change currency and date formatting, change accounting display settings, change how zeros are displayed, or add prefixes, suffixes, and leader dots.
Note: For values formatted as Number, Accounting, Currency, or Percent, you can choose Use alternate display for zeros in the Format panel. This allows you to display zeros as an em dash, en dash, hyphen, or blank.
Create custom date formats
If you don't see your desired date format in the Date display options, you can set up a custom date format. Once created, you'll be able to access these new formats throughout your spreadsheet.
To add a new format:
- Select a cell in your spreadsheet and open the Formats panel. Make sure your Format is set to Date.
- Under Date Options, click the arrow to view display settings.
- Click Custom Date Format at the bottom of the menu.
- Type your custom format in the text box. Use the ISO codes d, m, and y to represent the values for day, month, and year—check the table for a full list of text options.
- Click the checkmark to save your format. Your new format is saved under Custom Formats in the Display dropdown, and you can save up to five custom formats.
Custom date entry guide
Use the following text strings to manage how your date is displayed:
| ISO Code | Description | Example |
| m | Single-digit month | 2 |
| mm | Double-digit month | 02 |
| mmm | Month (abbreviation) | Feb |
| mmmm | Month (full) | February |
| d | Single-digit day | 5 |
| dd | Double-digit day | 05 |
| ddd | Day of week (abbreviation) | Mon |
| dddd | Day of week (full) | Monday |
| y or yy | Year (abbreviated) | 08 |
| yyy or yyyy | Year (full) | 2008 |
Use Period Format and display options
One use of advanced formatting is the Period Display options, which allow you to use the proper ISO format for XBRL (PnYnMnD) for data entry while viewing the more common decimal number.
For example, if the life left in a building is 5.7 years, the ISO format is P5Y7M12D. To display that value as a number with a decimal, use the Period Display options. For more information about formatting decimals for XBRL, see Format Time Period Links - ISO Codes.
To change the display of a period format:
- Select the cell or range.
- From the Edit toolbar, check that the value formatting for the cell or range is set to Period.
- Click the gear icon in the right panel to open the Formats panel.
- From the Display dropdown, select how to display the value. The cell value changes to the period format you select, but the exact value of the cell remains.
Use Entered In and Shown In
You can adjust the precision of numbers in your spreadsheet using the Entered In and Shown In options.
To change the Entered In and Shown In settings:
- Highlight a cell or range of cells.
- From the Edit toolbar, select an option for Entered In to scale the values you enter or Shown In to define how to display the values.
Note: Adjusting these settings may change the displayed value of a cell but does not change the exact entered value.
You can also change the number of decimal places shown using the Decimals dropdown.
Rounding and adjustment columns
As a best practice for rounding values, you can use adjustment columns in Spreadsheets. Adjustment columns allow for an increased audit trail using cell history and easy removal of adjustments during a roll forward process.
If you're transitioning GPBO from Classic, see Moving from GPBO to Adjustment Columns.
To set up an adjustment column:
- Insert two columns to the left of your linked data.
- Copy the content from the source link column into the first column. This is commonly labeled as the “Original Value” column.
- Input any needed adjustments or rounding into the second column. This is commonly labeled as the “Adjustment Value” column.
- Update the source link columns to sum the original and adjustment columns. This creates the final adjusted value that links to all your reports.
