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

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.
 
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.
 
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).
 
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.
 
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.
 
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.
 
Last edited:
Back
Top Bottom