Excel Keeping Row Data Consistent Over Multiple Columns

Question

Good day all,

Working on Mac 13.4.1

My company uses Excel to maintain a sizeable inventory of individual items that use 14 columns of different widths to contain the information.

For example: The row contains a part; column A is the entry date, column B is the quantity, Column C is the part number, column D is the serial number, etc. Every row is a different part with a different part number, serial, etc.

My inventory has over 7000 line items, most of which have their own unique part number and associated serial number. All the way over to column I is the location of where I have this part, in a specific container on a specific shelf. What I do as inventory comes in is I add it to the bottom of the excel sheet, select column I for the whole sheet and sort so that parts go to their specific storage area alphabetically by column I and I can scroll to the area I need or search by part number/serial number and find it's location.

Something that has happened about 2 years ago, that is now beyond recovery, is that at some point rows started shifting away from various columns. So all of a sudden, row part number number 350 has the serial number from row 355 and the item location from row 345, etc. All throughout the page and all at sporadic areas. At this point, it's too far gone to fix. EDIT: I should state, I don't insert individual cells, but it's acting like that happened at some point. If I ever insert, it's always a full row so everything shifts correctly.

My company did just move locations, and is now a priority to re-inventory every item. Now is my chance to create a new Excel file and see if there is a way to lock each row so that every row entry can not change. Any time I enter something new the whole rows shifts as one to its correction location. I already have the first row locked as that is where each column is labeled, but I need to make it so every row stays the same as entered. Data in these rows doesn't really change, just entry and removal. Is there any setting that could assist with this?

Thanks in advance

Answer 1

Another option for future reference is to convert the data rang to an Excel Table. That provides a number of feature including the AutoFilter. (The AutoFilter can be imposed without converting to a Table, though.) See this Microsoft Support Article [also available in Excel Help] for details.

Answer 2

You wrote "select column I for the whole sheet and sort". If I read that correctly, it is the cause of the problem: by selecting column I and sorting, you sort just that column, and not the rest of the data.

The correct way to do this is to select just one cell in column I and sort. Excel will then sort the entire range with column I as sort key.