Excel Functions Tutorials

- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)

Related Courses

Array formulas are very useful and powerful formulas which are used to perform some of the very complex calculations in excel, array formula is also known as the CSE formulas as to execute array formulas we need to press CTRL + SHIFT + ENTER together instead of just pressing enter, there are two types of array formulas one which gives us a single result and another which gives us a multiple results.

**Array Formulas in Excel (Table of Contents)**

## Array Formulas in Excel

Arrays in excel can be termed as array formulas in excel. Array in excel is very powerful formulas which enable us to perform complex calculations.

First things first what is an Array? An array is a set of values or variables in a data set like, {a,b,c,d} is an array which has values from “a” to “d”. Similarly in excel array is a range of cells of values,

In the above screenshot cells from B2 to cell G2 is an array or range of cells.

These are also known as “CSE formulas” or “Control Shift Enter Formulas” as we need to press **Ctrl + Shift + Enter** to create an array formula in Excel.

In excel we have two types of Array formulas:

- One which gives us a single result.
- Another which gives us more than one result.

We will learn both the types of Array formulas on this topic.

### Explanation of Array Formulas in Excel

As explained array formulas in Excel are powerful formulas which help us to perform very complex calculations.

From the above examples, we learned these formulas in excel do simplify the complex and lengthy calculations. There is one thing to remember though, in example 2 where we returned multiple values using these formulas in excel. We cannot change or cut the value of the cell as it is a part of an array.

Suppose we wanted to delete cell G6 excel will give us an error or if we change the value in the cell just type any random value in the cell excel will give an error.

For example, type a random number in cell G6 and press enter it gives the following error,

Upon typing a random value 56 in the cell G6 it gives an error that we cannot change a part of the array.

If we need to edit an array formula simply go to the function bar and edit the values or the range provided or if we want to delete the array formula delete the whole array i.e. cell range B6 to G6.

**How to Use Array Formulas in Excel?**

Let us learn an Array Formula by a few examples:

4.9 (1,353 ratings)

Array formulas can be used in two types:

- If we want to return a single value, use these formulas in a single cell as in example 1.
- If we want to return more than one value, use these formulas in Excel by selecting the range of cells as in example 2.
- Press CTRL + Shift + Enter to make an array formula.

### Example #1

In restaurant sales data, there are products, the price of each product and the number of product sold.

The owner wants to calculate the total sales done by those products.

Obviously, the owner can go ahead and multiply the number of items sold of each product then sum them up as in the below equation,

And he will get the total sales,

But this is a lengthy task and if the data would be bigger it would be much more tedious. Excel offers Array formulas for such tasks.

We will create our first Array formula in the cell B7.

**#1 –** In cell B7, type =sum and then press the tab button on the keyboard. It opens up the sum formula.

**#2 –** Select cell range B2 to G2.

**#3 –** Now put an asterisk “*” sign after that to multiply.

**#4 –** Select cell range B3 to G3.

**#5 –** Instead of pressing enter key press **CTRL + SHIFT + ENTER.**

Excel gave the value of total sales by multiplying the number of products to price in each column and summing them up. In the highlighted section we can see that excel created an array for cell range B2 to G2 and B3 to G3.

The above example explains how excel array formulas return a single value for an array or set of data.

### Example #2

Continuing with the same data now what if the owner wants to know the sales for each product separately i.e sales of product 1 and product 2 and so on.

He can either go the long way and in each cell perform a function which will calculate the sales value.

The equation in cell B5 he has to repeat it for cell c4, d4 and so on.

Again if it would be a larger data it would be a tedious and tiresome task.

In this example, we will learn how array formulas in excel return multiple values for a set of arrays.

**#1 –** Select the cells where we want our subtotals i.e per product sales for each product. In this case, it is cell range B8 to G8.

**#2 –** Type an equals to sign “=” a

**#3 –** Select cell range B2 to G2.

**#4 –** Type an asterisk “*” sign after that.

**#5 –** Now select cell range B3 to G3.

**#6 –** As for array formula do not press just enter, press **CTRL + SHIFT + ENTER.**

From the above example, it is explained how an array formula can return multiple values for an array in excel.

Cells B6 to G6 is an array.

### Example #3

For the same restaurant owner, he has the data for sales done by the restaurant for five months Jan Feb March April May and June. He wants to know the average growth rate for sales.

Now what the owner can normally do is subtract the sales value of Feb months to Jan in the cell C2 and so on then calculate the average.

Again if this would be a larger data it would be a very tiresome task. Let us do this with the array in excel formulas.

**#1 –** In the cell D8 type =average and then press the TAB button.

**#2 –** To calculate growth we need to subtract the values of one month from the previous month to select the cell range from B3 to B7.

**#3 –** Put a subtract (-) sign after that,

**#4 –** Now select cells from B2 to B6.

**#5 –** As for array in excel do not press enter, press **CTRL + SHIFT + ENTER.**

Array formulas in excel easily calculated the average growth for the sales without any hassle.

The owner now does not need to calculate each month’s growth and then perform the average function after that.

**Things to Remember**

- These are also known as CSE Formulas or Control Shift Enter Formulas.
- Do not make parenthesis for an array excel itself does that, else it would return an error or incorrect value.
- Entering parenthesis “{“ manually excel will treat it as a text.
- Do not press enter instead press CTRL + SHIFT + Enter to use an array formula.
- We cannot change the cell of an array so in order to modify an array formula either modify the formula from function bar or delete the formula and redesign it in the desired format.

### Recommended Articles

This has been a step by step guide to Array formulas in Excel. Here we discuss how to Use Array in Excel using basic SUM and AVERAGE formula, and use it to solve Array in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

- How to Use SUM Formula in Excel (with excel template)?
- Average Formula Excel
- Average Formula | Explanation
- Excel Randomize List
- Numbering in Excel
- What is Name Range in Excel?
- How to Show Formula in Excel?
- Top Formula Errors in Excel
- Excel Formula Cheat Sheet

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion