Power Query Problems

How to Organize Spreadsheet Tasks by Date in Excel Using Power Query

Kushani Nimanthika Oct 16, 2026 869 views

Question details

The user needs to consolidate project-based data spread across multiple columns into a single, chronologically sorted list of tasks and due dates.

How to Organize Spreadsheet Tasks by Date in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing multiple project timelines where tasks and dates are split into separate columns, requiring a unified chronological view to track upcoming deadlines.
Observed behavior: Tasks and dates remain separated by project columns, making it difficult to sort or view all project deadlines sequentially from oldest to newest.

Before you start

Ensure your source data is formatted as an official Excel Table by selecting your data range and pressing Ctrl+T before attempting to launch Power Query.

Solution 1Recommended

Use Power Query to Unpivot and Sort Columns

Power Query's 'Unpivot Columns' tool is the most reliable way to flatten multiple project columns into a single, sortable task list.

Unpivoting allows you to take data that is spread horizontally across many columns and stack it vertically. This is ideal for project trackers where each project has its own column for dates.

Load Data into Power Query

Click anywhere inside your project table. Navigate to the 'Data' tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.

Unpivot the Project Columns

Hold down the Ctrl key and click the headers of all the specific project columns you want to consolidate. Right-click any of the selected headers and choose 'Unpivot Columns' from the context menu.

Format and Rename the Output

Double-click the newly generated column headers to rename them to 'Project Task' and 'Due Date'. Click the data type icon on the left side of the 'Due Date' header and select 'Date' to ensure proper formatting.

Load and Sort the Data

Click the 'Close & Load' button in the top-left corner. Once the transformed data loads into a new worksheet, click the filter dropdown on the 'Due Date' header and select 'Sort Oldest to Newest'.

Use Power Query to Unpivot and Sort Columns

Automatic Updates: If you add new tasks or dates to your original table, simply right-click the new unpivoted table and select 'Refresh' to instantly update your chronological list.

Solution 2

Use Dynamic Array Functions (Excel 365)

For Microsoft 365 users, dynamic array formulas offer a quick way to stack and sort columns without utilizing the Power Query editor.

Free Microsoft Office alternative

Manage Your Project Schedules with WPS Office

If complex tools like Power Query disrupt your workflow, consider switching to WPS Office. It delivers a highly compatible, streamlined spreadsheet environment that makes data consolidation, sorting, and filtering effortless without the expensive subscription fees.

1. Download and Install: Get WPS Office for free from the official WPS website and complete the quick installation.
2. Open Your Spreadsheets: Drag and drop your existing Excel project trackers directly into WPS Spreadsheet without losing any data.
3. Organize Your Tasks: Use the straightforward tools found in the 'Data' tab to sort, filter, and organize your schedules chronologically.

Fully compatible with Microsoft Excel file formats (.xlsx, .csv, .xls).Intuitive data sorting, filtering, and conditional formatting tools to easily organize task dates.Free and lightweight suite that runs smoothly on older computers and laptops.Familiar tabbed interface ensures a zero learning curve for switching Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'From Table/Range' option greyed out in Excel?

This option is often disabled if you are actively editing a cell, have multiple disconnected objects selected, or if the current worksheet is protected. Press Esc to exit cell editing mode, unprotect the sheet via the Review tab, and ensure only a single cell in your data range is selected.

Can I unpivot only specific columns and leave others intact?

Yes. In the Power Query Editor, you can select the columns you want to remain as fixed rows (such as an Employee Name or ID column), right-click their headers, and choose 'Unpivot Other Columns'. This flattens the date columns while perfectly maintaining your primary identifiers.

How do I fix date formatting errors after unpivoting data?

Sometimes Power Query misinterprets dates as text or general numbers. Before clicking 'Close & Load', click the small icon on the left side of your new date column header (which usually displays 'ABC' or '123') and explicitly select 'Date' from the dropdown menu to force the correct data type.

Excel & Data Problems