Lesson 3: The power of Sheets: FILTER, SORT, UNIQUE, ARRAYFORMULA

Google Sheets has a set of functions that make spreadsheets truly come alive. =FILTER(range, condition) returns only the rows that meet the condition. For example, =FILTER(A2:B20, B2:B20>1000) returns all rows where the value in column B is greater than 1000. =SORT(range, sort_column, is_ascending) sorts the data. =UNIQUE(range) removes duplicates and returns a list of unique values. All these functions “spill”: the result automatically takes up as many cells as it needs. You don’t have to guess the size in advance or drag the formula down. This is a fundamental difference from classic Excel, where similar behavior arrived only in newer versions and works differently. Important: FILTER, SORT, UNIQUE, and QUERY formulas don’t carry over from Sheets to Excel one-to-one. =ARRAYFORMULA lets you apply any formula to a whole column at once. Instead of writing =B2*C2 in D2 and dragging it down to D1000, write one formula: =ARRAYFORMULA(B2:B1000 * C2:C1000). The result fills the whole range automatically. A real-life case: an orders table with customer names, quantities, and prices. =UNIQUE(A2:A100) gives an instant list of unique customers with no manual work. =FILTER(A2:C100, C2:C100>5000) shows only the large orders. =ARRAYFORMULA(B2:B100 * C2:C100) builds an “Amount” column without dragging.