One of the most common complaints I get from people who use Excel is that they have a hard time entering in numbers that begin with a leading zero, for example ZIP codes for cities and towns in the northeastern United States, which all begin with the zero. In this movie I will show you how to manage values that start with the zero using several different methods.
- View Offline
- [Voiceover] One of the most common complaints that I get from people who use Excel is that they have a hard time entering numbers that begin with a leading zero. One common example of those type of numbers are zip codes for cities and towns in the northeastern United States which all begin with a zero. In this movie I will show you how to manage values that start with zero using several different methods. This file contains a list of three addresses and one of them is in Connecticut, one in Vermont, and on in Maine in what I'm assuming is that all the US postal codes, which are called zip codes, would start with a zero.
Let me show you what would happen if I were to enter in a postal code or any other number beginning with a zero. I'm in cell E2 as you can see here so I don't know the real zip code for Berkshire, Connecticut so I'll just type 01234 which is the basic five number zip code and hit return. When I do, you can see that Excel changes the number so it's just 1234. That's what it does with general format numbers and also other number formats.
I'll press Command+Z to undo that last entry and move back to E2. So let's say I want to enter in a zip code. I'm not going to be using it as a number. Instead it's going to be text. To indicate that a value is text you start by typing a single apostrophe. Now what follows it will be treated as text so if I type 01234 and press return You'll see that I have what appears to be the numbers 01234 but it's being treated as text.
You also see this green indicator triangle at the top right corner of the cell and when you click the action button next to it you'll see an indication that the cell contains the number stored as text. So it's not a calculation error in the sense of trying to divide by zero but it's an inconsistency that Excel flags. So that didn't work well, I'll press Command+Z again to undo the entry and now what I can do is I can set the cells format to text.
So I'll select cells E2 through E4 and then on the home tab of the ribbon I'll click the Number Format Control's down arrow and here I have a number of formats from which I can select. The one that I want is at the bottom of the list and it's called Text. So I'll click that and then in cell E2 I'l type 01234, enter, and everything works as expected except that I get my error flag again where I have a number that is stored as text.
So I'll press Command+Z again and twice to get rid of the text formatting. Let's say that I want to enter zip codes specifically. I can apply a specific zip code number format to these cells so I still have cells E2 to E4 selected, I'll go back up to the Number Format Control, click the down arrow, and I will click More Number Formats. That opens the Format Cells dialog box and over in the category list I'll click Special.
In here you see the third list of four different special types of formats. The specific formats that you see will change based on your regional language settings and the operating system, but in this case I have Zip Code, Zip Code + 4, Phone Number, and Social Security Number based on my location, the US. So If I want to format the cells that are selected as zip code, in the type list, I'll click Zip Code and then click OK. Now I can click cell E2 and type in 01234, everything looks good and I'll do the same thing for Vermont, 02563, I apologize to anyone from those places if I'm...
butchering those zip codes, and for Maine I'll type 04592 and enter. And you can see that all the numbers are now being treated as zip codes and there's no annoying error indicator to get in the way.
- Entering a data series using the fill handle
- Creating hyperlinks
- Controlling the Ribbon
- Moving between worksheets quickly
- Setting a print area
- Selecting noncontiguous cells
- Applying a table style
- Creating substitute data sets using scenarios
- Wrapping and shrinking cell text
- Entering data quickly
- Removing duplicate values
- Inserting the current date or time
- Generating a list of unique random numbers
- Calculating running totals
- Summarizing data
- Dealing with formula errors