Chapter 10_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 10: UNDERSTANDING AND USING ARRAY FORMULAS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Array 1.3 Array formulas 1.4 Entering Array formula in Excel 1.5 Summary 1.6 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain What is Array in MS Excel  Explain are Array formula MS Excel  Explain how to enter array in MS Excel 1.1 Introduction Array formulas in Excel are an extremely powerful tool and one of the most difficult to master. A single array formula can perform multiple calculations and replace thousands of usual formulas. And still, 90% of users have never used array functions in their worksheets simply because they are scared to start learning them. Indeed, array formulas one of the most confusing Excel features to learn. The aim of this tutorial is to make the learning curve as easy and smooth as possible. Page 50 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 WHAT IS AN ARRAY IN EXCEL? Before we start on array functions and formulas, let's figure out what the term \"array\" means. Essentially, an array is a collection of items. The items can be text or numbers and they can reside in a single row or column, or in multiple rows and columns. For example, if you put your weekly grocery list into an Excel array format, it would look like: {\"Milk\", \"Eggs\", \"Butter\", \"Corn flakes\"} Then, if you select cells A1 through D1, enter the above array preceded by an equal sign (=) in the formula bar and press CTRL + SHIFT + ENTER, you will get the following result: 1.3 WHAT IS ARRAY FORMULA IN EXCEL? The difference between an array formula and a regular formula is that an array formula processes several values instead of just one. In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula. Not only can an array formula deal with several values simultaneously, it can also return several values at a time. So, the results returned by an array formula is also an array. Array formulas are available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower. And now, it seems to be the right time for you to create your first array formula. Page 51 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS Simple example of Excel array formula Suppose you have some items in column B, their prices in column C, and you want to calculate the grand total of all sales.Of course, nothing prevents you from calculating subtotals in each row first with something as simple as =B2*C2 and then sum those values: However, an array formula can spare you those extra key strokes since it gets Excel to store intermediate results in memory rather than in an additional column. So, all it takes is a single array formula and 2 quick steps:  Select an empty cell and enter the following formula in it: =SUM(B2:B6*C2:C6)  Press the keyboard shortcut CTRL + SHIFT + ENTER to complete the array formula. Once you do this, Microsoft Excel surrounds the formula with {curly braces}, which is a visual indication of an array formula. What the formula does is multiply the values in each individual row of the specified array (cells B2 through C6), add the sub-totals together, and output the grand total: Page 52 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.4 ENTERING ARRAY FORMULA IN EXCEL How to enter array formula in Excel (Ctrl + Shift + Enter) The combination of the 3 keys CTRL + SHIFT + ENTER is a magic touch that turns a regular formula into an array formula. When entering an array formula in Excel, there are 4 important things to keep in mind: Once you've finished typing the formula and simultaneously pressed the keys CTRL SHIFT ENTER, Excel automatically encloses the formula between {curly braces}. When you select such a cell(s), you can see the braces in the formula bar, which gives you a clue that an array formula is in there. Manually typing the braces around a formula won't work. You must press the Ctrl + Shift + Enter shortcut to complete an array formula. Every time you edit an array formula, the braces disappear and you must press Ctrl + Shift + Enter again to save the changes. If you forget to press Ctrl + Shift + Enter, your formula will behave like a usual formula and process only the first value(s) in the specified array(s). Because all Excel array formulas require pressing Ctrl + Shift + Enter, they are sometimes called CSE formulas. 1.5 SUMMARY  A single array formula can perform multiple calculations and replace thousands of usual formulas.  Essentially, an array is a collection of items. The items can be text or numbers and they can reside in a single row or column, or in multiple rows and columns.  an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula. Page 53 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.6 REFERENCES https://www.ablebits.com/office-addins-blog/2015/02/25/array- formulas-functions-excel/ https://support.microsoft.com/en-us/office/guidelines-and-examples- of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 https://www.vertex42.com/blog/excel-formulas/array-formula- examples.html https://exceljet.net/glossary/array-formula Page 54 of 63 All Rights Reserved. Vol. TLE001/03-2022