Best Practices for Naming Ranges in Google Sheets
Named ranges in Google Sheets are a powerful tool that can make your spreadsheets more robust and efficient to develop. To reach their maximum potential, it’s important to master both of their defining aspects: naming and sizing. In this post, we will look at the best naming practices.
Video guide to named ranges in Google Sheets:
![Youtube video Using Named Ranges in Google Sheets](/images/youtube-covers/google-sheets-named-ranges.png)
Naming plays a crucial role in managing the complexity of even moderately sized spreadsheets, which might comprise dozens of named ranges. Here, we will explore key considerations for naming ranges effectively and share guiding principles.
A well-crafted range name should:
- Be easily readable and convey which data it refers to.
- Prevent confusion and name collisions among different named ranges.
- Optimize the autocomplete feature when writing formulas.
These are some basic principles to help with choosing a good name.
1. Establish a Naming Convention
Establishing a naming convention, a staple in software development, involves defining rules for separating and capitalizing words in names that can’t include spaces. Suppose we have a sheet Orders
, and we need to create named ranges for each column for subsequent use in FILTER, SUMIFS, and similar formulas.
![Example of a sheet with orders data](/google-sheets/named-ranges/best-practices/example-orders-sheet.png)
Here are some options for naming the column containing client IDs:
orders_client_id
-— employs underscores to separate words (“snake_case”).orders__client_id
-— a variant of snake case using two underscores to distinguish logical parts of the name.ordersClientId
—- capitalizes the first letter of each word (“camelCase”).orders_clientId
—- a blend of the above methods to differentiate logical sections and multi-word fields.
Not all conventions are compatible with Google Sheets. For instance, kebab-case will not work. My preference is for snake_case or its double underscore variant in more complex scenarios, as they tend to be more readable and manageable.
2. Leverage Hierarchical Names
Determining what to include in the range name and in which order can significantly affect navigation. For instance, consider these options:
client_ids
client_ids__orders
orders__client_id
Despite all options being viable, the last one provides the most clarity (regardless of the chosen naming convention). It indicates that client_id
is part of an order, which is particularly useful when you also have a Clients
sheet containing client IDs. Starting with the dataset/sheet name also maximizes the autocomplete feature in Google Sheets.
![Autocomplete supercharged with a consistent naming convention](/google-sheets/named-ranges/best-practices/autocomplete-supercharged-with-naming-convention.png)
3. Include the Purpose and Specific Details in the Name
When naming ranges, it’s common to create several named ranges referring to the same data but with slight variations in the rows and columns included. Moreover, a named range can be used in other spreadsheets via IMPORTRANGE
. In these cases, it’s helpful to incorporate identifying details into the range’s name:
orders_all
– includes all data from the Orders sheet (useful for theQUERY
function).orders_e
– the range used to export data to other spreadsheets, scripts, etc.orders_i
– contains the raw data imported from another spreadsheet. This is helpful when you need to pre-process the data before using it.orders_pr
– includes processed data, for example, data fromorders_i
.clients__names__no_header
– lists client names without the header, which is useful for counting unique items or using in a dropdown. However, ensure that the range size is appropriate for this named range (more on sizing ranges here).sett__target_sales
– is a configurable value used in formulas and conditional formatting, typically situated on the Settings sheet.
4. Keep Names Descriptive, Yet Concise
Aim for names that are self-explanatory, enabling you to understand their context without referring back to the data. However, overly long names can make formulas more challenging to write and read, so strike a balance.
5. Maintain Consistency
Whichever naming convention and strategy you adopt, consistency is key. It will save time and reduce errors in the long run. However, don’t fret too much about it. As your document evolves, the data is likely to change, and you can always rename later, barring instances where formulas only accept ranges as strings like IMPORTRANGE
and INDIRECT
.
In conclusion, effectively naming ranges in Google Sheets is a vital skill that enhances the organization, clarity, and efficiency of your work. By adhering to the principles and strategies discussed here, you can transform complex spreadsheets into manageable and intuitive tools.