How to edit an array formula in WPS Office Excel?

Welcome to WPS Official Academy. In this free tutorial, you will know what an array formula in Excel is and how to edit an array formula in WPS Office Excel.

lWhat is an array formula in WPS Spreadsheet Excel?

Unlike a regular formula, an array formula can process several values and perform multiple calculations within a selected range at a time.

To explain more clearly, let's take an example.

In this table below, the data of products amount, and price is given. Assume that we need to calculate the sales of each product, and we may perform like this without an array formula.

1.Enter the formula =B2*C2 (Sales=Amount*Price) in the cell D2.

2. Drag the fill handle(+) to run the auto fill function.

There is no doubt that this operation is relatively convenient in this example; however, if there are hundreds of rows in the table to calculate, this method may be annoying and time-wasting.

At this time, we can apply a multi-cell array formula in this table.

lSteps to edit an array formula in WPS Office Excel.

o Apply a multi-cell array formula in WPS Spreadsheet

A multi-cell array formula can return the results in multiple cells at a time. To be less abstract, let's have a try now!

1. Select the range D2:D8, and enter the formula =B2:B8*C2:C8. Then, press the 3 keys Ctrl+Shift+Enter on the keyboard. Thus, the formula is enclosed between the curly braces {} ({=B2:B8*C2:C8}), and the results are returned in the selected range.

2.At this time, you cannot edit the individual cell in the array formula. And you can press the Esc key to exit the formula evaluation mode.

o Apply a single-cell array formula in WPS Spreadsheet

A single-cell array formula returns one result in one cell. Take the same table as an example. Suppose that we want to calculate the total sales of the products without calculating the sales of each product in advance.

1. Select the cell you want to apply the array formula, for example, D9 here. Enter the formula =SUM(B2:B8*C2:C8) in cell D9, and press  Ctrl+Shift+Enter. The formula is converted into {=SUM(B2:B8*C2:C8)}, and we can get the total sales easily.

lSteps to shrink and expand an array formula in WPS Office Excel.

1. To shrink the array formula, in other words, to apply the array formula to fewer cells, we need to delete the current array formula and enter a new one. If you try to delete the content of one individual cell, the system will warn that Part of an array cannot be changed.

2. To expand the range to which the existing array formula is applied, select all cells containing the formula and the empty cells you want to add, and press F2 to switch to the edit mode. Then, update the references in cell D2 and press Ctrl+Shift+Enter.

To master more operations about references in Excel, you can visit WPS Academy.

Relative reference, absolute reference, and mixed reference | WPS Academy Free Office Courses

WPS Office Spreadsheet not only covers all of the basic functions of Excel, but also enables advanced techniques for data cleaning and analyzing. WPS Office Academy teaches strategies for free.