Format numbers in Excel with keyboard shortcuts

Formatting changes in Excel worksheets improve their appearance and target specific data. Formatting changes the appearance of the data, but does not change the actual data in the cell. This is important when the data is used in calculations.

The instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel for Mac.

Content
  1. Number format in Excel
  2. Apply number formatting
  3. Apply number formatting with ribbon options
  4. Apply number formatting in the Format Cells dialog box
  5. Apply currency formatting
  6. Apply currency formatting with keyboard shortcuts
  7. Apply currency formatting with ribbon options
  8. Apply currency formatting in the Format Cells dialog box
  9. Apply percentage formatting
  10. Apply percentage formatting with keyboard shortcuts
  11. Apply percentage formatting with ribbon options
  12. Apply percentage using Format Cells dialog box

Number format in Excel

Number formatting in Excel changes the appearance of a number or value in a cell on a worksheet. Number format is associated with the cell, not the value in the cell. Number formatting doesn’t change the actual number in the cell, just how it looks.

For example, select a cell formatted for negative, special, or long numbers, and the formula bar above the worksheet displays a normal number instead of the formatted number.

There are several ways to change the format of numbers:

  • Keyboard shortcuts.

  • Format icons on the ribbon.

  • Format Cells Dialog .

Number formatting can be applied to a single cell, entire columns or rows, a selected range of cells, or the entire worksheet.

The default format for cells that contain all data is the global style. This style has no specific format and by default displays numbers without dollar signs or commas. In the general style, mixed numbers (numbers with a fractional component) are not limited to a certain number of decimal places.

Apply number formatting

The keyboard shortcut for applying number formatting to data is: ctrl + Shift + ! (Exclamation mark).

Formats applied to selected data numbers using keyboard shortcuts:

  • Two decimal places.

  • Comma (,) as thousands separator.

To apply number formatting to data using keyboard shortcuts:

  1. Select the cells that contain the data to be formatted.

  2. Press and hold ctrl + Shift .

  3. Press the exclamation mark key (! ).

  4. Release the keys ctrl + Shift .

  5. The numbers in the selected cells are formatted with two decimal places and a comma separator.

  6. Select a cell to display the original unformatted number in the formula bar above the worksheet.

For numbers with more than two decimal places, only the first two decimal places are displayed. The remaining decimal places are not removed and are used in calculations with these values.

Apply number formatting with ribbon options

Some commonly used number formats are available on the Home tab, as shown in the image above. But most of the number formats are in the Number Format drop-down list.

Choose from a list of number formats:

  1. Select the data cells you want to format.

  2. Click the down arrow next to Numeric format to open the drop-down list.

  3. Select Number to apply this setting to the selected data cells.

The numbers are formatted with two decimal places, as with the shortcut above, but the comma separator is not used with this method.

Apply number formatting in the Format Cells dialog box

All number formatting options are available in the Format Cells dialog box.

There are two options to open the dialog box:

  1. Select the dialog box launcher. It’s a small arrow pointing down in the lower-right corner of a group of numbers.

  2. Click ctrl + a .

The cell formatting options in the dialog box are grouped in number format tab lists on the Number tab. In this tab, the available formats are categorized in the left pane. When you select a parameter in the window, its attributes and a preview of that parameter are displayed on the right.

When you select Number, there are several attributes that can be configured:

  • The number of decimal places to display.

  • Using the comma separator up to thousands.

  • Formats for negative numbers.

Apply currency formatting

Apply currency formatting with keyboard shortcuts

The keyboard shortcut for applying currency formatting to data is: ctrl + Shift + $ (dollar sign).

Default currency formats applied to selected data using keyboard shortcuts:

  • dollar sign

  • Two decimal places.

  • comma (, ) as thousands separator.

To apply currency formatting to data using keyboard shortcuts:

  1. Select the cells that contain the data to be formatted.

  2. Press and hold keys ctrl + Shift .

  3. Press the dollar sign key ( $ ).

  4. Release the keys ctrl + Shift .

  5. The selected cells are formatted as currency and show dollar signs, two decimal places, and comma separators, if applicable.

  6. When you select a cell, the original unformatted number appears in the formula bar above the worksheet.

Apply currency formatting with ribbon options

The data format can be applied to the data by selecting: Currency in the Number Format drop-down list.

The dollar sign ($) icon in the Number group on the Home tab is not intended for the currency format. This is for the accounting format as shown in the image above.

The main difference between the two is that the accounting format aligns the dollar sign to the left of the cell and the data to the right.

Apply currency formatting in the Format Cells dialog box

The currency format in the Format Cells dialog box is very similar to the number format, except for the ability to select a currency symbol other than the default dollar sign.

The Format Cells dialog box can be opened in two ways:

  1. Select the dialog box launcher. It’s a small arrow pointing down in the lower-right corner of a group of numbers.

  2. Click ctrl + a .

In the dialog box, select Currency in the category list on the left to view or change the current settings.

Apply percentage formatting

Ensure that data shown in percent is entered in decimal form. For example, 0.33, which is displayed as 33% when formatted as a percentage.

With the exception of the number 1, whole numbers (numbers without decimals) are usually not formatted as percentages because the displayed values ​​are multiplied by 100.

For example, when formatted for percentages:

  • The number 1 is displayed as 100%.

  • The number 33 is represented as 3300%.

Apply percentage formatting with keyboard shortcuts

The keyboard shortcut that can be used to apply number format to data is: ctrl + Shift + % (percent symbol).

Formats applied to selected data numbers using keyboard shortcuts:

  • 0 decimals.

  • Percent sign added.

To apply percentage formatting to data using keyboard shortcuts:

  1. Select the cells that contain the data to be formatted.

  2. Press and hold keys ctrl + Shift .

  3. Press the percentage key (% ).

  4. Release the keys ctrl + Shift .

  5. The numbers in the selected cells are formatted to represent the percent symbol.

  6. Select a formatted cell to display the original unformatted number in the formula bar above the worksheet.

Apply percentage formatting with ribbon options

The percentage format can be applied to data with: Percentage Style (% ) in the number group on the Home tab, as shown in the image above, or by selecting per cent in the Number Format drop-down list.

The only difference between the two is that, like the keyboard shortcut above, Percent Style displays zero decimal places, while Percentage displays up to two decimal places. For example, as shown in the image above, the number 0.3256 is represented as:

  • 33% when formatting with Percentage style.

  • 32.56% if made up with a percentage.

The numbers are formatted with two decimal places, as with the shortcut above, but the comma separator is not used with this method.

Apply percentage using Format Cells dialog box

Given the number of steps required to access the percentage formatting option in the Format Cells dialog box, it is very rare to use this choice instead of one of the methods mentioned above.

The only reason to select this option is to change the number of decimal places displayed to numbers formatted for percentages. In the dialog box, the number of decimal places to display can be set from zero to 30.

Select one of these methods to open the Format Cells dialog box:

  1. Select the dialog box launcher. It’s a small arrow pointing down in the lower-right corner of a group of numbers.

  2. Click ctrl + a .

Leave a Reply

Your email address will not be published.