Data Import & Export

How to Sort Excel Data Horizontally Across Columns by Year

Huda Qurayshi Sep 28, 2026 870 views

Question details

The user needs to sort a dataset where years are arranged across columns in ascending order automatically, without manually dragging and dropping each column.

How to Sort Excel Data Horizontally Across Columns by Year

Product: Excel
Device & OS: not provided
Scenario: Reorganizing horizontal timeline or financial data across multiple columns based on the year headers.
Observed behavior: The user wants to achieve left-to-right sorting based on the header row containing year values.

Before you start

Ensure there are no merged cells within the header row or the data range, as merged cells will prevent the horizontal sorting function from working correctly.

Solution 1Recommended

Use the Built-in 'Sort Left to Right' Feature

This is the most direct method to rearrange columns chronologically based on a header row containing date or year values.

Excel has a hidden setting within the standard Sort dialog that allows you to change the sorting orientation from the default vertical (top to bottom) to horizontal (left to right).

Select the Data Range

Highlight the entire table, making sure to include the row with the years and all corresponding data rows below it. Do not include row headers (labels in the first column) if they should remain static.

Open the Sort Dialog

Navigate to the 'Data' tab on the ribbon and click on the 'Sort' button to open the custom sort dialog box.

Change Sorting Orientation

Click the 'Options' button at the top of the Sort dialog box. In the Sort Options window, select 'Sort left to right' and click 'OK'.

Apply Sorting Criteria

In the 'Sort by' dropdown menu, select the row number that contains your years (e.g., Row 1). Set 'Sort On' to Cell Values, and 'Order' to Oldest to Newest (or Smallest to Largest), then click 'OK'.

Use the Built-in 'Sort Left to Right' Feature

Selection Tip: Always remember to select the data rows along with the header row, otherwise, the headers will move while the underlying data remains in the original columns, scrambling your dataset.

Solution 2

Use a Helper Row for Text-Based Headers

Use this workaround if your year headers are formatted as text (e.g., 'FY2021', 'FY2022') and standard sorting does not produce the correct chronological order.

Solution 3

Transpose, Sort Vertically, and Transpose Back

A reliable alternative method if you prefer working with standard vertical sorting or have complex datasets that resist horizontal sorting.

Powerful Spreadsheet Tool

Easily Sort Columns Horizontally in WPS Spreadsheet

WPS Spreadsheet provides robust data management features, allowing you to instantly sort horizontal data across columns without complex formulas. It perfectly handles date values, handles large datasets seamlessly, and is completely free to use.

1. Highlight Your Data: Select the entire table in WPS Spreadsheet, making sure to include the row containing the years.
2. Access the Sort Tool: Click on the 'Data' tab in the top ribbon and select the 'Sort' icon to open the custom sort window.
3. Switch to Horizontal Sort: Click 'Options' in the sort window, check 'Sort left to right', and select the row number containing your years to apply the sorting.

Built-in 'Sort Left to Right' function for instant column rearrangementFully compatible with all Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive and familiar ribbon interface with zero learning curveLightweight, fast, and totally free to download and use

microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'Sort left to right' option grayed out or not working?

This typically happens if you have merged cells within your selected data range. Excel requires uniform row and column sizes to execute a horizontal sort. Unmerge all cells in the selection and try again.

Can I undo a horizontal sort if the data gets scrambled?

Yes, you can press Ctrl+Z (or click the Undo button) immediately after performing the sort to revert the data to its original state. Always ensure you select both headers and data to prevent scrambling.

Does sorting left to right keep my cell formatting intact?

Yes, when you sort columns horizontally, the cell formatting (like background colors, fonts, and borders) moves along with the data to its new position.

How do I sort columns horizontally if my headers are months instead of years?

If the months are formatted as actual Date values, the standard horizontal sort will arrange them chronologically. If they are plain text (e.g., 'Jan', 'Feb'), you will need to add a helper row with numbers (1 to 12) above them and sort by that helper row.

Excel & Data Problems