I’m a big fan of custom number formats in Excel. People generally think that formatting is all about making your data “pretty” for want of a better term. It’s not. It’s all about making your data easier to read. In this blog I’m going to start by showing you how to deal with leading zeros, aka insignificant zeros, in your Excel spreadsheets.
Entering Leading Zeros into a Cell in Excel
Let’s start by dealing with leading zeros. For instance, if you need to enter a part number or serial number into a cell in Excel that starts with one or more zeros, excel with ignore them by chopping them off. Excel sees them as insignificant. For example, if you were to type in 000467359, you would be left with 467359.
Formatting a Cell to Text to Deal with Leading Zeros in Excel
You can deal with these types of numbers in 2 ways. Format the cell to text and then type the number into the cell or use a custom number format. If you choose to format the cell to text you can do this as you type the number into the cell by starting the number with an apostrophe. For instance, if we were to use the previous example you would type into the cell ‘000467359. Excel would then treat the number as text and you would be left with 000467359, left aligned in the cell.
This is the default alignment for text and a dead giveaway for a number stored as text. You will also be left with a little green triangle in the top left corner of the cell. This is called an error indicator. Click into the cell and click the smart tag that appears, normally to the left of the cell and this will give you the option to – Ignore error.
Now that I’ve explained entering a number as text into an excel cell I’m going to tell you that I never use this method. I use a custom number format to deal with numbers like this. All the leading zeros need to be typed in whereas if you use a custom number format, Excel will do the hard work for you. Image if you imported data into Excel with a 100,000 records. Happy typing! Not only that, if you need to change the number of digits or the look and feel of how the number is presented then you have to manually re-enter the data or use formulas to change the data. This is not the case with a custom number format.
Setting up a Custom Number Format in Excel
Let’s now take a look at a custom number format to deal with leading zeros. If you know that the numbers in a column will always be 8 numbers in length and some of them may start with a leading zero then you may want to set a custom number format to deal with it. Firstly, let’s start with a simple example.
- Select the cell or range of cells that you want to format.
- Go into format cells (Ctrl +1 for the keyboard shortcut fans)
- Click the number tab if it’s not already selected.
- Select the Custom category at the bottom of the list-box
- Delete the word General under the Type textbox.
- Type in 8 zeros – 00000000
Using the 0 in a custom number format will display a zero in that position if there is no significant number in the same position. To explain further, the first zero on the right is for the units, the second 0 from the right is for the tens, the third zero is for the hundreds and so on. If there is no number in that position then an insignificant zero will be displayed. I personally refer to these as leading zeros however Excel refers to them as insignificant zeros.
If your eight digit number was in the following format XX-XX-XX-XX then you would enter the following custom number format: 00-00-00-00. The number is still a number however Excel will place a dash in between the tens and the hundreds and another dash between the thousands and the ten thousands and another dash between the hundred thousands and the millions. I need to point out that the real number that was originally entered into the cell doesn’t change however the way you see it does change. Also, this is still a number and can be used in any formula if required.
Conclusion:
I use custom number formats to display leading zeros as they are easy to set up, easy to change and the real value of the cell remains the same. Formatting a cell to text to display leading zeros causes all sorts of problems and should be avoided where ever possible.
Want to learn more on Custom Number Formats – attend an Excel Intermediate course
View all Microsoft Excel Training Courses
