How to Limit Number of Rows in Google Sheets Query [2020]

Multiple Ways of Serial Numbering in Google Sheets

As you may know, a standard format starts with a serial number column. To more precise it will be like serial number, description, client name, etc. Under the serial number column, you can number rows in different ways. Here we can learn about auto serial numbering in Google Sheets.

There are multiple ways one can adopt to put the serial number in Google Sheets. Before going to tell you how to auto number rows in Google Sheets in dynamic ways, I should tell you other available options that may be useful for beginners.

At the end of the post, I have also shared the information on how to auto-increment alphabets as well as Roman Numerals in Google Sheets.

So let us begin with different auto serial numbering in Google Spreadsheet.

Video

Auto Row Numbering Using the Fill Handle

Unlike Excel, Google Sheets fill handling behaves differently. You can’t use the fill handle to auto-fill the number in Google Sheets this way. It works perfectly in Excel. The below screenshot is applicable only in Excel.

Update: Now it works in Google Sheets too!

Here what you want to do is manually enter the first two serial numbers and then drag the fill handle to down or double click on the fill handle.

Google Sheets QUERY Function Examples

If you want to follow along with the solutions, please make a copy of the Google Sheet template above.

This is what our starting data looks like:

In this tutorial, I have used a named range to identify the data, which makes it much easier and cleaner to use in the QUERY function. Feel free to use the named range “countries” too, which already exists in the template.

If you’re new to named ranges, here’s how you create them:

Select your data range and go to the menu:

Data > Named ranges…

A new pane will show on the right side of your spreadsheet. In the first input box, enter a name for your table of data so you can refer to it easily.

SELECT All

The statement SELECT * retrieves all of the columns from our data table.

To the right side of the table (I’ve used cell G1) type the following Google Sheets QUERY function using the named range notation:

=QUERY(countries,"SELECT *",1)

Notes: if you don’t want to use named ranges then that’s no problem. Your QUERY formula will look like this:

=QUERY(A1:D234,"SELECT *",1)

For the remainder of this article, I’ve used the named range “countries” but feel free to continue using the regular range reference A1:D234 in its place.

The output from this query is our full table again, because SELECT * retrieves all of the columns from the countries table:

Wow, there you go! You’ve written your first QUERY! Pat yourself on the back.

SELECT Specific Columns

What if we don’t want to select every column, but only certain ones?

Modify your Google Sheets QUERY function to read:

=QUERY(countries,"SELECT B, D",1)

This time we’ve selected only columns B and D from the original dataset, so our output will look like this:

Important Note

Remember, our QUERY function is in cell G1, so the output will be in columns G, H, I, etc.

The B and D inside the QUERY select statement refer to the column references back in the original data.

WHERE Keyword

The WHERE keyword specifies a condition that must be satisfied. It filters our data. It comes after the SELECT keyword.

Modify your Google Sheets QUERY function to select only countries that have a population greater than 100 million:

=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)

Our output table is:

Let’s see another WHERE keyword example, this time selecting only European countries. Modify your formula to:

=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)

Notice how there are single quotes around the word ‘Europe’. Contrast this to the numeric example before which did not require single quotes around the number.

Now the output table is:

ORDER BY Keyword

The ORDER BY keyword sorts our data. We can specify the column(s) and direction (ascending or descending). It comes after the SELECT and WHERE keywords.

Let’s sort our data by population from smallest to largest. Modify your formula to add the following ORDER BY keyword, specifying an ascending direction with ASC:

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)

The output table:

Modify your QUERY formula to sort the data by country in descending order, Z – A:

=QUERY(countries,"SELECT B, C, D ORDER BY B DESC",1)

Output table:

LIMIT Keyword

The LIMIT keyword restricts the number of results returned. It comes after the SELECT, WHERE, and ORDER BY keywords.

Let’s add a LIMIT keyword to our formula and return only 10 results:

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT 10",1)

This now returns only 10 results from our data:

Arithmetic Functions

We can perform standard math operations on numeric columns.

So let’s figure out what percentage of the total world population (7.16 billion) each country accounts for.

We’re going to divide the population column by the total (7,162,119,434) and multiply by 100 to calculate percentages. So, modify our formula to read:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)

I’ve divided the values in column D by the total population (inside the parentheses), then multiplied by 100 to get a percentage.

The output table this time is:

Note – I’ve applied formatting to the output column in Google Sheets to only show 2 decimal places.

LABEL Keyword

That heading for the arithmetic column is pretty ugly right? Well, we can rename it using the LABEL keyword, which comes at the end of the QUERY statement. Try this out:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage'",1)

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage' ",1)

Aggregation Functions

We can use other functions in our calculations, for example, min, max, and average.

To calculate the min, max and average populations in your country dataset, use aggregate functions in your query as follows:

=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)

The output returns three values – the max, min and average populations of the dataset, as follows:

GROUP BY Keyword

Ok, take a deep breath. This is the most challenging concept to understand. However, if you’ve ever used pivot tables in Google Sheets (or Excel) then you should be fine with this.

The GROUP BY keyword is used with aggregate functions to summarize data into groups as a pivot table does.

Let’s summarize by continent and count out how many countries per continent. Change your query formula to include a GROUP BY keyword and use the COUNT aggregate function to count how many countries, as follows:

=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)

Note, every column in the SELECT statement (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY keyword (e.g. column C in this case).

The output for this query is:

Let’s see a more complex example, incorporating many different types of keyword. Modify the formula to read:

=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)

This may be easier to read broken out onto multiple lines:

=QUERY(countries,
"SELECT C, count(B), min(D), max(D), avg(D)
GROUP BY C
ORDER BY avg(D) DESC
LIMIT 3",1)

This summarizes our data for each continent, sorts by highest to the lowest average population, and finally limits the results to just the top 3.

The output of this query is:

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to number rows in Google Sheets. Want more? Check out all the Google Sheets Tutorials.

Named ranges in Google Sheets

In Google Sheets, you can assign a name to a range. Once you do this, you can use the name of a range instead of its reference in formulas and scripts.

There are several ways to create a named range:

1. Select Data —> Named ranges and enter the name and reference.

2. Select a range in the spreadsheet, right click and select Define named range to give it a name.

3. Create a named range by using Google Apps Script.

You can also create named ranges using Google Apps Script. The code below shows you an example of how to do that.

Once you create a named range, you can use it in formulas and scripts by using its name. So, instead of Sheet2!A1:C6, you can use the range’s name like this: StudentGrades.

Using named ranges has several benefits:

• Formulas and scripts will become more readable because the name of the range will help users understand the type of data contained in it. For e.g., StudentGrades is a lot more descriptive than Sheet2!A1:C6.

• When you make changes to a named range, all the formulas and scripts will immediately begin using the updated range.

Replace Dots with Comma to Convert Text to Numbers

Unfortunately, this will not always work when you receive data from a file in which you need to convert the “text” format into a “numbers” format.

Perhaps you are given files in which the commas have been replaced by “.” or “periods” and are being read in the “text” format.

This is sometimes the case when you get from countries that use dot instead of a comma.

In this case, you will want to use another formula:

This works in a very similar way to the “REGEXTRACT()” function in that it finds and replaces the “text” format of the number into the “number” format of the number.

This works by replacing the “.” with a “,”. This will help convert the number “143.244” to “143,244” which is the same as “143244” that can be used in a formula for a calculation. 

You can actually test out whether or not this works by using the number generated from the “text” format of that number by using it in a calculation. 

Creating a Formula to Automatically Increment

You can also just create a simple formula to add one to a value and then copy and paste the formula to increment in your Google Sheet.

This method is somewhat similar to the first one. Here are the steps

1. First, you need to have a starting value. I placed a 1 in my first row to have something to start incrementing with
2. In the next cell, press the equals sign on your keyboard (=) and click on your starting cell  to reference it (A2)
3. Press the plus sign (+) on your keyboard and then enter a 1 (or whatever number you want to increment by), then press ENTER to calculate the formula
4. Copy and paste this formula down to additional rows or double click the blue square in the bottom right corner

This method is very simple, you are just creating a custom formula to add “+1” to your cell and so it keeps incrementing on each additional row.

A Real Example of Using the QUERY Function

Using the QUERY function is a super versatile way of understanding the data in Google Sheets.

As you can see in the data above, the QUERY function is used to determine the customers who acquired the order sizes above 15 lots, despite the variant that they order. The function outputs the names of those who ordered the most.

The function takes three arguments. So in the equation, it will look like:

Here’s what the example does:

• We actively selected the cell where we write our formula, and we use the QUERY function to determine which customers bought more than 15 lots.
• We selected the entire data table, A1:D16.
• Our Query wants to limit the data to the customers that order more than 15 lots of any variant of product.
• Note that in our example, the query attribute uses the greater than function. This is the same thing as applying 16 and above to the formula.

This simple problem can be practiced to perfection. Use the link below to get a copy of this problem set:

Make a copy of example spreadsheet.

Stay up to date

Follow me via email to receive actionable tips and other exclusive content. I’ll also send you notifications when I publish new content. By signing up you agree to the Privacy Policy & Terms.

Tags