If you know how to utilize spreadsheets, they can be a very useful tool for a variety of vocations. To accomplish anything significant in the past, you would have to spend hundreds of dollars on software and countless hours of research. With Google Sheets and our step-by-step guide, you can be up and running in minutes for free!
This Google Spreadsheet guide will take you from a complete beginner to a forthright, proficient, expert-level user. Google Spreadsheets is a fantastic tool for everything from digital marketing to financial modeling, project planning to data analysis, and just about any other task that involves data collection and analysis.
And, if you're (somewhat) new to Google Spreadsheet, learning how to use it right pays off big time. This tutorial will quickly transform you from a rookie to master!
If you're new to Google Spreadsheets, then you'd best start from the beginning. Let's get right to it then.
HOW TO USE GOOGLE SPREADSHEETS
What is Google Spreadsheet?
Google Sheets is a cloud-based spreadsheet program that is available for free. That is, you open it in your browser tab as if it were a traditional webpage, but it has all of the features of a full spreadsheet application for performing advanced data analysis. It's a true win-win situation.
What is the difference between Google Spreadsheet and Excel?
You have no doubt heard about Microsoft Excel, the undisputed long reigning champion of the spreadsheet world. Google Sheets is a spreadsheet application with a lot of extra features. It looks and works like any other spreadsheet software, but because it's an internet application, it has a lot more features than most spreadsheet applications.
Here are some of the factors that contribute to its superiority:
- It's an internet spreadsheet that you can access from anywhere, so you won't have to worry about leaving your spreadsheet files at home.
- It runs on any device, featuring iOS and Android mobile apps as well as a web-based primary app.
- Google Sheets is a free online file, document, and presentation sharing tool that comes packaged with Google Drive, Docs, and Slides.
- It has almost all of the same spreadsheet capabilities as Excel, so if you're familiar with Excel, you'll be right at home in Google Sheets.
- Add-ons can be downloaded, created, and custom code can be written. One particular addon you can download is Kulfi Forms – an online form builder with awesome features.
- Because it's online, you can use it to automatically collect data from your spreadsheet and perform nearly anything else, even if your spreadsheet isn't online.
Why use Google Spreadsheet?
Here are a few reasons for a start:
- It's completely free!
- It's collaborative, so everyone on your team can see and work on the same spreadsheet at the same time.
- It has enough tools to perform extensive analysis, but it's also quite user-friendly.
Popular Google Spreadsheet Terms
To begin, let's go through some spreadsheet terms and concepts so you can better comprehend the words in this article:
- Cell: In a spreadsheet, a cell is a single data point or element.
- Column: A column is a row of cells arranged vertically.
- Row: A row is a column of cells that runs horizontally.
- Range: A range is a collection of cells that spans a row, column, or both.
- Function: A spreadsheet app's built-in action for calculating cell, row, column, or range values, manipulating data, and more.
- Formula: A formula is a set of functions, cells, rows, columns, and ranges that are combined to produce a certain outcome.
- Worksheet (Sheet): Your spreadsheet's named groups of rows and columns; one spreadsheet can have numerous sheets.
- Spreadsheet: The complete document including your worksheets is referred to as a spreadsheet.
Spreadsheets are based on the following principle:
The intersection of column E and row 10 occurs in only one cell. As a result, we may generate a unique reference to this cell, E10, by combining the column letter and row number. Now, if we need to refer to this cell, such as to access data in it, we use the address E10.
Let's dive in and start creating our own spreadsheets now that we have this information.
1. Make a spreadsheet and colonize it with information
The best part about Google Sheets is that it's completely free and compatible with any device, making it simple to follow along with the guides in this article. A Google free account and a web browser (or the Google Sheets app on your iOS or Android device) are all you'll need. Go to sheets.google.com on your Mac or PC, and you'll be well on your way to getting started.
In Google Sheets, there are three ways to make a new spreadsheet:
- Select “Google Sheets” from the blue “NEW” icon on your Google Drive dashboard.
- From within a spreadsheet, pick “File > New Spreadsheet” from the menu bar.
- On the Google Sheets homepage, choose “Blank” or choose a template.
This will start a fresh spreadsheet from scratch (or an auto-filled template if you select one of those). However, for this lesson, you should begin with a blank spreadsheet.
With recognizable text editing symbols and tabs for additional sheets, the Google Sheets UI should remind you of at least one other spreadsheet tool you've seen or used before.
The only distinction is that Google has decreased the amount of clutter and UI components visible. As a result, your initial step should be self-evident: add some data!
Now lets Add Data to Your Spreadsheet
The first noticeable thing as you look around the white-and-grey grid that takes up the majority of your screen is a blue border around the selected cell or cells.
If begin to type in a new spreadsheet, you'll notice that your data starts occupying the selected cell right away—usually the top left cell. When adding data, there's no need to double-click cells, and you won't even have need for your mouse much.
In a spreadsheet, a cell is a single square that is grouped into rows and columns with number and letter IDs, respectively. Each cell should have only one value, word, or piece of information in it.
Select whatever cell you'd like, then type something into it. When you've finished entering data into a cell, you have four options:
- To save the information and go on to the next row, press ENTER.
- To save the data, press TAB and then move to the next row to the right.
- Use your keyboard's ARROW KEYS to move 1 cell in that direction (up, down, left, and right).
- To get to a certain cell, simply click it.
Copy & Paste is self-explanatory, but there are instances when you try to copy a set of data from a website or PDF, and it simply pastes into one cell or formats everything with the original formatting. To avoid obtaining weird pasted data in your spreadsheet, seek for data that is truly in an HTML table.
Note: If you're trying to paste data into a cell, make sure you only click once in the cell; you don't want all of the data in one cell, right? That's what follows when you double-click a cell instead of just clicking it once.
Don't worry if your data is weirdly formatted; we'll remedy that in the next step!
It's also straightforward to import a file. You can either import the data straight into the current spreadsheet, create a new spreadsheet, or replace a sheet (i.e. a single tab) with it.
CSV (comma separated values), XLS, and XLSX are the most typical files you'll import (files from Microsoft Excel). Go to FILE > IMPORT > UPLOAD to import a file from a location other than Google Drive.
Since you'll use this one a lot once you've set up formulas in your spreadsheets, dragging to copy a cell value requires some explanation.
You can conduct a variety of operations by dragging the small blue dot (shown below) in the bottom-right corner of a marked cell across or down a range of cells.
This feature can be used in a variety of ways:
- Copying the contents of a cell to a group of adjacent cells (including formatting)
- Copying the “Formula” of a cell to other cells (this is an advanced feature that we'll go over in depth later)
- Organizing text data into an ordered list.
You'll be able to understand spreadsheets once you've grasped this concept. The rest is really minor details!
Let us sight an example of how you might go about making an ordered list: Try typing Frontliner 1 into Cell A1, then dragging the little blue dot in the bottom-right corner of the selected cell down or across any number of adjacent cells.
This dragging operation would just duplicate “Frontliner ” to whichever cells you drag over if there was no number following Frontliner. However, because the number is present, Sheets knows to add +1 to the following cell.
Let's say you've copied, pasted, imported, or written in a significant amount of data, and your spreadsheet is in good shape.
Now that we understand how to make a spreadsheet and adding data, how do we use this?
2. Formatting Data for Easy Viewing
You'll want to alter and format your data if you'll be keeping track of spending, logging student grades, or keeping track of customers in a homebrew CRM.
Above your first cell, you'll find the fundamental formatting options in Google Sheets. You can see a summary and shortcut key for each icon by hovering over it when you're working on a sheet.
Undo / Redo, Print, Styling, and Font Settings all work in the same way that your preferred word processor does. Treat it like any other document, with the same shortcut keys!
After you've entered data into your spreadsheet, you may need to make some formatting changes before you begin working with it. Although Google Sheets will frequently format your data correctly, it may be necessary to update it manually on occasion. Much of this may be done simply from the toolbar, but the Format tab also has advanced choices.
You can choose from a simple decimal, %, dates, currencies, and many other options when it comes to numbers. The main toolbar can also be used to increase or reduce the amount of decimal places shown. It's important to note that this just affects the visible number and does not change or round the real value.
Italic, bold, underline, and strikethrough, as well as font sizes, font kinds, and alignment, are all available for normal text. Formatting of cells is also possible. You can alter the background color, combine or unmerge cells, and add borders to the worksheet.
To save time formatting, click on the letter or number at the top or left of the screen to pick a complete column or row.
In Google Sheets, how do you merge cells?
Merging cells is one of the most fundamental spreadsheet formatting techniques, and it should be one of the first things you learn. Fortunately, it's also one of the simplest tasks in the application.
Simply pick multiple cells and click the merge icon in the upper middle of the toolbar. The icon is greyed out unless you have more than one cell selected, and it features two arrows pointing together.
Now that you've mastered the art of adding and formatting data, it's time to get to work computing sums, averages, and other statistics from your data!
3. How to use formulas to add, average, and filter data
Like most spreadsheet software, Google Sheets contains a number of built-in formulas for performing a variety of statistical and data manipulation activities. You can also connect jobs together by combining formulas to construct more sophisticated calculations. If you're used to crunching numbers in Excel, you'll find that most of the same formulas work on Google Sheets.
A formula is a piece of code that performs basic arithmetic on values or cells that you provide. A function is used to execute more complicated operations, such as calculating simple amounts, making payments on annuity investments, or even retrieving data from the internet.
The most important thing to remember as a novice is that all formulas and features begin with the letter =. This tells Google Sheets that you're entering something else than text or a number.
Formulas are the most straightforward to utilize. After you've written =, you can type A1, A2, etc., to input specific numbers or refer to values in other cells. To conduct fundamental arithmetic, use the +, -, *, and / symbols to alter these values.
Functions are similar to each other, however they necessitate more precise input. We won't go over every function in this Google Sheets tutorial (there are hundreds), but you can get a complete list by clicking the ∑ symbol in the upper right corner. You can also look for them by typing the function name in any cell following the symbol.
What is the SUM function and how do I use it?
Let's have a look at the SUM function to get a sense of how things work. This method simply combines all of the numbers in a range together. It appears as follows: =TOTAL (values or range). These can be particular cells or integers, such as =SUM(10, A2, A3, 15), or a range, such as =SUM(10, A2, A3, 15). (A1:A10).When you type =SUM(, you can select a range with your mouse, then hit Enter when you're done and watch the program do the job for you!
Learn more about specific functions by clicking the ∑ symbol, selecting the function, and then clicking Learn more at the bottom.
Advanced instruction and advice on how to utilize Google Sheets
One of the nicest (and terrible) aspects of spreadsheets is how versatile they are. There are simply too many options to cover in this basic primer to using Google Sheets, but we wanted to offer a few more items that even novices would find useful.
How to make charts in Google Spreadsheet
Although we've included it as an advanced tip, creating charts in Google Sheets is actually quite simple. There are also many different chart kinds to pick from, all of which are fully configurable.
Select the cells you want to use for the graph, incorporating headers if possible, once you have your data. Then, either click the Insert chart button in the top right corner or select Chart from the Insert menu at the top of the page.
Google Sheets will analyze the information and choose the most appropriate graph type for you. In the Chart Editor panel on the right-hand side of the screen, you can switch to any other form of graph. You may also add new series to the chart, change the labels, and make a variety of other changes to the chart itself.
You can adjust the layout of the chart on the Customize tab of the Chart Editor if you really want to go into the muck. You can rename axes, change the chart's style and colors, and make a slew of other changes. Playing around with the various options is the best way to learn about them.
Google Sheets: How to Share and Collaborate
Apart from being free, Google Sheets has one of the most significant advantages over Excel and other competitors: it's simple to interact with others on the same page.Worry not about document renaming and complicated version control systems; Google Sheets takes care of it all. In addition, anyone may view the spreadsheet from almost any device, even if it's already being edited by another person!
What is the best way to share a Google Sheets spreadsheet?
Giving other users access to Google Sheets is the first step toward cooperation. All new spreadsheets are set to private originally, and only your account can access them.
To share a Google Sheets spreadsheet, simply click the large Share button in the top right corner. You can then choose between manually entering email addresses to share with or using a shareable link instead.
If you're sharing with a larger group of individuals, shareable links are more efficient, but they can make the document accessible to anybody who has the link. However, selecting the Restricted option will limit access to the document by only making it accessible to email addresses you shared it with.
Make sure you specify the correct permissions on the right-hand side, regardless of whatever option you select.
The following are the permissions and their definitions:
- Viewer: The document can only be viewed in read-only mode by the viewer.
- Editor: All features of the spreadsheet can be changed and edited by the editor.
- Commenter: Only sees and adds comments to the document as a commenter.
Once the document has been shared, you may also update or remove permissions from the same menu.
Sharing with your Apps and Devices
Take advantage of these useful add-ons to ease your spreadsheet workflows and real-time data sharing:
- Apps for Google Docs on mobile devices. The Google Sheets mobile app allows you to browse and update your spreadsheets on the move, share links, and add users. It's a good complement to the web app, but not a replacement for it.
- Google Drive can be synced to your computer. You may effortlessly upload files from your local desktop environment to your Google Drive account. This allows you to share them with your teammates and instantly import them into spreadsheets and other documents.
- A third party addon like Kulfi Forms can help you create online survey forms, questionnaires, e-signatures, compute research data, etc., right there on your Google Spreadsheet. It is easy to use and available to use for free. You can learn more about Kulfi Forms Google Spreadsheet addon here.
Offline Mode for Your Spreadsheet
If you've enjoyed what you've seen thus far but are concerned that you won't be able to use Sheets without a connection, have no worry. When you rejoin to the internet, Google Sheets' “Offline Mode” will instantly sync your modifications to the document.
This is excellent in any situation where you'd need to use Google Sheets as if it were a desktop application, such as on a plane or a road trip.
What you'll need is the following:
- Google Drive Chrome Web App
- Google Drive Sync
- Google Chrome
The instructions for setting up your offline sync are simple, but the most of the work is done by downloading and using the three essential components listed above.
It's time to fill your spreadsheet with data now that you know how to create one. In an online spreadsheet, the ideal way to do this is using a form creator such as Kulfi Forms addon to collect data and save it straight to your spreadsheet.