How to Retrieve Data from Non-Adjacent Cells in Excel Using WPS Office
Question details
The user needs to automatically collect values from separate non-adjacent cells in a spreadsheet instead of copying and pasting them manually each day.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Daily reporting or data consolidation from a worksheet where the required values are stored in non-adjacent cells or columns.
- Observed behavior
- Manually copying and pasting individual non-adjacent cells each day is time-consuming and should be replaced with an automated retrieval workflow.
Make sure the source data uses a consistent layout, and identify the exact cells or columns you need before choosing a formula or Power Query approach.
Retrieve Non-Adjacent Cells with CHOOSECOLS, HSTACK, or INDEX Formulas
Use this method when the data layout is stable and you want formula results to update automatically whenever the source cells change.
Dynamic array functions such as CHOOSECOLS and HSTACK can combine non-adjacent columns or cells without manual copying. If your WPS Office version does not support dynamic arrays, INDEX is a reliable alternative for extracting specific cell references.
Open the workbook in WPS Office Spreadsheets and click the cell where you want the extracted data to appear.
Type a formula such as =CHOOSECOLS(A1:C10,1,3) to return only columns 1 and 3, or =HSTACK(A2,C2,E2) to combine selected cells horizontally from the same row.
Press Enter and check the spill range. Adjust the source range or column numbers if the returned values do not match your non-adjacent layout.
Use Power Query to Keep Only Non-Adjacent Columns
Use this method when you regularly import or refresh a table and need a repeatable way to extract specific non-adjacent columns.
Use VBA or Excel Automate for Complex Repeated Extraction
Use this method only when the extraction includes complex conditions, multiple sheets, or actions that formulas and Power Query cannot handle.
Retrieve Non-Adjacent Excel Data Directly in WPS Office
WPS Office Spreadsheets lets you extract non-adjacent cells with familiar Excel-compatible formulas, Power Query, and automation tools, so you can stop copying and pasting manually every day.
- 1. Open the workbook: Download and install WPS Office, then open your existing Excel workbook in WPS Office Spreadsheets.
- 2. Enter a retrieval formula: Click the target cell and type =CHOOSECOLS(A1:C10,1,3) or =HSTACK(A2,C2,E2) to collect the non-adjacent values you need.
- 3. Set up Power Query for repeated imports: Go to the Data tab, choose From Range/Table, and in the Power Query Editor keep only the required non-adjacent columns.
- 4. Refresh automatically: Update the source data or refresh the query to repopulate the extracted values without repeating manual selection.
Frequently Asked Questions
What formula pulls non-adjacent cells from the same row?
Use =HSTACK(A2,C2,E2) if your spreadsheet supports dynamic arrays. In older versions, create separate INDEX formulas for each cell and combine them with & or CONCAT.
How do I keep only specific non-adjacent columns in Power Query?
In the Power Query Editor, hold Ctrl and select the columns you need, right-click one of the selected headers, choose Remove Other Columns, and then click Close & Load.
Does CHOOSECOLS work in every Excel-compatible spreadsheet?
CHOOSECOLS is available in newer Microsoft 365 and WPS Office versions that support dynamic arrays. For older versions, use INDEX or Power Query instead.
Will formula results update when the source cells change?
Yes. When you use formulas such as CHOOSECOLS, HSTACK, or INDEX, WPS Spreadsheets recalculates automatically after the referenced cells are updated.
Is VBA required to automate daily non-adjacent extraction?
No. Formulas and Power Query are usually enough. VBA or Excel Automate is only needed for very complex repeated logic that cannot be handled by worksheet formulas or query steps.
