burger icon to open nav
X

Introduction to google sheets

I doubt anyone needs telling what they are, or about their capabilities, some may even regard them as the eighth wonder of the world, but up until recently: the only way we could access them from different devices was by carrying them around on USB drives.

With Google sheets, that is no longer the case, and just in case you’re wondering if they’re a viable alternative to Excel, here’s a couple of examples.

//img duos 2 sheet examples = sheet2 sheet3
Example of a Google recipe
Example of a Google Doc resume

Hopefully that’s grabbed your attention, and while this guide is only meant as a brief overview and a look at the basics, Hopefully: when you've mastered them, you'll be ready to move on to the more advanced guides.

Obviously this doesn’t go in too deep, but it's certainly enough to get you started, and there’s more advanced guides to follow.

What are Google sheets?

Few people realize that Google sheets are actual web pages, so this question is not as dumb as it seems.

Don’t worry about the technicalities. We can work on them just as we would with a desktop program, and download them in various formats, including Excel and CSV,

But this is only a small part of their overall functionality. We can share a link and invite multiple editors, email them in various formats, even publish them to the web for the world to see.

Although a little advanced at this stage, we can also write programs to give them greater functionality. If you’re familiar with spreadsheets, think macros on steroids.

Are you Google sheet ready?

If you’ve got a Google account you’re on your way. Sheets are part of the Google Drive range of file creation applications, as is gmail.

If you don’t have a Google account, or are unfamiliar with Drive, you may want to start with earlier guides that detail how to create an account and open up Google Drive. Then return here.

At this point I’ll assume you’re in your Drive and are in the folder you want to begin learning about Google sheets..

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.

Popout when inserting 1 row in a Google sheet

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.

Dropdown that appears when you highlight multiple rows in Google sheets and then right-click

At the very bottom of the sheet, we have one more option to add rows. The preset is 1000, but this can be edited.

Option at the bottom of a Google sheet to add more rows

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.

popup that appears when you click on a Google sheet tab

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.

Three tabs at the bottom of a Google sheet that have been coloured using the tab popup option

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.

A Google sheet that has a row and a column hidden

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.

The popup for resizing columns in a Google sheet

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.

The popup for copying a Google spreadsheet

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.

The paste special dropdown in a Google sheet

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:

  1. Highlight, right-click and copy the range in the original sheet
  2. In the new spreadsheet: right-click in the cell you want the pasting to begin
  3. Hover over Paste special and click Values only
  4. Repeat the above but this time select Format only
  5. * Reapply any borders manually
  6. * 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.

Coming up

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.

The main features of Google sheets top toolbar

For now the focus remains on the actual sheet presentation features found in the various toolbar options, rather than its numerous functions, which will come in the more advanced guides.

I’m also going to leave sharing, emailing, and downloading until the next section.

Google sheets toolbars

Using version history to revert back to previous versions

We all make mistakes and spreadsheet errors are no exception, fortunately we can use version history to see when errors occurred and restore a previous version.

If a sheet has multiple editors, we can also see by whom the mistakes were made. Or simply view other people's activity. Versions are separated by time and date, and can also be named to make them more identifiable.

How to view a sheet’s version history

Click File in the top toolbar and hover over Version history. You’ll then have the options to Name current version and See version history.

Selecting the latter will open up a sidebar on the right showing all the versions. Clicking on any of them will open them up.

The version history sidebar in a Google sheet

How to restore a previous version of a sheet

Restore this version button in Google sheets

Once a previous version is opened, at the very top you’ll see the option to Restore this version. You can also restore previous versions from the versions sidebar, along with the option of naming the version.

As some versions may have the same date, naming versions is useful if you want to discuss a particular version with team members

Clicking the Today back arrow will close the version history.

The sheet View dropdown

The options contained under the View label focus around the sheet’s appearance, which is currently our primary concern, as opposed to its functionality.

How to freeze rows and columns

While columns can be frozen, the most common use case is to freeze rows of headings to prevent us having to scroll back up to see what the hell it is we’re looking at.

After clicking View, and then hovering over Freeze, you’ll be presented with options to freeze rows and columns.

The paste special dropdown in a Google sheet

Frozen rows and columns are denoted by a thick border, below or to the right respectively. To unfreeze them navigate back to Freeze and select either No rows or No columns from the options.

Note that freezing only affects the current sheet, not the entire spreadsheet

Showing and hiding specific sheet features

Hovering over Show, having already clicked View, gives us several options for making further adjustments to the sheet’s look and feel

The image below shows us what features we can change when following this path.

The 'Show' options that can be turned on and off in a Google sheet

The presence or absence of a checkmark to the left of each feature denotes their status. Clicking on the feature toggles it on or off.

Showing and hiding gridlines

The gridlines are the border type lines that surround each cell. By default, these are set to visible. Whilever we’re editing the sheet, this is probably the best setting. But for presentational purposes, it's sometimes better to hide them.

Later I’ll discuss cell borders, which are a different feature to gridlines and aren’t turned on and off in the same way.

Showing and hiding formulae and formula bar

If an active cell, i.e. the one we’ve clicked on, contains a formulae, it will show in the formula bar just above the sheet cells. Otherwise the formula bar will display whatever is in the cell.

Google sheets format options

With the odd exception, much of what we see in the Format dropdown concerns basic styling which is also available in the second toolbar. Some other features are beyond where we need to go at this point and will be covered later.

Alternating row colours

Along with adding a touch of professionalism to our sheet, this feature is a real time saver. Normally giving us a dark heading and then allowing us to add alternating colours to the rows below

Example of alternate colours in a Google sheet

Here’s a look at a sheet with the alternating colours applied. To begin the process: highlight all the cells to be included, click Format, then alternating colours. A sidebar will open on the right.

The sefault options for alternating colours in a Google sheet

Google provides us with a range of preset colour schemes. All of which have a dark header and subsequent alternating rows consisting of a lighter shade of the header and white.

However: we can change any of the colours in the sidebar, just below the presets you’ll notice the option to customize your colours.

The custom styles options for alternating colours in a Google sheet

Once you’re satisfied with your choice, click the green Done button.

What’s next?

As the rest of the options available to us in the top toolbar will be discussed in future guides, it’s now time to move down to the second one.

The lower toolbar: Sheet, range, and cell formatting options

Much of what we see here involves nothing more than basic text editing similar to that found in most text editors. However: if you’re new to spreadsheets, there may be some features you’re not aware of.

Customizing some or all of the cells in a sheet

We already know that when applying a specific feature, it is applied to all highlighted cells. But if you want to apply the same feature to all the cells, there is a shortcut.

We could highlight all columns and then apply a feature to all the cells, but there’s an even shorter way. In the top corner of the actual sheet, where the row and column headings meet, is an empty label. Click that to highlight every cell on the sheet.

If you want to apply settings to a range of cells: click on the top left most cell and drag your cursor to bottom right of the range

A highlighted range in a Google sheet

Vertical alignment

A niddle aligned range in a Google sheet

Sometimes, in the case of headings for example, we want to make the cells larger, and vertically centred text certainly improves the appearance. As the image above shows.

Three cells showing top aligned, middle aligned, and bottom aligned text in a Google sheet

To vertically position text: make the row, column, range or cell active and then open the vertical alignment dropdown. You’ll then see the options to set the alignment.

The vertical alignment dropdown in a Google sheet toolbar

Cell borders

Opening the border dropdown gives us a wide range of border options, with the two icons to the right of the borders allowing us to set the border colour and thickness.

The border dropdown in a Google sheet toolbar

In the image below I’ve set the cell border thickness to thiick. Although we can set them to a similar thickness to the gridlines and distinguish them using the cell border colour.

Three border styles in a Google sheet

As I mentioned earlier, we can remove the gridlines but keep the borders.

Three border styles with the gridlines hidden in a Google sheet

Note that if you add borders and then decide to make any changes, especially to the colour and thickness, you may have to remove the borders altogether by clicking the bottom right option in the dropdown. Then reapply them.

Merging cells

While resizing cells does give us some flexibility, it may not be enough. This is where cell merging comes in handy. We can merge the cells vertically and horizontally.

A range of merged cells with borders in a Google sheet

In the above image I’ve also added borders to highlight the merge effects.

To merge cells, highlight the cells you want to merge then click the merge icon to the right of the borders icon. The cells will merge instantly, there is no dropdown.

Note the merge icon will remain greyed out until multiple cells have been highlighted.

How to email, download, share and insert a Google sheet into a google doc

It’s not always the case that we want to keep our sheets private. Financial reports, and projections are just two of the many reasons why we may want to give others access to at least parts of our spreadsheets.

Luckily there’s no shortage of ways to share our files,

How to Email a Google sheet

Normally when emailing, in whatever format, we send the entire spreadsheet. So if that’s not your desire, say for example you only want to send one sheet, you may need to copy the sheet into another spreadsheet first.

Once your file is ready for emailing, open the File dropdown and hover over Email. Then select Email this file.

Aside from the obvious input fields, namely recipient’s email address, subject, and message, at the bottom of the popout you’ll see which format the file is to be sent in. The default is PDF. clicking this will give you more options.

Popout shown when the option to email this file and choose the format in a Google sheet

Of the file format options, only PDF is not editable by the recipient and the send button will not become active until the recipient's email address has been added.

Emailing a spreadsheet to a group

If you have multiple recipients and they are part of a group set up by a business administrator, you can use the group email address to send to all recipients at once.

How to download a Google sheet

Being as all my files are cloud based, this option is something I never use. Nonetheless: as downloading a sheet is a simple process, I’ll go over it just in case.

As before: open the File dropdown, only this time hover over Download. There you’ll see the range of file format options

The download popout and format types in a Google sheet

Once you choose a file format, you’ll be prompted for a download location. This is the same for all file formats apart from PDF. In this case another tab will open with further options.

How to share and collaborate on a Google sheet

This is where Google sheets really begin to excel, if you’ll pardon the pun. Both as an alternative to emailing, and to allow others to edit the file.

As we already know, Drive files are actual web pages. When sharing, recipients receive a link to the entire spreadsheet. They then have options, also known as privileges, which we set. They are: editing, commenting, or viewing.

We can access the share feature from the file dropdown, or by clicking the blue Share burron at the top right. After this our options differ slightly depending on if we’re using a personal account or one of the business versions of Workspace, formerly G Suite.

For now I’m just going to cover the basics. The guide to sharing Drive files goes into much more detail. So for now, click the blue share button.

The share with people and groups dialog box in a Google spreadsheet

Notice at the bottom where it says Restricted. This means only people whose email address we add can access the sheet. Clicking on Change to anyone with the linkthis will allow us to copy the link and share the file on social media or by email.

How to set the share feature to 'anyone with the link' and set privileges in a Google spreadsheet

Notice the current access privilege on the right, currently set to viewer. Click on this to change the privilege.

If we were to keep the Restricted setting, we’d need to add at least one email address to Add people of groups input field.

The share with people and groups when set to 'Restricted' in a Google spreadsheet

Once we’ve added an email address, we can then set the access level on the right. On this occasion the default setting is Editor. Once you click Send, the recipient will receive a notification email with a link to the file.

They’ll also be able to access the file directly from their Google Drive Shared with me

How to add a Google sheet range to a Google Doc

This is an extremely useful feature when creating any type of report, especially when using charts created in a sheet, and can be set to update whenever the original data is edited.

First of all we need to highlight, right-click and copy the range. Then paste it into our document. After pasting we get the following prompt

The dialog box that appears in a Google Doc when pasting a Google sheet range

Note the update feature only applies to this document. If we create a PDF, and then update the original data we’ll have to create a new PDF.

Another thing to note is that while we can edit the data in the document, these edits won’t be reflected in the spreadsheet.

How to publish a sheet to the web

We can either publish to the web using a link, or embed our sheet into a web page, Apart from one factor, which is totally irrelevant to this guide, there doesn’t appear to be much difference between publishing a link to the web and allowing access to anyone with the link.

Publishing to the web using a link

We actually begin with the same process of setting the share options to anyone with the link and setting the access level.

Then we need to open the File dropdown, hover over Share and select Publish to the web. This will open up the following window.

The dialog box when publishing a Google sheet to the web

Note the Entire document heading. Clicking this will give us the chance to set which sheet we publish.

The dialog box for choosing which sheet to publish to the web when using a link

To the right of the Entire document heading you’ll see another heading titled Web page. This allows us to set the format the sheet is published in.

Embedding a spreadsheet or sheet into a web page

The embed option allows us to copy and paste our entire spreadsheet or a single page. If you select this option:

  1. Select the entire document or specific sheet
  2. Click Publish
  3. Confirm you want to publish
  4. Copy the HTML code that appears
  5. Paste the HTML code into your web page

How to unpublish

We can unpublish as easily as we publish. Here;s how it goes

  1. Once again navigate to Publish to the web
  2. At the bottom of the popup click Published content and settings
  3. Click Unpublish
  4. Confirm you want to unpublish

Google forms: the next step along the way

Google forms, where you can create surveys and quizzes, then store the responses in a Google sheet, is the logical next step.