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.

Apr 6 in Database 33 views

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.

• 13,460 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