Keyboard Shortcuts for Formatting Numbers
Free Online Microsoft Excel Tutorial
Excel 2010 Keyboard Shortcuts
* Shortcuts for Applying Formats
* Shortcuts for General Number & Scientific Number Formats
* Shortcuts for Time and Date Format
* Shortcuts for Percentage Format
* Shortcuts for Currency Format
Part of working smarter and not harder in Excel means becoming a pro at using keyboard shortcuts to format numbers.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Keyboard Shortcuts for Formatting Numbers Test
* Excel Shortcuts for Applying Formats
There is a lot more to number formats in Excel than just making your worksheet look neat and professional. The type of number format you apply to a cell adds value to the cell in that it tells a formula or function what type of data to expect and how to handle that data. This makes complex calculations, for example, subtracting times and dates and scientific calculations, possible.
It is important to decide the types of data you are expecting a column, row or section of cells to contain and then format those cells accordingly, preferably before the data has even been inserted.
1. To apply number formats, decide what type of data the cells, column or rows will contain.
2. Select the affected cells, columns or rows.
3. Press Ctrl+1 on your keyboard to launch the Number Format dialogue box (see screenshot below):
4. Select the most appropriate format for your selected data type from the list.
5. For example, if you study the screenshot of a section of a worksheet below, you will note the data types are text, date, time, currency and possibly percentage.
Formatting numbers and other data types in a worksheet not only adds context making it easier to read and understand the data, it also allows you to perform formula calculations. Often times when a formula is yielding unexpected results, the problem is not with the formula but with the underlying number format(s).
Excel often tries to think for you and will identify the content entered into a cell and assign a format to it automatically. This can be annoying when you are trying to enter specific types of data, for example, a telephone number or an item number that displays incorrectly. To ensure Excel treats such types of content as text, insert an ' (apostrophe) in front of the number. The apostrophe will not print or display in the cell, but will ensure Excel does not try to autoformat the cell.
To repeat your last formatting action into a next cell, press F4 on the new cell to be formatted. You can do this as many times as needed and it will save you a lot of time especially when formatting cells that are not located close together.
* Shortcuts for General Number & Scientific Number Formats
The General number format is probably the number type you will use most often. If you do not know what format the content of a cell should be or if the format is not pertinent to a formula or your work, you can apply the General format to it. You can also use the General format to remove problematic formatting from a worksheet, for example if formulas are not calculating correctly.
1. To apply the General format, select all the cells, rows or columns you wish to reformat.
2. Press Ctrl+Shift+~ (tilde) on your keyboard.
3. The format of the cells you have selected will now show as General in the Number format preview box (circled in yellow in the screenshot below):
Another commonly used number format is to format numbers entered into a column (or row) with two decimal places, a thousands separator and a minus sign for negative numbers. Applying this format to a column in which there is a formula that calculates values based on other cells, will automatically apply this format to the result of the formula. This makes data much easier to interpret.
1. To apply the Number format, select the cells you wish to apply this format to.
2. Press Ctrl+Shift+! to apply the Number format.
The Scientific format is for all you mathematical and scientific types out there, or if you are like me and just looking for an easy way to do your math homework, you may want to write this shortcut down.
1. To format cells that will contain numbers that need to be formatted as scientific numbers with two decimal places and an exponentiation symbol, first select the cells you wish to format.
2. Press Ctrl+Shift+^ to apply the Scientific number format to the contents.
3. The result will look like this:
4. You can increase the number of decimal points by using the Increase Decimal button in the Number group under the Home tab on the Ribbon. You can also decrease the number of decimal points by using the Decrease Decimal button located next to the Increase Decimal button.
* Excel Keyboard Shortcuts for Time and Date Format
Working with times and dates in Excel may be a little difficult and/or frustrating when you are not aware that these need to be formatted to display correctly. Excel will automatically format values that look like times and/or dates into the default time or date format. It can be annoying, for example, if you want to enter a date to display as 1 January 2015, but Excel reformats it to 01-Jan-14.
All dates and times are read by Excel as number values. For example the number 1 represents 1 January 1900 and 12 October 2014 is read as having a value of 41924, as that is the number of days that have passed since 1 January 1900. To experiment with the values of dates and times, enter a random date and a random time (into separate cells) and change the format to Number to see the actual date and/or time value.
1. To format dates that are displaying as numbers or text into the date format, select the affected cell or cells and press Ctrl+[Shift]+#. On some keyboards Shift key is not required, depending on where the # key is located.
2. To format times that are displaying as numbers or text into the time format, select the affected cell or cells and press Ctrl+Shift+@.
* Excel Keyboard Shortcuts for Percentage Format
The percentage format allows you to display percentiles with the percentage symbol. This makes data easier to read and understand and may also assist you when calculating formulas.
1. To apply the percentage format, first select the cell or cells you wish to apply it to.
2. Press Ctrl+Shift+% to apply the percentage format to the contents in the cells.
Remember to change values into decimal values first before applying the percentage format. You can do this by using a formula that divides the values by a 100.
* Excel Keyboard Shortcuts for Currency Format
Oftentimes users of Excel will manually insert the currency symbol for amounts entered. The problem with doing this is you will not be able to use any formulas that need to calculate numbers on cells containing a manually inserted symbol. The other issue is if you want to remove the symbol, change the currency to a different currency or use the Accounting formula, you will have to remove the manually inserted currency symbols first.
1. To apply the Currency format to number values, select the relevant cells, rows and/or columns.
2. Press Ctrl+Shift+$ to apply the currency format to the contents. This will insert the currency symbol for the country or region that your computer is set to by default.
The only way of changing the default currency permanently, is to change the country or region of your computer and depending on whose computer you are using, you may have the wrath of the entire IT department on you!
You can change the currency to any country by changing it for each worksheet using the Format Cells dialogue box (Ctrl+1) and changing the currency symbol in the dropdown list.
Well done! Now you have finished the Tutorial try the Test...