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, and selecting the Value formatting tab.
The Value formatting 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.
- Select the Value formatting tab, and select Date in the Value Format Style selector.
- Under Date Options, click the Date Display selector to view the date format options.
- Scroll to the the bottom of the menu and click Custom date display.
- In the Custom date display dialog, enter 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 Apply to save your format. Your new format is saved under Custom in the Display dropdown.
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 3.4 years, the ISO format is P3Y5M7D. 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 in your spreadsheet and open the Formats panel.
- Select the Value Formatting tab, and select Period in the Value Format Style selector.
- In the Period Options section, use the Period Format pull-down to select how you want the value displayed. 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. To learn more on how to create an adjustment column, read Moving from GPBO to Adjustment Columns.
Things to know
- When overriding links, only non-numeric formatting (such as Text) is supported. You will need to remove numeric formatting before using any overrides.