Intermediate‎ > ‎

Intermediate Spreadsheets


Google Spreadsheets
Use the training resources on this page to expand upon your basic Google Spreadsheet skills.  The more training you have using any tool, the more impact it will have on your students.
 


 













Inserting a hyperlink into a Google Spreadsheet

Watch the Google Gooru video below about how to insert a hyperlink into a Google Spreadsheet.

How to add a Hyperlink to a Google Spreadsheet

If you post a website URL in a cell on Google Spreadsheet, it will automatically become a hyperlink to that website. But what if you want the link to be hidden behind simple text? This will allow you to post a link with anchor text such as “Click Here” or “Information about Whales.”

To create a hyperlink in a Google Spreadsheet:

  1. Double-click on the cell you want to paste the link in.
  2. Type the following formula:
    • =HYPERLINK( “[URL]” ; “[anchor text]” )

    Replace [URL] with the address of the website you wish to navigate to, and [anchor text] with the words you want to appear.
For example, typing =HYPERLINK ("www.dpsk12.org";"DPS Homepage") will create a hyperlink labeled "DPS Homepage" that links to that page.






Printing a Google Spreadsheet Without Grid Lines


Watch the Google Guru video below about how to print a Google Spreadsheet without grid lines.

How to Print a Google Spreadsheet without Gridlines - YouTube.mp4


Unlike in Microsoft Excel, Google Spreadsheet will print the gridlines on your spreadsheet even if they are not visible in the open file. Here is the simple way to remove them on the printed copy.

1. Select "Print" as you normally would.
2. On the Print menu, check the box that says "Remove Gridlines." Be sure to apply this to all sheets in the document.

The document will now print without any gridlines, making a neater, cleaner list.







Removing Duplicate Entries in a Google Spreadsheet

Watch the Google Gooru video about how to remove duplicates in a Google Spreadsheet.

How to Remove Duplicates in Google Spreadsheets


Sometimes you will have a large list of data in your spreadsheet, but you will want to see only the unique entries. This formula will help you separate out all the duplicates and leave you with one singular list.

1. Click in the cell where you want your list to go, and begin typing the unique formula by typing =unique(

2. Leaving the parentheses open, highlight the column/s of data that you want to sort.

3. Close the parentheses around the formula and hit Enter. This will create a list of all the unique entries from the columns you selected.

If you want to replace your original list with the unique items:

4. Select the cells containing your unique list.

5. Copy the cells.

6. When pasting, select "Paste Special" and choose "Paste Values."

You now have a list of only the unique entries from your original data.





Embedding a Google Spreadsheet Onto a Website

Watch the
Google Gooru video below about how to embed spreadsheets onto a website.

How to Embed a Spreadsheet onto a Website

You can share your Google Spreadsheets on a website in one of two ways-- either by getting an embed code from the spreadsheet, or by going through a Google site.

To publish your spreadsheet with an embed code:

1. With your spreadsheet open, go to File > Publish to Web
2. In the dialogue box that pops up, make sure that "Automatically Republish" is checked. You can decide whether or not you want to secure your spreadsheet by requiring people to be logged into their Google accounts to see it.
3. Click "Start Publishing."
  You will receive an embed code that you can copy/paste onto any website creator.


To publish your spreadsheet through Google Sites:
1. On the Google Site page you want the spreadsheet to go on, go to Insert > Spreadsheet.
2. Select the spreadsheet you would like to use. Save your changes.

Google Spreadsheets will automatically update in places where it has been embedded. This means if you change the spreadsheet in your Drive, it will publish your changes wherever you have embedded the spreadsheet. This prevents you from needing to update your spreadsheet everywhere you have it published.


Quickly Editing Charts in Google Spreadsheet


Watch the Google Gooru video below about how to quickly edit charts in a Google Spreadsheet.

Quickly Edit Charts in Google Spreadsheets

Google Spreadsheet features a Quick Edit feature on its charts and spreadsheets. This allows you to make quick changes to a chart you have made without needing to go through the more detailed Edit screen.

To quickly edit a chart you have made:
1. Open the chart you would like to edit.
2. While hovering over the chart, click on the pencil icon for "Quick Edit Mode."

From here you can change the colors, labels, axis values and other cosmetic features of the chart without having to go through and create a whole new one.








Protecting Cell Ranges in a Google Spreadsheet

Watch the Google Gooru video below about protecting cell ranges in a Google Spreadsheet.

Protecting Cell Ranges in Google Spreadsheets

Protecting cell ranges is an easy way to keep other viewers from changing the data in a group of cells, or to protect the formulas or calculations you have set up in a spreadsheet. You can determined who is allowed to view or edit your protected cell ranges.

To protect a range of cells in your spreadsheet:
1. Highlight the cell/s that you want to protect.
2. Right-click on the cells and select "Name and Protect Range."
3. If you choose to name the cells, you must use letters, numbers or underscores only, and you cannot begin the name with the words "True" or "False." Once you have named the cells, click on "Protect."
4. To control who can edit or view your protected range of cells, click on "Modify Permissions." You can protect your cells from any editing except from yourself, or you can allow a colleague to edit those cells as well.

You can quickly view which ranges you have named and protected by going to Data on the menu, and selecting "Named and Protected Ranges."



Using named ranges in Google Spreadsheets


Watch the Google Gooru video below to learn about using named ranges in Google Spreadsheets.

Using Named Ranges in Google Spreadsheets


By naming cell ranges, you can create "groups" of cells that are recognized as having the same function. This way, you can make an edit in one place to update formulas all across the spreadsheet.

To name a range of cells in your spreadsheet:
1. Highlight the cell/s that you want to protect.
2. Right-click on the cells and select "Name and Protect Range."
3. If you choose to name the cells, you must use letters, numbers or underscores only, and you cannot begin the name with the words "True" or "False."

To edit your range to include more cells:

1. On the "Name and Protect Range" menu, click on the pencil icon to Edit.

2. Change which cell ranges you want to include in the named range. 





How is DPS doing?



The DPS Google Support Site with further integration ideas and resources can be found here.



Comments