Bottom line: Learn 8 quick keyboard shortcuts to apply number formatting in Excel for Windows & Mac.
Skill level: Beginner
Video Tutorial
Download the File
You can download the Excel file that contains the printable shortcut guide below.
Number Format Shortcuts Guide
If you spend a lot of time formatting numbers in Excel (who doesn't?) then you will love these shortcuts. The Ctrl+Shift+Number Key shortcuts allow us to quickly apply some of the most popular number formatting options including: currency, percent, number, date, and time.
Here is a quick guide that explains what each shortcut does.
The diagram shows the Ctrl & Shift keys on the left side. However, you can also use the Ctrl & Shift keys on the right side of the keyboard. This might make it easier to use two hands to press the combination.
To apply the formatting shortcut:
- Press & hold Ctrl
- Press & hold Shift
- Then press one of the number keys at the top of the keyboard.
The format will be applied to the selected cell(s).
The shortcuts work on both the Windows and Mac versions of Excel.
You can use the
General Number Format Shortcut
Ctrl+Shift+~ is the shortcut to apply the General format.
General is the default number format for all cells on a blank sheet. If the cell contains a number then it displays raw numbers without any comma separators, currency/percentage symbols, etc.
Number Format Shortcut
Ctrl+Shift+1 [!] is the shortcut to apply the standard Number format.
,##0.00
The Number format has a separator for thousands, millions, etc. and two decimal places. The characters used for the separator will depend on your locale settings in Windows.
Time Format Shortcut
Ctrl+Shift+2 [@] is the shortcut to apply the Time format.
h:mm AM/PM
The Time format displays hours:minutes and AM/PM at the end. Checkout my article on the date system for Excel to learn more about date & time data types.
Date Format Shortcut
Ctrl+Shift+3 [#] is the shortcut to apply the Date format.
dd-mmm-yy
This shortcut uses the day-abbreviate month-year format. I don't know of any way to change the shortcut to apply a different date format. It's unfortunate because this is NOT a date format I use very often.
Please leave a comment below if you do know how to change the default date format for Ctrl+Shift+3.
Note: Assigning this shortcut to a macro would be a potential workaround. However, you would lose the undo history when the macro runs.
Checkout my Formatting Shortcuts Add-in for ways around this, plus the ability to create keyboard shortcuts for custom number formats, font & fill colors, and cell styles.
Currency Format Shortcut
Ctrl+Shift+4 [$] is the shortcut to apply the Currency format.
$#,##0.00_);[Red]($#,##0.00)
The Currency format display a currency symbol, thousands separator, and red text for negative numbers.
What about the Accounting Format Shortcut?
This was a question that came up from Kim and Gina on the YouTube video. Unfortunately there is no dedicated Ctrl+Shift shortcut, however, there are some alternatives.
One way is: Alt, H, A, N, Enter. That basically presses the Accounting format button on the Home tab of the ribbon.
Another option is to right-click that button and Add to Quick Access Toolbar. Then move it to the first position in the toolbar. The shortcut to press the first button is Alt+1. The second button is Alt+2, and so on. Here is an article that explains more about the QAT shortcuts.
Percent Format Shortcut
Ctrl+Shift+5 [%] is the shortcut to apply the Percent format.
0%
The Percent format display the percent symbol and no decimal places. Please leave a comment below with tips on how you quickly increase/decrease the number of decimal places.
Scientific Format Shortcut
Ctrl+Shift+6 [^] is the shortcut to apply the Scientific format.
0.00E+00
The Scientific format is typically used for very large numbers. It displays a decimal number, followed by an E (for exponent), and then the power that the decimal number is raised to.
Apply Black Border Shortcut
Ctrl+Shift+7 [&] is the shortcut to apply a black border to the selected cell(s).
If a range of cells is selected then the black border will be applied around the entire selection.
Clear All Borders Shortcut
Ctrl+Shift+_ [underscore] is the shortcut to remove ALL borders from the selected cells. If the selection contains borders on the interior cells then those will also be removed.
Therefore, this shortcut is NOT the exact opposite of Ctrl+Shift+7 when a range of cells is selected.
Additional Resources
If you have numbers stored as text, checkout these articles:
- Keyboard Shortcuts to Convert Text to Numbers
- Menu Key Shortcuts for Text to Numbers and Right-click Tasks
What Are Your Favorite Ways to Apply Number Formats?
The Ctrl+Shift+Number shortcuts are awesome for quick formatting tasks. However, there are A LOT of different ways to apply number formats in Excel.
What are some of your favorite shortcuts and techniques for applying number formats? Please share your answer by leaving a comment below. Thank you! 🙂
Hi Jon,
Thanks for an excellent compilation of formatting shortcuts
I would also add here another useful shortcut which is relatively close to the others: CTRL + 1
It opens “Format Cells…” form, where we can play with the formats too
Thanks Ivan! That’s another great shortcut to know!
Hello Jon,
Thks for the tips but none work w/an azerty keyboard as used in Europe…. 🙁
Pierre
Hi Pierre,
Sorry to hear that. I’m not sure about the equivalents for the AZERTY keyboard. I couldn’t find any info on this. Please let us know if you are able to find any of the shortcuts. Thanks!
I am working on an azerty Mac book and it works like a charme 🙂
Hi Jon,
Are there similar shortcuts for an AZERTY keyboard (Belgian period)?
Not all of the previous shortcuts work on my keyboard.
Kind regards,
Jo
Hi Jo,
Great question! Unfortunately I don’t know the equivalents for AZERTY keyboards and it’s tough to find resources on it. Please let us know if you figure out the equivalents, or which ones work for you. It would be great to have a resource for others in Europe.
Thanks!
<SHF – Copy cell above current cell (numeric values including formatting)
Copy cell above current cell (numeric values not including formatting)
why not mention this tip too
Convert Text to Numbers – Excel Keyboard Shortcuts December 6, 2009 Jon Acampora
Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.
oops, lost part of that previous reply
’ CTL SHF – Copy cell above current cell (numeric values including formatting)
` CTL – Copy cell above current cell (numeric values not including formatting)
and
1 CTL to launch for Formt dialog to get to all formatting
Thanks for the suggestions Ron! I’ll link up the post you mentioned and do a separate post with the copy shortcuts. Those are great ones to know!
Hi Jon
The keyboard that i am using in England works with the same characters wherever they are on the keyboard. . . i use CONTROL SHIFT 4 ($) and get the £ sign format.
note:-
My @ sign is not above the number 2 but still returns the time format.
My # sign is not above the number 3 and does not require the SHIFT key but works great for the date.
Best regards
Kevin
Hi Kevin,
Thanks for letting us know! I’m happy to hear that most of the shortcuts work for you.
Hello Jon!
The only shortcut that seems to work with an AZERTY (french) keyboard is the % mark, with Ctrl+Shift+ù (%). On the figures rank of the keyboard, C+S+1 opens the cell format dialog box, C+S+2 formats in bold, C+S+3 formats in italics, C+S+4 underlines, C+S+5 strikeouts, C+S+ +(=) opens the cell insertion dialog box. C+S+< deletes the content, that's all I've seen. A bit disappointing, compared with what can be done with the qwerty keyboard.
Best regards.
Jacques
Hi John!
I run payroll reconciliations daily and several of my wage codes begin with 3E. 3E10, 3E20, 3E50, 3E70 etc. When these codes are exported out of our pay system into excel they are automatically formatted as scientific. The only way I have been able to prevent this is by making them text which creates downstream errors with formulas. Is there any way around this auto change to scientific format?
very useful article
Thanks
Plz
I need an article explaining custom format in excel
regards
There is a need for a 10 key especially in the areas of finance and for those who rely heavily on spreadsheets. To avoid shoulder, arm, and wrist discomfort, here are the compact keyboards with 10-keys that we recommend and am sure that Shakespeare would use.
Hi Jon,
Thanks for the useful video.
Ctrl+Shift+1 seems not working anymore in office 365, any other recommended shortcut for standard number format?
Many thanks
How to enter a negative dollar amount on a computer thanks
Help! I always used ctrl + shift + number for formatting. However, since a week this does not work anymore. When for instance trying to get a percentage format (ctrl + shift + 5), the cell is overwritten with a 5 when pressing down or when just clicking on a different cell. Same for number formatting (ctrl + shift + 1), but then with a number 1 that overwrites the cell being formatted.
Any advice?
Hi, Harry! I have a similar problem.. ctrl+shift+7 stopped working some time ago.. and occasionally some others ctrl+shift+x .. and I can’t find the solution..
I look for a shortcut to the number format with zero decimals or to the custom format
For some EU (in my case Belgium) users, please try “Ctrl” + “!” to change from 1234,56 into 1.234,56
So no “Shift” needed in this case.
hi,
I want to know how to change next days date by using a shortcut key
Kindly let me know about it
thank you
how to change the default date format
Is there a shortcut to display a numbert as an integer (so that zero is 0 not 0.00, two is 2 not 2.00, etc??)
Thanks