Join Dennis Taylor for an in-depth discussion in this video Learning the important formatting keystroke shortcuts, part of Excel 2007: Advanced Formatting Techniques.
Excel has quite a few keystroke shortcuts and it really makes your use of this software a lot more efficient and quite a few of them are related to changing the display of values. In Excel, when you enter values if you type no special characters and you haven't formatted cells, theformat is called General Format and the data that we are seeing in columns A, B and C were entered that way, also the scientific type data in columns I and J are entered that way. In columns F and G we see information that was entered using slashes and colons as appropriate for ease-of-use.
But let's talk about the idea that we sometimes want to change the way these look and it would be nice if we had keystroke shortcuts instead of relying upon either menus or buttons found in the Ribbon. Imagine columns B and C. Now the standard way that many of us and myself included would format these very often would be the dollar sign button found here that applies Accounting Format. If the columns are wider it keeps the dollar sign off to the left and that's just fine for many people. Now a keystroke shortcut not exactly in tune with that, in fact it isn't really but it's similar, is for currency and it's Ctrl+Shift+$. Relatively easy to remember.
It's found on the number 4 key. So Ctrl+Shift+$ and the obvious difference between this and Accounting will be the location of the dollar sign. You won't always see this unless the column is a bit wider. You see what happens there. So, Ctrl+Shift+$, fast and easy. If you need to adjust the number of decimal places, you will need to use the button found on the Ribbon up here, Decrease the Decimals. Here we go. So, Ctrl+Shift+$ for Currency Format. Now if you didn't want dollars signs and you just wanted commas, the way we often can get there is by this button right here.
But the keystroke shortcut for that is Ctrl+Shift+!. That's on the number 1 key. And it simply puts in the comma and here too it displays two decimals and we can adjust that if necessary. Now the data in column F is certainly readable and this is a standard display used in the United States. But in most other countries in the world, for example, the first one would be 20/5. But if you would like to change the display of these there is one keystroke shortcut associated with dates and it is Ctrl+Shift+#.
And the entries in column G you can see are entered in 24 hour style and that may be just fine. But for certain people it's much clearer when they see the AM and PM. The one keystroke shortcut associated with time is Ctrl+Shift+@. This on the number 2 key. You see that display. In columns I and J, we have examples of scientific data and although the numbers are readable and understandable and recognized of course, these are decimal numbers. Everything is below zero.
These are all above zero. But a different way of displaying this information is to use scientific notation, which uses the letter E and raises numbers to the powers of 10. You wouldn't be using that much. You had a knowledge of that but if you would like to display these in scientific notation that is certainly neater and tidier looking, it is Ctrl+Shift+^ and that's above the number 6 key. Then it displays these with the E notation. By the way you might see that sometimes if you have large numbers in place here and that's in general format. If the column is made too narrow, we see this display and that really confuses people at times, in particular if they don't know about the scientific format.
Now percent. I don't have a formula here just yet, but I want to show the percent of change between these two amounts. So a simple formula would simply be this. = C2/B2-1. This is going to show us the percent of change. Now that is 4.8% but almost nobody would leave it displayed like this. So we would like to change this into percent format and sure enough, Ctrl+Shift+%. That's on the number 5 key. There we are, and here too.
If you'd like to see more decimals and it's quite common when the value is below 10% to show a decimal or so, we must go into the Ribbon and use the Increase Decimal button. Possibly two, possibly one as necessary. And we can double-click to copy this and so that's percent format. So we have seen quite a few choices here and all these choices begin with Ctrl+Shift and then use the keys in the upper left part of our keyboard, keys 1 through 6. I have used the symbol in each case and there's one more of these, which you don't probably need very often but if you ever needed to convert numbers into General format, usually we move away from General format, but if we want these to be so called general format, we are going to be using the key that's usually to the left of the number one.
It's got a Tilde on it. That's a squiggly little character that looks like this and usually on that same key we will see this little symbol too, the accent, and that looks something like that. If we hold down Ctrl+Shift and click that key, say on these cells here, Ctrl+Shift and that little tilde or whatever we want to call it. That will revert or change the display to the General format. And at all times here you can verify the format of a cell simply by right- clicking on it and jumping into Format Cells. It will tell you the current format. But these keystrokes will be invaluable for some people and it speeds up the way we get things done in Excel.
- Saving time with keyboard shortcuts
- Adjusting fonts, cell borders, and fills
- Setting themes
- Formatting numeric data with cell formats
- Using the Conditional Formatting feature
- Hiding repeating column information
- Printing double-spaced data
- Elbow formatting