34

In Google Spreadsheets I often use A1 notation to refer to a whole column:

A1:A

For reasons I explain below, I'd like to use R1C1 format, to refer to a whole column, something like:

R1C1:C1

It's not possible to use A1:A in Excel, so as R1C1 notation originated from Excel, it seems unlikely that its possible to do this in R1C1 notation. If it is, it'd make my life easier.

Background: I have a spreadsheet visits, which pulls information from another spreadsheet data, which has a dynamically changing format.

To get around this, I use a formula to generate an address in a cell, based on the current layout of data. I can then reference this address cell in other formulae using indirect. And it's much easier to generate an address using R1C1 than A1.

3
  • "As it's not possible to use A1:A in excel" - do you mean "As it's not possible to use R1C1 in excel" ? Oct 18, 2012 at 12:22
  • No, I meant that the range A1:A only works in Google Docs - with excel you have to specify both row and column for the end of a range.
    – s6mike
    Oct 18, 2012 at 18:21
  • 5
    I can't read R1C1 notation without wondering about a R2D2 one. Jul 13, 2015 at 16:39

6 Answers 6

13

Short answer

Yes, it's possible to refer a whole column using R1C1 notation: Use INDIRECT("C1",FALSE)

Explanation

Google Sheets doesn't include a feature to change the reference notation from A1 to R1C1 but the last could be used with the INDIRECT built-in function.

Example

In a sheet having 1000 rows, the following formula will return 1000

=ROWS(INDIRECT("C1",FALSE)) 
1
  • 8
    Genius! Thanks :) Now almost 4 years have passed since I asked the question, now I just need to remember what I was doing when I asked it!
    – s6mike
    Sep 3, 2016 at 11:05
10

A1 notation is supported now. To center column A:

SpreadsheetApp.getActiveSheet().getRange("A1:A").setHorizontalAlignment("center");
1
  • 2
    A1 notation has always been supported, but my questions asks whether it's possible to to use R1C1 notation to refer to a whole column in Google Spreadsheets.
    – s6mike
    Nov 25, 2015 at 22:56
2

No, but you can work around it, by knowing which row is the last row with data in a column, using the COUNTA function.

INDIRECT("A1:A") could be written something like INDIRECT("R1C1:R" & COUNTA(R1C1:R1000C1) & "C1")

You need to determine a sensible 'max rows' value. I've used 1000 in the example, so this formula should work in a spreadsheet with up to 1000 rows.

Look up COUNTA in the Google Spreadsheets function list.

2
0

Yes it is possible:

A1 Notation: A:A is an absolute reference to Column A whereas R1C1 Notation with square brackets is a relative reference, so you count from the current cell.

Example: If current cell is D1, then as column A is 3 columns to the left you can refer to it as C[-3]:C[-3].

-1

The acceptable format varies depending on the method being used. In this one, for example, the description allows for R1C1 notation, but it's unclear (to me at least) whether the input or output is R1C1 notation.

I recommend adopting the use of getRange(row, column, numRows, numColumns) instead (Official doc here). Based on the given details of your intended use, this would suit your purposes quite well. I frequently use a variable for the 'row' parameter to retrieve a whole range of values in a loop. e.g., getRange(row, 2, 1, 7) where row = 3 would return the range C2:C8. Then, simply increment 'row' to return the next range D2:D8.

2
  • 1
    You are not answering the question of how to refer to the whole column, instead of a specified number of routes in that column. Using getRange repeatedly in a loop is not recommended unless necessary, it leads to poor performance and quota issues.
    – user79865
    Jul 27, 2016 at 21:25
  • 1
    As for what you found unclear in getRange method: its output is a range object, not a string, so there is no question of notation for it. The notation is specified for input, which is a string.
    – user79865
    Jul 27, 2016 at 21:27
-2

YES...refer to whole column as C:C... rather than range as C15:C25 ...

2
  • Welcome to Web Applications. As this is a "late answer" (the question is old and already has multiple answers), please ensure that this answer adds something not already said in previous answers. It might be a fuller explanation if that is not already available, and explain why this answer is correct. Related help article How to Answer May 1, 2023 at 23:04
  • Actually this is using A1 notation, as I mentioned in the first code snippet: but i asking how to solve this using r1c1 notation.
    – s6mike
    May 2, 2023 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.