Power Query Problems

How to Automatically Create a Condensed Excel Table from Populated Cells

Nimra Malik Sep 27, 2026 868 views

Question details

The user needs to consolidate populated cells from specific columns into a condensed list on a new worksheet, ideally updating the source sheet automatically upon task completion without VBA.

How to Automatically Create a Condensed Excel Table from Populated Cells

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pulling scattered task data from multiple columns into a streamlined, centralized action-item list on a separate sheet.
Observed behavior: Looking for a non-VBA method to extract specific column data and write back completion statuses automatically between worksheets.

Before you start

Ensure your source data is formatted as an official Excel Table (Ctrl+T) so that any queries or formulas can automatically expand to include new rows as you add them.

Solution 1Recommended

Use Power Query to Extract and Condense Data

Power Query is the most robust non-VBA method to pull data from specific columns, unpivot them, and create a condensed structured list.

Power Query can easily extract populated cells from columns G, I, and K, along with their column headers. However, note that Power Query is a one-way extraction; automatic write-back to the source sheet upon task completion will still require VBA or Office Scripts.

Load data into Power Query

Select your source table and navigate to Data > From Table/Range on the Excel ribbon to open the Power Query Editor.

Select and unpivot specific columns

Hold the Ctrl key and click the headers for columns G, I, and K. Right-click any of the selected headers and choose 'Unpivot Only Selected Columns'.

Remove empty cells

Click the filter dropdown arrow on the newly created 'Value' column, uncheck '(null)' or blank values, and click OK to keep only the populated cells.

Load to a new worksheet

Click 'Close & Load' > 'Close & Load To...' from the Home tab, select 'Table', and choose 'New Worksheet' to generate your condensed table.

Use Power Query to Extract and Condense Data

Refresh Required: Power Query does not update instantly. When you change data in the original sheet, you must right-click the condensed table and select 'Refresh'.

Solution 2

Use Dynamic Array Formulas (FILTER and VSTACK)

A lightweight approach using native Excel functions to pull data instantly without needing to manually refresh a query.

Data Extraction Made Easy

Condense Data Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful dynamic array functions and data consolidation tools to effortlessly extract and organize populated cells without complex VBA coding.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx file containing the scattered data.
2. Apply dynamic array formulas: Navigate to your target sheet and type =FILTER() to instantly pull non-empty rows from your desired columns.
3. Consolidate your data: Alternatively, go to the Data tab and use the 'Consolidate' feature to merge scattered ranges into a single, comprehensive summary table.
4. Format as a table: Select your extracted data, go to the Home tab, and select 'Format as Table' to apply clean, readable styling.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports advanced dynamic array formulas like FILTER for instant data extractionIntuitive interface for fast data consolidation and unpivotingFree to use with comprehensive spreadsheet capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Can I make the second worksheet automatically update the first one without VBA?

Standard Excel formulas and Power Query are one-way data extraction tools. To automatically synchronize data or write back a completion status from the second sheet to the first, you will generally need to use VBA or Office Scripts.

How do I remove blank rows when condensing my Excel table?

If you are using Power Query, apply a filter to your target column and uncheck 'null' or '(blank)'. If you are using dynamic array formulas, include a condition in your FILTER function such as range<>"" to exclude empty cells.

Why doesn't my Power Query table update automatically when I change the source cells?

Power Query tables do not recalculate in real-time like standard formulas. To see the latest data, you must right-click anywhere inside the condensed output table and click 'Refresh', or go to the Data tab and click 'Refresh All'.

How can I pull the column headings along with the populated cells?

In Power Query, when you select your specific columns (G, I, K) and choose 'Unpivot Only Selected Columns', it automatically generates an 'Attribute' column. This column will contain the original headers alongside the extracted cell values.

Excel & Data Problems