Google Formulas in Sheets

Combining Columns in Google Sheets

Google sheets have a variety of purposes. You can use them for budgeting or sometimes they hold the results from Google forms. Sometimes, the information in each cell would work better if it was in a cell together. Example: “George” is in cell A1 and “Jones” is in cell A2 and you wish the names were in a cell together “George Jones”. One of my favorite functions is CONCATENATE. To combine “George” and “Jones”, go to cell A3 and type the following formula =concatenate(A1, “ “,A2).

An additional feature I found in the CONCATENATE formula includes adding a carriage return (enter). Sometimes you want and/or need data to have an enter after it. You can’t do it through search and replace, but you can do it through CONCATENATE. For example, =concatenate(A1, “ “,A2,char(10)). You could use the char(#) portion of the formula whether you are combining with other cells or not.

Google Forms: Data Validation

Google Forms have many uses in the educational setting. One feature of Google forms that can be especially helpful is the data validation feature on items and/or questions. Today, our district was working on a form to allow students to vote for homecoming maids. In this instance, students could only vote for 3 students of those listed as options in the question. Data validation works perfectly for this!

• Type in your question/item
• Click on the 3 dots on the bottom right corner of your question/item
• Click on data validation
• Click to select the first option (Select at least, Select at most, Select exactly)
• Input the number for the number of options you want to allow 
• Type in the information you would like to appear in the event the user does not follow the rules for options you created in the previous steps.
• Add more questions/items and data validation if needed for those items as well.

How cool is that? This is actually a super, simple solution to a situation that happens more often than you might think. We have also used the data validation feature to password protect forms, but this way expands the flexibility of Google forms. What other uses do you see for data validation?

Google Sheets Add On: Row Call

I have shared about the Row Call add on for sheets several times. Sometimes people don’t see a good use for it right off the bat. This year, I found a perfect use for it as we were submitting technology agreements. I needed to sort which teachers had completed the document in each building. Row Call performed perfectly. It took the submitted entries and divided each entry out into separate sheets based on which building they indicated that they worked in which was located in Column D.

Row Call is a super spreadsheet sorter. Do you have data in a spreadsheet that you need instantly sorted to different sheets? Check it out and put it to work for you!

New Google Earth Experiment: Tourbuilder

New Google Earth Experiment:  Tourbuilder

This week’s tidbit is another simple share. Check out the new tourbuilder by Google by clicking on the link above. There is a sample tour located here. Notice in the sample that each pushpin location includes a picture and information about that stop on the journey.

I see tons of ways that this could be used in education. Students could explore significant battles in history and share pictures with information about the battle locations. Students could share places they visited over the summer with stories relating to those visits. Students could explore their state by studying different locations and sharing the importance of those locations as they relate to the state’s history.

Check out the gallery of tours already created. What ideas come to mind as you see the Google tourbuilder technology tool?

April Tip #2: Google Sheet AddOn: Save as Doc

Google Sheet AddOn: Save as Doc

This week’s tidbit is another AddOn for Google Sheets. We recently did a survey using Google forms. As we were looking over the survey results in the Google sheet it was hard to read all of the input scrolling through the sheet. The perfect answer to read through the results was the AddOn for Google sheets called Save as Doc. To add this AddOn

• Open your Sheet of Form Results (This would also work on a regular Google sheet not connected to a form as well)
• Click on AddOns - Get Addons
• Search for Save as Doc and add it to your Google sheet.
• Click on AddOns - Save as Doc - Start
• An information box appears on the right hand side of your screen
• In the top box, type in the name you want for your created Google document
• Click the box to include your headings
• Leave the default selection stating that the first row of the sheet contains your headings or select the alternate choice of first row of selection contains your headings
• Next, pick your heading style (Note: All headings appear in the Google doc using this heading style with individual answers under the heading)
• Check the box to add a page break after each row (This puts each line of information in the Google sheet on its own page
• Then click the box that says Select All Data
• Once you click that box, text should appear below that says “All data has been selected!”
• Click Save as Doc
• The Google doc will generate
• Click at the bottom to Open your Document

Here is a sample Google sheet as well as the sample result from the Save as Doc AddOn. This worked on survey results, but it’s also a great way to print out student answers to a Google form as well.

First April Tip: Mapping Data Points

Maps

Maps are so cool. They are visual representations of where things are located. A cool new add on for Google Sheets allows you to create maps of spreadsheet data. First, you need this document. When you click on the document, it will force you to make a copy. Now that you have a copy, start adding some sample information. To start, I would just add 2 lines of information. You can re-build your map multiple times, so it’s ok if you don’t have all of the information there yet. You can change the headings and/or the information listed. I’ve just included those as samples.

Now, add the mapping add-on. Click on ADD-ONS, Get ADD-ONS. Search for Mapping Sheets and click to add it. Once it’s added, go back to your sheet and click on ADD-ONS, Mapping Sheets, Start Mapping. The menu will appear on the right hand side of the screen. I leave the Title and Filter as the Name of the School. Change the location to be the location from the drop down menu to the right. Click BUILD at the bottom. Click VIEW to see your map. Add the link to a web page to show your mapped data. If you go back and add more information, you will have to click the build button again. Your link will NOT change though, but update with the new built information.

We used this add-on to map the data for the location of our schools with an administrator name and phone number.  We then added a link to our web page. Here’s what our link looks like. What do you think? Share your ideas for how this tool could be useful in the comments section.

March Tech Tip #1

Simple Google Sheets (Excel) Formula Functions

I love spreadsheets.  In fact, I'm a spreadsheet geek. For the start of this month's tech tips, I thought I would share a simple spreadsheet formula that I used this week. I used it to determine if teachers had completed a form that they were supposed to complete. I have used it in the past to tally the number of responses given or to count items listed in an inventory.

The formula function is called COUNTIF. This works in Excel and in Google Sheets. The concept behind countif is that it will COUNT the number of items in a range of cells, IF it contains the given search criteria. For example,

=COUNTIF(B1:B100,"Michaels")


You don't have to type the criterion in, it can also be a cell reference, such as

=COUNTIF(B1:B100,A1)

or it can be a mathematical expression as shown in the sample to the right. There are a variety of reasons to use the COUNTIF formula. You may want to know

• how many times particular criteria shows up in the range of cells 
• if criteria exists within the range of cells

I have included a link with some sample data to give you an idea of how this might work. Keep in mind the sample data combines a couple of ideas which might not always be seen together. I combined them in order to show you the data with one link. 

Please comment or e-mail me if you have questions or want to share ways you use the COUNTIF formula. Remember, it's not what you know, but what you SHARE!

February Tip: Password Protecting Google Forms

Password Protecting a Google form

Sometimes, you might want to control when students can start answering questions on a google form. To accomplish this, add a password.

• Open Google drive
• Click on NEW
• Click on Google Form
• Name the form
• Create your first question which will be your password question
• Make the question a short answer question and then use data validation to control the text entered that
  
  will allow the student to move on to the actual quiz.
• You can select what data you use for validation,
• Make sure you mark the question required.
• To prevent any other questions showing up until a correct password is entered, add a section break or a page break to your quiz.
• Enter your remaining questions.

To test out using my sample, click on this link and you will see what happens when you enter the correct password (unique) and incorrect password.

Keeping People Updated in the New Year

Teachers are often required to have a web page. Many will readily tell you that they are not web page designers, they are teachers. Therefore, I have found a simple way for teachers to create and update web pages.

To accomplish this, a teacher simply follows these steps

• Open Google drive
• Click on NEW
• Click on Google Doc
• Name the document
• Type information on the page
• Click on File
• Click on Publish to the Web
• Click on Publish
• Click OK (you are sure you want to publish)
• 
  A screen appears with the link to send to your district’s webmaster
• Click on the envelope icon (Gmail) and your e-mail will open where you can type in your webmaster’s e-mail address and the link will already be in the body of the e-mail for your district webmaster to create a link off of the district web page to your web page.
• Once this link is connected, you don’t have to work with the webmaster again. Your page becomes live at that moment. All new changes will automatically appear on the published page.
• You now have an easy way to keep parents current on all events
• Here is a link to a sample, very simple published doc as a web page

Now, a couple of notes

• It’s really plain. However, I believe most people just want to know current information and aren’t as concerned about the looks.
• It does take 5 minutes sometimes to update, so be patient if you type into a document and it hasn’t appeared on your published document yet.

The first time I presented this to teachers, they were blown away that it was really that easy. Most importantly, since presenting this over 2 years ago to a group of teachers, they are still doing weekly updates on their web pages.  

Many people just can’t accept the plain simplicity of the regular document, so I offer this alternative which is simply a formatted, centered table within a Google doc. The instructions to publish are the same as above, but you start with making a copy of this document.

Let me know how this works for you and I'd love to share the pages you create using this method.