How to sort a column but keep intact rows in Excel?
When working with Excel worksheets that contain several columns and rows, you may frequently need to sort the data in a specific column—for example, to arrange names alphabetically or to order sales amounts from largest to smallest. However, simply sorting a single column without considering the entire row will disrupt your data correspondence, causing values in other columns to no longer match up with their intended records. The ideal approach is to sort a column while ensuring each complete row remains intact, preserving all relationships across columns. The following guide explores practical solutions to achieve this in Excel, covering native features, add-ins, formulas, and VBA automation, suitable for various common scenarios.
Sort a column but keep rows by Sort function
Sort a column but keep rows by Advanced Sort function
Sort a column but keep rows using an Excel Formula helper column
Sort a column but keep rows with a VBA Macro
Sort a column but keep rows by Sort function
Excel’s built-in Sort function is commonly used to sort lists while maintaining the integrity of all rows. It is a suitable option for basic sorting tasks on tables or data lists and is quick to apply for most situations.
1. Select the column data you want to sort.
2. Go to the Data tab in the ribbon, then click Sort. See screenshot:
3. If your data selection triggers the Sort Warning dialog, make sure to choose the option Expand the selection. This is a critical step—keeping this selected will make Excel sort your chosen column and rearrange all corresponding rows together, so none of your row data becomes mismatched. Then click Sort.
4. In the Sort dialog, specify the column you want to sort by, set your sorting criterion (such as smallest to largest, largest to smallest, or alphabetical order), and then confirm the sorting order (either ascending or descending). See screenshot:
5. Click OK. The data in the selected column will now be sorted, and all corresponding rows will remain properly aligned.
Tips and Precautions: Before sorting, double-check that your selection covers all relevant columns in your dataset to avoid accidental data disruption. Sorting is best performed on complete tables organized with clear headers; irregular data ranges or merged cells may result in sorting errors or incomplete results.
Applicable Scenarios: This method works well for most standard tables and is recommended for quick, straightforward sorting needs.
Limitations: The built-in Sort function does not directly support more advanced sorting needs—such as sorting by absolute values, months, custom lists, or multi-level criteria. This is where specialized solutions such as Kutools' Advanced Sort, formula-based helper columns, or VBA macros can be helpful.
Sort a column but keep rows by Advanced Sort function
If you need to sort by more complex criteria (for instance, sort by absolute values, months, days, frequency, or last names), the built-in Sort function might not provide all needed features. Kutools for Excel’s Advanced Sort utility is tailored for these situations, offering additional sorting capabilities and flexibility.
After installing Kutools for Excel, please follow these steps:
1. Select the entire data range you want to sort—be sure that all relevant columns are included to keep your rows intact.
2. Navigate to Kutools Plus > Sort > Advanced Sort. See screenshot:
3. In the Advanced Sort dialog, choose the column you want to sort, select your sorting rule (such as absolute value or other advanced options), and determine the sort order. See screenshot:
4. Click Ok to apply the sort. Kutools will rearrange your selected column according to your specified criteria and keep all row data correctly matched and intact.
Advantages: Kutools for Excel dramatically expands sorting possibilities, especially for users working with complex or special data patterns that Excel’s native sort cannot handle. It is user-friendly and integrates seamlessly into Excel’s ribbon.
Precautions: Be sure your data is well organized; avoid merged cells or empty rows which may interfere with advanced sort operations. Review your results after sorting for accuracy.
If you are interested in Kutools for Excel’s Advanced Sort utility, you can click here to know more details about this utility.
Sort a column but keep rows using an Excel Formula helper column
For some sorting tasks, you may need to organize your data by custom rules—such as ranking numbers or ordering by specific criteria not directly available in the Sort dialog. In these cases, inserting a helper column with formulas to generate ranks or sort keys is a practical solution. This method is highly applicable to scenarios like sorting based on unique formulas, dynamic changes, or when data needs to be visually sorted while maintaining intact rows.
Let’s consider an example: Suppose your data spans columns A to C, and you wish to sort by values in column B.
1. In an adjacent column (for example, column D), enter the following formula in cell D2 to generate sort order based on values in column B:
=RANK.EQ(B2, $B$2:$B$10,1)This formula ranks each value in column B within the range B2:B10. The third argument 1 sorts in ascending order; use 0 for descending order.
2. Press Enter, then copy the formula down along column D to cover your entire data range (select D2, drag the fill handle down to D10).
3. Now, select all your data columns (A to D), and apply the Sort function from the Data tab, choosing the helper column (D) as your sort key. Make sure "Expand the selection" is chosen when asked, to keep rows intact.
Tips: Adjust formula ranges as appropriate for your dataset. Helper columns can be hidden after sorting for a cleaner presentation. Ensure your helper formulas reference the right range for accurate ranking. Formulas such as =SORT() or =LARGE() can also be used based on specific needs.
Sort a column but keep rows with a VBA Macro
If you need to automate sorting tasks—such as sorting by multiple conditions, or repeatedly sorting data as part of regular work—or if standard sorting and formulas are insufficiently flexible, using a VBA macro is a practical solution. VBA automation is suitable for scenarios where customized logic, batching, or frequent sorting is required.
1. To add this macro, click Developer in the ribbon > Visual Basic, which opens the Microsoft Visual Basic for Applications window. Then click Insert > Module, and copy and paste the following code:
Sub SortColumnKeepRows()
Dim SortCol As Range
Dim FullRange As Range
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set FullRange = Application.InputBox("Select full data range (include all columns)", xTitleId, ws.UsedRange.Address, Type:=8)
Set SortCol = Application.InputBox("Select the column for sorting", xTitleId, ws.UsedRange.Columns(1).Address, Type:=8)
FullRange.Sort Key1:=SortCol.Cells(1), Order1:=xlAscending, Header:=xlYes
End Sub2. To run the macro, click the 
“Run” button. Two prompts will appear: first, to select your full table range (all columns), and second, to choose which column to sort by. After confirming, your data will be sorted, keeping all rows intact.
Tips: Modify the Order1:=xlAscending parameter for descending sort (xlDescending) if needed. This script safely handles errors if invalid ranges are selected and works for both standard and custom table sizes.
Advantages: VBA solutions are highly customizable for complex workflows, multi-level sorting, and batch operations. Once set up, you can reuse macros for repeated tasks without manual configuration.
Cautions: Always save your workbook before running macros, as VBA actions cannot be undone using standard Excel undo. Check your selected ranges to avoid sorting only part of your data. If you encounter permission errors, ensure macros are enabled in Excel’s Trust Center settings.
Demo: Advanced Sort using Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in