Any Excel experts able to help me?

[GMH1974]

I work in payroll. I've only been there a little over a month. Each month I have to extract reports from the payroll system and download them in Excel and both times I've done this, when I try and sort the staff numbers into numerical order, it doesn't sort them properly unless they're 4 digit numbers. It puts 130 between 1255 and 1355 for example. They are all formatted as numbers. I've tried manually re-typing in the numbers it doesn't like and adding zeros at the beginning. What can I do? I need to be able to do V lookups and pivot tables and there's over 550 rows of data. I need it to be behaving correctly before I start trying to analyse it. Thank you.

I think it may help to include the leading zero in the 3 digit numbers.

To do this, highlight column, format cells, custom, type 0000 in the type box and click OK.

Try sorting again. Let us know if it worked.

Thank you, I'll try on Tuesday when I'm back in.

Are you sure that they are all formatted as numbers?

I would try using data tools to reformat the whole column again.

Also try removing any trailing or leading spaces that could be making the numbers act like text.

Thanks for the tip, another thing I hadn't spotted in Excel...

You need to format as numbers, sounds like they are formatted as text

Get chat gpt to do it

I find AI can usually have some good suggestions for my excel queries. I tend to use Chat GPT.

I agree. It sounds like they are formatted as text not numbers.
No way should you need to add leading zeros to make numbers sort numerically!

Agree I'd check the formatting to numbers. This is the method I use:

• enter the number 1 in an empty cell
• copy this cell
• select the cells with text numbers
• go to Paste Special, select "Multiply", and paste