One way to think about Google Sheets is that it is like a page from a book with a grid pattern on it, but as the name Sheets suggests, you can have more than one page in the book. In one file you can have numerous sheets, with either separate things on them or interconnect the things between the sheets.
So, in this post let’s look at:
- Renaming sheets
- Adding colours to the sheet tabs
- Duplicating sheets
- Adding sheets
- Deleting sheets
- Hiding sheets
- Moving sheets
- Moving the tabs to find a particular sheet
- Sheet tab menu
- Adding references to other sheets
Most of what we’re going to look at, happens at the bottom of your screen around the tab area. When you create a new spreadsheet, by default, you’ll start with just one sheet called “Sheet1”, and the sheet has a tab with the name on it “Sheet1”.
We can change the name of sheet, by double-clicking on the tab and typing in the name we want. This is more important once you have more than one sheet, but isn’t essential, although I usually give them names so it reminds me what’s on each sheet.
You could also click on the triangle on the tab, to bring up the tab menu. Then select “Rename”, but this isn’t as quick as just double-clicking on the tab.
Adding colours to the sheet tabs
Apart from being pretty, colouring the tabs can help you find your data quickly. I’ve also used it to show the status of the data. Green being ready or completed and red meaning some work is needed on the sheet.
Click on the triangle on the tab to bring up the tab menu. Select “Change colour”. Then select one of the colours from the palette.
There are a couple of ways to add more sheets. The first is to duplicate the current one. This has the advantage of copying the current data and formatting, which could save you time later on.
Click on the triangle on the tab to bring up the tab menu. Select “Duplicate”.
It will create a new sheet to the right of the current one, with the name “Copy of + name of the duplicated sheet”, e.g. “Copy of Sheet 1”. If you duplicated that one, you’ll get one with the name “Copy of Copy of Sheet 1”. Not the prettiest of names, so you’ll want to rename them. Note, duplicating also copies the colour of the tab.
You can add a blank sheet by clicking on the plus button to the left of the tabs. Here, the newly created sheet will be called “Sheet2”. If you already had, let’s say 5 sheets, the new one would be called “Sheet6”.
To get rid of a sheet, click on the triangle on the tab to open the tab menu, then select “Delete”, then a warning will pop up, to check you want to do it, just click OK. If you only have 1 sheet, this option is disabled, as you can’t have a spreadsheet with no sheets.
To change the position of a sheet, you just need to change the position of the tab. Click and hold on the tab and drag it it to the position you want, then let go.
Moving the tabs to find a particular sheet
If you have a lot of sheets, some of the tabs won’t be visible on the screen. The wider your screen, the more you will be able to see. To find the sheet you want, click on the arrows to the right of the tabs. These move along a page full of tabs at a time. For example, if I can only see 9 tabs at the bottom of the screen, when I click on the move arrow, it will move along 9 tabs to the next set of 9, so the last tab on the right moves to the first one on the left, assuming I’ve got that many sheets.
Tip: I usually try to use short names for my tabs, so I can see as many on the screen as possible.
Sometimes you may want to hide sheets. Typical reasons are that your end-users only need to see certain sheets; you have a sheet with all the calculations on it which you don’t want someone to accidentally mess up; you have archive data which you want to keep but it’s not used now, etc.
To hide the sheet, click on the triangle on the tab to open the tab menu, then select “Hide sheet”.
Sheet tab menu
Clicking on the ‘hamburger’ menu to the left of the tabs, opens up the sheet tab menu. This is useful for a couple of reasons. Firstly, if you have hidden a sheet, as explained above, this is where you can unhide it. To do so, click on the menu, then find the sheet you want (you may need to scroll down). Hidden sheets are greyed out. Click on the sheet name and it will appear in the tabs, in the same position it was before you hid it. Sheets also, automatically opens that sheet. Here the sheet “currency” is hidden. Clicking on it unhides it.
It’s also useful if you have a lot of sheets, then you can navigate quickly to the one you want.
So far we’ve looked at how we control the sheets, now let’s look at how you can connect data from one sheet to another.
Adding references to other sheets
Using an example to show how it works, I’ve got the exam results of two classes (A & B) and I want to compile both sets of data on a separate sheet. On sheet “ClassA” I fill in their exam results in the table, I then do the same on sheet “ClassB”.
On sheet “Year10” I want both classes together, as maybe this is what I need to present to the head of that year, so obviously he/she will want to see all the data together as a summary. So, I set up a blank table ready to collect the results from the other sheets.
In cell B3 on the Year10 sheet, I type the following formula:
This tells Sheets to look at the page called ClassA and then the cell A3, and put the contents in the cell I’m in. The exclamation mark after the sheet name is what tells Sheets that this is the sheet name.
As you can see it’s put the name Fred which is in cell A3 on the ClassA sheet. I could type in formulas for all of them, but that would be a slow way to do it. So, as I know that there are 3 other students below Fred, I just click on the cell B3, and copy-drag (clicking and dragging the little blue square) down to cell B6. This then automatically updates the formulas so that it picks up the correct reference on sheet ClassA.
Now, I just need the exam results. I know they are in the cell to the right of the names, so I select all the names and copy-drag them to the cells to the right. Now all the results are there.
I repeat the same for the second class. This time in cell B7 I type:
Then I repeat the same process as above to copy this formula, so that I get the other names and exam results.
Here are the formulas that the above produced in each cell.
Of course, I could just copy the results from each class and paste it in the new sheet, but I want is to create a connection between the sheets, so that if I change anything on the first two sheets, it automatically updates the third sheet. This allows me to use the sheet for future uses, without having to input all the data in twice.
So, as an example, let’s change the results of class A. On sheet Class A, I enter the new results and when I go to sheet Year10, I can see they have automatically updated.
This is obviously a simple example, but the main way I use multiple sheets and references, is to organise my data better. Often, I have a sheet for each set of data and then a separate sheet, which pulls in certain bits to create some kind of summary on one page. It allows me to show a certain group of people some information (e.g. class A) without showing them the other (class B).
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store