4 Ways to Find the Top or Bottom Values Using Google Sheets

In this example, we will be looking at four different methods for sorting a table of data in Google Sheets. All of the examples are from this Google Sheet. We will review simple sorting, filter creation, utilizing the SMALL and LARGE functions, and using the SORTN function. These techniques can also be found in this this video on the Prolific Oaktree YouTube channel.

Sorting Data

The first technique is simply to sort the data in a sheet utilizing the menu options, then deleting what information we don’t want. This is a very rudimentary and simple method, but it unfortunately ends with us losing data (that we might need later).

First, begin by selecting the data that you want to sort, then go to “Data” in the top menu, and navigate to “Sort Range.” We specifically want to sort row C.

Sort range options

Click the “Data has a header row” box, and click the dropdown menu to “Run Time.”

Has header row

Now that the data is sorted, you can manipulate it as you see fit, including deleting or moving the data you do not need. This technique, however, puts the data out of order and makes you lose the rest of the data.

sorted table

Creating Filters

The second method of manipulating data is to select the data again, go to the Data menu once again, and this time instead of choosing sort, we will create a filter. Both sorting and filtering are covered more in-depth in Prolific Oaktree’s Google Sheets Basics course.

create a filter menu option

Filters can sort, but have much more functionality. For this example, we will sort by Throw Distance. We’ll get the top three throwers. Click the dropdown at the right side of the header.

filter dropdown button

This dropdown puts the data in order for you. There are a variety of ways you can sort here.

filter dropdown button

Hit Clear and then choose the longest three by scrolling down to the end and put checkmarks on the last three values.

filter values

You can use functions in this menu for additional sorting capabilities. For this example, we will simply use the final three pieces of data. There are ties, so the sorter decides to show all of these entries as well. The final sorted list is still in its original order.

filtered

The advantage to using Filters is that the data still exists and is still able to be interacted with. This means you won’t have to worry about missing data.

The LARGE and SMALL functions

The following two methods are much more powerful ways to manipulate and view your data that draw from the data without directly affecting it. We have created another area of the sheet where we can manipulate the data separately from the original list itself. Here, we will go over the LARGE and SMALL functions within Google Sheets.

What the LARGE function does is return the largest number in a chosen dataset. In this picture, we can see “$D$3:$D$21” which tells the function to look at the data collected from D3 through D21.

range of LARGE function

From here, we tell the function how to rank the data it finds. We can simply type in “1” or any other number depending on our needs, but in this case, we will refer to “H3,” which is the cell that contains our ‘Rank 1’.

LARGE function

We did this as a cell reference to H3 so I could drag the LARGE function down to do 2, 3, and 4.

Using ‘$’ on the range function in the formula tells the function “don’t shift this range down when I drag this formula down.” It’s a fixed reference for a range.

Rank 2 has the same formula, but by dragging it down it is now looking for the second-largest value, and so on.

selecting the range

The SMALL function is simply the opposite of the Large function, and in the example here, it is picking the fastest run time and sorts to the slowest run time.

SMALL function

These functions don’t interact with any other row, but also don’t affect the dataset itself.

SORTN Function

Native to Google Sheets and not found in Excel, the SORTN function is a powerful function that you can utilize to maximally sort to your desired preferences.

SORTN

Start by highlighting all the data in your Sheet. This function will auto-populate all the fields, but you only have to type it once. Every variable is broken up with commas.

Highlighting range for SORTN

The function asks of these options, how many do you want me to return? We’ll put six. In the upper left corner of the screen, we can manually input 6 with another comma.

Data returned from SORTN

After 6, it asks what we would like to do with ties. For now, let’s choose 0.

SORTN tie value

The next variable is what column we are going to look at. This number is the number of columns from the left where the data resides. If we want to sort by throw distance, for example, it is the third column from the left, so we will enter 3.

Column value of SORTN

Then we will select FALSE for “how to sort”. Then we hit Enter.

SORTN how to sort values

We have now picked up the longest six throw distances and it picked up names and jersey numbers, while leaving all the original data. Additionally, any changes to the original data will get automatically populated in the new function’s list.

Result of SORTN

Every situation is different, but now you have four different options to choose from. Let’s hope you can find the one that’s best for you!

 Live examples in Sheets

Go to this spreadsheet for examples of methods to find the top or bottom values that you can study and use anywhere you would like.

Collect Data into Google Sheets | Forms, Quizzes, Surveys

By effectively utilizing Google Forms, you can sort, manipulate, and track a detailed database of information gathered from a number of sources. This tutorial will show you how to create a Google Form, how to send it to as many people as you would like, and how to manipulate the data afterward in Google Sheets. This tutorial is also in a video on YouTube.

Creating the Form

Begin by opening the main dashboard of Google Drive, and by clicking the “new” button and selecting “Form.” You can also right-click the dashboard, navigate the cursor to “More,” and create a new form.

create a new form

The form menu will appear, and you can begin filling in the questions you would like to be answered. Start by adding a title to your form, then fill in the questions below.

Give your form a title

For our example, we will be taking the names, emails, and other information from the recipient.

Question Types

The first type of question will be short answer – the first field, ‘First Name’, has a selection box to the right. This allows the report creator to decide how the recipient will answer the question. You may choose between a variety of options, but most reports utilize common question types like short answer or multiple choice.

Short answer question type

The ‘First Name’ field will be left as short answer, but we will make it a required field as well. Making a field required will place a red asterisk next to the question for the recipient, and they will not be able to submit the form without answering this question. This option is a toggle on the bottom right of the question box. We will do the same process with the next two fields, ‘Last Name’ and ‘email address.’

Name fields

To ensure the recipients enter valid dates, we will choose the “Date” option in the question type selection box. This ensures that the data we receive from the form recipients will be uniform and in a valid format. The specific date format (whether month/day/year, day/month/year, or other variation) can be modified as well.

date field question type

For questions with a specific answer out of a list, we will use the multiple-choice option.  We will create the “What time should we contact you?” field this way, allowing three options for recipients to answer. The number of choices can be added with the “Add Option” button, or subtracted with the “X” button on the right side.

Multiple choice question type

Additionally, multiple-choice questions can have multiple answers. The “Do you own a car?” field will use a check-mark multiple choice option, which allows users to select as many options as are applicable.

Check mark

Sending the Form

With a paid G Suite account, you have the option of clicking a box at the top of the “Send Form” window to automatically collect email addresses within your organization. Otherwise, there are multiple ways to send the form to recipients.

If you contact me here or email me at [email protected], you will receive a code for 20% off of your first year of G-Suite. If you sign up for G Suite using this link and apply the code during checkout, the discount will be applied.

Option to collect email addresses

You can email a notice to your list about the form. This notifies the users about the form itself, and you can also choose to include the form itself inside the email so that recipients can immediately fill it out upon receiving the notice.

Include the form in the email

Google Forms can also produce a link that you can send via a variety of forms. This gives your users a clickable option that doesn’t use their email (for sending through text messages, for instance).

Send form as link

Lastly, you can embed the form as HTML directly into your website. Using this option keeps the form directly onto your webpage so your users don’t have to leave.

embed the form in html

Analyzing the Data

To analyze the responses received, you can go back to the main page of the form and click the “Reponses” tab next to the “Questions” tab. This allows you to graph the data received from the form.

See responses to form

On the upper-right side of this menu, you can see the database itself. This holds all the raw data you have received.

Option to review responses in Sheets

Now that you have the data in Google Sheets, you can manually sort and analyze everything at a glance! Learn more about how to sort and analyze data in online training courses that Prolific Oaktree offers with videos, linked Google Sheets, notes, and quizes.

View entries in Sheets

Rows not hidden yet

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.

Rows not hidden yet

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.

First row selected

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.

Right-click 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.

Minus sign

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.

Arrow pointing to minus sign

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

Collapsed rows

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

Step-by-Step Video

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.

Expanded rows

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.

Right-click group rows

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.

Second level minus

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

Parent and child rows collapsed

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.

Columns selected

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

Right-click group columns

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.

Minus columns

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.

Move plus/minus to bottom

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!

Follow image below for the live Google Sheet with this data

docs share icon

Headers

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
Headers
Headers

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.

Formula for ending inventory 
Ending Inventory

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.

Formula for ending value 
Ending Value

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

To share this Sheet with everyone in your organization, consider upgrading your Google environment to G-Suite. This would also allow you to have  more space in Google Drive and to use a email address with Gmail. Send me a message for your unique 20% discount code for your first year.

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.

Formula for total
Total Inventory Value

Formatting your Sheet

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.

Number format
Number Format

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.

Bottom Borderl
Bottom Border

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.

Formula for actual value
Actual Value

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.

Adjust your GL

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.

That wraps it up for creating and using your new simple inventory management template. Hopefully you find this helpful for your business!

Template

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

Table of data before being used in a Pivot Table

Google Sheets Pivot Tables – Basic Tutorial

This tutorial will walk you through the steps of creating your first Pivot Table. The linked file used in these steps can be found in this Pivot Table Course with the source data and the Pivot Table shown in the pictures. The course contains an additional twelve videos with linked files, notes, and quizes all about Pivot Tables.

Start with Good Data

Table of data before being used in a Pivot Table
Table of data before being used in Pivot Table

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

Create Your Pivot Table

Go to Data then Pivot table
Go to Data then Pivot table

Google Sheets – Learn the Basics

Basics Course Thumbnail

Learn all of the foundational skills necessary to create and use spreadsheets. 10 downloadable videos, LINKED GOOGLE SHEETS for you to copy and use, quizzes, and built-in notes. You'll have lifetime access to this course.

Learn More

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.

Suggested Pivot Table Configurations
Suggested Pivot Table Configurations

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

Add Sales Rep as a Row
Add Sales Rep as a Row
Add Ship Mode as a Column
Add Ship Mode as a Column

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.

Leave on the Show totals option
Leave on the Show totals option

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.

Add the COUNT of Sales
Add the COUNT of Sales
Basic Pivot Table
Basic Pivot Table

Add the Dates

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.

Add Date as Another Row
Add Date as Another Row
Pivot Table with Too Much Detail
Pivot Table with Too Much Detail

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. More detailed information on how to summarize dates in a pivot table can be found here.

Pivot Table Grouping by Year
Pivot Table Grouping by 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.

Pivot Table Grouping by Year
Pivot Table Grouping by Year

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. The drop down says it’s showing all items. Select clear, type in 2018, and choose select all.

Pivot Table Date Filter
Pivot Table Date Filter
Pivot Table Just 2018
Pivot Table Just 2018

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.

More training is available on how to prepare data for a Pivot Table as part of a Pivot Table Course designed with beginners in mind.

Finished Pivot Table
Finished Pivot Table