Google Sheets 101: The Beginner’s Guide to Online Spreadsheets

Google Sheets is a spreadsheet app on steroids. It looks and functions much like any other spreadsheet tool, but because it ‘s an on-line app, it offers much more than most spreadsheet tools. here are some of the things that make it so much better :

• It’s a web-based spreadsheet that you can use anywhere—no more forgetting your spreadsheet files at home.
• It works from any device, with mobile apps for iOS and Android along with its web-based core app.
• Google Sheets is free, and it’s bundled with Google Drive, Docs, and Slides to share files, documents, and presentations online.
• It includes almost all of the same spreadsheet functions—if you know how to use Excel, you’ll feel at home in Google Sheets.
• You can download add-ons, create your own, and write custom code.
• It’s online, so you can gather data with your spreadsheet automatically and do almost anything you want, even when your spreadsheet isn’t open.

Whether you ’ re a spreadsheet novice or an Excel seasoned looking for a better way to collaborate, this bible will help you get the most out of Google Sheets. We ‘ll start out with the basics in this chapter—then keep reading to learn Google Sheets ‘ promote features, find its best add-ons, and learn how to build your own .
concerned in writing your own scripts for Google Sheets ? We ‘ll dig into those in chapter 8 with tutorials on writing Google Apps Script.

Getting Started with Google Sheets

The best room to learn a tool like Sheets is to dive straight in. In this chapter, you ‘ll learn how to :

Common Spreadsheet Terms

To kick things off, let ‘s cover some spreadsheet terminology to help you understand this the terms in this book :

• Cell: A single data point or element in a spreadsheet.
• Column: A vertical set of cells.
• Row: A horizontal set of cells.
• Range: A selection of cells extending across a row, column, or both.
• Function: A built-in operation from the spreadsheet app, which can be used to calculate cell, row, column, or range values, manipulate data, and more.
• Formula: The combination of functions, cells, rows, columns, and ranges used to obtain a specific result.
• Worksheet (Sheet): The named sets of rows and columns making up your spreadsheet; one spreadsheet can have multiple sheets
• Spreadsheet: The entire document containing your worksheets

If you ‘ve never used Google Sheets—or, specially if you ‘ve never used a spreadsheet before—be certain to check out Google ‘s Getting Started Guide for Sheets. You may besides want to bookmark Google ‘s spreadsheet function number as a quick reference.
With that cognition in hand, let ‘s prima donna in and start building our own spreadsheets .

1. Create a Spreadsheet and Fill It With Data

The best character about Google Sheets is that it ‘s barren and it works on any device—which makes it comfortable to follow along with the tutorials in this book. All you ‘ll need is a web browser ( or the Google Sheets app on your io or Android device ), and a unblock Google account. On your Mac or personal computer, head over to sheets.google.com, and you ‘re ready to get started .
There are 3 ways to create a modern spreadsheet in Google Sheets :

1. Click the red “NEW” button on your your Google Drive dashboard and select “Google Sheets”
2. Open the menu from within a spreadsheet and select “File > New Spreadsheet”
3. Click “Blank” or select a template on the Google Sheets homepage

This will create a fresh blank spreadsheet ( or a pre-populated template if you choose one of those ). For this tutorial, though, you should start with a blank spreadsheet .
The Google Sheets interface should remind you of at least one other spreadsheet app you ’ ve seen before, with familiar text editing icons and tabs for extra sheets .

The only remainder is that Google has reduced the clutter and count of expose interface elements. indeed your first job should be obvious : Add some data !

Adding Data to Your Spreadsheet

Look around the white-and-grey grid that occupies most of your screen, and the beginning thing you ’ ll notice is a aristocratic outline around the selected cell or cells .
equally soon as you open a new spreadsheet, if you good start typing you ’ ll see that your data starts populating the selected cell immediately—usually the top leave cellular telephone. There ‘s no want to double suction stop cells when you add information, and not much need to use your mouse .
An individual square in a spreadsheet is called a cell ; they ‘re organized into rows and column with number and letter IDs, respectively. Each cell should contain one measure, word, or slice of data.
Feel free to select any cell you ’ d like, then go ahead and type something in. When you ’ re done entering data into a cell, you can do one of 4 things :

1. Press ENTER to save the data and move to the beginning of the next row
2. Press TAB to save the data and move to the right in the same row
3. Use the ARROW KEYS on your keyboard (up, down, left, and right) to move 1 cell in that direction
4. Click any cell to jump directly to that cell

If you don ’ t want to type in everything manually, you can besides add data to your Sheet en masse via a few unlike methods :

1. Copy and paste a list of text or numbers into your spreadsheet
2. Copy and paste an HTML table from a website
3. Import an existing spreadsheet in csv, xls, xlsx and other formats
4. Copy any value in a cell across a range of cells via a click and drag

Copy & Paste is pretty self-explanatory, but there are times when you ’ ll judge to copy a “ spreadsheet-y ” set of data from a web site or PDF, and it will fair paste into one cellular telephone or format everything with the original style. Try looking for data that ’ s actually in an HTML table ( like movie data from IMDB, for exemplar ) to avoid getting funky pasted data in your spreadsheet .
Note: Make sure you only click once on a cell before pasting data, so Google Sheets will turn it into a list with each item in its own cell. If you double-click on a cell, Google Sheets will paste all the datum into one cellular telephone which is probable not what you want.

If you do end up with curiously formatted data, do n’t worry : we ’ ll fix that in the next section !
Importing a file is childlike equally well. You can either import directly into the current spreadsheet, create a new spreadsheet, or replace a sheet ( i.e. an person yellow journalism ) with the imported datum .
The most coarse files you ’ ll import are CSV ( comma separated values ) or XLS and XLSX ( files from Microsoft Excel ). To import a file from outside of your Google Drive, go to the FILE > IMPORT > UPLOAD menu .

I prefer to import the datum into a new sheet every time to keep my old data and newfangled imported data discriminate. alternatively, if you have a Google Sheet ( or a CSV, XLS, or other spreadsheet file ) saved in your Google Drive report, you can import that directly into your spreadsheet using the lapp process—just search your drive from the import window .
Dragging to copy a cell value needs a act of explanation, because you ’ ll habit this one a set once you ’ ve set up formulas in your spreadsheets .
By dragging the small blue dot ( pictured below ) in the bottom-right corner of a highlighted cell across or down a rate of cells, you can perform a issue of different functions .

There are a act of ways you could use this feature :

1. Copying a cell’s data to a number of neighboring cells (including formatting)
2. Copying a cell’s “Formula” to neighboring cells (this is an advanced feature, and we’ll cover it in detail later)
3. Creating an ordered list of text data

here ’ s an exercise of how to creating an order list might work : Try adding the text Contestant 1 to Cell A1, then clicking and dragging the little blue point in the bottom-right corner of the highlighted cell either down or across any number of neighboring cells .

If there was no number after Contestant, this dragging action would just copy “ Contestant ” to any cells you drag over. But because the numeral is there, Sheets knows to increment the future cellular telephone +1 .
Let ’ s assume that you have either copied, pasted, imported, or typed-in a good ball of data, and that your spreadsheet is looking pretty healthy .
now, How can we use this data ?

2. Format Data for Easy Viewing

Whether you ’ re chase expenses, recording students ’ grades, or keeping track of customers in a home brew CRM ( as we ‘ll build in chapter 3 ), you ‘ll want to manipulate and format your data .
The basic format options in Google Sheets are available above your first cell. They ‘re labeled in the picture below, but for quick reference while you ‘re working on a sheet, just hover over an picture to see its description and shortcut keystone .

print, Undo / Redo, and the Font Settings / Styling function similarly to what you ‘d expect from your favorite password processor. The shortcut keys are the lapp equally well, therefore equitable treat it like you ’ rhenium editing any other document !
As for everything else, the best way to show you how everything works is to dive right into an exercise .
I ’ m going to create a promptly list of potential breakfast options for tomorrow good morning, along with their ingredients, counts, prices, and links to YouTube videos for how to make them ( who knew you could make a 3-minute television about scrambled eggs ? ) .

It ’ south functional, enough that you could use this very well to keep track of information. In fact, a huge majority of my own spreadsheets look like this—Google Sheets makes it so simpleton to capture information, share it, and refund to it late for character that it acts as my highly-structured note-taking joyride .
But let ’ s assume that you have to deal with dozens of spreadsheets per sidereal day ( or worse, that you have to share spreadsheets back-and-forth ) and this is what person sends you. It ’ s truly boring, and if it was a boastfully datum hardening it would be irritating to skim through .
For the bare exemplar above a lack of significant format is “ all right. ” It does the basics, storing my data and allowing me to save it. But it ’ s not something I would want to come spinal column to each sidereal day .
Since I eat breakfast every dawn, let ‘s take some time to make this spreadsheet more user-friendly with some format !
first we ’ ll “Freeze” the first row in place. That means if we scroll down the spreadsheet, the first row will still be visible, no topic how much data lies below it. This allows you to have a long list and helps to keep tab on what you ’ re actually looking at .
There are two ways to freeze rows :

1. Click VIEW > FREEZE > 1 ROW in the navigation bar to lock the first row in place
2. Hover the dark grey bar in the top left of the spreadsheet (until it becomes a hand) and drag between rows 1 and 2

Freezing my heading row is the first thing I do in every sheet I make .
now, let ‘s make the header text crop up with some bare textbook format ( remember, the textbook format tools are in the toolbar, just above your first row ) :

1. Drag to select the cells you want to format
2. Bold the text
3. Increase font size to 12pt
4. Center-align the whole row
5. Give give your cells a grey fill

The future thing I ’ ll do to clean this up a bite is format my “ average Price / Serving ” to be a dollar prize. hera ‘s how things look at first :

nowadays, let ‘s clean that up with the “ Format as $ ” button for the specific values ( or integral row ) highlighted .

You ‘ll see that your selected cells are now displayed as a dollar measure, rather than a regular phone number .
Note : if you perform this operation with the solid rowing / column highlighted, future values will take the format american samoa well !
now that you ’ ve got the hang of insert and formatting your data, it ’ second about time we start actually calculating some sums, averages, and more from your data !

3. Add, Average, and Filter Data with Formulas

Google Sheets, like most spreadsheet apps, has a crowd of built-in formulas for accomplishing a number of statistical and data manipulation tasks. You can besides combine formulas to create more herculean calculations and string tasks together. And if you ‘re already accustomed to crunching numbers in Excel, the accurate like formula work in Google Sheets most of the time .
For this tutorial, we ’ ll focus on the five most common recipe, which are shown in the formula shed down menu from the top navigation .

You can click a formula to add it to a cell, or you can start typing any rule with a = sign in a cell followed by the formula ‘s name. Sheets will auto-fill or suggest formulas based on what you type, so you do n’t need to remember every formula .
The most basic recipe in Sheets include :

• SUM: adds up a range cells (e.g. 1+2+3+4+5 = sum of 15)
• AVERAGE: finds the average of a range of cells (e.g. 1,2,3,4,5 = average of 3)
• COUNT: counts the values in a range of cells (ex: 1,blank,3,4,5 = 4 total cells with values)
• MAX: finds the highest value in a range of cells (ex: 1,2,3,4,5 = 5 is the highest)
• MIN: finds the lowest value in a range of cells (ex: 1,2,3,4,5 = 1 is the lowest)
• Basic Arithmetic: You can also perform functions like addition, subtraction, and multiplication directly in a cell without calling a formula

We ‘ll explore these formulas by improving our breakfast spreadsheet .

Using the SUM Formula

Let ’ s starting signal with adding up the total number of ingredients required for each recipe. I ’ ll use the SUM formula to add each value in the recipes and get a sum measure .
There are three ways to use the basic formula accessible via the top seafaring :

1. Select a range then click the formula (this will put the result either below or to the side of the range).
2. Select the result cell (i.e. the cell where you want the result to appear), then click on the formula you want to use from the toolbar. Finally, select the range of cells to perform your operation on.
3. Type the formula into the result cell (don’t forget the = sign) then either manually type a range or select the range

I ‘ll demonstrate all three methods in the gif below. First, I ‘ll sum my ingredients by selecting a range, and clicking SUM from the formula menu. Second, I ’ ll select a result cell and highlight the rate of cells to be summed together. ultimately, I will demonstrate typing a rule and image manually .

Note : In order to select a image of cells, click the first cell and hold SHIFT then click the last cell in the range. so if you want A1 through A10, pawl A1 then hold SHIFT and snap A10.
When you ’ ve finished selecting the cells that you want to add together, bid ENTER .
In my case, you see a grey help section pop up when I start typing the formula. When you create a recipe for the first meter, you ’ ll rather notice a amobarbital sodium foreground and a question mark next to the cell .
You can click the interrogate mark to toggle avail context for formulas on or off. These tips will tell you what type of information can be used in each formula, and will make your formula creation ( specially when you start combining formula ) much easier .
now that we have a recipe set up to SUM all of the ingredients together, let ’ s make sure that it applies to all of the cells in that row. I ’ ll choose my formula cell and drag the blue dot across the other cells to copy the formula to those cells .

You ’ ll notification that when you copy the formula to a neighbor cell, it shifts the range that the modern formula is referencing. For exemplify, in the “ Scrambled Eggs ” column it was SUM(B2:B8) but in “ french Toast ” it ’ sulfur SUM(C2:C8) .

Using the COUNT formula

now that we know how many parts are needed for each recipe, I ’ d like to know how complicated it is to make. I ’ ve simplified this by assuming that fewer ingredients means that the recipe is less complicate.

In decree to count the phone number of ingredients in each recipe, I ’ ll use the COUNT formula .
The count formula basically checks to see if the cells in a rate are empty or not, and returns the sum that are filled .
This formula will be set up in my spreadsheet the same way as my SUM row .

here ‘s a magic trick we did n’t cover in the previous section, though : highlight the cellular telephone stove that you ’ re trying to count and checking in the bottomland right corner of your spreadsheet. If you ’ ve highlighted a pure list of numbers, Sheets will mechanically SUM them for you and display the solution. If you ’ ve highlighted a blend roll of numbers and textbook, it will COUNT the values .
You besides have the option to perform any of the five number-based operations on a crop of numbers by clicking the SUM button in the bottom right and selecting the newly default option convention from the pop-out menu. From then on, anytime you highlight a crop it will perform the last-selected formula .

so according to my spreadsheet, “ cereal ” is the least complicate breakfast, but I ’ m even not convinced that an easy breakfast is worth it .
What if it costs besides much ? What if the excess attempt of cooking another meal saves me money ?
Let ’ s refine our decision by figuring out the average cost per serving of the breakfast choices by using the AVERAGE rule .

Using the AVERAGE formula

I ’ ve added some fake minimum and maximum prices per unit on my ingredients list to the right of my breakfast options. We ’ ll want to get an average price for each ingredient using the low and gamey rates, then multiply the result average price of the ingredient by its respective unit count in each recipe .
I ’ ll begin by highlighting the range of values ( in this case it ’ s two side-by-side rather than a vertical range ) and selecting the AVERAGE rule from the toolbar .
This will drop the leave into the column to the right of the maximum price column. next, I drag the formula down to apply it to the early min and soap price combinations .

I ‘ll label my column “ median unit price ” so we know what we ’ ra looking at. then, let ‘s move on to calculating the monetary value of the breakfast using simple arithmetical .

Using Simple Arithmetic Formulas

We need to calculate the entire cost of the breakfast by multiplying the average monetary value of each ingredient by its whole count in the recipe. To accomplish this, manually type a recipe into the “ Avg Price ” row .
Our basic arithmetic convention would look like this for the “ Scrambled Eggs ” column :
=$I2*B2+$I3*B3+$I4*B4+$I5*B5+$I6*B6+$I7*B7+$I8*B8
The $ symbol before column I ( the average prices ) tells Sheets that no matter where we put the formula in our spreadsheet, we constantly want to reference the I column.That manner, if we copy the recipe to the other recipes, it will always use the average unit cost column rather than shifting the reference point to the following column over when you drag to copy ( like it did in the SUM and COUNT examples ) .

If you do n’t want to type those values in manually, there are cleaner ways to perform this type of recipe : You could accomplish the like price calculation by using this advance formula :
=SUM(ARRAYFORMULA(B2:B8*$I2:$I8))
There are many formulas in Sheets that take wish of complex tasks for you, many of which we ‘ll dig into in the adjacent chapters .
now that we have some working data and calculations, possibly my coworkers ( who are likely planning to eat breakfast tomorrow ) might benefit from this sheet .
Let ’ s prepare to share our spreadsheet, and invite some collaborators to view, edit, and use our data .

4. Share, Protect, and Move Your Data

What makes Sheets so herculean is how “ in synchronize ” you ‘ll feel with your coworkers. jointly editing a spreadsheet is one of the critical functions of Sheets, and Google has made it a seamless experience .
here ’ s how it works :

1. Click either FILE > SHARE or use the blue “Share” button in the top right
2. Click “advanced”, then enter emails of who can view or edit your spreadsheet
3. Select any other privacy options and hit done

When you open the “ gain ” sharing panel, you ’ ll see a number of options .

The nonpayment functionality when you click the “ Share ” Button is to copy a connection to the spreadsheet to your clipboard .
When you parcel this associate with person via a messenger or electronic mail, if they click the link it will bring them to the spreadsheet. however, unless you ’ ve invited them via e-mail ( in the e-mail field ) and selected “ toilet Edit ”, they will still need to request license to make changes .
If you ’ d like to give anyone within your constitution or company editor-level access, click the “ change… “ button in the “ Who has Access “ section and blue-ribbon “ On – ( Your Organization name ) ** ”. ( note : this choice will only appear if you ‘re using Google Apps for Work. )

person is “ In your organization ” when they have an e-mail address and Google account for your party. In this subject, I ’ ve named by “ company ” MichaelGrubbs, therefore everyone in my arrangement has an @michaelgrubbs.com electronic mail address and anyone signed in to one of those accounts can access the spreadsheet .
You can learn more about communion and permissions here —you ’ ll want to make surely you are using the right permissions for the audience you ‘re sharing with.

Sharing Spreadsheets with Your Devices and Apps

even though Google Sheets and Drive are built for sharing between users, you ’ ll poster that many times your spreadsheets are created as internal documents, and share is secondary to actually getting sour done .
You can streamline your spreadsheet workflows and real-time data-sharing by taking advantage of these helpful add-ons :

1. The Google Docs mobile apps. You can use the Google Sheets mobile app to view and edit your spreadsheets, share links on the go, and add users. It’s a solid companion to—but not a replacement for—the web app.
2. Google Drive sync to your desktop. Google Drive allows you to easily upload files from your local desktop environment to your online Drive. This makes them accessible to your collaborators and also allows you to quickly import them into spreadsheets and other documents.
3. A Third-Party tool like Flickroom. You can use Flickroom to automatically add data to your spreadsheets, send files to your Google Drive account, alert you of change to your Sheets… you name it

Let ’ s continue working on our spreadsheet model to demonstrate using Flickroom, an app integration tool, to make Google Sheets even more potent .
rather than hitting the “ Share ” clitoris on my spreadsheet to send it to my colleagues, I ’ d like to send a Slack message alerting them that I ’ ve created this newfangled spreadsheet .
You can automatically send a message to a Slack distribution channel with Flickroom ’ s Google Sheets Trigger and Slack Action .
I ’ ve set my Zap up to look for new Spreadsheets in my Google Drive then post the file name and a link to the spreadsheet in a Slack Channel .

This is great for updating your team when you create new documents that you ’ d like to cursorily loop everyone in on .
You can set up filters and conditions to decide when to post, and you have complete dominance over what information you ’ d like to include in your message. You can besides trigger messages based on different actions in Google Sheets—like when person a newly rowing or changes the datum in a cell. Check out the Flickroom ‘s Google Sheets page for more data on supported data and triggers .
immediately let ’ s switch the guidance of the data-flow and consider how our colleagues would interact with our spreadsheet .
I ’ d like to allow myself and my team to interact with my spreadsheet and keep track of what they had for breakfast in a breakfast log. Without an automation joyride like Flickroom, tasks like this cursorily become the reason that people fail to collaborate successfully using spreadsheets .
Think about it, if this were a normal spreadsheet without any automation, you ’ vitamin d be asking person to :

1. Break out of their current activity
2. Track down the spreadsheet
3. Fill in a few pieces of potentially inconsequential data
4. Save and re-share this file (if it’s not already an online and synced document)
5. Repeat for any number of tasks / documents

This is where automating tasks becomes sol critical .
Let ’ s set up our spreadsheet so that it has a clean sail to receive some automated data. I ’ ll create a new worksheet using the + button in the bottom left .

nowadays, I ’ ll use Flickroom again and make Slack the triggering action with Google Sheets on the receiving end of the automation ( the “ Action ” english of the Zap ) .
I ’ ve set up my Zap to immediately take a Slack message posted into a dedicate channel and create a newfangled quarrel in the breakfast log along with the clock time and user who posted it .
Check it out in real-time :

And this can work for hundreds of early applications that you can use as Triggers or Actions with Flickroom. You can send data to your spreadsheet via e-mail, monitor your sociable channels, set it on a schedule ; there are dozens of different ways to accomplish any given task with the apps you ‘re already using .

Downloading Your Data

If you need to send your files to external collaborators, upload a file into another organization, or good like having backups for posterity, then turn towards one of Google Sheets ‘ many data export options .

The most coarse exports will be either .xls ( Excel document ) or .csv ( comma-separated values ). If you ‘re not certain which format to use, a .csv is normally the best stake .

Use Your Spreadsheet in Offline Mode

If you love what you ’ ve seen so far but were worried that you wouldn ’ deoxythymidine monophosphate be able to use Sheets without a connection, then fear not. Google Sheets has an “ Offline Mode “ that will mechanically sync your changes to the text file when you reconnect to the internet .
This is utilitarian for any situation where you ‘d need to treat Google Sheets like a background application—on a flight or a road tripper, for case .
here ’ s what you ’ ll need :
Instructions for setting up your offline synchronize are actually straight-forward, but the bulk of the process is precisely downloading and using the three core components above .
actually turning it on looks like this ( get fix to be amazed ) :

And good like that, you can use Google Sheets even when you ‘re offline—no WiFi necessity .
For more tips on using Google Sheets offline, jump to the end of chapter 6.

That’s All For Now

here ’ s what you ’ ve fair learned how to do if you followed along for the hale chapter ( you can hit each link to back-track ) :
Google Sheets is a potent tool—it ‘s everything you ‘d expect from a spreadsheet, with the excess perks of an on-line app. While the example spreadsheet that we created may have been a bite silly, the practical applications of using Sheets for your workflows ( both business and personal ) are boundless.

Whether you need to make a budget, outline your adjacent proposal, gather data for a inquiry project, or log information from any other app that connects with Flickroom, a Google Sheets spreadsheet can bring your data to life. And with everything stored in Google Drive, you ‘ll never worry about losing your files again—even if your computer dies .
immediately that you know how to make a spreadsheet, it ‘s time to fill your spreadsheet with data. The best way to do that in an on-line spreadsheet is with a form—and in chapter 2, we ‘ll look at the barren Google Forms instrument that can help you gather data and save it directly to your spreadsheet .
Go to Chapter 2 !

Source : https://flickroom.net
Category : Web Browsers