Google Sheets
Name of Guide
Table of Contents Introduction........................................................................................................3 What is Google Sheets.....................................................................................3 Uses of Google Sheets.....................................................................................3 Create a Sheet................................................................................................4 Title a Sheet...................................................................................................5 Menu Toolbar..................................................................................................5 Working in Sheets...............................................................................................6 Add Data in a Cell.......................................................................................7 Add Rows and Columns................................................................................7 Delete Rows and Columns............................................................................8 Hide/Unhide Rows and Columns....................................................................9 Freeze/Unfreeze Rows and Columns............................................................. 10 Search for a Word......................................................................................... 11 Find and Replace....................................................................................... 11 Protect Data................................................................................................. 12 Set Notifications............................................................................................ 14 Filter........................................................................................................... 15 Conditional Formatting................................................................................... 16 Insert Charts................................................................................................ 17 Data Validation............................................................................................. 18 Important Google Scripts.................................................................................... 20 Installing Scripts........................................................................................... 20 Flubaroo ..................................................................................................... 21 Yet Another Mail Merge.................................................................................. 23
Copyright © 2015 Northside ISD Technology Services
Google Sheets
Introduction What is Google Sheets A Google Sheet is an online collaborative spreadsheet similar to Microsoft Excel.
Uses of Google Sheets Google Sheets are very similar to Microsoft Excel. In addition to displaying data in rows and columns, Google Sheets can do the following: • Collect responses from Google Forms • Inventory lists • Reporting Metrics • Sign ups • Mail Merges • Self-Grading Quizzes • Budgets • Monitor Work Flows
Copyright © 2015 Northside ISD Technology Services
3
Google Sheets
Create a Sheet 1. Navigate to your inbox. 2. In the top right corner of the screen, click the Apps Grid. 3. Select Drive. 4. Select the NEW button located in the top left corner of the screen and choose Google Sheets.
Copyright © 2015 Northside ISD Technology Services
4
Google Sheets
Title a Sheet 1. On the top right part of the screen, click Untitled Spreadsheet.
2. Type the Sheet Title in the space provided and select OK.
Menu Toolbar Within the Menu Tool Bar you will see the following: File – You will have the option to rename, share, copy, move, download the sheet as an excel file, and see the revision history of the document. Edit - Undo/redo recent edits, cut, copy, paste, and use the find and replace tool. View – Freeze/Unfreeze rows, remove gridlines, view formula bar and protected ranges. Insert – You can insert images, charts, comments, rows, columns, and links. Format – Allows you to format text. Data – Provides options to manipulate and sort information on the sheet. Tools – Enables you to access more advanced features such as protecting your sheet and searching for scripts.
Copyright © 2015 Northside ISD Technology Services
5
Google Sheets
Working in Sheets Google Sheets function very similar to excel worksheets. You have the option to name, move, color, and add/delete sheets. 1. To Duplicate, rename, and color your sheet select the drop down arrow next to Sheet1.
2. To add another sheet, select the + on the bottom left part of the screen.
3. After you have created 2 or more sheets, you can move and rearrange by selecting the drop down arrow next to a sheet and selecting Move Left or
Copyright © 2015 Northside ISD Technology Services
6
Google Sheets
Add Data in a Cell To add data in a cell simply select the cell and start typing.
Add Rows and Columns 1. Select a row or column. 2. Select INSERT from the toolbar. 3. If you selected a row, you can add one above or below. If you selected a column you can choose to insert one to the left or right.
Copyright © 2015 Northside ISD Technology Services
7
Google Sheets
Delete Rows and Columns 1. Select a row or column. 2. Select EDIT from the toolbar. 3. Click DELETE.
Copyright © 2015 Northside ISD Technology Services
8
Google Sheets
Hide/Unhide Rows and Columns Sometimes you may want to hide columns or rows while you are working on a Google Sheet. 1. Select the row(s) or column (s) you wish to hide and right click. 2. Select HIDE COLUMN
Copyright © 2015 Northside ISD Technology Services
9
Google Sheets
Freeze/Unfreeze Rows and Columns There will be times when you want to be able to keep an area of a worksheet visible while scrolling through information, particularly in regards to row and column headings. 1. Select the row(s) or column (s) you wish to freeze, and from the toolbar, select View . 2. Choose to freeze or unfreeze the row or column.
Copyright © 2015 Northside ISD Technology Services
10
Google Sheets
Search for a Word This function is an excellent tool and very valuable if your sheet contains a lot of information. When using this tool, you will be able to search for a word or phrase in the document. 1. On your keyboard, press “Ctrl” “F” simultaneously. 2. Type the word or words in the box on the top right part of your screen. 3. If the word appears multiple times, you will be able to scroll using the arrows located to the right of the search box.
Find and Replace The Find and Replace tool allows you to automatically find all words or phrases and replace it with different words or phrases. 1. On your keyboard, press “Ctrl” “F” simultaneously. 2. Click the icon with three dots as shown below.
Copyright © 2015 Northside ISD Technology Services
11
Google Sheets
A menu will appear with a list of options. 3. Type in the word or words you would like to find as well as what you would like to replace it with. NOTE: You will have the option to search within the entire workbook, a specific sheet, or a range of cells. Also, you can refine the search to match sentence case, entire cell contents, and formulas.
Protect Data Google Sheets allows you to restrict collaborators from editing a specific sheet or range of cells. 1. Select the cells or range of cells using your mouse. 2. On the toolbar, select Data and choose Protected sheets and ranges.
A menu will appear that shows you the range of cells you have selected, as well as the option to set different permission levels for collaborators. Copyright © 2015 Northside ISD Technology Services
12
Google Sheets
3. To set the protected data permission settings for different collaborators, click Set Permissions.
4. From this menu you will be able to set the permissions settings for each collaborator.
Copyright © 2015 Northside ISD Technology Services
13
Google Sheets
Set Notifications Setting notifications allows the user to know when changes are made to a shared Google Sheet. 1. From the toolbar, select Tools and choose Notification rules.
2. A window will appear that will allow you to set different notification rules depending on if the Sheet is being used to collect responses from a Google Form or not. 3. You will also have the option to receive an email notification each time the Sheet is edited or to receive one email at the end of the day detailing the changes. 4. Click Save after you have made your selection.
Copyright © 2015 Northside ISD Technology Services
14
Google Sheets
Filter The filter tool allows you to quickly sort and organize data. 1. Select the entire sheet by clicking in the area above Row 1 and to the left of Column A. 2. Select the DATA on your toolbar. 3. Choose FILTER. OR 4. Select the FILTER ICON.
Copyright © 2015 Northside ISD Technology Services
15
Google Sheets
Conditional Formatting Conditional formatting allows you to apply certain formatting rules in a Google Sheet. To access the conditional formatting tool: 5. Select the range of cells you wish to apply conditional formatting. 6. Click Format on the toolbar and choose Conditional Formatting.
A window will appear that allows you to set rules. In the example below I chose to have the rule based on the text contained in the cell. 7. Select Save Rules.
NOTE: You also have the choice to set formatting based on whether a cell is empty, contains words or phrases, date, and numbers.
Copyright © 2015 Northside ISD Technology Services
16
Google Sheets
Insert Charts 1. Select the DATA you wish to be displayed in the chart. 2. Select INSERT and choose CHART
3. Choose the type of CHART you wish to display. 4. Click INSERT. 5. The chart will be inserted into the spreadsheet.
Copyright © 2015 Northside ISD Technology Services
17
Google Sheets
Data Validation Data Validation helps you control what type of information can be entered in your cell or worksheet. To access the Data Validation tool: 1. Select the cell or cells you wish to apply the validation.
2. Select Data on the toolbar and Choose Validation.
Copyright © 2015 Northside ISD Technology Services
18
Google Sheets
3. You will be able to select different criteria. In the example below, I chose List of Items.
4. Type in the list of choices and click Save.
5. The selected cells now have a drop down arrow that will display the items you typed on the previous screen.
Copyright © 2015 Northside ISD Technology Services
19
Google Sheets
Important Google Scripts A script is a cloud-based language that creates add-ons in Google Sheets to automate certain tasks.
Installing Scripts You can access the Google Add-On store by: 1. Select ADD-ons from the toolbar and Choose Get add-ons.
2. The store will appear and you will be able to search for an Add-on using the search box.
3. To download the Add-on select FREE located in the top right side of the add-on icon.
Two popular and useful add-ons to consider installing are called Flubaroo and Yet Another Mail Merge. Copyright © 2015 Northside ISD Technology Services
20
Google Sheets
Flubaroo A script that will automatically grade student tests or quizzes. Once you have downloaded the Flubaroo Add-on you will now see it under Add-ons in your toolbar. 1. Select Add-ons from your toolbar and Choose Flubaroo. 2. Select Enable Flubaroo in this sheet.
3. Select Add-ons from your toolbar and Choose Flubaroo. 4. Select Grade Assignment.
5. Choose a grading option for each question and click Continue.
Copyright © 2015 Northside ISD Technology Services
21
Google Sheets
6. Select which submission will be used as the key.
7. A new sheet will be created in the response workbook that will display the student grades.
Copyright © 2015 Northside ISD Technology Services
22
Google Sheets
Yet Another Mail Merge Yet Another Mail Merge is a script that allows users to easily and quickly select a draft from GMAIL and replace the template information with names and other key information. Once you have downloaded the Yet another Mail Merge Add-on you will now see it under Add-ons in your toolbar. In the following example I created a form asking students what they did this summer and viewed the responses in the spreadsheet. Make sure the option to capture NISD usernames is checked, as this will provide the email list.
1. Compose a draft in GMAIL and tag the column headings NOTE: I titled my Draft “Happy Summer.”
2. Select Add-ons from your toolbar and Choose Yet Another Mail Merge. 3. Select Start Mail Merge.
Copyright © 2015 Northside ISD Technology Services
23
Google Sheets
4. A window will appear asking you to select a draft in GMAIL. Choose the appropriate draft and click Send Emails.
5. A column will appear next on the sheet letting you know the status of the email.
Copyright © 2015 Northside ISD Technology Services
24
