Tuesday, 30 September 2014

5 tips for working with Google Sheets

Google Sheets has heaps of features for making working with your data easy. Here are five of our favourites.

Tip 1: Insert drop-down lists in a cell

You can control the data people enter into a cell by using a drop-down list of options for them to select from. This is achieved using the validation feature. To create a drop-down list of options:

1. Select the cell (or multiple cells) you want to create the text in.

2. Select the Data menu > Validation.

3. In the Criteria section, select List of items.


4. Enter the name of each item in the list, separated by a comma.


5. Select the appropriate handling of invalid data.

6. Ensure Display in-cell button to show list is ticked.

7. Click Save. Your list will now be shown in the cell.


Tip 2: Wrap text in a cell

Wrapping text in a cell makes it break over multiple lines. This allows you to easily see all the contents of the cell. Click on the cell and then click the Wrap Text button in the menu to wrap the text. You can apply this to multiple cells by selecting multiple cells before pressing the button.


Tip 3: Show all formulas

Sometimes you want to quickly see the formulas that are used in your sheet.
Pressing the Ctrl and ~ keys on a PC, or Cmd and ~ on a Mac will show formulas. Simply press the key sequence again to return to the formula output view.

Tip 4: Conditional formatting

Conditional formatting is used to change the appearance of cells when they contain certain types of data. This is great to use in conjunction with data generated by Google Forms. For example, you may want to make red all the cells where respondents have got an answer incorrect (ie. when the form is used as a test), or expressed dissatisfaction (ie. when the form is used as a survey). To set up conditional formatting:

1. Select the cell (or multiple cells) you want to create the text in.

2. Select the Format menu > Conditional formatting.

3. Select a type of criteria from the Text contains box.

4. Enter the value that will trigger the formatting (eg. the incorrect answer or low rating).

5. Select a text and/or background colour to format the cells in.


6. Click Save rules.

Tip 5: Freeze rows and columns

Freezing rows and columns keeps a designated number of rows or columns on the screen at all times, no matter how far you scroll. This is great when you are working with long sheets. A good idea is to freeze heading rows or columns. To freeze rows or columns:

1. Select the View menu > Freeze rows (or Freeze columns).

2. Select the number of rows or columns you want to freeze.


Friday, 19 September 2014

Approval workflows in Google Docs & Sheets

Managing document reviews and approvals can be time consuming, but the Worflows add-on in Google Docs and Sheets makes it quick and easy. Simply enter the email address of the reviewer, along with a message, and they are notified that you want them to review your work. Once they’ve approved it, you’re notified by both email and a message in the Doc or Sheet. This tools is great to use with both students and colleagues. Get students to add you as a reviewer once they’ve finished a draft, or get everyone’s OK on an important document.

How do I get it?

1. Open a Google Doc or Sheet.

2. Select the Add-ons menu and Get add-ons.

3. Locate or search for Workflows.

4. Click the +FREE button to install.

Workflow add-on install

5. Grant the add-on permission to integrate with Google Drive.

How do I use it?

1. Open the Google Doc or Sheet you want to get reviewed and approved.

2. Make sure the people you want to review and approve the document have edit access to it.

3. Select the Add-ons menu > Workflows > Show workflow. The add-on will open in the panel on the right.

4. Enter the email address of the person you want to review and approve the doc. Click Add. Repeat if you require multiple reviewers.

5. Enter a message.

Create workflow request

6. Click Request approval.

7. The list of reviewers and the approval status will be shown.

Workflow request status

What does the reviewer see and do?

1. The reviewer gets an email with content similar to that shown below.

Workflow approval request emaiil

2. After clicking Review document, the reviewer needs to install the Workflows add-on if they do not have it already. They will be prompted to do this when the document opens.
Tip: You may also like to include a note in your review request advising reviewers that they will need to do this.

3. Once the add-on is installed, the reviewer sees the review request in the right-side panel.

Approve or reject request

4. The reviewer can add a return comment and click Approve or Reject.

5. You will receive an email notifying you of the approval status. The workflows panel within your Doc or Sheet will also show the status.

Workflow status email

Workflow approval status in doc

Friday, 12 September 2014

Automatically decline calendar invitations during specific times

The Automatically declining events Google Calendar lab gives you the ability to automatically decline calendar invitations during specific time periods. This is great for those times when you will definitely not be attending meetings (think annual leave or professional development).

This lab works its magic by giving you an additional availability option when creating an event , called Busy (decline invitations). To put it to use, just create an event in your calendar to block out the time you’re unavailable, and select this new option. Here’s how to do it step-by-step:

To enable Automatically declining events

1. Click the Settings cog > Settings.

2. Select the Labs tab.

3. Find and enable the Automatically declining events lab.

Automatically declining events lab setting

4. Click Save.

To use Automatically declining events

1.Create a new calendar event at the time you are unavailable.

2. At the bottom of the event, select the Busy (decline invitations) option.

Busy - decline invitations option

3. Save the event.

Any calendar invitations for events during this time slot will now be declined automatically.

Popular Posts