Format numbers

Import number formatting, like dates, currency, or fractions to cells in a worksheet. For instance, if you’re completing your quarterly budget, you can apply the Currency number format. This will present your numbers in a way to symbolise money.

Applies to: Excel for Microsoft 365, 2019, 2016, 2013, 2011, 2010 & 2007 (both Windows and Mac where applicable).

Alternatively, if you have a column of dates, you can clearly state that you prefer the dates to display as March 14, 2012, 14-Mar-12, or 3/14.

Using number formatting

  1. Firstly, choose the cells with your particular numbers you have to format.
  2. Secondly, press CTRL+1 (on Windows). Alternately, press Control+1, or Command+1 (on a Mac)
  3. Thirdly, click the Number tab. Do this in the Format Cells popup window.
  4. Finally, select your desired number format Category. Then, pick precise formatting changes by the right.
Format Cells dialogue box showing Currency formats within number formatting.

Tip: Do you have numbers appearing in your cells as #####? This likely means your cell is too narrow to display the full number.

Simply double-click the right border of the column including the cells with #####.

This will change the column width and row height to occupy the number. As well as this, you can drag the right border of the column to customise the size to your preferences.

Stop your numbers from automatically formatting

Often, you could type numbers into a cell—or transfer them from a different data source—however, Excel formats those numbers differently than from what you anticipate. If, for instance, you enter a number along with a slash mark (/) or a hyphen (-), Excel may assume you’re entering a date and set a Date format.

You can stop this automatic number formatting by defining the Text format to the cells.

This is simple to do:

  1. Firstly, choose the cells with the required numbers to impede automatic formatting.
  2. Secondly, for Windows, press CTRL+1. On a Mac, press Control+1, or Command+1.
  3. Finally, on the Number tab, pick Text in the Category list.
Save as Text in the Format Cells dialogue box as an example of number formatting.

Need more help?

Here are some ways to resolve your queries and/or make a suggestion to Excel.

See Also

Format a date the way you want

Display numbers as percentages

Format negative percentages to make them easy to find

Format numbers as currency

Create a custom number format

Convert dates stored as text to dates

Convert numbers stored as text to numbers

Leave a Reply

%d bloggers like this: