Format numbers, dates, and currencies

Google Sheets lets you format your data in several different ways so that your spreadsheet and its contents are displayed how you want them.

To format or change the format of numbers, dates or currencies in a spreadsheet, follow these steps:

  1. Select the range of cells you'd like to format or modify.
  2. Click the number format icon in the toolbar or click the Format menu and hover over “Number”.
  3. Select the format you'd like to apply to the range of cells.

Custom formatting in the new Google Sheets

In the new Google Sheets, you can apply custom formatting for currencies, dates, and numbers. For each of these, you can search in the text boxes found in the formatting menus to find the format that works for your spreadsheet. If you don’t find what you are looking for, you can now create your own custom formatting within the menus.

Custom date formatting

To apply a custom date or time format to your spreadsheet:

  1. Highlight the data you want to format.
  2. Click the Format menu and hover over “Number” (or click the number format icon in the toolbar).
  3. Hover over "More Formats."
  4. Select More date and time formats....
  5. Search in the menu text box to select a format. You can also add your own custom date or time format into the text box.
  6. Click Apply.

By default, the time and date options that surface will be based on your spreadsheet locale.

If you want to add more detailed time or date values to your formatting, for example the hour or minute, click the down arrow in the right corner of the menu text box and select an additional value. You can adjust the specific formatting for these values by clicking on the arrows in the value and choosing an option. To delete a value from your formatting, click the value and select Delete.

Custom currency formatting

To apply a custom currency format to your spreadsheet:

  1. Highlight the data you want to format.
  2. Click the Format menu and hover over “Number” (or click the number format icon in the toolbar).
  3. Hover over "More Formats."
  4. Select More currencies....
  5. Search in the menu text box to select a format. You can also add your own custom currency format into the text box.
  6. Click Apply.

You can also change a few properties about the currency (for example, how many decimal places to show) by clicking the drop-down menu in the right corner of the input box and choosing a desired option.

Custom number formatting

To apply a custom number format to your spreadsheet:

  1. Highlight the data you want to format.
  2. Click the Format menu and hover over “Number” (or click the number format icon in the toolbar).
  3. Hover over "More Formats."
  4. Select Custom number format….
  5. Search in the menu text box to select a format. You can also add your own custom number format into the text box.
  6. Click Apply.

When creating a custom format, note that the formatting can consist of up to 4 parts separated by semicolons: positive;negative;zero;non-numeric. Financial formats are also supported.

You can use colors in the formatting, for example to differentiate between positive and negative numbers, by adding a color in brackets (for example, [Red]) anywhere within the desired part of the format. The colors that can be used are:

  • Black
  • White
  • Red
  • Blue
  • Green
  • Magenta
  • Yellow
  • Cyan
  • Color# (where # is replaced by a number between 1-56 to choose from a different variety of colors)

Here is a list of common syntax characters that can be used to create a custom number format:

Character Description
0 A digit in the number. An insignificant 0 will appear in the results.
# A digit in the number. An insignificant 0 will not appear in the results.
? A digit in the number. An insignificant 0 will appear as a space in the results.
$ Formats numbers as a dollar value.
.(period) Formats numbers with a decimal point.
/ Formats numbers as a fraction.
% Formats numbers as a percent
E Formats numbers as an exponent.
"text" Adds text to the formula. Insert the desired text within quotations for it to appear.
@ Displays text entered into a cell.
You can also apply this custom formatting to non-adjacent cells. To select non-adjacent cells, click and hold Ctrl on your keyboard (Cmd on a Mac) as you select the cells you want to format.