Are you curious about how to sort horizontally in Excel? It’s a handy skill for restructuring datasets where column headers are found in rows, and data for those headers is in columns. In this Excel tutorial, we’ll guide you through the process.

Excel’s built-in Sort & Filter command button is fantastic for sorting data by rows while keeping the column headers intact. However, there are situations where you need to reorganize the columns, not just the rows. That’s where horizontal sorting comes into play.

Excel provides several approaches for horizontal sorting, including formulas, user interface options, PivotTables, and Excel VBA. Whether you’re new to data analytics in Excel or a seasoned pro, mastering different horizontal sorting methods is essential. Stick to this article until the end to discover the most efficient techniques you should know.

What Is Excel Horizontal Sort?

Excel horizontal sort is a method to rearrange the column headers or data in columns instead of rows as opposed to rearranging data in rows rather than columns in vertical sorting.

Excel’s horizontal sorting tool isn’t readily available. The Sort functions provided by the Sort & Filter tool are Sort A to Z and Sort Z to A sort your data by rows and not columns, and that’s vertical sorting. This is also known as top-to-bottom sorting.

However, if you go to Sort Options and change the sorting orientation to left and right, you can activate the horizontal sorting function. It’s useful when you want to organize information by columns rather than the default vertical sorting.

This means shifting data within columns while keeping row headers fixed in place. Horizontal sorting in Excel is valuable for managing data in a way that suits your specific needs, making it easier to analyze and work with datasets efficiently.

Reasons to Sort Horizontally in Excel

Here’s why and when you might need to use your Excel horizontal sorting skill during data preparation and analysis in Excel:

1. Horizontal sorting can make it easier to read and compare data when you have long lists or wide tables, as it reduces the need for excessive scrolling.
2. If you need to copy and paste data between Excel and other software like Word or PowerPoint, horizontal sorting can help maintain the alignment of your data.
3. Sorting horizontally can be helpful when you want to group related information together, such as organizing sales data by months or products in rows rather than columns.
4. Some data imports or exports may align data horizontally, and sorting it in the same orientation can save time and prevent errors during data processing.
5. Certain reports or presentations may require a horizontal layout for data presentation, making horizontal sorting necessary to meet those formatting needs.

Now explore below the various intuitive and easy ways to sort datasets horizontally in an Excel worksheet:

Sort Horizontally in Excel Using Sort Options

You can use the Filter & Sort tool the following way to perform a horizontal sort in Excel:

1. Highlight the dataset you want to sort by column or from left to right.
2. Click the Sort & Filter command inside the Editing commands block of the Home tab.
3. A context menu will show up.
4. There, click on the Custom Sort option.

5. You should see the Sort dialog box.
6. Click Options and choose Sort left to right under the Orientation section of the Sort Options dialog.

7. Click the Sort by drop-down menu and select by which row you want to sort the columns. In this tutorial, it’s Row 2.
8. Click the Order drop-down menu and choose a sort order like A to Z or Z to A.
9. Click OK to perform a horizontal sort.

Sort Horizontally in Excel Using a Function

Another quick way to sort a dataset from left to right or horizontally is by using the built-in SORT function.

Find below the formula syntax and steps you must use:

1. Go to a cell where you want the sorted results of a dataset.
2. Enter the equals sign followed by the SORT function.
3. Then, enter the following arguments for the formula in a parenthesis:
   • Enter the array to be sorted, for example, B2:H5 in this tutorial.
   • Indicate the sort index, the row number by which you want to sort the columns, like 1 for the name row in the current example.
   • Do you want to sort in ascending order or descending order? Or, you might want to sort A to Z or Z to A. You can enter 1 for ascending order or A to Z and enter -1 for descending or Z to A.
   • Enter the argument TRUE to sort by column or sort horizontally.
4. The formula becomes as mentioned below:

=SORT(B2:H5,1,1,)

5. Hit Enter to sort data horizontally in Excel.

The resultant dataset generated after sorting creates spill values. The exact formula stays in the first cell that you highlight to enter a function. If you want to perform further data analysis by referring to the cell range of the result, it won’t work.

So, follow these steps to convert spill values to real values that can be referred to other data analysis and visualization tasks in your Excel worksheet:

1. Copy the result by highlighting it and pressing Ctrl + C.
2. Now paste it in the same place or anywhere else by pressing Ctrl + Alt + V.
3. Select the Values option in the Paste Special dialog.
4. Click OK to paste the copied data as values.

Sort Horizontally in Excel Using Power Query

When importing data from another database or needing to organize existing workbook data, you can also sort the dataset horizontally using the Power Query tool. Here’s how:

Importing New Dataset or Adding Existing Dataset

1. Click the Data tab on the Excel ribbon.
2. Go to the Get & Transform Data section.
3. Click on Get Data.
4. Choose any dataset sources from the context menu, like From Database > From Oracle Database.

5. If it’s a dataset from the same worksheet, just highlight the entire dataset.
6. Click the From Table/Range command inside the Get & Transform Data commands block.
7. On the Create Table dialog, click OK.

8. You should now see your imported or existing dataset in the Power Query tool.

You must import your dataset with a blank row at the top to help with the transposing action in Power Query.

Transposing and Sorting in Power Query

1. Select all the columns in the Power Query Editor tool.
2. Click the Transform tab on the Power Query ribbon menu.
3. Inside the Table commands block, click on the Transpose option.
4. The imported data will get transposed.

5. Highlight the first row that contains the row headers from the original dataset.
6. Go to the Home tab and click on Remove Rows > Remove Top Rows option.
7. On the Remove Top Rows dialog, enter number 1 and click OK.

8. This should delete the unnecessary row from the dataset.

9. Now, select a column by which you want to sort the dataset. In this example, it’s Column1.
10. Click the column drop-down arrow and choose the sort logic, for example, Sort Ascending.
11. Power Query should quickly sort the dataset by the selected column.

Transposing Back to the Original Structure

1. Select all the columns in your Power Query Editor canvas.
2. Click Transpose from the Transform tab.
3. The sorted dataset in the Power Query should be now in the original structure as you imported it.

Importing to an Excel Worksheet

1. Click the File tab on the Power Query Editor.
2. Select the Close & Load To option from the context menu that shows up.

3. On the Import Data dialog, click the Existing worksheet option.
4. Highlight a cell for the importing of the sorted dataset from Power Query.
5. Click OK to complete the import process.

6. Now, copy and paste the original row headers to the imported dataset.

So, now you’ve got a horizontally sorted dataset in Excel using Power Query.

Sort Horizontally in Excel Using Excel VBA

You can also use Excel VBA to sort horizontally in Excel. This method is suitable for frequent sorting of large datasets. You can just write and save the script once in the workbook’s VBA Editor and use it for many datasets.

Find below the script you should use along with simple steps:

1. Press Alt + F11 to bring up the Excel VBA Editor.
2. There, click the Insert menu and choose Module.
3. Now, copy and paste the following VBA Script inside the new module:

Sub SortbyLeftRight()

    Range("B7").Select
    ActiveCell.Formula2R1C1 = "=SORT(" & Range("B2").Address(ReferenceStyle:=xlR1C1) & ":" & Range("H5").Address(ReferenceStyle:=xlR1C1) & ", 1, 1, TRUE)"
    Range("B7:H10").Select
    Selection.Copy
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:H10").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
    Range("D15").Select
    
End Sub

4. Click the Save button.
5. Choose No on the warning dialog box.

6. On the Save As dialog, click the Save as type drop-down and choose XLSM file.
7. Click Save to save the existing XLS file in XLSM.
8. Close the Excel VBA Editor.

To run the script, follow these steps:

1. Press Alt + F8 to call the Macro dialog box.
2. Choose the SortbyLeftRight macro.
3. Hit the Run button.

Excel should instantly sort columns B through H of the selected dataset in the A to Z order of the Names row.

Here’s how to modify the script to use it on your own worksheet:

• Change all the occurrences of cell address B7 to the destination cell address.
• The input cell range is B2:H5 and represented by Range("B2") and Range("H5") in the above script. Suppose the sort data range in your worksheet is C5:I100, change Range("B2") to Range("C5") and Range("H5") to Range("I100") and so on.
• Remove this code element from the script if you don’t want currency formatting for the resulting dataset:

Range("B8:H10").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"

Sort Horizontally in Excel Using Office Scripts

Since Excel VBA-based automation isn’t available on Excel for the web app, you can use Office Scripts instead. It’s also available in Excel for the Microsoft 365 desktop app.

Here are the steps and the necessary script to use Office Scripts:

1. Click the Automate tab of the Excel ribbon.
2. Select the New Script option.
3. Copy and paste the following script into the Code Editor panel:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B7 on selectedSheet
	selectedSheet.getRange("B7").setFormulaLocal("=SORT(B2:H5,1,1,TRUE)");
}

4. Click the Save script button to save the code for the future.
5. Hit the Run button to execute the code.

Here’s how you can configure the script:

• Replace the cell range B2:H5 in the SORT function with the actual dataset cell range of your worksheet.
• In the "=SORT(B2:H5,1,1,TRUE)" formula, the first 1 indicates the row by which sorting needs to be done. So, change it accordingly.
• The second 1 indicates sorting in ascending order. If you need sorting in the descending order, enter -1.
• B7 is the destination cell for the sorted dataset. So, modify it according to your requirements.

Note: You can access the Office Scripts coding tool in Excel if you own or use Microsoft 365 Business Standard or a better subscription package. If the Automate tab isn’t showing on your Excel web or desktop app, it’s likely you don’t have such a subscription. If you’re using an employer-managed Microsoft 365 subscription, consult with the organization’s IT admin if you don’t have access to Office Scripts.

Conclusions

So, now you know how to sort data in Excel horizontally from left to right instead of the normal top-to-bottom sorting. You learned simple methods like using the Sort Options in the Sort & Filter tool or SORT function for casual or occasional horizontal sorting in Excel for a small dataset.

If you’re importing data to an Excel worksheet from a third-party database and need horizontal sorting, you can do so directly in Power Query, the tool that enables you to import datasets to Excel and cleanse them for further analysis.

Finally, you work with a large volume of data in Excel regularly and want to automate the process of horizontal sorting. In this situation, you can use Excel VBA and Office Scripts.

Did you try any of the above-mentioned methods? Do you know any secret method for Excel horizontal sorting? Don’t forget to comment below!