Google Sheets cheat sheet
Create & collaborate with online spreadsheets to analyze data, create project plans, & more—all in your browser.
Kyd Tantano Masong
Last Update a year ago
Work with your data
1. Copy formatting from any text and apply it to another selection of text
2. Format data as currency, a percentage, change decimal places, and more
3. Add or edit cell borders
4. Merge cells
5. Change text alignment
6. Change how text wraps or rotate text
7. Add links, comments, charts, filters, or functions
| FUNCTION | DESCRIPTIONS |
|---|---|
| AVERAGE | Statistical Returns the numerical average value in a dataset, ignoring text. |
| AVERAGEIFS | Statistical Returns the average of a range that depends upon multiple criteria. |
| CHOOSE | Lookup Returns an element from a list of choices based on index. |
| COUNT | Statistical Returns the count of the number of numeric values in a dataset. |
| COUNTIF | Statistical Returns a conditional count across a range. |
| DATE | Date Converts a provided year, month, and day into a date. |
| FIND | Text Returns the position at which a string is first found within text. |
| GETPIVOTDATA | Text Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings. |
| IF | Logical Returns one value if a logical expression is true and another if it is false. |
| INDEX | Lookup Returns the content of a cell, specified by row and column offset. |
| INT | Math Rounds a number down to the nearest integer that’s less than or equal to it. |
| LOOKUP | Lookup Looks through a row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column. |
| MATCH | Lookup Returns the relative position of an item in a range that matches a specified value. |
| MAX | Statistical Returns the maximum value in a numeric dataset. |
| MIN | Statistical Returns the minimum value in a numeric dataset. |
| NOW | Date Returns the current date and time as a date value. |
| ROUND | Math Rounds a number to a certain number of decimal places according to standard rules. |
| SUM | Math Returns the sum of a series of numbers and/or cells. |
| SUMIF | Math Returns a conditional sum across a range. |
| TODAY | Date Returns the current date as a date value. |
| VLOOKUP | Lookup Searches down the first column of a range for a key and returns the value of a specified cell in the row found. |
Conditional Formating | Highlight
Google Sheet formula that will highlight the cell using conditional formmating when I type a number in [cell name] and display the highlighted output from the [CellRange]
You can use conditional formatting in Google Sheets to highlight cells in the range [CellRange] based on the value you type in cell [cell name]. To achieve this, you can follow these steps:
- Select the range [CellRange] where you want to apply the conditional formatting.
- Click on "Format" in the top menu, then select "Conditional formatting."
- In the "Conditional format rules" panel that appears on the right, change the dropdown menu from "Cell is empty" to "Custom formula is."
- In the text field that appears, enter the following custom formula:
Example: