How To Create A Difference Formula In Excel

Array Formula in Google Sheets and Excel differ entirely.

First, see some of the benefits of using Google Sheets and Excel Array Formulas.

As a side note, the comparison of the functionality is between Sheets and older versions of Excel, not with Excel 365.

In Excel 365, Microsoft introduced dynamic array functions, and the SPILL feature completely transformed the Excel Array capability.

So if you are a Microsoft 365 user, you may not find the comparison interesting. But it will help you to get to know Sheets better.

Google Sheets and Excel Array Formula Benefits

Using Array Formulas in spreadsheet solutions to the likes of Excel and Google Sheets is sheer magic. But do note that you cannot use array formulas in all cases.

Here are some of the benefits of using Array Formula in Google Sheets and Excel.

It is a single formula that handles multiple rows. That means a formula in one cell can take values in other rows/columns and populate an expanded result called array result or a single cell result.
It is easy to edit, especially in Google Sheets and Excel 365.
When you use formulas in many cells, it would be a time taking process to edit.
It improves Spreadsheet's performance.
Array Formula expands the functionality of functions like Sumif, Vlookup, etc.
It supports Infinite ranges in Google Sheets.

You May Like: How to Use Vlookup to Return An Array Result in Google Sheets.

Array Formula in Google Sheets and Excel

Google Sheets has an ArrayFormula function, but Excel doesn't.

So using a Google Sheets ArrayFormula in Excel may return the error #NAME?. It's because of the unrecognized text in the formula.

Excel does offer the array formula functionality differently.

We can use open/close Curly Brackets around the formulas to make it array formulas in Excel.

Again simply typing the Curly Brackets is not enough. You should enter it using the keyboard shortcut Ctrl+Shift+Enter.

When you copy an Excel array formula to Google Sheets, it might behave as a non-expanding formula.

So, when you open Google Sheets files in Excel or vice versa, you will face such formula issues.

Let's see how the array formula differs in Google Sheets and Excel with the help of some formula examples.

How Array Formula Differs in Google Sheets and Excel

To understand the difference, let's learn the usage. That's the best way.

I am beginning with a multi-cell array formula in Excel and Google Sheets.

Multi-cell Array Formulas

The multi-cell array formula returns an expanding result.

Google Sheets and Excel Non-Array Formulas:

I have a table containing the purchase order details of a few aggregate/sand materials and want to calculate the total amount of each product based on their order quantity.

Here is how to achieve that using non-array formulas.

Array formula in Google Sheets and Excel

Related:- How to Sum, Multiply, Subtract, Divide Numbers in Google Sheets.

Array Formula in Google Sheets Using the Function ArrayFormula:

Double click on cell F2 and type the below formula.

          =ArrayFormula(D2:D6*E2:E6)

Double click on cell F2 and type=D2:D6*E2:E6 and then hit Ctrl+Shif+Enter. By doing so, Google Sheets will insert the ArrayFormula function.

Excel Array Using the Curly Braces:

The Excel Array approach is entirely different, and I feel the Google Sheets is way better than Excel in this case.

Excel only supports the keyboard shortcut approach. Excel in Office 365 is an exception, though.

In Excel, select the cell F2:F6.

Type the formula=D2:D6*E2:E6 and then hit Ctrl+Shift+Enter.

I have detailed above the difference in the application of Array Formulas in Excel and Google Sheets. Now here is the after effect .

Excel:

When you try to delete/insert row/rows in this formula range, you will get notified by the below message.

You Cannot Change Part Of An Array

Then how can I edit the Excel Array Formula range?

Select the cells containing the formula (here in our example, it's F2:F6).

Then hit delete.

If you want, you can copy the formula before deleting it. Now you can make changes to the individual cells.

Google Sheets:

In Google Sheets, you can insert rows or delete new row(s) in the formula range.

The formula would cover the newly inserted rows automatically. No warning messages or restrictions there.

Multi-cell expanding formula explained in Sheets

So, here, the Google Sheets ArrayFormula scores.

Infinite Ranges in Array Formula in Google Sheets and Excel

Google Sheets array formula supports infinite/open ranges like A:A or A10:A.

Example:

The formula in cell F2 may slow down your Sheet's performance.

Also, it will place unwanted values in blank rows.

To sort out that, when you use infinite ranges it's advised to use an IF + LEN combo together with the array formula as below.

          =ArrayFormula(if(len(A2:A),(D2:D*E2:E),))

This may not work in Excel Array.

Single-cell Array Formulas

Some array formulas don't expand their result.

When you sum two columns and multiply them using a single formula, you can use a single cell array formula.

Google Sheets:- Enter the formula in cell D2.

Excel:- Enter the formula in cell D2 and press Ctrl+Shift+Enter.

That's all about the usage of array formulas in Google Sheets and Excel.

ArrayFormula Function Alternatives in Google Sheets

In Google Sheets, the function ArrayFormula is not a must to handle expanding array results! Yes! You have heard me right.

With an example, I can explain this to you.

The following formula will return the column numbers 1 to 10 horizontally.

          =ArrayFormula(column(A1:J1))

Here you can replace the ArrayFromula with the Index function as below.

          =INDEX(column(A1:J1))

Conclusion

There are a few tutorials in the pipeline in that I am going to compare some of the popular Excel and Google Sheets formulas.

It's just like an Excel Vs. Google Sheets Formula comparison.

I could not find any such effort so far from anyone.

This tutorial is just the beginning in that direction.