Category: Cloud Software

Software that operates, at least partially, online

Raw data before being used in a pivot table

Google Sheets – Pivot Tables | Summarize by Year, Month or Quarter

Posted on | by Prolific Oaktree | 4 Comments on Google Sheets – Pivot Tables | Summarize by Year, Month or Quarter

Start with a Pivot Table

If you have a table of data in Google Sheets and you want to look at it by a certain type of date, let’s say if you want to look at it by day of the week, the first thing that you want to do is create a Pivot Table. Using a Pivot Table will help you avoid constructing complex formulas with SUMIFS.

We are starting with a table, (shown in the image below) for our raw data and we will be creating the Pivot Table on a new sheet called Pivot Table 1 which is shown at the end of this article in the embedded view of the Sheet.

This tutorial assumes that you have already completed your Pivot Table and you have a basic knowledge of how to use it. See this video if you need some basic help on Pivot Tables.

If you need a primer on Pivot Tables, this video will walk you through them.

Pivot Table Articles
Raw data before being used in a pivot table
Raw data used in pivot table

A well-formed table of data

As can be seen in the raw data above, it is important that you start with good data that has descriptive headers for each column and no empty rows. If you have empty rows, be sure to delete them before attempting to create a Pivot Table.

Once you are ready to create your Pivot Table, go to the menus, select Data then Pivot Table. The Blank Pivot table will be created on a new tab in your spreadsheet as shown in the picture below.


College Compare Add-On Icon College Compare
Analyze college data


Insert Special Characters Add-On Icon Insert Special Characters
Symbols, shapes, and more


StaySorted Logo StaySorted
Automatically sort new entries


TIMEDIF Logo TIMEDIF
Formatted duration between datetimes


TripTally Logo TripTally
Calculate distances

New, blank pivot table
Pivot table before customizing

Add the Dates

To get the dates to be the first column on the left, we want dates to be the rows so add Ship Date to the Rows field. As mentioned above, working with pivot tables is a lot easier if you have descriptive headers in your source data. Each one of these headers is telling us exactly what’s in the column.

Now I have my dates on the left-hand side and then I want show the items in each column. Let’s add items to the Columns field. To fill the table with the number of items shipped, tell it to use the COUNTA function for the Ship Date which will count each instance of a Ship Date as one.

Add the ship date field
Add the ship date field to the rows

Add the ship date to the value field
Add the ship date to the value field

Group by Day of the Week

Come over to the column that has the dates in it. Select any one of the days. Right click and do create pivot date group. It doesn’t matter what day you click on in the source table. Google Sheets will give you the option to sort by date or time as long as you left-click on a valid date or time inside the pivot table.

Group the days by day of week
Group the days by day of week

You can do this by week, month, day of the week or even units of time smaller than a day such as hour or minute. Since we are doing Day of the Week, it summarizes all of the data from Sheet1 into Monday, Tuesday, etc. If you have things in this column that aren’t working right, go back to your data and make sure that the dates are valid. There are several different ways to get valid dates and to check and make sure that they’re working. So that’s mostly it. We’ve summarized this data by month with just a few clicks! Now that you have the grouped dates, you create interesting tables like summing by day of the week.

 Live examples in Sheets

Go to this spreadsheet for examples of a grouped pivot table that you can study and use anywhere you would like.

Insert checkbox menu option

Google Sheets – Create an Interactive Task List

Posted on | by Prolific Oaktree | 9 Comments on 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 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.

Types of drop downs

List of five tasks
List of five tasks

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.

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Conditional formatting rules
Conditional formatting rules

Watch the Video

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.

 Live examples in Sheets

Go tothis spreadsheet for examples of adding and subtracting days, months, or years that you can study and use anywhere you would like.
Cells with data to be combined

Combine Cell Contents from Two Different Columns in Google Sheets

Posted on | by Prolific Oaktree

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

Google Maps What's Here

How to Find the Plus Code for Any Location

Posted on | by Prolific Oaktree

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!

Two columns with matches highlighted

Google Sheets – Compare two lists for matches or differences

Posted on | by Prolific Oaktree | 14 Comments on Google Sheets – Compare two lists for matches or differences

If you’re using Google Sheets and you have two separate columns of data in which you’re looking for matches, there’s a pretty easy way to highlight the matched items. You can also do it the other way around and highlight items that don’t match. You’re typically doing this because your lists are long and you can’t do it visually. However, for this example the lists are purposely small so it’s easy to see.

Two columns with matches highlighted
Matches between two columns are highlighted

Conditional Formatting

We’re going to do it through Conditional Formatting. Go to your menu, then to Format, and select Conditional formatting. The Conditional formatting menu options will pop up on the right-hand side and there’s everything you need right here.

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Conditional formatting menu option
Menu option for conditional formatting
Rules for conditional formatting
Rules for conditional formatting

First, apply the conditional formatting to the range which, in this shared spreadsheet (which is also embedded near the end of this page), is C1 to C10. Note that the range is different in the video but the process is the same. You can select the range with your mouse or type it in as C1:C10.

At first, the range should be highlighted because it is set to highlight if it’s not empty. Change this to a custom formula, which is at the bottom. Click on Custom formula is and then it gives you a box that’s waiting for the formula.

Custom formula
Custom formula

You are going to be typing in a formula just like you’re typing into a spreadsheet cell. One of the differences though is now Google Sheets is not going to help you. Once you start typing the function, no helper text will pop up to explain the function. If you want help, type the formula into a cell in the spreadsheet and it’ll help you build it. Then, you can paste it into the custom formula box.

Video explanation

MATCH formula

You want to use a function called MATCH. MATCH is going to look at each cell in the range and check to see if it exists in the other range that you specify. The first parameter that you want, it’s going to do this one cell at a time, is C1. Now type a comma and Sheets will be looking for the range that you want to try to look up C1 in to see if it’s a duplicate. You don’t want this range to shift down every time it evaluates a cell on the right., so use dollar signs before the row numbers to fix the range as such A$1:A$7.

Next, your spreadsheet wants to know if this is going to be an exact match. Enter a 0 (zero) for yes so if it finds exactly Yellow. Now close it off with the parentheses. Your code should look like this:

=MATCH(C1,A$1:A$7,0)

The box that contains this function should have changed from red to blue meaning that the function is now a valid function. It should be working after the box turns blue. You can see it highlighted the color red in green because red is on the list on the left but it didn’t highlight orange because orange isn’t in that list. If you click done, you’ve highlighted everything that exists in the other list.

=ISNA(MATCH(C1,A$1:A$7,0))

If you want to do this the opposite way, and highlight the items in this list that aren’t in the list on the left, wrap your formula in the function called ISNA. The ISNA function is saying – look, if the MATCH function doesn’t work, highlight it. Not if it does work. After the ISNA, put a parenthesis and then go to the very end and close it with the parenthesis.

View of Sheet with ISNA applied

 Live examples in Sheets

Go to this spreadsheet for examples of comparing two lists that you can study and use anywhere you would like.

Using a plugin

As an alternative to the options above, you may want a plugin to do the heavy lifting for you. I like to use a plugin called Power Tools. This will give you a menu option with, among other things, several options to deal with duplicates. You can use the plugin to combine or remove duplicated rows.

Dedupe and Compare Menu
Dedupe and Compare Menu

Conclusion

After following this tutorial, you should be able to highlight matches or differences between two columns. If you have any questions, feel free to leave a comment below.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.