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.

Slide with table embedded into it

Embed a Live Google Spreadsheet into Google Slides

If you are using Google Slides to create a presentation with data from a Google Sheet, you may want to show that data as a linked table. If you create the table in Google Slides with no linking, it will not update if the data in the Google Sheet changes. Also, this is double work as the data is already in the Sheet, so re-typing it is a waste of time. Below are instructions on how to embed a live Google Sheet directly into your Google Slide. The table that is created will update with one-click and can be styled however you like. Live links to the Slide and Sheet shown in this tutorial are at the end of this article.

Delete the existing text box

Slides wants a blank area for the table. If you have a text box in your slide, delete it to make a nice, big open space.

Text box to be deleted
Text box to be deleted
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.

Create the Sheet

You need a spreadsheet created in Google Sheets with the table of data that you want to display in your Google Slide. In your spreadsheet, highlight the range that you want, right-click, and select Copy.

Table to Copy
Table to be copied

Paste it into your Slide

Then, go to the location in your Google Slides where you would like the table to be inserted. Right-click with your mouse and choose Paste. After clikcing, a window will pop-up asking you if you want to Link to spreadsheet or Paste unlinked. Choose Link to spreadsheet and click Paste.

Table to Link
Choose Link to spreadsheet

Oh yes, that’s a live, linked table that you’re seeing.

Slide with table embedded into it
Google Slide with embedded Sheet

Working with your embedded table

Updating the embedded table

After linking the table, if you want to change the data within the table, you can go back to the Google Sheet to make the changes. When you come back to the Google Slide after making the changes, there will be a new option available to update the table when you right click on the table as shown in the picture below.

No update option showing
Update option showing

Adding rows

If you add rows to your table in Sheets, you may notice that the added rows don’t show up in the linked table in Slides. You will need to go back to the table in Slides after making the change, left click the more button (three vertical dots), and choose Change range.

Change range option
Option to change the range

Conclusion

Following the steps above should provide you with an easy way to insert a live, linked spreadsheet into your Google Slides. Enjoy!

Live Google Slide with an embedded Sheet

Live Google Sheet that is being embedded

Customization Menu

Scatter Charts in Google Sheets – Step-by-step with examples

What is a scatter chart?

A scatter chart (AKA scatter plot or XY graph) uses points along a two dimensional graph to show the relationship between two sets of data. Its simplicity can make it quite effective at cutting through the noise of large amounts of data in a Google spreadsheet. Once you can see the relationship, it can be used to predict future outcomes within certain confidence levels.

When to use a scatter chart

Before you jump into using a scatter chart, be sure that it is the right type of chart for you. This chart type decision tool can help you decide.

As an example, if you want to see if more people will use the water park as it gets warmer, a scatter chart could be a good tool. If you use any more than two variables, the chart can start to appear confusing and thus reduce its usefulness.  

These charts can be customized in many ways. The first example below has had its points reduced to 2 px, a linear trendline added, and the correlation shown in the upper right hand corner of the chart. These options can be found on the customize menu when inserting a chart.

Customization Menu
Customization menu
Example scatter chart
Example scatter chart

Video Explanation

Correlation

Mathematically speaking, creating a scatter chart lets you visualize the correlation between data points, if there is any. In the example above, the correlation is .928, with the highest value of any correlation being 1. The chart was created in Google Sheets using random values for the Y axis and (Y + random values) for the X axis.  The random value range was kept small so that it would create a tight correlation. Most users for which a spreadsheet chart is made will probably not remember what a correlation value means. A trendline is the layman’s tool for seeing the correlation.

Trendline

You can draw a line through the data along the path which is the best fit based on the points already on the chart. You can see how close the correlation is to 1 or negative 1 by simply looking at the distance that the dots are away from the line.  This line can be used to extrapolate correlations outside of the current data. If you are trying to find data points inside the current set, it is called interpolation. This line can be straight (linear), or it can be a curve (exponential or polynomial).

Choices of trendlines
You can choose from several types of trendlines

Choices of trendlines
You can choose from several types of trendlines

Linear

Below is a simple scatter chart with a correlation value of 1. This means that each of the two variables move in lock step with eachother in the same direction. If the left shoes size increases by one, so does the right. The line of best fit with a perfect correlation is linear and extends 45 degrees from left to right. Note that it will only be 45 degrees on the chart if the scale of the x and y axis are the same scale.

Scatter chart with correlation of 1
Scatter chart with correlation value of 1

Some types of data could have a negative correlation. For example, the relationship between BTU’s needed to heat a room and the outside temperature would create a graph that slants down as it moves along the x axis.

Scatter chart with correlation of -1
Scatter chart with correlation value of -1

Of course, not all data are so highly correlated. If it were, there would be no “scatter” in a scatter chart. This chart shows data with a correlation between -1 and 1. Note that the slope calculation and correlation value were included in the chart by choosing “Use Equation” as the label and placing a checkmark next to R2 to the correlation in the customization are of the chart menu.

Trendline Labels
Setting labels for trendlines

Scatter chart with some correlation
Scatter chart with correlation value shown on chart

Exponential

Exponential trendlines are a good fit for data that increase or decreases at a constantly increasingn rate.

Scatter chart with an exponential trendline
Scatter chart with an exponential trendline

Polynomial

A polynomal trendline should be used when data flucuates between values. Note that the polynomial trenline is more tightly correlated with this data. The data is the same as the data used for the exponential trendline.

Scatter chart with a polynomial trendline
Scatter chart with a polynomial trendline

Error Bars

To illstrate that your data in not precise and may be within a certain range, error bars can be used. These bars are found in the customization menu and be set for different ranges in percentage or absolute terms. The chart below shows error bars added to a simple scatter chart to show uncertainty in the data. They were created by using a constant value of 2.25. These can be used with our without a trendline depending on the point that you are trying to make to the viewer of your chart.

Scatter chart with error bars
Scatter chart with error bars

What is a scatter chart?

A scatter chart (AKA scatter plot or XY graph) uses points along a two dimensional graph to show the relationship between two sets of data. Its simplicity can make it quite effective at cutting through the noise of large amounts of data in a Google spreadsheet. Once you can see the relationship, it can be used to predict future outcomes within certain confidence levels.

When to use a scatter chart

Before you jump into using a scatter chart, be sure that it is the right type of chart for you. This chart type decision tool can help you decide.

As an example, if you want to see if more people will use the water park as it gets warmer, a scatter chart could be a good tool. If you use any more than two variables, the chart can start to appear confusing and thus reduce its usefulness.  

These charts can be customized in many ways. The first example below has had its points reduced to 2 px, a linear trendline added, and the correlation shown in the upper right hand corner of the chart. These options can be found on the customize menu when inserting a chart.

Customization Menu
Customization menu
Example scatter chart
Example scatter chart

Video Explanation

Correlation

Mathematically speaking, creating a scatter chart lets you visualize the correlation between data points, if there is any. In the example above, the correlation is .928, with the highest value of any correlation being 1. The chart was created in Google Sheets using random values for the Y axis and (Y + random values) for the X axis.  The random value range was kept small so that it would create a tight correlation. Most users for which a spreadsheet chart is made will probably not remember what a correlation value means. A trendline is the layman’s tool for seeing the correlation.

Trendline

You can draw a line through the data along the path which is the best fit based on the points already on the chart. You can see how close the correlation is to 1 or negative 1 by simply looking at the distance that the dots are away from the line.  This line can be used to extrapolate correlations outside of the current data. If you are trying to find data points inside the current set, it is called interpolation. This line can be straight (linear), or it can be a curve (exponential or polynomial).

Choices of trendlines
You can choose from several types of trendlines

Choices of trendlines
You can choose from several types of trendlines

Linear

Below is a simple scatter chart with a correlation value of 1. This means that each of the two variables move in lock step with eachother in the same direction. If the left shoes size increases by one, so does the right. The line of best fit with a perfect correlation is linear and extends 45 degrees from left to right. Note that it will only be 45 degrees on the chart if the scale of the x and y axis are the same scale.

Scatter chart with correlation of 1
Scatter chart with correlation value of 1

Some types of data could have a negative correlation. For example, the relationship between BTU’s needed to heat a room and the outside temperature would create a graph that slants down as it moves along the x axis.

Scatter chart with correlation of -1
Scatter chart with correlation value of -1

Of course, not all data are so highly correlated. If it were, there would be no “scatter” in a scatter chart. This chart shows data with a correlation between -1 and 1. Note that the slope calculation and correlation value were included in the chart by choosing “Use Equation” as the label and placing a checkmark next to R2 to the correlation in the customization are of the chart menu.

Trendline Labels
Setting labels for trendlines

Scatter chart with some correlation
Scatter chart with correlation value shown on chart

Exponential

Exponential trendlines are a good fit for data that increase or decreases at a constantly increasingn rate.

Scatter chart with an exponential trendline
Scatter chart with an exponential trendline

Polynomial

A polynomal trendline should be used when data flucuates between values. Note that the polynomial trenline is more tightly correlated with this data. The data is the same as the data used for the exponential trendline.

Scatter chart with a polynomial trendline
Scatter chart with a polynomial trendline

Error Bars

To illstrate that your data in not precise and may be within a certain range, error bars can be used. These bars are found in the customization menu and be set for different ranges in percentage or absolute terms. The chart below shows error bars added to a simple scatter chart to show uncertainty in the data. They were created by using a constant value of 2.25. These can be used with our without a trendline depending on the point that you are trying to make to the viewer of your chart.

Scatter chart with error bars
Scatter chart with error bars
chrome new tab

Google Sheets – Split view and new window

If you are working on a large table of data in Google Sheets, often it is helpful to be able to see and edit more than one area of the spreadsheet at a time. This is not available from the menus in Sheets and is one of the few ways in which Excel is superior to Sheets. There is the option to Freeze rows or columns, but the frozen section of the spreadsheet will not independently move and therefore the Freeze option is limited to applications such as freezing headers at the top or side of a spreadsheet. Further, if you headers are half way down the spreadsheet, the Freeze option will not do you any good.

The solution

If you are working on a mobile device you may be out of luck, but if you are accessing Sheets through a browser, there is a great work around that will allow you to see the same spreadsheet in two or more different windows. You can even spread these views over multiple monitors which you cannot do in Excel without some pretty nasty workarounds.

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.

If you are using the Chrome browser, do the following:

First, be sure to have your spreadsheet open in your browser. Then, open a new browser tab by pressing “t” while holding down the control button (ctl+t) or clicking the shape to the right of your open tab.

chrome new tab

Once you have a new tab open, the first thing you do is open the same spreadsheet again in this new window. Now, you have the spreadsheet open in two places. You can edit it in either window. Click and hold your left mouse button on the middle of the new tab, where the title of the page is, and pull the tab away from the browse. In browsers other than chrome, you may have have to open another browser window. This creates a new window with a separate instance of Chrome (or other browser) but keeps the spreadsheet inside of it.

If you want more windows, just repeat the same process. To rearrange the windows, you can use the Windows key. Hold the Windows key down and press the left arrow to have the window fill up the left half of the screen and the right arrow for the right half. You also can resize the windows the more traditional way with your mouse. If you have multiple monitors, you can spread these windows across them.

Using multiple tabs in other browsers

 ff new tab

ie new tab

Update: Thanks to ADTC in the comments below for this tip. If you’re using Chrome, you can just right-click on the tab that you’re using and choose duplicate.
insert drawing

How to draw a straight line in Google Sheets

If you are using Google Sheets, you may be having some trouble finding how to insert a line. Once you have figured out how to insert it, getting it to be straight can be frustrating. Follow these easy steps to get it done.

Go to Insert, then Drawing.

insert drawing

From here, choose Line.

line

Now, here’s where the real trick comes in.  If you want to draw a line, go ahead.  But, if you want to draw a straight line, hold down the shift key while you draw!  There it is, a straight line.

straight line

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.