Format Numbers To Millions and Thousands in Google Sheets

When dealing with large numbers in financial data, you may want to format numbers in either thousands, millions, or a combination of both. Custom number formatting is the best way to achieve this in Google Sheets. But what if you have a formula that generates the numbers dynamically? Let’s explore the options for formatting numbers in Google Sheets without altering the underlying value.

Formatting Numbers to Thousands (K) in Google Sheets

We will begin with the Custom number format, which is the preferred method for formatting numbers into thousands or millions in Google Sheets. In the custom number format, ‘#’ and ‘0’ serve as number placeholders, and a comma is used as the thousand separator. For example, ‘#,##0,”K”‘ can be applied to format numbers in the range D2:D13. Here’s how you can do it:

1. Select the cell range D2:D13.
2. Click Format > Number > Custom number format.
3. In the Custom number format field, enter the format string: ‘#,##0,”K”‘.
4. Click Apply.

The numbers in the selected cells are now formatted in thousands with the format ‘#,##0,”K”‘. Please note that there have been no changes in the underlying value of the cells.

Using Formulas

If you prefer using formulas over the custom number format, you can use either the TEXT or QUERY functions. The TEXT function returns the numbers in text format, while the QUERY function retains the underlying values as numbers. Here are examples of how to use these functions to format numbers into thousands:

TEXT Formula (for the range B2:B13): =ArrayFormula(TEXT(D2:D13,"#,##0.0,K"))
QUERY Formula (for the range B2:B13): =QUERY(D2:D13,"select D format D '#,##0.0,K'")

Please note the following points:

1. In both functions, you must remove the double quotes around ‘K’ in the format string.
2. In the TEXT function, the format string must be placed within double quotes, whereas in QUERY, it should be within single quotes.

Formatting Numbers to Millions (M) in Google Sheets

To format numbers into millions in Google Sheets, you can use the format string ‘#,##0.0,,”M”‘. Apply it to the selected range, and the numbers will be formatted accordingly. Here’s how you can do it:

1. Select the range.
2. Click on Format > Number > Custom number format.
3. Copy and paste the format string into the field: ‘#,##0.0,,”M”‘.
4. Click Apply.

Please note that the custom number format must be placed within double quotes for the TEXT function and within single quotes for the QUERY function.

Dynamic Formatting Numbers to Millions and Thousands in Google Sheets

Sometimes, it’s impractical to apply the same format to a range of cells when there is a significant difference between two numbers. To address this, you can conditionally format the numbers. For example, you can format numbers below 1,000,000 in thousands and those above in millions. Apply the following format string to the selected range:

[>=1000000] #,##0.0,,"M";[>0] #,##0.0,"K";[Red]General

Please note that 0 and negative numbers won’t be converted and will retain their original format.

Formatting Numbers to Millions or Thousands in Charts

You can also format numbers in thousands or millions for chart axis labels or data point labels in Google Sheets. Here’s how:

1. Double-click on the axis or data point labels.
2. Under “Number format,” select “Other custom formats.”
3. Copy and paste the format string from the tables mentioned earlier.
4. Click Apply.

Repeat the same steps for both the vertical axis and data point labels. If you have multiple series, you will need to format each one separately.

That’s all! Now you know how to format numbers in thousands and millions in Google Sheets without altering the underlying value. Enjoy exploring the possibilities.