Formatting Numbers in Excel Using Shortcut Keys

Businessman
baona / Getty Images

Formatting changes made to Excel worksheets enhance their appearance and focus attention on specific data. Formatting alters the appearance of ​data but does not change the actual data in the cell. This is important when the data is used in calculations.

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

01
of 04

Format Numbers in Excel

Number Format Categories in the Format Cells Dialog Box in Excel

Number formatting in Excel changes the appearance of a number or value in a cell in the worksheet. Number formatting is attached to the cell and not to the value in the cell. Number formatting does not change the actual number in the cell, just the way it appears.

For example, select a cell that has been formatted for negative, special, or long numbers and the plain number rather than the formatted number displays in the formula bar above the worksheet.

There are several ways to change number formatting:

  • Shortcut keys on the keyboard.
  • Formatting icons on the ribbon.
  • The Format Cells dialog box.

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

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

02
of 04

Apply Number Formatting

Formatting Options on the Home Tab of the Ribbon in Excel

The key combination to apply number formatting to data is Ctrl+Shift+! (exclamation point).

The formats applied to the selected number data using shortcut keys are:

  • Two decimal places.
  • The comma ( , ) as a thousands separator.

To apply number formatting to data using shortcut keys:

  1. Highlight the cells containing the data to be formatted.
  2. Press and hold Ctrl+Shift.
  3. Press the exclamation point key ( ! ).
  4. Release the Ctrl+Shift keys.
  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 involving these values.

Apply Number Formatting Using Ribbon Options

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

To choose from the list of number formats:

  1. Highlight the cells of data to be formatted.
  2. Select the down arrow next to Number Format to open the drop-down list.
  3. Select Number to apply this option to the selected cells of data.

Numbers are formatted to two decimal places as with the keyboard 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 small downward pointing arrow in the bottom right corner of the Number group.
  2. Press Ctrl+1.

Cell Formatting options in the dialog box are grouped together on tabbed lists with the number formats located under the Number tab. On this tab, the available formats are subdivided into categories in the left-hand window. When you select an option in the window, the attributes and a sample of that option display on the right.

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

  • The number of decimal places to display.
  • The use of the comma separator for thousands.
  • The formats for negative numbers.
03
of 04

Apply Currency Formatting

Accounting vs. Currency Formatting in Excel

Applying Currency Formatting Using Shortcut Keys

The key combination to apply currency formatting to data is Ctrl+Shift+$ (dollar sign).

The default currency formats applied to the selected data using shortcut keys are:

  • The dollar sign.
  • Two decimal places.
  • The comma ( , ) as thousands separator.

To apply currency formatting to data using shortcut keys:

  1. Highlight the cells containing the data to be formatted.
  2. Press and hold the Ctrl+Shift keys.
  3. Press the dollar sign key ( $ ).
  4. Release the Ctrl+Shift keys.
  5. The selected cells are formatted as currency and, where applicable, display dollar signs, two decimal places, and comma separators.
  6. When you select a cell, the original unformatted number displays in the formula bar above the worksheet.

Apply Currency Formatting Using Ribbon Options

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

The dollar sign ( $ ) icon located in the Number group on the Home tab, is not for the Currency format. It 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 on the left side of the cell while aligning the data on 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 option to choose a different currency symbol from the default dollar sign.

The Format Cells dialog box is opened one of two ways:

  1. Select the dialog box launcher. It's the small downward pointing arrow in the bottom right corner of the Number group.
  2. Press Ctrl+1.

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

04
of 04

Apply Percent Formatting

Applying Percent Formatting in Excel

Ensure that data displayed in percent format is entered in decimal form. For example, 0.33 which, when formatted for percent, displays as 33%.

With the exception of the number 1, integers (numbers with no decimal portion) are not normally formatted for percent because the displayed values are increased by a factor of 100.

For example, when formatted for percent:

  • The number 1 displays as 100%.
  • The number 33 displays as 3300%.

Apply Percent Formatting Using Shortcut Keys

The key combination that can be used to apply number formatting to data is Ctrl+Shift+% (percent symbol).

The formats applied to the selected number data using shortcut keys are:

  • 0 decimal places.
  • The percent symbol is added.

To apply percent formatting to data using shortcut keys:

  1. Highlight the cells containing the data to be formatted.
  2. Press and hold the Ctrl+Shift keys.
  3. Press the percent symbol key ( % ).
  4. Release the Ctrl+Shift keys.
  5. The numbers in the selected cells are formatted to display the percent symbol.
  6. Select any formatted cell to display the original unformatted number in the formula bar above the worksheet.

Apply Percent Formatting Using Ribbon Options

Percent format can be applied to data using either Percent Style ( % ) located in the Number group on the Home tab, as shown in the image above, or by selecting Percentage from the Number Format drop-down list.

The only difference between the two is that Percent Style, like the keyboard shortcut above, 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 displayed as:

  • 33% when formatted using Percent Style.
  • 32.56% when formatted using Percentage. 

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

Apply Percent Using Format Cells Dialog Box

Considering the number of steps required to access the percent format option in the Format Cells dialog box, there are very few times when this choice needs to be used instead of one of the methods mentioned above.

The only reason for choosing to use this option would be to alter the number of decimal places displayed with numbers formatted for percent. In the dialog box, the number of decimal places displayed can be set from zero to 30.

Choose one of these methods to open the Format Cells dialog:

  1. Select the dialog box launcher. It's the small downward pointing arrow in the bottom right corner of the Number group.
  2. Press Ctrl+1.