Excel: I want to display zeros. E.g 001, 002, 003 etc.

Ricochet J · 26 Sep 2012 at 13:18

As per title.

How do I display zeros? E.g 001, 002, 003 etc

Thanks

Pho · 26 Sep 2012 at 13:38

Format the cell as text or prefix your numbers with ' - e.g. '001 '002 etc.

One · 26 Sep 2012 at 13:39

Pho said:
Format the cell as text or prefix your numbers with ' - e.g. '001 '002 etc.

The latter would leave you with an annoying ' though wouldn't it? Text cell formatting should work fine though, and you can still use the values in formulae.

div0 · 26 Sep 2012 at 14:00

Format - Cells

Select 'Custom'

Then where it says 'Type:' just manually enter '00#' and press Ok.

You can now enter any number and it will be in the form you stated.

Ricochet J · 26 Sep 2012 at 14:17

Thanks all. I went with div0's suggestion in the end.

Pho · 26 Sep 2012 at 14:20

desires said:
The latter would leave you with an annoying ' though wouldn't it? Text cell formatting should work fine though, and you can still use the values in formulae.

Nope excel hides it and just displays the number (though it does stick a green triangle at the top corner to let you know what you've done).

kissenger · 3 Oct 2014 at 11:32

Sorry to revive this super old thread, but I googled the problem and this is the first hit. Devo's suggestion works, but it doesn't allow you to enter the first few manually (e.g., 001, 002, 003, etc) and then select the cells, hit the button at the lower right of the bottom selected cell and drag down to the rest of that column to make it read, '004, 005, 006', etc after that.

Is there a way to do that? It just turns everything to '001, 001, 001', etc.

this_is_gav · 3 Oct 2014 at 11:39

kissenger said:
Sorry to revive this super old thread, but I googled the problem and this is the first hit. Devo's suggestion works, but it doesn't allow you to enter the first few manually (e.g., 001, 002, 003, etc) and then select the cells, hit the button at the lower right of the bottom selected cell and drag down to the rest of that column to make it read, '004, 005, 006', etc after that.

Is there a way to do that? It just turns everything to '001, 001, 001', etc.

Make the second cell the following number (002), highlight both and then use the corner to drag down.

kissenger · 3 Oct 2014 at 12:18

this_is_gav said:
Make the second cell the following number (002), highlight both and then use the corner to drag down.

Yeah, that's what I mean. I'm doing exactly that and the numbers are all turning to '001'.

Edit: I just tested it out and what you said works on a new sheet, but not in the sheet I'm working with, which has been filtered in some columns.