I hadn't seen this before but when I went to try I couldn't get it to work in Google sheets, am I using the wrong syntax? Typing "=SUM(A1)" just gave me the sum of A1 not A1 through the rest of the data in the column while similarly "=SUM(A1:)" resulted in an error. "=SUM(A1:A)" gives me the whole column as expected, not stopping when the data breaks after A11.
This is the correct behaviour, =SUM(A1) should give you A1 in both Excel and Google Sheets. You are passing in a single cell into SUM, so only asking it to add that cell up (not the cells below).
You must refer to a range of cells for it to work - in Excel this would be for instance =SUM(A:A).
In Google Sheets: generally you write a formula and hit Ctrl+Shift+Enter which will turn it into an array formula.
In Excel there are many more options in how to handle this, one of which is to define a table in Excel (Insert -> Table) and then refer to the table and column (=TableName[Column]).
