Tonight I worked on a presentation I am doing on Google Sheets. I think it is a very underused tool in education. I need to learn more but here is a beginner's primer. Note: This is mostly a curation from other sites and I will be adding references when it is not after midnight!
Unlocking the Small Powers of Google Sheets
To hide a sheet:
To unhide a sheet:
Use Autofill to Complete a Series
- In a column or row, enter text, numbers, or dates in at least two cells next to each other.
- Highlight the cells. You’ll see a small blue box in the lower right corner.
- Drag the blue box any number of cells down or across.
- If the cells form a series of dates or numbers, the series will continue across the selected cells.
- If the cells don’t form a series of dates or numbers, the list of values will repeat across the selected cells.
Filter your data
To filter your data:
Filter by condition: Choose from a list of conditions or write your own. For example, if the cell is empty, if data is less than a certain number, or if the text contains a certain letter or phrase.
Filter by values: Uncheck any data points that you want to hide and click OK. If you want to choose all data points, click Select all. You can also uncheck all data points, by clicking Clear.
Create, save, or delete a filter view
Note: If new data is entered on the spreadsheet the filter view will apply to the new data
Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.
- Select the cells you want to apply format rules to.
- Click Format → Conditional formatting. A toolbar will open to the right.
- Create a rule.
- Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met.
- Color scale: Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow.
- Click Done.
Use data validation rules to control the type of data or the values that users enter into a cell.
- To use data validation on a set of cells, select Data → Validation
- Next to Criteria, choose an option:
- List from a range: Choose the cells that will be included in the list.
- List of items: Enter items, separated by commas and no spaces.
- The cells will have a Down arrow. (You can remove the arrow by uncheck "Display in-cell button to show list.")
- If you enter data in a cell that doesn’t match an item on the list, you’ll see a warning. If you want people to only enter items from the list, choose "Reject input" next to "On invalid data."
- Click Save. The cells will show a drop-down list.
Copy Down - automatically copies formulas to new rows. Just add the formulas to a top row in the spreadsheet worksheet where your form results will be added—row 2 by default—and turn on copyDown. Then, as results are added to your spreadsheet, it'll copy those formulas down to each new row, calculating values, evaluating results, and more automatically.
rowCall - creates a new sheet for each unique type of response and sorts the data into the appropriate sheet automatically, so you never have to search through and filter form data again.
autoCrat - AutoCrat is a multi-purpose document merge tool that allows you to take data from a spreadsheet and merge it into a document via a template. Tell autoCrat which fields to merge via <<merge tags>> and then let autoCrat mass-generate personalized documents. Optionally send the documents as email attahchments. Optionally tell autoCrat to run when new forms are submitted to created truly automated processes.
Split Names - Quickly split full names to several columns with first, middle, last names, titles, and/or post-nominal letters.
formMule - sends targeted, personalized emails from a Google Sheet. Run as either a manual email merge or -- more powerfully -- as a triggered merge that runs automatically on form submit or on a timer. Can use up to 15 different user created templates.