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!
What is Google Sheets
The Editing Window
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
This immediately applies the correct formatting to the currency values, along with decimal points.
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.
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.
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.
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.
And that’s it. If we scroll down now, we will notice that the frozen row stays in place.
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.
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.
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.
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.
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.