Pages

How to name a column (or row) in a Google Spreadsheet

This article explains two ways that you can name a column inside a Google Spreadsheets worksheet.

The same approach can be used for rows - you simply need to use the word "row" instead of   "column" in the following directions.





Option 1 - Type in the name

To use this approach:
  • Type in the name at the top of the column.    
  • Optional - make it bold or a different colour.   
  • Turn on Freeze panes (View > Freeze Column > Freeze Column 1) so that when someone scrolls down the sheet, the name-value is still visible.

This is the most "reader friendly" approach, because it draws attention to the name and the data.

But to use the contents of the column in a formula, you still have to refer to it explicitly not by name, eg =Max(A2:A4).


Option 2 - Use the Range Naming tool

To use this approach:
  • Select the column
  • From the menu, choose Data > Names Ranges ...
  • Type in the column name
  • Press Done.
  • Optional but recommended if you have named the entire column:  
    Format the column background (with the Fill Colour icon from the menu bar), to remind yourself that the entire column is the named range

This is the most "formula friendly" approach, because it lets you use the name in formulas eg==Max(Animals)

It also means that the first row of data is in Row 1 of the spreadsheet, which some people find less confusing to look at.
(Warning:  in general, basing formulas on the row number rather than the actual data is bad spreadsheeting practise, because it makes it a lot harder to change the data later.   If you really want the row number, put it into a new column and use the data from that.)

However you cannot to put anything else except relevant data into the named column:  all formulas that refer to it must go into other columns.   And unfortunately, after you have named the range, even when you select it, there is nothing on-screen to remind you that the range is named, unless you turn on the Data > Name Ranges panel - and even that only shows you a list of the ranges not a visual indicator.


Examples

There is an example spreadsheet showing both of these approaches here.


Versions

This approach works in Google Sheets files created since the "new" version was released, and older ones converted to the new format.   It may not be available in uncoverted olde
r version spreadsheets.

1 comment:

  1. The freeze row/column command is the best news I've had all week. Where have I been all this time? Thank you.

    ReplyDelete