Software

Office challenge: How do you display leading zeroes in Excel?

Learn the answer to last week's Word challenge and test your Excel skills in this week's challenge.

Leading zeroes in a text cell are easy—you just type them if the input cell is a Text-formatted cell. In contrast, if you depend on the default format, General, Excel won't display leading zeroes in a numeric value. That's because leading zeroes just don't make sense in a numeric value. You can have it both ways though. How would you get Excel to display leading zeroes in a Number-formatted value?

Last week we asked…

Why won't my right-aligned text in my footer align properly after I change the right margin? It's a bit of a trick question. Using the special tabs in the header and footer sections does help you quickly center and right-align text, but the results aren't dynamic. That's because you can't change the margin for the header or footer sections. The problem really has nothing to do with tabs or legacy formatting that you don't know about.

My solution was to insert a table into the footer. Doing so won't change the header/footer's margin, but it gives the illusion of doing so. Let's take a look at a quick example:

  1. Open the footer section of any document.
  2. Insert a three-column, one-row table.
  3. Using the alignment tools, center and right-align the text in the second and third columns, respectively.
  4. Right-click the table's move handle (the four-arrow icon at the table's top-left edge).
  5. Choose AutoFit from the resulting context menu and then select AutoFit to Window.
  6. You can also turn off the cell border display if you like.
  7. Close the footer section.

When you change the document's margins, the table in the footer will adjust accordingly—the AutoFit to Window property sees to that. Notice how much wider the margins are in the following figure. You're not actually changing the footer's margins, so if there's anything else in the footer, it won't update.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

18 comments
bobcox
bobcox

the column as text before you paste or enter the data.

skurtz
skurtz

Type a number into the cell, click on format, selecte Custom format and select the @ sign for the format.

jbenton
jbenton

If you want a set no of leading digits, definitely use custom formatting as above; much simpler than formulae, VB or converting to text You can also fill any leading space with zeroes by using *0General as your custom format (right align for best results, but I can't think why you'd want to do this!)

alta
alta

Format the cells as custom and then enter the number of zeros for the length of the numbers entered in the cells into the custom box on the right hand side. Eg: 000000 - the invoice numbers could be 6 numbers long. If the number entered is 345 it will display as 000345.

stapleb
stapleb

If the number was to be treated as text, that is not needed in calculation, I would either format the cells as text, or type an apostrophe before entering the number. If the entry was to be treated as a number and it was to contain the same number of digits I would use format cells, custom format and enter 0000 for four digits, or however many I needed.

seanferd
seanferd

Leading zeroes make perfect sense in 24 hour time format, as well as when preceding a decimal point. Or are we talking strictly Number(2) format? If it is always always a consistent number of integer places before the decimal point, and you always want those places filled with a zero if no other integer is entered (let's say four places), then [Custom → 0000.00]. "0"# will always display one leading zero. Adjust to suit. edit: "0"#.00 for two decimal places. Salt to taste.

remymaza
remymaza

I just use this method via VBA... You can also do this in the GUI, but I find it easier to control output when I'm staring at the code. Columns("E:E").Select Selection.NumberFormat = "_(* 0000_)"

STL
STL

If you need leading zeros in your cell lead it with ' and that will keep your zeros.

RU7
RU7

Make the column wider and zeros get added. When cells get too narrow for the actual data they convert to scientific, then to ###. And double-clicking the column label's border still works to auto-fit the contents. One pecularity though, when the cell is left aligned, the actual digits are right aligned and the leading zeros are left aligned. If the cell is somewhere between widths that are a precise number of digits wide, there is a space between the leading zeros and the actual digits. Another thing to remember is that you cannot have white space on either side of the number.

leisemannl
leisemannl

If you just need a few #'s here and there --- key an ' (apostrophe) then key the number with the leading zeroes. OR if you have a list of numbers without the leading zeroes and you need them .... use the formula =Text(a2,"0000000") where "0000" has the shortest or most common length of your numbers. You can use this if most of your numbers need leading zeroes, and if some of the numbers are longer than the length you set, and don't need leading zeroes. Example: 12345 will appear as 0012345 and 123BCD5678 will appear as 123BCD5678. You just then copy the formula as a value over the original column of numbers and voila! you have your leading zeroes where you need them. I use this a lot with reports that come from the UK office - they don't seem to understand that numbers can function as text, too. I use it to make lookup formulas work so I can transfer data easily between different reports and formats. Lu-C

gkeramidas
gkeramidas

Columns("E:E").NumberFormat = "_(* 0000_)"

Glenn from Iowa
Glenn from Iowa

Rick, I was unable to access the download. I don't know if this was intentional or not by TR, but it looks like the download has been missing at least since December of last year. Do you have an alternate location for these or would you be willing to post them somewhere else on TR?

sopaulj
sopaulj

Looks like you already have good suggestions. If you're not using those numbers for calculation, as a last resort, you can change the cell format to "Text". That is one lazy way to make available those leading zeros.

RU7
RU7

I found out that by adding one or more spaces to the beginning or the end of the *0General format spec you can have white space.

Mark W. Kaelin
Mark W. Kaelin

That is an old download that must have gotten lost in the redirect shuffle. Let me see if I can find it and republish.

Editor's Picks