In this article:

Google Sheets Non-Contiguous Range (How to Use It in 2024)

May 8, 2024

Google Sheets Non-contiguous Range

A non-contiguous range in Google Sheets refers to a selection of cells that are not adjacent to each other. In other words, it's a way to select multiple groups of cells that are separated by other cells not included in the selection. This can be useful for applying formulas, formatting, or other operations to several distinct areas of your spreadsheet without affecting the cells in between those areas.

For example, if you have data in cells A1:A10 and C1:C10, and you want to format both of these ranges at the same time without affecting column B, you would use a non-contiguous range selection to select both A1:A10 and C1:C10 simultaneously.

How to Select Non-contiguous Range in Google Sheets

Follow the steps below to select a non-contiguous range in Google Sheets. 

1. Select Your First Range

Click and drag to select your first range of cells. In our dataset, let's say you want to highlight the scores of specific students for comparison. Start with John Doe's score, which is in cell B2.

google sheets non contiguous range

2. Use Ctrl (or Cmd on Mac) to Select Additional Ranges

Hold down the Ctrl key (Cmd key on a Mac). While holding this key, click and drag to select your next range of cells. For example, to select Lisa Ray's score next, click and drag from cell B5 while holding down the Ctrl (or Cmd) key. You can release the mouse button but keep holding the key down to select more ranges. Let's also select Nancy Gray's score in cell B8 in the same way.

After selecting the scores of John Doe, Lisa Ray, and Nancy Gray (cells B2, B5, and B8), you can release the Ctrl (or Cmd) key. Your non-contiguous ranges (the selected scores) should now be highlighted.

3. Apply Desired Actions to Selected Ranges

With your non-contiguous ranges selected, you can now perform any action on these cells. This could include applying formatting to make these scores stand out, or using the “=AVERAGE(B2, B5, B8)” formula elsewhere to calculate the average score of these three students.

We hope that you now have a better understanding of how to set up Google Sheets non-contiguous range. If you enjoyed this article, you might also like our article on the ratio formula in Google Sheets or our article on how to remove whitespace in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->