=).A1:B10.Ctrl+Shift+Enter or the ARRAYFORMULA function.A2:A). This is useful for formulas that need to automatically include new rows of data.The syntax for a function is its name followed by arguments in parentheses.
=FUNCTION_NAME(argument1, [argument2], ...)argument1: The first input, which can be a value, text, cell reference, or range.[argument2]: Optional additional inputs are shown in square brackets.While Sheets shares many functions with Excel (SUM, AVERAGE, IF), its power lies in functions designed for web-based, collaborative data.
| Function | Syntax | Description |
|---|---|---|
| QUERY | =QUERY(data, query, [headers]) | Runs a Google Visualization API Query Language query across data. Essentially, it's SQL inside your spreadsheet. The most powerful function in Sheets. |
| FILTER | =FILTER(range, condition1, [condition2]) | Filters a range of data based on one or more conditions, returning a new array of the results. |
| SORT | =SORT(range, sort_column, is_ascending) | Sorts the rows of a given range by the values in a specified column. |
| UNIQUE | =UNIQUE(range) | Returns the unique rows from a source range, discarding duplicates. |
| VLOOKUP | =VLOOKUP(search_key, range, index, [is_sorted]) | Searches down the first column of a range for a key and returns the value of a specified cell in the row found. |
| Function | Syntax | Description |
|---|---|---|
| IMPORTRANGE | =IMPORTRANGE(spreadsheet_url, range_string) | Imports a range of cells from a different Google Sheet. You must grant access the first time. |
| GOOGLEFINANCE | =GOOGLEFINANCE(ticker, [attribute]) | Fetches current or historical securities information from Google Finance. |
| IMPORTHTML | =IMPORTHTML(url, query, index) | Imports data from a table or list within an HTML page. |
| Function | Syntax | Description |
|---|---|---|
| JOIN | =JOIN(delimiter, value_or_array) | Joins the text from one or more strings and/or arrays, separated by a delimiter. |
| SPLIT | =SPLIT(text, delimiter) | Divides text around a specified delimiter, putting each fragment into a separate cell in the row. |
| ARRAYFORMULA | =ARRAYFORMULA(array_formula) | Enables the display of values returned from an array formula into multiple rows and/or columns. |
Combine functions to pull data from a "raw data" sheet into a clean, sorted, and filtered dashboard.
=IMPORTRANGE(...) to pull in data from another spreadsheet.=SORT(FILTER(RawData!A2:D, RawData!D2:D > 100), 4, FALSE)=QUERY(RawData!A:D, "SELECT B, SUM(D) WHERE A = 'Completed' GROUP BY B", 1)A2:A instead of A2:A100. Your formulas will automatically include new rows as they are added.Data > Named ranges and name it `sales_amount`. Your formula becomes much more readable: =SUM(sales_amount).=ARRAYFORMULA(A1:A * B1:B) to multiply columns A and B for every row automatically.| Task | Excel Approach | Google Sheets "Superpower" |
|---|---|---|
| Filtering Data | Table Filters, Advanced Filter, newer `FILTER` function. | The =FILTER() function is native, simple, and can be combined with other functions easily. |
| SQL-like Queries | Power Query / Get & Transform Data. | The =QUERY() function provides powerful SQL-like capabilities directly in a cell. |
| Linking Workbooks | Direct cell linking (can be brittle and slow). | =IMPORTRANGE() is a robust, explicit function for pulling data from other Sheets. |
| Array Formulas | Requires Ctrl+Shift+Enter. | Handled elegantly by the =ARRAYFORMULA() wrapper function. |
Goal: From a master sales log, create a summary of total sales for "Electronics" in "North America", grouped by sales representative.
Data Range: `SalesLog!A:E` (Date, Rep, Region, Category, Amount)
=QUERY(SalesLog!A:E, "SELECT B, SUM(E) WHERE C = 'North America' AND D = 'Electronics' GROUP BY B ORDER BY SUM(E) DESC LABEL SUM(E) 'Total Sales'", 1)"SELECT B, SUM(E)...": Select the Rep (B) and the sum of the Amount (E)."WHERE C = 'North America' AND D = 'Electronics'...": Filter for the correct region and category."GROUP BY B...": Aggregate the sums for each unique sales rep."ORDER BY SUM(E) DESC...": Sort the results from highest to lowest sales."LABEL SUM(E) 'Total Sales'": Give the aggregated column a clean header name.1: Indicates that the source data has one header row.#N/A: "Not Available." A lookup function (like `VLOOKUP`) failed to find a match.#REF!: "Reference Error." A cell or range that the formula refers to is invalid. This often happens after deleting rows/columns. For IMPORTRANGE, it can mean you haven't granted permission yet (hover over the cell and click "Allow access").#ERROR!: A general parsing error. Check for typos in function names or incorrect syntax within your formula, especially in complex `QUERY` strings.#VALUE!: The formula has received the wrong data type, like trying to perform math on a text string.