Google Sheets – Create an Interactive Task List

This tutorial will show you how to create an interactive to-do list in Google Sheets including automatic strikethroughs when you mark tasks complete with a checkmark.

Insert Checkboxes

Insert checkbox menu option
Insert-> Checkbox

As shown in the image above, the core functionality of this list will be driven by checkboxes. You can enter get them into your spreadsheet by going to the Insert menu and choosing Checkbox. Insert one and then copy and paste it down until you have as many as you want. Add your tasks in the column to the right of the checkboxes.

Conditional Formatting

Now, if you’re like me, when you’re done with the task, you’d love to be able to check it off and get a little strikethrough, right? You can feel like you’re accomplishing something. The strike through will come from using the conditional formatting feature.

Conditional formatting
Conditional formatting menu option

After selecting Conditional formatting, a Conditional formatting rules box will appear on the right. Look closely at the picture below. For the range, we have specified C5:C which will select everything in column C from row 5 and below, assuming that is where you have placed your list of tasks. Once you move out of this input field, you should see that everything in column C starting a row 5 and down to the end of where you have things typed is highlighted.

Conditional formatting rules
Conditional formatting rules
Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you'll like that.

Custom formula

Right now, it’s just applying formatting as Cell is not empty because that’s the default choice. Change that by going to the drop down menu below Format cells if… and choosing Custom formula is. Now this box is waiting for a custom formula. Left-click into it to put the cursor in it. Whenever you’re typing a formula, even if it’s in here, you start it with an equals sign. Type the formula =B5=TRUE. Make sure you don’t use the period at the end. When a checkbox is checked, it changes the value of the cell from FALSE to TRUE. This formula will check for the TRUE state.

If the value is true, we will apply Custom Formatting style. Choose a style to make strike it through and make the background gray so it looks like it’s going away.

If you want to add something else at the bottom, you won’t have to redo this rule because that formatting contains the entire column after C5.

Completed Task List

Finished task list

Pretty easy to put together. Really satisfying to use. Have some fun with it, and let us know how it turns out.

Follow image below for the live Google Sheet with this data

docs share icon

Combine Cell Contents from Two Different Columns in Google Sheets

Separate Cells Needing to Be Joined

If you’re using Google Sheets and you have two columns of data that you want to join together, there are a few easy steps that we can walk through to get this done. In the following example, we have people’s first names and last names. This is a fairly common scenario. We are going to join them together and put a comma in between them.

Cells with data to be combined
First name and last name, not yet combined

The Formula

Place your cursor to the right of the two cells that you want to combine. In this example, it would be cell C2. As always, when you are entering a formula in Google Sheets, start out with an equal sign, and then indicate what you want to have joined together using this formula – =A2&", "&B2.

After entering this formula, you should see the first combined name.

Video Explanation

To copy this all the way down, place your cursor in the lower right hand corner of the rectangle. There’ll be a small solid blue square and your cursor will turn into a blue plus sign. Double-click the blue box and it copies the formula all the way down.

The Result

Cells with data to be combined
First name and last name, and combined

Now, all of your names should be combined into one column, viola!

Live Google Sheet with This Data

docs share icon

How to Find the Plus Code for Any Location

If you want to find a Plus Code for a certain location, it is not that easy. At this point, you can’t do it on Google Maps. When you left-click on an area and choose What’s here?, it will give you latitude and longitude but Maps does not give you the Plus Code.

Google Maps What's Here
Place info on Google Maps
Latitude and Longitute
Latitude and longitude

However, what you can do is, you can go to plus.codes and use the Find your code option.

Plus Codes Website
plus.codes

Zoom in or out until the area that you want fills the screen. A Plus Code designates an area in the shape of a rectangle. It’s not a point like latitude and longitude. You can take advantage of that by zooming in and out until you get to the size of an area that you want. After you have the right area, go to the menu and choose the option to see the Grid.

Menu on plus codes site
Map menu
Grip option on menu
Option to show grid

Video explanation

After choosing the option to show the grid, you will see the rectangles that are available to you. In this case, the area labeled 86FVCF fits Hocking Hills State Park which is the area that I am looking for. 86FVCF is the first part of the full Plus Code that you will see later in this article.

Seeing 86FV in grid
Grid shown over map

There is also a shortened code available at the bottom of the screen. CFG7+JP, works for anyone that lives close to the park. It’s a shortened, partial version of the code. CFG7+JP does not contain the beginning 86FV portion of the 86FVCF code, so it works when you are already relatively close to the area.

Plus code
Shortened Plus Code

On the left side of the plus.codes screen, you will see the full Plus Code and the shortened Plus Code that can be used for local navigation. If you want the entire Plus Code, which is probably the best practice if you are copy/pasting, go to the left of the screen and copy it. This is your full Plus Code. It’s not case-sensitive, but since we’re copy and pasting it in, it doesn’t matter.

Codes
Left side of plus.codes showing full and shortened Plus Codes

Go back to your mapping program, which is Google Maps in this case, and enter this Open Location Code into the search bar. Google Maps will go to the area that corresponds to the Plus Code and drop a pin in the middle of it. Maps shows you the latitude and longitude of the center of the Plus Code but it will not show the grid with the codes on it.

Plus code in Google Maps
Google Maps searching for Plus Code
Hocking Hills on Map
After searching for Plus Code

Now you know how to find your Plus Code using plus.codes with the grid feature turned on and you can take that Plus Code and enter it into Google Maps, you know how to find and use a Plus Code. I hope that was helpful!

Create an Employee Timesheet Using Google Sheets Includes Template

If you want to track your employees’ time, there is an easy way to do it using Google Sheets that does not involve complicated formulas or macros. This technique will also have the ability to calculate an overnight shift. The linked file can be copied into your own Drive and used as a template.

Data validation

One of the most difficult things when working with dates and time in Sheets is getting them in the cells correctly. A protection against that, data validation, can be used to reject invalid inputs.

Click on the Data menu option and then Data Validation.

Data validation menu option
Menu option for Data validation

Once the Data validation window pops up, complete it with the parameters in the following pictures including choosing Date from the criteria drop-down box and rejecting invalid input with a message.

Is valid date 
Choose date and is valid date

This will set data validation to show the message “Enter a valid date or time” if an invalid date is entered. Times are covered under the date formatting as card card-body bg-light so this data validation is also going to work if it’s a time. Now, if a user enters a date or time that Sheets doesn’t recognize as such, they will get an error message letting them know. There are several different ways to enter a date using a valid format, but there’s infinitely more ways to screw it up. You could do 2-20-18. You could type out February. But, as long as it’s valid, this timesheet will accept it.

The same thing is going to happen with time. If it’s 5:43 and the person types 5 43 in hits enter, it’s not going to work. You have to have a number, a colon, and a number. If you want seconds, do another colon and two digits after that such as 5:43:22.

The formula

Once the validation of the dates and times is setup correctly, the spreadsheet is ready for a start day and time and an end day and time. This formula is going to calculate the hours. So this formula is going to look a little complicated at first, but it’s really not too bad.

Copy and paste this code for column D

=((DATEVALUE(B5)+TIMEVALUE(C5)-(DATEVALUE(B4)+TIMEVALUE(C4)))*24)

Is valid date 
Timesheet showing formula

As seen in the shared sheet, this formula takes the row it is on and subtracts it from the row above it. To do arithmetic with these dates and times, Sheets has to convert them into a value first. That is what the DATEVALUE and TIMEVALUE functions are doing. The result will be a fraction of a day so multiply that by 24 and that gives you the number of hours worked. Every other row, you want to put in this formula.

If this person takes breaks, just repeat the same formula. If you want to make this longer, just copy and paste it down as many times as you want.

Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you'll like that.

Conclusion

After following this tutorial, you should be able to create a time sheet to track the hours of your empoyees. If you have any questions, feel free to leave a comment below.

Follow image below for the live Google Sheet with this data

docs share icon

Embed a Google Doc into a Microsoft Sway Presentation

If you’re using Microsoft’s Sway and you want to embed a Google Doc, there’s just a couple easy steps to do it.

Sway with Google Doc
The title of your Sway

Create a new Sway and give it a title. I’ve called this one Embedding a Google Doc.

Media group
Choose the media group

Next, click the plus sign to add more content. Choose the Media group then click the Embed option.

Embed
Choose the embed option

Video explanation

https://www.youtube.com/watch?v=dQT1h2Ph0CY”

Publish to the web
Publish your Doc to the web
Publish button
Click the publish button

Sway is now waiting for the embed code. You need to get the code from your Google Doc by going to the document’s File menu and choosing Publish to the web. Once in the Publish to the web page, you need to click the blue Publish button one time to open up the Doc. After you have clicked Publish, you can highlight the embed code and copy it to your clipboard.

Embed code for Docs
Embed option in Google Docs

Go back to your Microsoft Sway. Go to the box waiting for your input, and paste the code.

Embed card with code
Sway card with embed code

You’ve embedded that Google Doc. This is live. If you were to update this in Docs, this document that’s showing here would be updated. You can also interact with this document to a certain extent with the icon lower right hand corner. You can download it or print it.

Finished sway with Google Doc
Sway with embedded document