Formatting Numbers in Excel Using Shortcut Keys

Businessman
baona / Getty Images

Formats are changes that are made to Excel worksheets in order to enhance their appearance and/or to focus attention on specific data in the worksheet.

Formatting changes the appearance of data, but does not change the actual data in the cell, which can be important if that data is used in calculations. For example, formatting numbers to display only two decimal places does not shorten or round values with more than two decimal places.

To actually alter the numbers in this way, the data would need to be rounded using one of Excel's rounding functions.

01
of 04

Formatting Numbers in Excel

Number Format Categories in the Format Cells Dialog Box in Excel
© Ted French

Number formatting in Excel is used to change 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. In other words, number formatting does not change the actual number in the cell, but 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 is displayed in the formula bar above the worksheet.

Methods covered for changing number formatting include:

  • 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 and mixed numbers - numbers containing a fractional component - are not limited to a specific number of decimal places.

02
of 04

Applying Number Formatting

Formatting Options on the Home Tab of the Ribbon in Excel
© Ted French

The key combination that can be used 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 down the Ctrl and Shift keys on the keyboard
  3. Press and release the exclamation point key ( ! )  - located above the number 1 - on the keyboard without releasing the Ctrl and Shift keys
  4. Release the Ctrl and Shift keys
  5. Where appropriate, the numbers in the selected cells will be formatted to display the above-mentioned formats
  6. Clicking on any of the cells displays the original unformatted number in the formula bar above the worksheet

Note: for numbers with more than two decimal places only the first two decimal places are displayed, the rest are not removed and will still be used in calculations involving these values.

Apply Number Formatting Using Ribbon Options

Although a few commonly used number formats are available as individual icons on the Home tab of the ribbon, as shown in the image above, most number formats are located in the Number Format drop-down list - which displays General as the default format for cells  To use the list options:

  1. Highlight the cells of data to be formatted
  2. Click on the down arrow next to the Number Format box to open the drop-down list
  3. Click on the Number option in the list 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 through the Format Cells dialog box.

There are two options for opening the dialog box:

  1. Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon
  2. Press Ctrl + 1 on the keyboard

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. Click on an option in the window and the attributes and a sample of that option are displayed to the right.

Clicking on Number in the left-hand window shows the attributes that can be adjusted

  • number of decimal places displayed
  • the use of the comma separator for thousands
  • displaying options for negative numbers
03
of 04

Apply Currency Formatting

Accounting vs. Currency Formatting in Excel
© Ted French

Applying Currency Formatting Using Shortcut Keys

The key combination that can be used 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 ( , )

Steps to Applying Currency Formatting Using Shortcut Keys

To apply currency formatting to data using shortcut keys:

  1. Highlight the cells containing the data to be formatted
  2. Press and hold down the Ctrl and Shift keys on the keyboard
  3. Press and release the dollar sign key ( $ ) - located above the number 4 - on the keyboard without releasing the Ctrl and Shift keys
  4. Release the Ctrl and Shift keys
  5. The selected cells will be formatted currency and, where applicable, display the above-mentioned formats
  6. Clicking on any of the cells displays the original unformatted number in the formula bar above the worksheet.

Apply Currency Formatting Using Ribbon Options

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

The dollar sign ( $ ) icon located in the Number group on the Home tab of the ribbon, is not for the Currency format but for the Accounting format as indicated 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 itself 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 can be opened one of two ways:

  1. Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon
  2. Press Ctrl + 1 on the keyboard

In the dialog box, click on 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
© Ted French

Ensure that data being displayed in percent format is entered in decimal form - such as 0.33 - which, when formatted for percent, would display correctly as 33%.

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

For example, when formatted for percent:

  • the number 1 would be displayed as 100%;
  • the number 33 would be displayed 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

Steps to Applying Percent Formatting Using Shortcut Keys

To apply percent formatting to data using shortcut keys:

  1. Highlight the cells containing the data to be formatted
  2. Press and hold down the Ctrl and Shift keys on the keyboard
  3. Press and release the percent symbol key ( % ) - located above the number 5 - on the keyboard without releasing the Ctrl and Shift keys
  4. Release the Ctrl and Shift keys
  5. The numbers in the selected cells will be formatted to display the percent symbol
  6. Clicking on any of the formatted cells displays 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 the percent icon located in the Number group on the Home tab of the ribbon, as shown in the image above, or by selecting the Percentage option from the Number Format drop-down list.

The only difference between the two is that the ribbon icon, like the keyboard shortcut above, displays zero decimal places while the drop-down list option 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 the ribbon icon
  • 32.56% when formatted using the drop-down list option 

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.

The Format Cells dialog box can be opened one of two ways:

  1. Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon
  2. Press Ctrl + 1 on the keyboard
Format
mla apa chicago
Your Citation
French, Ted. "Formatting Numbers in Excel Using Shortcut Keys." ThoughtCo, Dec. 14, 2017, thoughtco.com/formatting-numbers-using-shortcut-keys-excel-3123611. French, Ted. (2017, December 14). Formatting Numbers in Excel Using Shortcut Keys. Retrieved from https://www.thoughtco.com/formatting-numbers-using-shortcut-keys-excel-3123611 French, Ted. "Formatting Numbers in Excel Using Shortcut Keys." ThoughtCo. https://www.thoughtco.com/formatting-numbers-using-shortcut-keys-excel-3123611 (accessed January 24, 2018).