Thursday 24 November 2016

Unlocking the Small Powers of Google Sheets

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

Hiding Columns

To hide a sheet:
  1. Click the sheet you want to hide.
  2. On the sheet tab, click the Down arrow .
  3. Click Hide sheet. This option won’t show if your spreadsheet doesn’t contain two or more sheets
To unhide a sheet:
  1. Click ViewHidden sheets. If your spreadsheet doesn’t have any hidden sheets, this option will be grayed out.
  2. Click the sheet that you no longer want hidden.
  3. The spreadsheet will reappear.

Use Autofill to Complete a Series

  1. In a column or row, enter text, numbers, or dates in at least two cells next to each other.
  2. Highlight the cells. You’ll see a small blue box in the lower right corner.
  3. 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:
  1. Select a range of cells.
  2. Click DataFilter.
  3. To see filter options, go to the top of the range and click Filter Filter.
  4. To turn the filter off, click DataTurn off filter.
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
  1. Open a spreadsheet in Google Sheets.
  2. Click DataFilter viewsCreate new filter view.
  3. Sort and filter the data.
  4. To close your filter view, go to the top right and click Close X.
  5. Your filter view is saved automatically.
  6. To delete or duplicate a filter view go to the top right and click Options Settings →  Delete or Duplicate.
Note:  If new data is entered on the spreadsheet the filter view will apply to the new data

Conditional formatting

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.
  1. Select the cells you want to apply format rules to.
  2. Click Format →  Conditional formatting. A toolbar will open to the right.
  3. 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.
  4. Click Done.

Data Validation

Use data validation rules to control the type of data or the values that users enter into a cell.
  1. To use data validation on a set of cells, select DataValidation
  2. Next to Criteria, choose an option:
    1. List from a range: Choose the cells that will be included in the list.
    2. List of items: Enter items, separated by commas and no spaces.
  3. The cells will have a Down arrow. (You can remove the arrow by uncheck "Display in-cell button to show list.")
  4. 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."
  5. Click Save. The cells will show a drop-down list.

Useful Add-ons

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.

1 comment:

  1. Awesome Informative article I am a regular user of Google Sheet but I never explore the sheet in this way you have mentioned amazing options here for google sheet. Google sheet add ons always make my work easier and faster. Google is really a great tool made by google.