My question is because I wanted to sort the data as provided in the following question:
How to select all the column based minimum date value in a sheet

What I wanted to do is select the data (Range A1:F9) and sort it:

NAME CARD NUMBER ACCOUNT NUMBER SBL Transaction Type SBL Transaction Amount SBL Transaction Date
B 4779 1 POS purchase 280 02-08-2021
B 4779 1 POS purchase 1 03-05-2021
S 1475 2 POS purchase 389 05-04-2021
S 1475 2 POS purchase 755 05-11-2021
S 1475 2 POS purchase 1794 05-15-2021
A 0173 3 POS purchase 1 02-01-2021
A 0173 3 POS purchase 1 02-02-2021
A 0173 3 POS purchase 1 02-03-2021

What I did is select the data including header =SORT(A1:F9,6) and which results in sorting the header along with the data (of course):

I then got the idea to have the SORT function to disregard the first row and then sort the rest.
My first attempt was =IF(ROW(A1:F9)=1,A1:F9,SORT(A1:F9,6))

As you can see the header worked fine, but the sorted data includes the header, since it's the last in range after sorting A1:F9.

Excluding the header row from the sort range =IF(ROW(A1:F9)=1,A1:F9,SORT(A2:F9,6)) also doesn't work, since the range is then smaller than the output range resulting in the following error and still skips one data line:

I managed to get it correct by inserting a substitute header of al zeroes to the sort range and than sort stack the sorted range onto the headers with this function (which also gives room for two row headers):

But I wondered if there's a more elegant way using formula to sort a data range without affecting the header.

Result is =LET(data;A1:F9;header;A1:F2;sortCol;6;SORTBY(data;IF(ROW(data)>ROWS(header);1;0);1;INDEX(data;;sortCol);1)) thanks to Darren Bartrup-Cook's contribution.

Apr 6, 2022 in Database 5,462 views

## 1 answer to this question.

Select the column range you will sort without the first row, and then click the Enterprise > Advanced Sort.

Select My data has headers from the Advance Sort window, then specify the column to sort, sort criteria, and sort order before clicking the OK button.

answered Apr 11, 2022 by
• 23,260 points

## How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

need the row/column combinations marked with an ...READ MORE

## How to sort by dates excel?

You can sort dates in a worksheet ...READ MORE

## How to sort the data in sql ?

Hi Akash,  If you want to sort ...READ MORE

## How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

## Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

## Excel - Split cell with a delimiter and if delimiter not found get complete cell value to another cell

Select the row or column that you ...READ MORE

## Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

## how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE