Key Vocabulary
- A Google Sheets document is referred to as a workbook. Each of the tabs inside the workbook is referred to as a sheet.
- Google Sheets processes two data types: Numbers and Strings
- Number: any numerical value (integer, decimal, date/time, boolean as 1 or 0)
- String: text values
- A function is the mechanism that performs calculations using specific values in a particular order. A formula refers to the actual written expression that a utilizes a function.
- Functions can be used to create formulas that manipulate data and calculate strings and numbers. These terms are often used interchangeably since it is a small semantic difference.
- Reference: the cell(s) whose value(s) you want a function to use
- Null: there no value in the cell
References
A reference points to the cell(s) whose value(s) you want a function to use
Reference Syntax
a reference has two components, the column and the row. Columns are alphabetical and rows are numerical. References must always contain those two components in that order.
- A range refers to any groupings of cells. If you want a formula to reference everything between
A1 and D10, you would type A1:D10
Types of references:
Relative
Static
Referencing between sheets
- By default, all references point to the cell or range in the current sheet
- If you want to reference another sheet in the same workbook, you need to use the following syntax:
'name_of_sheet'!cell_reference
- Make note of the required syntax in gray text above
- Ex: Suppose you’re writing a formula in Sheet2 and you want to reference a range in Sheet1.
'Sheet1'!A1:B10 will look at Sheet1 and perform the calculations to output in the selected cell in Sheet2
- Note: in order to reference cells from a sheet in a different workbook, you will need to use
=importrange() on a helper Sheet within the current workbook. There is no way to directly reference to another workbook’s data otherwise.
Functions