Sign in or start a free trial to avail of this feature.
10. Formatting Numbers Part 2
Let's apply the different number formats to our dataset columns in this lesson. This will serve to make our dataset much easier to read.
Formatting a Date Column (00:15)
The format cells dialog box provides various date formats, including short forms, such as 01/01/2013 and long forms such as 1 January 2013. More date formats are available in the Custom category. For example, the type dd-mmm-yy produces dates in the form 01-Jan-13.
Formatting Other Columns (01:13)
The Product Price column should be in either the Currency or Accounting format. Accounting can be more readable if the numbers have different lengths.
The Order Number column should be Text, as we don’t want any calculations to be performed on this column.
Formatting Keyboard Shortcuts (02:49)
Using the Format Cells dialog box can be time consuming once you get to know how number formats work. Various shortcuts exist for common commands associated with number formats. For example, Alt, H, 0 can be used to add a decimal place to a cell, while Alt, H, 9 removes a decimal place.
One useful shortcut is Paste Formats, which has the shortcut Alt, E, S, T. This pastes the format of a cell into another location, without pasting any of the cell content.
In the previous lesson we used the format cells dialogue box to change the number category of a cell.
Let's now escape from the format cells dialogue box, and put our learnings to the test. Let's start with the order date. I want to select all of the order date entries with Ctrl + Shift + Down Arrow, and I'm going to change the font of this to the long form. Let's press Ctrl + 1, let's press Alt +T to change the type, and press OK to implement the long form. This doesn't look quite right, so let's try and change it to another format.
Ctrl + 1 again, and I know that there are more formats available in Custom. I'll go to Custom by pressing C, and then move to Type and press Tab. And this allows me to scroll down and pick a date option, which I'll eventually reach. This looks pretty good.
Press OK, and that looks much better. Let's quickly fix the column width by selecting it with Ctrl + Space, and then Alt + O C A to autofit.
Let's now move on to the next column.
Phone number to me looks Okay, as does address and order quantity. But I'd like to change product price by adding thousand separators and the dollar sign. So let's move over to this data column, so Ctrl + Shift and press Down Arrow to select the data, and then we press Ctrl + 1 to bring up the format cells dialogue box. I'll first try Currency, so let's press Alt + C to enter category drop down, press C for Currency, and the first thing we'll have to do is change the symbol. So Alt + S and we'll find the dollar sign. Here it is. Press Enter.
Let's also change the decimal places, because I don't want any appearing for product price. Alt + D, then we can press zero, then we press OK, and that looks better. Not quite ideal, but definitely better.
Let's try accounting to see if it improves it somewhat.
Ctrl + 1 again. Alt + C, and then move to accounting and press OK. And I think that looks better and makes it more readable. The last column we'll look at is order number, and we're going to change these numbers to text, so that no mathematical formulas can be performed on them.
Let's go back into our format cells dialogue box, and let's move to text by pressing T twice. Then press OK, and now the numbers are aligned on the left, so that we know it's text, and these numbers are protected from mathematical formulas. When starting off, it's good practice to use Ctrl + 1, so that you can see all of the format types on offer, before picking one. However, after a while it can become a little tedious, and thankfully, Excel provides a number of useful shortcuts to automatically change the number type of a cell. I've included some on this list in the show notes. Excel 2007 and Excel 2010 also provide the ability to access options in the ribbon from the keyboard. The two most useful commands in this regard are Alt + H 0 for adding a decimal place, and Alt + H 9 for removing it. When we press Alt + H, it brings up all the shortcuts in the home tab of the ribbon, then we press zero to add a decimal place, and then we can press Alt + H 9 to reduce it again. Very simple, but very useful. Before I leave this topic, there's one last command I need to cover, which is Paste Formats. After spending a bit of time finding the right format for a cell, it would be nice to be able to transfer this to other places on the data sheet. And to do this, we use the formula Alt + E S T, which just pastes the format to a new cell. Let's quickly type a date in the short form in a new cell. Let's say the 1st of January, 2013.
Now let's copy one of the cells in the new format with Ctrl + C, then let's go to our new cell and press Alt + E S T. And there we have our new date, in the correct format. Again, very simple, but very useful.
Alt + E S T is one of the most commonly used commands in Excel, and will save you buckets of time when formatting spreadsheets.
I've covered a lot of ground today, with quite a few shortcuts included, but hopefully this lesson has removed some formatting confusion that may have existed before. In the next lesson, we'll move onto formatting cell borders, fonts and fill colors.