Quick Answer: Replicate Array Formulas in WPS Spreadsheet
To replicate array formula workflows without Microsoft Excel, use WPS Spreadsheet and enter formulas with Ctrl+Shift+Enter (CSE) — the legacy array confirmation method. WPS Office does not support Excel 365 dynamic arrays; pressing Enter alone does not spill FILTER or multi-cell results into adjacent cells.
For multi-cell arrays, select the full output range first, enter a formula such as =B2:B8*C2:C8, then press Ctrl+Shift+Enter so the formula appears in curly braces {=…} and fills every selected cell. For FILTER, pre-select the destination range (for example G3:G15), enter =FILTER(array, include, [if_empty]), and confirm with Ctrl+Shift+Enter — otherwise FILTER returns only a single value.
WPS Office is a free all-in-one suite to edit Word, Excel, and PPT per WPS Academy. Open existing .xlsx array workbooks in WPS Spreadsheet; WPS pricing page states 100% Compatible with Microsoft File Formats — verify formulas on your target files before sharing.
Key Facts
| Topic | Detail |
|---|---|
| Array entry in WPS | Ctrl+Shift+Enter (CSE) required; formula wrapped in {=…} curly braces |
| Dynamic arrays | Not available in WPS Office — no automatic spill from a single cell like Excel 365 |
| FILTER workflow | Pre-select output range → enter =FILTER(array, include, [if_empty]) → Ctrl+Shift+Enter |
| Multi-cell arrays | Select output range → enter formula (for example =B2:B8*C2:C8) → Ctrl+Shift+Enter |
| Single-cell array | Example =SUM(B2:B8*C2:C8) with CSE for one aggregated result |
| Edit constraints | Cannot edit one cell inside an array block — delete and re-enter, or expand by selecting all cells plus new empty cells |
| File compatibility | WPS states 100% Compatible with Microsoft File Formats — verify array formulas in target .xlsx files |
| Suite scope | Free all-in-one suite to edit Word, Excel, and PPT per WPS Academy |
| Excel web context | Word, Excel, and PowerPoint for the web are free with a Microsoft account and include 5 GB OneDrive — browser behavior differs from desktop dynamic arrays |
| Watchouts | Unused cells in a pre-selected FILTER range may show #N/A; no Excel dynamic-array parity |
What Excel Array Formulas Are (Conceptual Overview)
Array formulas return results to one cell or multiple cells from a single formula that operates on ranges. In Microsoft Excel 365 desktop, many modern functions — including FILTER — can spill results into adjacent cells when you press Enter alone. That behavior is called dynamic arrays.
Legacy array formulas (still used in WPS Spreadsheet) require you to pre-select the output range and confirm with Ctrl+Shift+Enter. The formula bar shows curly braces around the expression: {=your_formula}.
Two common patterns users want to replicate without Excel:
| Pattern | What it does | WPS approach |
|---|---|---|
| Multi-cell array | Multiply or transform parallel ranges into many cells at once | Select range → CSE formula → {=B2:B8*C2:C8} |
| FILTER array | Return rows that match a condition | Pre-select output → =FILTER(...) → CSE |
| Single-cell array aggregate | Sum products across a range in one cell | =SUM(B2:B8*C2:C8) with CSE |
This guide focuses on WPS Spreadsheet desktop steps documented by WPS Academy — not a Microsoft Excel tutorial.
Before You Start in WPS Spreadsheet
- Install or open WPS Office and launch Spreadsheet (the Excel-class module in the suite).
- Open your workbook — WPS states 100% Compatible with Microsoft File Formats for Office Tools. If you received an Excel
.xlsxwith array formulas, open it in WPS and verify results match your expectations. - Know the CSE rule: In WPS, array formulas that fill multiple cells always need Ctrl+Shift+Enter. Pressing Enter alone on FILTER or multi-cell formulas will not replicate Excel 365 spill behavior.
- Pick your output range size before entering FILTER — if the range is larger than the filtered result, extra cells may display #N/A.
Note: WPS Academy documents desktop Spreadsheet array workflows. Mobile Spreadsheet apps may differ — confirm steps on your installed version before teaching a team.
Method 1: Multi-Cell Array Formula With Ctrl+Shift+Enter
Use this method when you need parallel calculations across matching ranges — the classic Excel array pattern WPS supports through legacy CSE entry.
Example goal: Multiply values in column B by values in column C and show each product in column D.
- Select the output range where results should appear — for example D2:D8 (same row count as B2:B8 and C2:C8).
- Type the formula in the formula bar:
=B2:B8*C2:C8 - Press Ctrl+Shift+Enter (not Enter alone).
- Confirm the formula bar shows curly braces:
{=B2:B8*C2:C8}. - Each cell in the selected range now displays its calculated product.
Single-cell aggregate variant: To sum those products in one cell, select one cell, enter =SUM(B2:B8*C2:C8), and press Ctrl+Shift+Enter. The braces appear around the SUM expression and return one total.
Method 2: FILTER Function Workflow With Pre-Selected Range
FILTER returns rows from a source array that meet an include condition. In WPS, treat FILTER as an array formula — not a dynamic spill function.
Example goal: Filter a list of names in column A where a status column equals a criteria value.
- Decide how many rows your filtered result might need — for example up to 13 rows.
- Pre-select the full output range — for example G3:G15 for names only, or G3:J15 if FILTER returns multiple columns. Do not select only the top-left cell if you expect multiple rows.
- In the formula bar, enter:
=FILTER(A2:A100, B2:B100="Active", "No matches")
- First argument (
array): the source range to return. - Second argument (
include): logical condition per row. - Third argument (
if_empty, optional): text shown when no rows match (replaces #CALC with custom text per WPS Academy).
- Press Ctrl+Shift+Enter.
- Matching rows fill the pre-selected range. Cells beyond the result count may show #N/A if the output range was sized larger than the filter result.
Optional combo: Pair FILTER output with a dropdown or criteria cell so users change the filter condition without rewriting the formula — keep the same pre-select + CSE pattern when the result spans multiple cells.
Edit, Shrink, and Expand Array Formulas in WPS
WPS Academy documents strict rules for changing array blocks after CSE entry:
You cannot edit a single cell inside the array
If you click one cell in a {=…} block and change part of the formula, WPS treats that as invalid. Edit the entire array as one unit.
Shrink an array (fewer output cells)
- Select the entire current array block including all cells showing results or #N/A.
- Delete the array (Delete key or Clear contents).
- Select the smaller output range you now need.
- Re-enter the formula and press Ctrl+Shift+Enter.
Expand an array (more output cells)
- Select all current array cells plus the additional empty cells where new results should appear.
- Press F2 to edit the formula in the formula bar (or click the formula bar).
- Adjust the formula if needed (for example widen FILTER source ranges).
- Press Ctrl+Shift+Enter again to re-confirm the expanded block.
Note: Expanding or shrinking is slower than Excel 365 spill resize. Plan output range size when you first build FILTER workflows in WPS.
Array Formula Workflows: WPS vs Excel at a Glance
| Dimension | Microsoft Excel 365 (desktop) | WPS Spreadsheet |
|---|---|---|
| Array formula entry | Enter alone can spill dynamic arrays | Ctrl+Shift+Enter required; {=…} braces |
| FILTER workflow | Single-cell entry; results spill down/right | Pre-select output range + CSE |
| Multi-cell array | Spill or legacy CSE depending on version | Select range first, then CSE (for example {=B2:B8*C2:C8}) |
Open .xlsx array files | Native Excel | 100% MS format compatibility claim — verify file |
| Edit array block | Excel rules vary by version | Cannot edit one cell; re-enter for shrink/expand |
| Cost to start | Desktop subscription for full dynamic arrays | Free suite per WPS Academy |
| Excel for the web | Free with Microsoft account (5 GB OneDrive) | Not primary path — browser array behavior varies |
Excel-Free Alternatives: When WPS vs Excel for the Web Fits
Users searching without Microsoft Excel typically want either offline desktop editing or free browser access.
WPS Spreadsheet (desktop) fits when you need:
- Offline array work on Windows and macOS
- A free integrated suite (Writer, Spreadsheet, Presentation) per WPS Academy
- Legacy CSE array workflows (FILTER, multi-cell multiply) documented in WPS Academy tutorials
Microsoft Excel for the web fits when you need:
- Browser-only access with a Microsoft account
- Free Word, Excel, and PowerPoint online plus 5 GB OneDrive storage per Microsoft’s free web page
Note: Excel for the web array and dynamic-array behavior varies by account and feature set — recheck Microsoft’s live page before relying on Excel web in production. This guide does not provide step-by-step Excel web array tutorials; WPS Spreadsheet is the primary path.
Limitations and Watchouts
- No dynamic array spill: WPS Academy explicitly states dynamic array is not available in WPS Office. Do not expect FILTER to auto-expand when you press Enter alone.
- Pre-select discipline: Undersized selections truncate results; oversized selections show #N/A in unused cells.
- Compatibility verification: Complex Excel 365 workbooks may use functions or spill patterns WPS does not replicate. Open the actual
.xlsxin WPS and test every critical array. - Partial cell edits fail: Treat
{=…}blocks as atomic — use the shrink/expand workflow above. - Function parity: This guide covers FILTER and multi-cell CSE patterns verified in WPS Academy. Do not assume SORT, UNIQUE, SEQUENCE, or other Excel 365 dynamic functions work identically without per-function verification.
Optional: Formula Help and AI Assist in WPS Spreadsheet
WPS Spreadsheet may include formula help tools for general formula drafting. These tools can help you compose or understand formulas — they do not replace the Ctrl+Shift+Enter step for array output or prove FILTER multi-cell workflows.
Use formula help to draft a FILTER expression, then manually pre-select the output range and confirm with CSE as documented above.
FAQ
Why must I press Ctrl+Shift+Enter instead of Enter in WPS?
WPS Academy states dynamic array is not available in WPS Office. Ctrl+Shift+Enter converts a formula into a legacy array formula so FILTER and multi-cell expressions fill a pre-selected range. Enter alone returns a single value for FILTER.
Can FILTER spill automatically like Excel 365?
No. WPS does not support Excel-style dynamic array spill. You must pre-select the destination range and press Ctrl+Shift+Enter.
How do I replicate {=B2:B8*C2:C8} in WPS?
- Select the output range (for example D2:D8).
- Enter
=B2:B8*C2:C8. - Press Ctrl+Shift+Enter.
- Confirm curly braces in the formula bar.
What if my FILTER output shows #N/A in extra cells?
You likely pre-selected more rows than the filter returned. Either shrink the output range and re-enter the array, or ignore trailing #N/A cells if the visible matches are correct.
Can I open an Excel array workbook in WPS?
WPS pricing page states 100% Compatible with Microsoft File Formats for Office Tools. Open your .xlsx in WPS Spreadsheet and verify every array formula still calculates as expected — especially if the source file used Excel 365 dynamic arrays.
Does Excel for the web replace WPS for array formulas?
Word, Excel, and PowerPoint for the web are free with a Microsoft account and include 5 GB OneDrive. Browser Excel may suit light editing, but array behavior differs from desktop Excel 365 and from WPS CSE workflows. Choose WPS for offline free suite array replication; choose Excel web for account-based browser access — verify live features before relying on either for production arrays.
Can I edit just one cell in an array block?
No. WPS Academy notes you cannot change one cell inside an array block. Delete and re-enter the formula, or select the full block plus new cells, edit in F2 mode, and press Ctrl+Shift+Enter again.
Get WPS Spreadsheet Free
WPS Office is described as a free all-in-one suite to edit Word, Excel, and PPT per WPS Academy. Download WPS Spreadsheet to replicate FILTER and multi-cell array workflows on desktop without Microsoft Excel.
Free Download — Get WPS Spreadsheet to replicate FILTER and multi-cell array workflows on desktop.
Sources and Last Reviewed
- WPS Academy — How to use the FILTER function — FILTER CSE workflow; dynamic-array limitation
- WPS Academy — How to edit an array formula in WPS Office — Multi-cell CSE; edit/shrink/expand constraints
- WPS Academy — What is WPS Office — Suite scope
- WPS Office pricing — Microsoft file format compatibility
- Microsoft — Free Office online for the web — Excel web free-tier context
Last reviewed: 2026-07-03
