# Google Sheets – Group Rows and Columns with Linked Example File

When working with spreadsheets of data in Google Sheets, you’re often switching between different levels of information, and you might find yourself wishing you could control the visibility of data you don’t need so that you can make the presentation clearer. There is a way to do exactly this, by grouping rows and columns so you can easily collapse and hide them or expand them into view.

### Grouping Rows

In the example below and in this linked example Sheet, we have individual sales data for a range of products, with quarterly subtotals, and an annual total. To see the quarterly sales information more clearly, we would like to hide the data for the individual products.

To do this, we’ll create groups for the data we don’t need to see. Begin by selecting some rows that you would like to hide.

With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 9’ (or whatever row numbers you have selected) from the menu.

This will create a group for the selected rows, which you will see depicted by a thin bracket on the left of the selected rows, along with a small button with a minus sign inside it. This button lets you collapse or expand the group.

If you click the minus button, the group of rows will be collapsed and hidden from view. The button remains visible (this time with a plus sign) so that you can click on it again to expand the rows.

Repeat these steps for each of the other quarterly product data so that you end up with only the quarterly sales figures showing.

Now we’re showing only quarterly sales figures, and the data is much easier to read!

### Layered Groups

Let’s say we want to be able to show only annual sales figures as well. We can create another group, alongside the groups we just created, to toggle on and off everything except the annual sales data.

Select all of the rows corresponding to individual product data and quarterly sales figures. Do not include the row with the annual sales figures at the end, or the header row at the top.

With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 31’ (or whatever row numbers you have selected) from the menu.

This will create a new group that allows us to collapse all the quarterly sales data and only show annual sales data, alongside the groups that we created before.

Now if you click the button for the new group, only the annual sales figures will be showing.

### Grouping Columns

It is also possible to create groups for columns, using the same steps as we used for rows. Select the columns that you would like to hide.

Right click anywhere inside the selection and click ‘Group Columns C – D’ (or whatever columns you have selected) from the menu.

This creates a group for the columns, depicted above them by a thin bracket and a toggle button, which we can click to collapse or expand the columns as we did with the rows.

### Group Options

Right clicking on the group bracket brings up a menu that gives us some options about how to display the group. For example, if you would like the toggle button to appear at the bottom of the group, right click on the group bracket on the left of the rows, and click ‘Move +/- button to the bottom’ from the menu.

You can see there are several other useful options on the menu that give you control over how your data is being displayed.

### That’s it!

Grouping rows and columns in Google Sheets gives you control over what is displaying in your spreadsheet, giving you the ability to highlight different levels of information at different times without the view being cluttered with data you don’t need to see. Hope this tutorial has been useful for you and your business!

# Google Sheets Inventory Tracking Template | Step-by-Step Tutorial

This article will walk you through how our inventory tracking template is created.

### Prepare the Sheet

In order to have all of the right column headers, start the spreadsheet by adding the following labels in the first row:

• Item
• Beginning balances
• Purchases
• Sales
• Ending balance
• Purchase Price
• Ending Value

### Enter the Items and Amounts

Enter your item descriptions in the first column under the Item header. When you use this spreadsheet for the first month, you need to hard-code the beginning balances. In subsequent months, you will be able to link the beginning balances to the prior period’s ending balances. We will review how to do that later but, for now, just type your amounts in.

We’re using an ice cream shop as an example so the example is using vanilla as one of the flavors. During the first month, you purchased four units and you sold two.

For the ending balance, you will use a formula. If your table is set up with the same rows and columns as the example, your formula for the ending balance should be =C4+D4-E4.

Consider using this template. This is the end result of what are discussing below and what is shown in the video below as well.

### Purchase Price and Ending Value

For purchase price, use the last price that you paid for a gallon of this ice cream flavor. That way, the value will reflect the market value closely if it’s the most recent market price. For the ending value, we’re going to take the ending balance, which is column F, multiply it by the latest purchase price, which is G4, =F4*G4 and that’s the ending value of your vanilla inventory.

You need to remember to update the purchase price every time you buy it or it’s not going to reflect the current value.

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.

### Total Inventory Value

Next, we’re going to total the value of our inventory. Go to the bottom of the ending value column and type =SUM for your formula. Open up the parentheses and choose the range of all the ending values. I went back in and I filled out some activity for two more flavors of ice cream.

The ending value of this inventory is the total shown in the ending value column. To reflect the proper value of your inventory in your financial records, you need to adjust it to this number if that’s not what the balance is now.

Let’s do a little bit of formatting so it’s easier to read. In order to get all of the numbers in the value column to have two decimal points, change the formatting to Number by going to the Format menu, choosing Number, and then selecting Number again. Now all of the decimal points are lined up which makes it visually easier to read.

Let’s do a bottom border to show that this is the sum at the end of the table. Using a thick line at the end of a column of numbers helps a reader see that it is the end of the series.

### Book to Actual Comparison

After all of your careful tracking, some of your inventory is going to mysteriously shrink, right? Or, you’re going to purchase something and record it incorrectly. In other words, this ending value over the months is going to become inaccurate no matter how hard you try to keep it right.

What you can do is a monthly or a quarterly physical inventory. Let’s add a physical count column. Let’s recheck the purchase price to make sure there are no errors there either. Then, add in actual value using a formula, in this case, of =I6*J6.

You don’t need to do physical counts throughout the month. You don’t really need to do one every month. But, if you want to double check yourself, this is a great way to do it. If any of these amounts or prices are different, the ending value of the count won’t match the ending book value in the spreadsheet to the left.

After you do your physical count and you check your prices, this is the dollar amount that you should have recorded in your general ledger as your inventory. For the months that you do a physical count, you should adjust your inventory to actual and then book the difference to your cost of goods sold.

Note that the first worksheet in the template is linked to a second that you can use for a subsequent month. This process can be repeated for as many months as you would like. Link the beginning values of the subsequent month to the ending values of the preceding month. You do this by typing =, left-clicking on the cell that you want in the other worksheet, and hitting enter.

### Template

Go to the Template here. Choose File -> Make a Copy to copy it into your drive.

# Google Sheets Pivot Tables – Basic Tutorial

This tutorial will walk you through the steps of creating your first Pivot Table. I already made one on this linked Google Sheet with the source data on the “Raw Data” sheet and the Pivot Table on the, you guessed it, “Pivot Table” sheet. The Pivot Table in the linked file is what you will have at the end of this tutorial.

The table of data in the image above is a good example of what makes good data for a Pivot Table. It has headers and the names of the headers describe the data that’s underneath it. The Sales Rep header is on a column that contains the names of the sales reps (duh). More importantly there are no breaks in this data meaning that there are no blank lines. Also, the table is so large that you can’t just look at it and get the information. If the table of data was small, there could be no need for a Pivot Table since you could see all of your answers by eyeballing the data yourself.

### Video Explanation

Make sure that you have selected a cell within the source table of data then go to the menu, select Data, and Pivot table as shown in the image above. That gives you a blank slate that you’re going to work with. Sheets will “suggest” different Pivot Table configurations using artificial intelligence to as shown in the image below. Unless you have very simple data, and you just want to have it summarized by one dimension, these are not going to guess what you want because there are so many different combinations. But, if you do just try one of these and click on it, it’s going to build a table for you which can be helpful if you have simple needs.

### Add Data to the Pivot Table

Now we will start building out our Pivot Table. If we want to analyze the data by Sales Rep first, and get the Sales Reps names going down the left-hand column, this is where it gets little bit confusing and it may be more clear to watch in the video. You want the Sales Reps names in the leftmost column, but you want the name of each salesperson to be in a row. You need to add the Sales Rep for Rows even though these rows will be filling the first column. Sheets will fill them into the Pivot table in alphabetical order.

For the columns, you want the Ship Mode, and again, this is a little bit confusing, right? This is a row. It’s going to be your row of headers, but each column is going to have the data in it so it’s called Columns. Let’s add the Ship Mode.

You’ll notice, each time you add a field, it’s asking if you want to show the totals or not. Let’s leave both of those checked and there will be a Grand Total for the Sales Rep and a Grand Total for the Ship Mode.

The Values field is going to be what it shows you in the middle of your pivot table. For this table, we will be looking at the number of sales, not the dollar amount. Go to values and add our Sales column. It’s the field with the dollar amounts in it. Sheets defaults to summing dollar amounts. We want to count each one of them as one so we are going change the function from SUM to COUNT.

Now that we have Sales Rep and we have a count by each Ship Mode, the last thing we want to do is look at the data by year. Let’s have the dates on the left-hand side to the right of Sales Reps. Remember this is called rows, even though it’s a column. Let’s add another column of rows, and we are going to make it the date.

### Group the Dates

This isn’t what you want, right? We want it by year but, in order to do that, you had to add the dates. What you can do is right click on any of the dates, and create a pivot data group, and select Year.

This is going to summarize the data by year because Google Sheets recognizes the data as valid dates. It can extract the Year and summarize by just that.

### Filter by Date

Let’s say you’re just looking for 2018. Let’s go back to the right, scroll down to the filters. We’re going to add a filter for the date. This will be a little bit tricky how we’re gonna do this. Let’s do a drop down it said showing all items. We’re going to select clear and then we’re going to type in 2018. We’re going to hit select all.

What we’re doing here is, we’re saying unselect everything, show no dates. Then, if you type in 2018, it’ll show only the 2018 dates that are in the original table. If you click select all, it’s going to select only the 2018 dates. Click OK. You have this filtered by 2018. Click OK, and there you are.

### Completed Pivot Table

A really easy to understand pivot table with just the data that you need and, if you want to change anything, this is always live. I could just go back here and customize it however you want. So that’s all. Taking a good solid list of data that has columns with consistent data types in it, no blanks, and we’ve created this pivot table that gave you the exact information that you wanted to see.

# Google Sheets – Group Data Inside a Pivot Table

### Pivot Table Groups

If you’re using a Pivot Table in Google Sheets, and you want to create groups within that pivot table, you can do it with just a few clicks.

As can be seen in the linked example Sheet, we have a table of sales transactions and we are going to group them by region.

This tutorial assumes that you you have already completed your Pivot Table and you have a basic knowledge of how to use them. 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.

### Raw Data

When you look at the table below, we can see we have different regions. We have West, East, North, and, um, just one mile left of North.

Let’s make the rows of our Pivot Table the value in the Region column from the table of raw data. Select any cell in the table of data and go to Data and Pivot table. This will start to fill your Pivot Table. Click ADD for the rows and select “Region”.

To fill in the center of the Pivot Table with data, select ADD for the Values and choose SUM which is the default. This will show the sum of the sales by Region.

### Grouping the Data

Now let’s group together the compass directions and then group the One Mile Left of North in another group because he’s a little bit different so we want to analyze him differently. What you want to do highlight the three that you want to group separately, right click, and create a Pivot group as shown in the image below.

Now the Pivot Table has put the three compass direction territories together and then the one oddball on its own. Now you can expand and collapse these groups in the same way that you can in a spreadsheet without a Pivot Table. That’s the way to group data in a Google Sheets pivot table. That’s basically all.

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

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.

As can be seen in the linked example Sheet, we are starting with a table, (also 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 know how to get this far with a Pivot Table. 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.

### 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.

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.

### 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.

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!