We could all use a little more time. In fact, I’ve recently wondered where August, September, and October went. I can’t be the only person to experience this phenomenon! But regardless of how time feels to you right now, here are seven tricks for saving time with Google Sheets.
1. Merge cells to create first initial last name (i.e. ehopkins) usernames.
Did you know there’s a quick way to merge a cell with a first name (Emily) and a cell with a last name (Hopkins) to make a username (ehopkins)? Well, it’s true. Using one of the formulas below, you can easily do this with a list of hundreds of names. If you ever have to upload CSV rosters to create accounts, this will save you tons of time.
Here are two formulas to choose from:
- =(CONCATENATE(LEFT(CELL,1),””, CELL))
- =(LEFT(CELL,1) & “” & CELL)
Here’s what it looks like in action:
Take a look at the following trick to see how to change the case for your usernames.
2. Change text in cells to lowercase, uppercase, or proper case.
You can use one of the three functions below, either with another function or on its own, to change the case of your text in cells.
=PROPER
=UPPER
=LOWER
See them in action:
3. Want to see all Google Sheets keyboard shortcuts? You can!
If you’re using a Mac, simply press the command and forward slash keys. On a Windows machine, press the control key and forward slash. A window full of keyboard shortcuts will appear for you. It’s magic.
4. Easily create QR codes for any cell.
I love this one. You can create QR codes with information from your spreadsheet using this function.
=IMAGE(“https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=”&CELL&”)”)
Just as an example, let’s imagine that you have a spreadsheet of usernames and passwords for your students. You can create a QR code for their passwords and provide them with a printed copy of their username and QR code. And if they forget their password, they can simply scan their code!
Here is a short clip to show how this can be done:
5. Create a drop-down list in any cell.
- Select the cell where you’d like to have a drop-down list.
- Click on Data in the Sheets menu and select Data validation.
- From the list, choose List of items and type your items into the text box, separating them with commas.
- Click Save.
- The dropdown box will appear, and you can drag it to place it in more cells inside your spreadsheet.
See this in action:
Check out this blog article about drop-down menus in lesson plans if you’d like to go deeper.
6. Send a Google Sheets comment through email.
- Click the cell you’d like to add a comment to.
- Either click Insert in the Google Sheets menu and then Comment; or you can select the add comment icon in the toolbar.
- When you see the comment box pop up, type @ and select an email address for the person you’d like to recive the comment. If you do not see their name on the list, you can type their email address in manually after the @.
- Type your comment after their email address.
- Click the checkbox next to “Assign to…”, then click the green Assign box. The assinee will recieve an email with their comment.
7. Fill multiple cells quickly and easily.
If you drag one cell down, you can fill cells with the same contents, but there are a few more tricks for this. Watch this short video by @JakeMillerTech to learn these tips to “fill down” cells and give him a follow on Twitter for even more great tips. These are fantastically useful, and I learned something new watching this.
I hope that you find these Google Sheets tips useful. If you have any to add, shout them out in the comments.
