You can format your data in several different ways in Google Sheetsso that your spreadsheet and its contents are displayed how you want them.
Format numbers, dates and currencies
To format or change the format of numbers, dates or currencies in a spreadsheet:
- On your computer, open a spreadsheet in Google Sheets.
- Select the range of cells that you'd like to format or modify.
- Click Format
Number. - Select the format to apply to the range of cells.
Number.Custom formatting of numbers, dates and currencies
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.
To apply a custom date or time format to your spreadsheet:
- On your computer, open a spreadsheet in Google Sheets.
- Highlight the data that you want to format.
- Click Format Number.
- Click Custom date and time.
- 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.
- 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, e.g. the hour or minute, click the down arrow in the right-hand 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.
To apply a custom currency format to your spreadsheet:
- On your computer, open a spreadsheet in Google Sheets.
- Highlight the data that you want to format.
- Click Format Number.
- Click Custom currency.
- Search in the menu text box to select a format. You can also add your own custom currency format into the text box.
- Click Apply.
You can also change a few properties about the currency (e.g. how many decimal places to show) by clicking the drop-down menu in the right-hand corner of the input box and choosing a desired option.
To apply a custom number format to your spreadsheet:
- On your computer, open a spreadsheet in Google Sheets.
- Highlight the data that you want to format.
- Click Format Number.
- Click Custom number format.
- Search in the menu text box to select a format. You can also add your own custom number format into the text box.
- Click Apply.
When creating a custom format, note that the formatting can consist of up to four parts separated by semicolons: positive;negative;zero;non-numeric. Financial formats are also supported.
You can use colours in the formatting, e.g. to differentiate between positive and negative numbers, by adding a colour in brackets (e.g. [Red]) anywhere within the desired part of the format. Formatting colours must be used in English. The colours that can be used are:
- Black
- White
- Red
- Blue
- Green
- Magenta
- Yellow
- Cyan
- Colour# (where # is replaced by a number between 1–56 to choose from a different variety of colours)
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. |
| . (full stop) | Formats numbers with a decimal separator.
|
| , (comma) | Formats numbers with a thousands separator. |
| / | Formats numbers as a fraction. |
| % | Formats numbers as a percentage |
| 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. |
| * | Repeats the following character to fill in the remaining space in the cell. |
| _ (underscore) | Adds a space equal in width to the following character. |
Need more help?
Try these next steps:
Visit the Learning Center
Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.
