Creating and getting to know Google sheets
Create a sheet by either right-clicking on an empty space and selecting Google Sheets, or clicking on the New tab at the top of the left sidebar.
As an aside: During the sheet creation process, you may have noticed a dropdown arrow to the right of each file type. Hovering over this provides the options of Blank spreadsheet and From a template.
While we’re going to be starting with a blank, it's worth noting that we also have access to a sheet template library. This contains a range of preformatted sheets for a variety of purposes.
As expected the rows are labelled numerically and the columns alphabetically, i.e. 1,2,3, etc, and A,B,C, etc, respectively. It will help in the future if you remember what I’m referring to when I mention “labels”.
How to name your spreadsheet
Your new sheet will be unceremoniously titled “Untitled spreadsheet”. Click on this and name it what you want. Alternatively you can right-click on the sheet in its parent folder. Then select Rename from the dropdown.
Don’t worry about getting it right the first time. You can always repeat the process later. But be careful. Unlike some applications, it is possible to have two files of the same type with the same name, in the same folder.
How to add and delete more rows and columns in Google sheets
By default: each sheet is created with 26 columns and 1000 rows. It is entirely up to us where, and how many we add or remove,
Adding and removing rows from sheets
I’ll begin with a single row
To add or delete a row at a given point: hover over then right-click the row in question. As the image below shows, we then have a few options, including: Add row above, Add row below, and delete row.
Now for multiple rows
However many rows we highlight, determines how many we can add or delete. In the example below, I clicked on the row label where I wanted to begin, then dragged the mouse down over five rows. With five rows highlighted, I then right-clicked to get the view you see below
The difference this time is that I could add five rows. Either above the first highlighted, or below the last. Also note the option to delete the five rows.
At the very bottom of the sheet, we have one more option to add rows. The preset is 1000, but this can be edited.
Let’s say for example you only use 20 rows and, for aesthetic reasons you want to remove all those below. Highlight all the rows, right-click, then delete.
I have to say that scripts can be written, or add-ons installed, to remove empty rows and columns, but I won't be broaching that topic just yet.
Adding and removing columns from sheets
For the most part,removing or adding columns to sheets is the same as rows. The only difference being is there’s no option to add more at the very end.
Let’s say we wanted to add five columns to the end. To do this we need to highlight the last five columns, right-click and then insert them.
How to add more sheets to Google sheets
Upon creation: our spreadsheet will have one sheet. At the bottom you’ll see the option to add more. We may also want to give our sheets a more descriptive name and add a little colour to make them more distinguishable.
How to rename individual sheets
Double clicking on the name tab at the bottom will allow you to edit the name. Another option is to click the more icon and select rename from the popup.
For this image, to ensure all options are visible, I’ve added two more sheets, and selected the middle sheet.
Changing the sheet name border bottom colour
In the image above you’ll notice other options. One of them being Change colour. Hovering over this will bring up the colour palette.
Moving the sheet tabs around
The same image above offers the options to Move left and Move right. This is of course providing there’s more than one sheet and the one we;re working with isn’t already on the end.
Personally: I prefer to left click on the tab and drag it left or right. Finally: here’s the three tabs I edited.
How to hide sheets, rows and columns
Sometimes rows and columns contain data that is needed at the core level, but not in our view. The simple hide function allows us to trim our view to the cells we’re actually working with.
Likewise with entire sheets. Hiding sheets removes their tab index from the bottom of the page, giving us a less cluttered tab area.
We have to be careful if we’re using these methods to hide sensitive information. While we can use protection to prevent anyone we share the spreadsheet with from viewing the data, they could make a copy of the sheet. This will give them full access to the hidden data.
We do get some control over these issues, which I’ll discuss later and in the following guides.
How to hide and unhide rows and columns in sheets
For a single row or column, right click the label of the one you want to hide. For multiple rows and columns, highlight them as we did previously and then right-click. Select Hide row or Hide column.
You’ll then see up and down arrows where the hidden rows where, and left and right arrows where the columns were. Clicking on any of the arrows will reveal the hidden items. As the image below shows, hidden areas can also be denoted by the skipped labels.
how to hide an entire sheet
Right-click on the tab of the sheet you want to hide and select the Hide sheet option. To unhide the sheet there is no right-click option. Instead we have to venture into the top toolbar
Click View at the top and notice Hidden sheets and an indicator showing how many are hidden. Hovering over this will reveal the names of all those hidden. While clicking on the hidden hidden sheet’s name will unhide it.
Changing the width and height of rows and columns
We can resize rows and columns individually or in groups, including the entire sheet.
We can resize a row or column individually by clicking on its label and hovering over the right edge of a column, or the bottom edge of a row, until a two-way arrow appears. Then left click and drag to achieve the desired size.
Another method is to right-click on the row or column label and selecting the Resize the column or Resize the row option. This will present you with an input field to add the size to.
To resize multiple rows and columns: highlight the labels of all the rows or columns in question, then right-click and select the same resize option.
Why and how to duplicate a Google sheet
The main reason I duplicate sheets is to prevent having to repeat the styling and formatting. Then all I do is rename the sheet in the tab at the bottom.
Making a sheet template
If several of my sheets share some of the same features, but also differ in some ways. I often create a template that has all the common features. I then duplicate it as many times as needed, rename them, and then add each sheet’s unique features.
Copying a Google spreadsheet or range
While copying an entire spreadsheet is a simple process, there are a number of things to be wary of when copying and pasting ranges into another spreadsheet.
Copying a spreadsheet
If you already have the document open, from the File dropdown, click Make a copy. This will open a popup where you’ll be able to rename the file and import the share settings.
Alternatively: from the folder the file is in, right-click on the file and select Make a copy. This will make a copy in the same folder named Copy of [spreadsheet name].
Copying and pasting a range
This is where a little attention to detail is needed, depending on what our purpose is.
Any formula in the range being copied should be self contained, that is they must not rely on data from outside the range. If they do, we can only copy the values using the Paste special feature from the dropdown that appears when we right-click on the cell we want the top left of our range to appear.
This creates another problem: if we have any colour and borders applied to our range, none of it will be applied. Neither will any borders. Instead of me explaining what can and can’t be done, here’s a chronological list of what needs to be done:
- Highlight, right-click and copy the range in the original sheet
- In the new spreadsheet: right-click in the cell you want the pasting to begin
- Hover over Paste special and click Values only
- Repeat the above but this time select Format only
- * Reapply any borders manually
- * Reapply any row and column adjustments manually
*At the time of writing the options to paste borders and column widths don't work. This appears to be a bug on Google’s part which they have already fixed.
Well that’s the surface well and truly scratched. You’ll notice I’ve barely mentioned the two top toolbars yet. Instead I’ve focused on right-click options.
Quite a few of the features we can access by right clicking are also accessible from these toolbars. There is no best way, it's merely a personal preference.