Sometimes it makes sense to segregate information into smaller organized chunks. Just like we organize our pages in a notebook using appropriately named tabs to quickly identify what they are for. Similarly, in Google Sheets, we can work with data in multiple worksheets within the same spreadsheet file. This way it is easier to manage the data. In this article, we will try and understand how to work with multiple sheets.
A worksheet is analogous to a page in a notebook. Just like there can be multiple pages in a notebook, there can be multiple pages in Google Sheets. However, by default, every Google Sheets file contains at least one worksheet. Because what’s the purpose of a notebook without a single page in it? Please see the snapshot below.
At the very bottom of the Google Sheets application user interface, we will notice the available worksheet tab(s). At the moment we see there’s only one sheet named “Sheet1“.
Renaming and adding more worksheets
We can change the name of the sheet by double-clicking on the tab, then keying in name of our choice. Once done, hit Enter/Return or click away. Alternatively, right-click on the tab, and then choose Rename option. To add more worksheets, we just have to click on the + option at the bottom left corner. A good case of Google Sheets file with multiple sheets that are renamed is below. It is a spreadsheet that contains monthly sales data segregated into multiple worksheets appropriately named for the purpose.
To activate a different sheet, just click on that worksheet tab. However, there can be scenarios where there are more sheets than the UI can display all at once. For example, in the screenshot above, the Jan 18 worksheet isn’t visible, although we know it is there. In such cases, Google Sheets lets us horizontally scroll through the list of available worksheet tabs. Notice the left/right triangular arrow buttons adjacent to the rightmost worksheet tab? They weren’t there when in the first screenshot. They automatically appear when the number of worksheets grows in the file. Use those buttons to traverse to either left or right and accordingly uncover those sheets that we need.
If that seems a little tedious to click multiple times, there’s an easier way. Use the Ctrl button in combination with those triangular buttons to quickly view the first/last worksheet. Alternatively, click on the “All sheets” option (the one that looks like four stacked thick lines) at the bottom left corner. It displays the list of the available worksheet names and the active worksheet has a check mark against it. To activate another sheet, just click on the concerning name on the worksheet list.
While the mouse is a great tool, there are some of us who like to use the keyboard shortcuts for their ease of use and speed. Below are the key combinations that let us toggle between the sheets.
- Activate the next worksheet:
- Windows: Ctrl + Shift + Pg Down
- Mac: Cmd + Shift + Fn + ↓
- Activate the previous worksheet
- Windows: Ctrl + Shift + Pg Up
- Mac: Cmd + Shift + Fn + ↑
Now let’s say you need to toggle between sheets that are far away from each other (Jan 18 and Sep 18 for example). Using the above keyboard combinations for this purpose isn’t any good. And at times like these, we may even long for the good old alt + tab keyboard combination that easily toggles between windows. While that isn’t directly helpful here, we can use it to our advantage with a simple hack. We just have to open the same Google Sheets file on a new window and select the sheet of our interest. Now we can just do alt + tab, to switch between these two worksheets even if they are not close to each other.
At times, we may have the order of the worksheets not to our liking. In that case, we can quickly re-arrange them in any order that we prefer. We may use the mouse, hover on to the worksheet tab, click and drag it to the position where we deem it fit, and release the mouse. The worksheet of interest now snugly fits into the place we chose for it.
Another alternative is to right-click on the worksheet tab, then choose Move left/Move right option whichever is applicable. But if we need to move the sheet a long way left or right, the mouse click-drag method is faster.
Cell references become explicit
If we were to create some formulas on the worksheet where it references the same worksheet, it was pretty straightforward. Something like SUM(F2:F100). But, if we are to calculate the values using cells from a different worksheet, the cell references get prepended with the corresponding sheet’s name. Please have a look at the screenshot below.
In the cell G2, we calculate the SUM of the range of cells within the same worksheets. But in the cell G3, we are calculating the SUM of the cell range that is on the Jul 18 worksheet.
- Every worksheet is supposed to have a unique name, and a worksheet can’t be without a name.
- We can add up to 200 worksheets per a Google Sheets file.
- Each worksheet in the file will have an equal real estate to work with.
- Unlike Excel, we can’t group sheets to work on them in one shot. On the similar lines, Google Sheets doesn’t support 3D formula referencing.