In this tutorial, I will show you how to use a data range in Google Sheets.
Although it’s possible to use Google Sheets as storage or as a simple calculator, it’s definitely far from being ideal. One way or another, if you want to take advantage of its powerful functions, you will need to use data ranges.
In the years that I’ve used spreadsheets professionally, I’ve almost always used a data range in Google Sheets. It is one of the more fundamental features in Google Sheets enabling users to “call” a specific cell or even groups of cells for different uses.
What is a Data Range in Google Sheets
In Google Sheets, a data range represents a cell or a cluster of adjacent cells in your spreadsheet. You’re likely using a data range in Google Sheets every time you work on the data stored in it. Especially in scenarios where you need to use functions where a data range is often a parameter needed for the said function to work.
5 Different Forms of a Data Range in Google Sheets
Data range in Google Sheets can vary from a single cell to multiple rows or columns. Below is a list of the different forms of data ranges that are regularly used in Google Sheets.
1. A Single Cell Range in Google Sheets
The simplest form of a data range in Google Sheets is a simple single-cell address.
As seen in the example below where the cell containing “D.” is selected, the cell address B4 is shown on the Name box.
B4 is the data range for this particular cell or content.
When used in a formula to mention the data range or use it as a function’s parameter, it will be highlighted in the sheet.
Using a single-cell data range in Google Sheets is most useful in scenarios where you want to copy data from a cell in another part of the sheet into a different cell.
2. A Basic or Common Range in Google Sheets
Mostly used as part of functions, a data range in Google Sheets most commonly appears as a group of adjacent cells. It can be a short stack of cells arranged in columns, rows, or a combination of both.
I’ve listed examples of different ways of forming a data range in Google Sheets.
In my example below, I selected a data range consisting of a few cells arranged in a short column.
I can also use this data range in a formula. When entered as it is into a different column, the value that will appear is equivalent to the relative position of the new cell it will be put into.
Below, I used the data range C5:C11 and typed “=C5:C11” in cell H5. As seen on the screenshot, a small popup mentions “Walker” which is the value in C5.
If I move the formula from H5 into H7, the popup now mentions “Kerkhof” which is in C7.
Data ranges arranged in short rows act similarly to columns.
Using this short row data range, A4:D4, in a formula highlights it as seen below.
At the same time, putting in the formula “=A4:D4” in another row shows the value in the respective cell from the data range in the new cell.
As seen in the last screenshot, the popup shows “D.” (from B4) in the cell B27 where the formula was entered.
Some functions such as VLOOKUP and SORT oftentimes need a data range in Google Sheets consisting of multiple rows and columns worth of values.
In the example below, I have identified the data range C4:E10.
I can also input this in a formula in another cell on a different row or column, but unlike the previous examples, it doesn’t have a popup indicating the value to be displayed. It also won’t show anything once it is entered other than a #VALUE error message.
Other than functions specifically needing a range in this form, you can also use it as an array by enclosing it in brackets like “{C4:E10}” where it will be displayed in the cell where the array formula was entered.
3. Column Ranges in Google Sheets
Entire columns can be also used as a data range in Google Sheets.
You only need to identify the column letter and you can either use it as C:C for the entire column C or C2:C for the entire column starting from cell C2 and below.
One of the more commonly used functions with data ranges for entire columns is the FILTER Function, as seen below.
For columns with headers to be sorted using the SORT Function, it’s recommended to exclude the row for headers. Therefore, the data range for this example is C2:C where C1 was excluded.
The advantage of using a data range like this where the end has no number (C2:C), is that whenever another row is added, anything that is put into the new cells under column C will be automatically included in the original data range.
4. Row Ranges in Google Sheets
Similar to how Column Ranges work, Row Ranges take entire rows to act as a data range in Google Sheets.
Instead of the more commonly used column letters which depict entire columns, rows are depicted by their row number.
It works in such as way that row 4 as a data range is 4:4 while rows 4 & 5 as one data range is 4:5.
Functions such as TRANSPOSE will use data ranges like this to convert them from rows into columns.
Same with columns, you can also cut a row shorter so that it’ll start on a specific column instead of taking on the entire row. For example, B4:B will start from B4 and include all the cells to the right of it.
An advantage of using row data ranges is that whenever a new column is added, the row data range is automatically updated to include the new values in the new columns.
5. Entire Sheet Range in Google Sheets
Not as commonly used as the previous examples of a data range in Google Sheets, an entire sheet range is aptly named in that it is a range that includes everything on a sheet.
It can be defined by using all the rows as the range like 1:1000 where the sheet only has 1000 rows.
Or it can also be defined as the A:Z where the farthest column in the sheet is column Z.
This is often useful for LOOKUP Functions used on large datasets.
A shortcut for taking everything in a sheet (including all blanks) is done by pressing the small box at the corner of the row and column bar, below the Name box.
Calling a Data Range From Another Sheet in Google Sheets
To use a data range in Google Sheets placed in different sheets, use the following formats:
=sheetname![data_range]
Examples:
=TITLE!A10
=Data!M14:O18
Or
=’sheet name’![data_range]
Examples:
=’Other Sheet’!E25
=’Basic Range’!L4:L5
The rule is to use the sheet name, an exclamation point, and the data range. If the sheetname includes a space it must be enclosed in single quotes.
In the next example, I used the ARRAYFORMULA Function with the entire sheet range of the Sheet: All.
This caused all the values to be transferred over to the All sheet from the ‘Other Sheet’ sheet.
Take note that this only works if both sheets are contained within the same workbook.
IMPORTRANGE Function
Another powerful function that can be used to make better use of a data range in Google Sheets is the IMPORTRANGE Function.
The syntax of IMPORTRANGE is
=IMPORTRANGE(spreadsheet_url, range_string)
Where
- spreadsheet_url is the URL of the source worksheet
- range_string is the string specifying the data range to be imported
The spreadsheed_url parameter must be enclosed in double quotes if the URL is inputted as is in the formula. Otherwise, if placed in a separate cell, a simple cell reference can be used as well.
The range_string parameter must have a syntax similar to the one I discussed in the above section, “Calling a Data Range From Another Sheet”. Unless you want IMPORTRANGE to extract data from the first sheet, then it can just be the data range enclosed in double quotes.
When the IMPORTRANGE Function is used for the first time for a spreadsheet, it will request that you allow access to it. Take note that you must at least have View access on the source worksheet.
After clicking on ‘Allow access’, it may take a few seconds to load and then your data should populate on its destination cell.
In the screenshot above, I edited the spreadsheet_url to appear as SOURCE SPREADSHEET URL.
That said, the exact URL that I used for this example is seen below. Basically, you just need to copy the URL as is and enclosed it in double quotes as you use it as the first parameter of the IMPORTRANGE Function.
Google Sheets is often used differently by each person because of its high versatility and use. But knowing about data range in Google Sheets is fundamental and mastering the usage of this will help you in more ways than you can imagine.
Normally, ranges can be used as it is, but depending on your scenario the INDIRECT Function may help you in using more advanced ways to utilize a data range in Google Sheets.
Frequently Asked Questions about What is a Data Range in Google Sheets
Can I have non-adjacent cells in a single data range in Google Sheets?
You can only have adjacent cells in a single data range in Google Sheets but certain formulas accept multiple ranges and cells. You can also use brackets (={ }) to define arrays to contain non-adjacent cells which will be recognized as a data range in Google Sheets.
Where can I use a data range in Google Sheets?
A data range in Google Sheets can be used in various formulas as well as whenever calling a cell reference is necessary. Data ranges are also used with charts, data validation, and conditional formatting, among many others.
Conclusion on What is a Data Range in Google Sheets
In Google Sheets, a data range represents a cell or a cluster of adjacent cells in your spreadsheet. Using functions where a range is often a parameter needed for the said function to work is an example of a data range in action.
