Spreadsheets

|

July 1, 2020

Google Sheets 101: The 2020 Beginner's Guide to Online Spreadsheets

Side close-up of a person typing on their MacBook in front of a dark background.
SECTIONS
  1. What is Google Sheets
  2. Getting Started
  3. The Editing Window
  4. Managing Data
  5. Further Reading

Whether you are budgeting expenses or managing a complex group project, a spreadsheet can go a long way in organizing things properly. And, while MS Excel might have served well a decade ago, these days, there is no better spreadsheet application than Google Sheets.

Not only does it have features that cannot be found on an offline spreadsheet application, but with its intuitive UI and clean design, it is also far more accessible. And, with our guide, you can start using Google Sheets to its fullest extent today!

Sections

  1. What is Google Sheets
  2. Getting Started
  3. The Editing Window
  4. Managing Data
  5. Further Reading

What is Google Sheets?

Everyone has encountered MS Excel at least once. Maybe it was only once during a computer class at school, but the chances are that you have already seen a spreadsheet.

Google Sheets is an online, browser-based spreadsheet application. Along with Google Docs and Google Slides, it forms a suite of free-to-use office applications bundled with a simple Google Drive account.

Contrary to what you might believe, Google Sheets isn’t a "lightweight" version of Excel, but a full-fledged spreadsheet app that actually boasts of more functionality than even Microsoft’s own offering.

Power of the Cloud

Google Sheets is a cloud-based app. This means that your spreadsheets are stored on an online server that can be accessed on multiple devices from multiple accounts. You can start a sheet on your laptop and finish adding details on your workstation, using your mobile to make last-minute changes.

More importantly, your entire team can work on a single spreadsheet simultaneously, making collaboration a breeze. And don’t worry about it becoming a mess of conflicting edits; Google Sheets has options for customizing the access level of each user, besides keeping track of every change.

You can also forget about losing your data due to a sudden system crash as Google backs up the document on its servers every few seconds. Even if the computer you are working on explodes, you can immediately resume work exactly where you left on a different device.

Expressive Visualizations

Anyone who has ever made a presentation knows the value of visual information. Hard numbers might be what decide the effectiveness of a business proposal, but most people find a spreadsheet difficult to understand.

That is why every spreadsheet application needs tools to convert all that neatly organized data into a visually appealing format.

While in Excel you had your good old bar graphs and pie charts, in Google Sheets you have much, much more. With features like slicers and scorecard charts, you can get more out of your graphs and tables.

But, the most powerful visualization tool in Sheets is the Google dashboard. With a few clicks, you can turn your boring spreadsheet into a dynamic representation that conveys a wealth of information at a glance.

Getting Started

To get started with Google Sheets, you first need a Google account. In case you have never even used Gmail before, take a moment to signup with a simple form.

Once you have an account, simply head over to Google Sheets to get started with your first spreadsheet.

When you open Google Sheets for the first time, you are offered a tour of its features. Feel free to take the tour if you want (though our guide will teach you all that you need to know anyway).


Google Sheets dashboard with a "Welcome to Google Sheets" pop-up, offering a tour.


After dismissing the prompt you can finally see the ‘homepage’ of Google Sheets. It looks a little empty right now, but once you have created a few spreadsheets you can easily access and organize them from this screen.


Google Sheets dashboard with no spreadsheet files yet

As you can see, there are a few handy templates at the top to help you get started. Common use cases of a spreadsheet such as a to-do list or budgeting are already present for bootstrapping your progress.

For now though, let us get acquainted with the basics of using Google Sheets with a blank document.


Blank spreadsheet on Google Sheets platform with the top left corner cell selected by default


This view should be familiar to anyone who has used a spreadsheet before. If anything, the interface is actually simpler, as Google has opted for a cleaner approach than most offline spreadsheet applications.

The spreadsheet itself is composed of a grid of horizontal rows and vertical columns, creating ‘cells’ at every intersection. Each of these cells can hold any kind of data, ranging from simple numbers to names and even entire sentences.

Entering Data

Filling up these cells with data is a simple matter of clicking on a cell and typing it in. Even better, you can move from one cell to another without having to move your hand from the keyboard.

Hitting ‘enter’ after typing in the data takes you to the next cell down the column while tapping the ‘tab’ button takes you down the row. You can also use the arrow keys to freely jump between cells in any direction.

By default, a new sheet begins entering data from the very first cell on the top left corner; feel free to click elsewhere if you have a different layout in mind.

Formatting

Generally speaking, formatting works the same in Google Sheets as in any word processor like Google Docs. Drop down the ‘Format’ menu to get access to options such as font size, italics, font color, and alignment.

But, a spreadsheet also has some additional options that you may not have seen earlier. One of them is currency formatting.

Currency Formatting

Unlike a document, a spreadsheet is going to deal a lot with currency values (especially if you are making a budget), and without the proper formatting they end up looking a bit odd. In very large spreadsheets, the lack of good formatting can make it hard to parse information quickly.

For example, this is how a row of expenses looks like by default.


2 rows filled in a spreadsheet, one with headers that show item names, and the other with corresponding costs, formatted without the dollar sign


While we can go in and insert a dollar sign before each number, that approach is not advised. For one, it can quickly get tedious in larger sheets. Second, such a method is not reliable, as you might miss a few entries. But the most important reason is that manually adding a dollar symbol changes the number into a text entry, making it impossible to apply mathematical formulae on it (more on that later).

Fortunately, the method for currency formatting is rather straightforward. Select the row containing our currency values (by clicking on the row number itself, or click-dragging through the cells) and then click on the ‘$’ symbol in the toolbar above.


Same rows as before, except with the corresponding costs now possessing a dollar sign in front.

This immediately applies the correct formatting to the currency values, along with decimal points.

Alternating Colors

Another formatting feature applicable to spreadsheets is alternate colors.

To improve the readability of your tables, you can apply bands of alternating color as a background. To do so, head to the Format menu and select the ‘Alternate Colors’ option.


Spreadsheet with items as columns and costs as rows, highlighted for "Apply to range" on the Alternating colors tab


A box pops up with a variety of color schemes to choose from. Click whichever you like, and your table will become a lot more interesting to look at.


Same table as above, but now with pink and white alternating colors

Freezing Rows and Columns

A common issue faced when working on large spreadsheets is that the header row (where you put column titles) disappears once you have gone too far down the sheet. This forces you to scroll back up every few seconds simply to determine the identity of each column, which not only wastes time but also leads to errors.

To overcome this problem, Google Sheets allows you to ‘freeze’ a row (or even multiple rows). The frozen row will remain on the top no matter how far you scroll down. This can be applied on columns as well, for the situations when you are using the leftmost columns for denoting categories as well.

But how do you freeze a row? First, take a look at a normal table of values.


Spreadsheet with items as columns and costs as rows, as mentioned above

See that unnamed grey cell at the top-left corner? The thick horizontal and vertical borders are actually rulers that you can drag. Say we want to freeze the topmost header row. We will click and drag the horizontal border between rows 1 and 2.

Spreadsheet with items as columns and costs as rows. The first column with item headers is now frozen


And that’s it. If we scroll down now, we will notice that the frozen row stays in place.

Item header row #1 remaining as the first visible row and rows 6 onwards laying directly underneath

Editing and Permissions

Inserting new Rows and Columns

Often you might find yourself needing an additional column between two existing columns already filled with data. Before you start copy-pasting half the table to move it aside, let’s see how can insert new columns (or rows).

To begin, select any of the two columns you wish to insert your new columns between. (In case you are wondering how, just click on the column name. The same goes for selecting rows as well.)


With the column selected, right-click on it, to be presented with a drop-down menu. There are two options before you: ‘Insert 1 left’ or ‘Insert 1 right’. Clicking on the latter gives us this.



Notice that Google Sheets adjusts the column numbering automatically. This works with formulae as well (which we will work with later), so adding a new column or row does not mess up all those carefully constructed calculations populating your spreadsheet.

Resizing Rows and Columns

Sometimes, there are columns that need to be wider than others. In a spreadsheet holding employee information, for example, the name column must be wide enough to accommodate two words, even when other columns holding numeric data can much narrower.

Changing the size of rows and columns is actually quite easy. Hovering the cursor at the edge of the name cells (the alphabets for the columns and the numbers for the rows) changes it to a double-sided arrow, which can then be click-dragged to resize the entire row or column. This is similar to how one resizes windows.

In this sheet, we have increased the width of the D column and shrunk the others.


Setting Permissions

When collaborating with a bunch of different people on a single spreadsheet, it can be a bit difficult to keep track of what changes were made by whom. Often, the additions by one member can overwrite the work already done by another, leading to confusion.

That is why Google Sheets lets you set the accessibility of individual cells in addition to restricting access to the whole document itself.

You can decide who gets to make changes to a designated range of cells (or a whole spreadsheet). This is very useful for important columns that hold the field names of the table, as they are usually not meant to be tampered with after the spreadsheet has been created.

Managing Data

Spreadsheets differ from word processors in not only how they are presented, but also in how they handle data. At the very basic level, Google Sheets differentiates between numbers and text, allowing you to perform automated calculations with your data.

Here, it’s important to know all the different data types to organize your spreadsheets more efficiently and use your data.

Basic Functions

One of the most advanced – and the most useful – features of Google Sheets are functions. Functions enable you to set up automatic calculations in your spreadsheet. These calculations are also self-updating; any changes to the data (or even new entries) result in re-evaluating the result.

But you don’t have to be a maths prodigy to take advantage of dynamic calculations in your spreadsheet. Using Google Sheets’ built-in functions, you can implement simple calculations without typing a single formula.

Clicking on the formula drop-down menu gives you access to five most commonly needed formulae: SUM, AVERAGE, COUNT, MIN, MAX. From adding up numbers to finding the maximum value in a large range, these formulae include all the common functions you would require in a usual spreadsheet.

Using any of these functions is quite easy. Just select the range of cells you wish to apply the formula to, and select it from the menu.

As you can see, the appropriate formula appears in the cell (and in the formula bar above). Tap enter and the result will appear. Try changing the values of any of the entries, and the total will change to match the correct result by itself.

Further Reading

While Google Sheets is very intuitive to get started with, it has a fair share of advanced features that can help you get more out of your spreadsheets. Learn how to harness the complete suite of utilities available with Google Sheets in our advanced guide.

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.