Here’s a tip for you that might help keep your formatting consistent in your excel spreadsheets. Generally speaking, especially if you’re doing calculations, you don’t want to keep leading zeros on numbers in Excel (001234 versus 1234, which is automatically will correct to). However, if you’re entering numbers that need to keep everything entered, zeros or otherwise (like check numbers, phone number, ID numbers, etc…), you’ll want those numbers to be kept as-is. Excel will try to be smart, however, and remove those zeros. Here’s a few ways you can fix that.

Keep the Leading Zero as You Type:

If you’re wanting to keep the leading zero on a single number value, insert a single apostrophe character (’) before you type the number. That will tell Excel to treat the number as text and not monkey with it.

You’ll get a green triangle warning, which shows that Excel thinks there’s a formatting error. If you click on the yellow exclamation drop down that appears, you can tell Excel to ignore it, that this is what you were intending to do:

Set Formatting to Keep Leading Zero

If you are able to plan ahead, and you know that you’re going to need to keep the leading zero on a range of cells, it’s easier to apply formatting in advance. Select the cells you want to format an there are two ways to apply the “text” formatting:

• Select the formatting drop-down and select “Text”
  
• Right-click, select “Format Cells” and select “Text” under the “Number” tab.

Custom Formatting to Force the Issue

The previous options are fine for most folks, but if you want to keep the number stored as a number in Excel (say because you needed to do calculations on it or something) but still keep leading zeros, and you, it gets a bit more complicated, but nothing crazy. Say you have numbers that are always a standard length (say six characters) and you want to keep consistent so 123456 and 045678 are formatted properly and still kept at numbers (so you can run other calculations or increments and such based on them). Excel custom formatting will force the issue.

Right-click on the cells you want the custom formatting, and select “Format Cells.” In the pop-up that you see, make sure you’ve click on the “Number” tab and then you’ll want to hit the “Custom” category. Type in a zero for each digit that’s in your fixed-length number (in our case, six zeros). So it’ll look like this:

Now whatever number you put in the field, it will automatically add zeros to the beginning of the number to fill out, based on how many digits you put in:

If you want to go crazy with custom number formatting, here is Microsoft’s documentation on what all those variables mean.

Any other Excel tips or tricks you’d like to see? Comment below and we’ll share them in an upcoming article!