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.



Post a Comment

Popular Posts