Excel has a powerful feature called Array Formulas. Let’s see an example on how they can be used to improve our worksheets. We have the following data:
We have a list of articles, with it’s unit price and quantity that was sold. If we want to calculate the total sales value, we need to multiply each unit price for the quantity and then sum all of the values. We could add a column to the right of the Qtd column where we put a formula on cell E3 this formula =C3*D3 and then copy down until row 8. Then we need to sum all of the values on column E with a formula like =SUM(E3:E8) to get the sales total, right? Your sheet would look like this:
As you can see, we needed to make the calculation in two steps: first calculate the sales value for each article and then sum all of the sales values. What if we could do this with a single formula? Wouldn’t it be better? With array formulas this is possible. Let’s see how. Let’s get back to our original table, without the Sales Value column. We want to multiply the value of each row on column C by the value on that row on column D. If we put a formula like =SUM(C3:C8*D3*D8) on cell D10 and press Enter, you will get a #VALUE error:
Go back to the formula on cell D10 (press F2 to edit) and, instead of pressing Enter, press Ctrl+Shift+Enter at the same time. Your formula will look like this now:
Excel automatically adds the braces ({ }) to identify that this is an array formula. Don’t put the braces yourself because Excel will not interpret like an array formula this way! You always have to Ctrl+Shift+Enter each time you edit your array formula and Excel will add the braces for you. Now you just need to format the cell D10 value as currency.
Let’s see another example of array formulas use. Imagine that we wanted to see what a 5% increase on the quantity of articles sold would do for our total sales value. So wee need to multiply the Qtd column by 1.05 and then multiply that for the unit price value and sum all of the values for each article. Again, we have multiple operations and we want to make them on a single formula. We will use an array formula for that also, like this:
We start by multiplying each value on column D by the value on cell D11 (the 5% increase), then multiply that value for the value of column C and sum all of the results for each row.
As you can see by this two simple examples, Excel is able to handle multiple operations on a single formula. This will help you simplify your worksheets calculations, avoid using additional columns or rows to perform intermediate calculations, and improve your worksheets performance.
