Software

Question

Excel 2007 corrupts data during sort

We're seeing an issue in Excel 2007 related to sorting data using the sort within an auto filter. Excel behaves differently if you run the sort from the filter drop-down or if you run it from the Ribbon. From the Ribbon, the sort function works every time for all columns. From the drop-down, Excel only sorts the filtered columns.

This behavior is different than 2003 where Excel sorts all of the columns regardless of how many columns have filters. This is a problem since there are manyspreadsheets created in earlier versions of Excel with filters on only some columns and there is no warning that the data will become inconsistent. Any assistance is greatly appreciated.

Below are the steps to recreate the problem.

1. Create a blank spreadsheet in Excel 2007.2. Populate the first row with column headers.3. Populate a few rows worth of data.4. Select some, but not all, columns and click the Filter button on the Ribbon. This applies filters to the columns you select, not all columns.5. Use the sort feature in the drop-down menu in the column header to sort the data.6. No warning is given. Only filtered rows sort. Your table now has inconsistent data

All Answers

re: sorting....

Typically, sorts work on what has been selected. If nothing is selected, it defaults to what is visible. If you have filters in place thus hiding rows, they don't sort and are copied over the top of by sorted data.

Try highlighting all data that you want to sort, from A1 to ZZ99, or wherever it ends, which will select the hidden rows too. That will hold the rows together as one unit and includes hidden data that has been filtered out in the sort criteria.

Personally, I don't like sorting with filters in place because of this problem. You can't see what it's doing. So, I forget about the filters. I use one big sort to get what I don't want to count into one group and then further sort smaller groups of rows down until I get just the group I really want to sort. Then, I can apply filters to hide what I don't want to see.

Start or search

Create a new discussion

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

Post type

Subject title

Topic Tags

Select up to 3 tags (1 tag required)

Cloud

Piracy

Security

Apple

Microsoft

IT Employment

Google

Open Source

Mobility

Social Enterprise

Community

Smartphones

Operating Systems

Windows

Mac

Malware

Tablets

Networking

Browser

Hardware

Software

Web Developerment

Linux

Off Topic

Message Body

Track this discussion and email me when there are updates

Please note: Do not post advertisements, offensive material, profanity, or personal attacks. Please remember to be considerate of other members. If you're new to the TechRepublic Forums, please read our TechRepublic Forums FAQ. All submitted content is subject to our Terms Of Use.