Free All-in-One Office Suite with PDF Editor

Edit Word, Excel, and PPT for FREE.

Read, edit, and convert PDFs with the powerful PDF toolkit.

Microsoft-like interface, easy to use.

Windows • MacOS • Linux • iOS • Android

### Catalog

# 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.

##
**l****What 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.

##
**l****Steps 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.

##
**l****Steps 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.

Also Read:

- 1. How to change or edit legend text in excel
- 2. Free Download 10 QuickBooks Invoice Template Excel Easy To Edit
- 3. How to edit resumes in pdf format in WPS Office
- 4. How to edit the locked text content in WPS Writer
- 5. How to insert, edit and delete comments in WPS Writer
- 6. How to edit PPT in WPS Office Presentation