Scale large values with custom number formats

Scale large currency values such as TTM revenue in the cell format, not by dividing the formula. SheetsFinance returns figures in base units, so keep the raw number and apply a custom number format in Google Sheets or Excel.

When you need this

Use this pattern when an SF value looks unreadable as a full dollar amount, and you want the cell to show millions or billions without changing the underlying data. Dividing by 1000000 or 1000000000 in the formula alters the raw value and breaks sums, ratios, and further calculations that expect base units.

Get TTM revenue first

Pull trailing-twelve-month revenue with the base income statement type and "ttm" in the year argument:

=SF("AAPL", "income", "revenue", "ttm")

The ticker can be a cell reference instead of a hardcoded symbol:

=SF(A2, "income", "revenue", "ttm")

For historical TTM revenue as of a date, use the incomeTTM type:

=SF("AAPL", "incomeTTM", "revenue", "2020-06-30")

Values return in base units (the full figure), not millions or billions. For the full metric list and TTM options, see Income Statement and Financial Statements.

Apply a custom number format

Select the result cell, then open the custom number format dialog in your spreadsheet app:

• Google Sheets: Format → Number → Custom number format

• Excel: Home → Number → More Number Formats (or Format Cells) → Custom

Enter a format code that scales with trailing commas. Each trailing comma divides the displayed value by 1,000 without changing the stored number:

• One trailing comma scales by thousands

• Two trailing commas scale by millions

• Three trailing commas scale by billions

Quoted text at the end adds a suffix label.

Format patterns

Use these patterns for large currency outputs such as revenue and market cap:

$#,##0,,"M"

$#,##0,,,"B"

Two commas show millions with an M suffix; three commas show billions with a B suffix.

For example, if the cell value is 394328000000:

• Default display: 394328000000

• With $#,##0,,"M": $394,328M

• With $#,##0,,,"B": $394B

For one decimal place at millions scale, a pattern such as 0.0,, is also valid in Google Sheets and Excel custom formats.

What not to do

Do not wrap the SheetsFinance call in a division to force display units:

=SF("AAPL", "income", "revenue", "ttm")/1000000000

That returns a scaled number, not a formatted display of the original value. Prefer the custom format on the unmodified formula cell.

What is next

For composing multi-metric statement pulls, start from Income Statement. For another valuation formula pattern, see Build enterprise value from components.