Let’s consider the following sales dataset where we wish to calculate the total costs and then add them all to get the total amount of sales. We can use the array formula in this case and Ctrl + Shift + Enter is one of the shortcuts used in Excel to perform the calculations with array formulas. It supports performing complex calculations using the standard excel functions.
While working with large datasets in Excel, sometimes we need to perform a calculation on multiple values in a column and get the consolidated result in some other cell. In such situations, we need to use Array Formulas. Array Formulas provide two major advantages: dealing with a set of values and returning the multiple values simultaneously.
Step 1 – Calculate the total sales using Array Formula
– We have to create an appropriate formula to calculate the total sales without calculating the individual sales of the items. So, we’ll use the following formula;
– However, to enter this formula we’ll use the shortcut key CTRL+SHIFT+ENTER and this will convert the formula to an array formula as shown below and we’ll get the result of total sales in the desired cell.
– We’ll check the result by calculating the total sales through conventional methods as well. For this purpose, we’ll first calculate the total sales of individual items and then sum them as shown above.
Note: If you are using Excel 365 or Excel 2021, you don’t need to enter the formula with CTRL+SHIFT+ENTER. Excel will automatically detect the array formula and produce the same result even with simple enter.