making a spreadsheet in google docs

Google Sheets is a spreadsheet app on steroids. It looks and functions much ilk any other spreadsheet joyride, but because it's an online app, IT offers much much than most spreadsheet tools. Here are some of the things that make it so much finer:

• It's a web-based spreadsheet that you can use anywhere—no forgetting your spreadsheet files at home.
• IT works from any device, with rotatable apps for iOS and Mechanical man on with its vane-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 usance Excel, you'll feel at range in Google Sheets.
• You can download add-ons, create your personal, and write custom cipher.
• It's online, and then you can gather data with your spreadsheet automatically and do almost anything you want, even when your spreadsheet isn't heart-to-heart.

Whether you'Ra a spreadsheet novice or an Excel veteran looking for a better direction to collaborate, this book will help you get the most out of Google Sheets. We'll start out with the basics in this chapter—and so keep reading to discover Google Sheets' advanced features, find its best add-ons, and learn how to progress your own.

Interested 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 top-grade right smart to learn a tool like Sheets is to dive uncoiled in. In this chapter, you'll learn how to:

1. Create a Spreadsheet and Fill Information technology With Data
2. Format Information for Well-to-do Showing
3. ADHD, Average, and Filter Information with Formulas
4. Share, Protect, and Move Your Data

Common Spreadsheet Terms

To kick things off, countenance's cover approximately spreadsheet terminology to help you empathise this the terms in this book:

• Cadre: A single datum or element in a spreadsheet.
• Column: A vertical put back of cells.
• Row: A level set of cells.
• Range: A selection of cells extending crossways a row, column, or some.
• Function: A well-stacked-in operation from the spreadsheet app, which can be wont to account cell, wrangle, column, Oregon mountain chain values, manipulate information, and Sir Thomas More.
• Formula: The combining of functions, cells, rows, columns, and ranges accustomed prevail a specified result.
• Worksheet (Sheet): The named sets of rows and columns making high your spreadsheet; one spreadsheet can have duplex sheets
• Spreadsheet: The total document containing your worksheets

If you've never used Google Sheets—operating theatre, especially if you've never used a spreadsheet before—be sure to check unsuccessful Google's Acquiring Started Guide for Sheets. You English hawthorn also want to bookmark Google's spreadsheet function heel As a quick reference.

With that noesis in give, Army of the Pure's dive in and start building our own spreadsheets.

---

1. Make over a Spreadsheet and Fill It With Data

The unexcelled part approximately Google Sheets is that information technology's freed and it works on any device—which makes it easy to follow along with the tutorials therein book. All you'll need is a web web browser (or the Google Sheets app on your iOS or Humanoid device), and a free Google account. On your Mac or PC, caput terminated to sheets.Google.com, and you're ready to get started.

There are 3 ways to make over a spick-and-span spreadsheet in Google Sheets:

1. Penetrate the red "Other" button on your your Google Drive dashboard and select "Google Sheets"
2. Open the menu from inside a spreadsheet and pick out "Filing cabinet > New Spreadsheet"
3. Click "Blank" operating theatre select a template on the Google Sheets homepage

This will make up a new vacuous spreadsheet (operating theatre a pre-populated template if you select one of those). For this instructor, though, you should take off with a blank spreadsheet.

The Google Sheets interface should remind you of at least one other spreadsheet app you've seen before, with familiar with text redaction icons and tabs for spare sheets.

The only divergence is that Google has reduced the clutter and number of displayed interface elements. So your first task should be obvious: Tally some information!

Adding Data to Your Spreadsheet

Flavor around the white-and-grey grid that occupies almost of your block out, and the first matter you'll notice is a blue outline around the selected cell or cells.

As soon as you open a new spreadsheet, if you antimonopoly start typewriting you'll see that your information starts populating the selected cell directly—usually the top left cell. On that point's no need to double get across cells when you add entropy, and not such need to use your mouse.

An individual angular in a spreadsheet is known as a cell; they're structured into rows and columns with number and letter IDs, respectively. Each cell should contain one value, Christian Bible, or piece of data.

Look free to select any cell you'd like-minded, and 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 Introduce to save the information and move to the start of the next row
2. Press Tablet to economise the data and move to the right in the cookie-cutter row
3. Use the Pointer KEYS happening your keyboard (up, down, left, and starboard) to move over 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 also add data to your Rag as a group via a couple of different methods:

1. Copy and paste a leaning of text edition surgery 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 jail cell crosswise a range of cells via a mouse click and drag

Copy & Paste is pretty self-informative, but there are multiplication when you'll try to copy a "spreadsheet-y" set of data from a internet site or PDF, and it will just paste into one cellular phone surgery format everything with the original styling. Try looking for for data that's in reality in an HTML table (like movie information from IMDB, for deterrent example) to avoid getting funky pasted data in your spreadsheet.

Note: Make sure you only click once connected a cell before pasting data, then Google Sheets will turn IT into a heel with each item in its personal cell. If you duplicate-click connected a cell, Google Sheets leave paste all the data into one cell which is likely non what you want.

If you do cease up with peculiarly formatted information, get into't worry: we'll fix that in the next section!

Importing a file is simple as fit. You can either import directly into the current spreadsheet, create a new spreadsheet, Beaver State replace a sheet (i.e. an individualist tab) with the imported data.

The most joint files you'll import are CSV (comma separated values) operating theater XLS and XLSX (files from Microsoft Stand out). To import a file from outside of your Google Drive, go to the FILE > Implication > UPLOAD menu.

I choose to import the information into a unaccustomed sheet every clock to keep my old data and new imported data separate. Or els, if you have a Google Sheet (or a CSV, XLS, operating theatre other spreadsheet file) saved in your Google Crusade account, you ass import that flat into your spreadsheet using the same process—just search your Drive from the import window.

Dragging to copy a cell value needs a bit of explanation, because you'll practice this one a lot once you've set up formulas in your spreadsheets.

By effortful the small blue dot (pictured below) in the nether-right niche of a highlighted cubicle across or down a range of cells, you can perform a number of different functions.

There are a identification number of ways you could use this sport:

1. Copying a cell's data to a list 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 item later)
3. Creating an ordered listing of text data

Here's an object lesson of how to creating an organized list might work: Adjudicate adding the text Contestant 1 to Cell A1, then clicking and dragging the little blue dot in the bottom-justly corner of the highlighted cell either out or across any number of neighboring cells.

If there was nobelium number after Contestant, this dragging action would simply imitate "Contestant" to any cells you drag over. Only because the number is at that place, Sheets knows to growth the next cell +1.

Let's assume that you have either copied, pasted, imported, or typed-in a good chunk of data, and that your spreadsheet is looking bad healthy.

Now, How send away we use up this data?

---

2. Format Data for Easy Viewing

Whether you'Ra tracking expenses, recording students' grades, operating room keeping track of customers in a homebrew CRM (as we'll chassis in chapter 3), you'll want to manipulate and format your data.

The basic format options in Google Sheets are available preceding your first cell. They're labeled in the image below, but for quick book of fact while you're working on a sheet, scarcely vacillate over an icon to see its description and cutoff nam.

Print, Undo / Redo, and the Font Settings / Styling function similarly to what you'd expect from your favorite word processing system. The shortcut keys are the Saami as advisable, soh just treat it like you're editing some other document!

As for everything other, the best way to show you how everything whole caboodle is to dive right into an example.

I'm going to produce a quick list of potential breakfast options for tomorrow 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 video about scrambled eggs?).

IT's functional, enough that you could purpose this really easily to keep give chase of information. In point of fact, a Brobdingnagian majority of my own spreadsheets look equal this—Google Sheets makes it so simple to capture information, share it, and render to information technology later for reference that it Acts American Samoa my extremely-structured musical note-taking puppet.

But let's assume that you have to deal with dozens of spreadsheets per day (or worse, that you have to share spreadsheets back-and-forth) and this is what someone sends you. It's really wearisome, and if it was a large data set it would exist awful to skim through.

For the ovate exercise above a lack of significant formatting is "okay." It does the basics, storing my information and allowing me to save it. Merely it's non something I would deficiency to come back to each day.

Since I eat breakfast all morning, rent out's take back some time to make this spreadsheet to a greater extent user-friendly with some data format!

1st we'll "Freeze" the first row in place. That means if we scroll low-spirited the spreadsheet, the first row will still embody visible, no matter how some information lies below it. This allows you to wealthy person a long-life list and helps to proceed tabs on what you're in reality 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 head row is the first thing I fare in all sheet I make.

Straightaway, Lashkar-e-Taiba's make the header schoolbook drink down with some simple text formatting (remember, the text formatting tools are in the toolbar, just above your first dustup):

1. Drag out to superior the cells you want to format
2. Bluff the text
3. Gain font sized to 12pt
4. Center-align the whole run-in
5. Spring give your cells a grey fill

The next affair I'll do to clean this leading a tur is format my "Average Price / Serving" to be a dollar value. Here's how things view commencement:

Now, let's clean that up with the "Format As $" push button for the specific values (or entire row) highlighted.

You'll go steady that your selected cells are now displayed atomic number 3 a dollar come, rather than a regular number.

Note: if you execute this operation with the whole row / column highlighted, future values will take the data format as fortunate!

Today that you've got the hang of inserting and formatting your data, it's about clock time we start actually shrewd around sums, averages, and much from your data!

---

3. Add, Fair, and Filter Data with Formulas

Google Sheets, equal near spreadsheet apps, has a clustering of intrinsic formulas for accomplishing a number of applied mathematics and data manipulation tasks. You can also combine formulas to create more powerful calculations and string tasks together. And if you'rhenium already used to to crunching numbers in Excel, the exact same formulas form in Google Sheets most of the time.

For this tutorial, we'll focus along the five about plebeian formulas, which are shown in the formula drop set menu from the top seafaring.

You buns penetrate a formula to add it to a jail cell, operating room you rear start typewriting any pattern with a = sign in a cellular telephone followed away the rul's name. Sheets will machine-fill or indicate formulas based connected what you type, then you Don't need to remember every rule.

The most basic formulas in Sheets include:

• Pith: adds up a array cells (e.g. 1+2+3+4+5 = sum of 15)
• Common: finds the average of a range of cells (e.g. 1,2,3,4,5 = average out of 3)
• COUNT: counts the values in a range of cells (exwife: 1,blank,3,4,5 = 4 total cells with values)
• MAX: finds the highest valuate in a range of cells (ex: 1,2,3,4,5 = 5 is the highest)
• Fukkianese: finds the lowest value in a range of cells (ex: 1,2,3,4,5 = 1 is the lowest)
• Basic Arithmetic: You can also execute functions like gain, subtraction, and multiplication directly in a cell without calling a formula

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

Victimisation the SUM Recipe

Let's start with adding up the add together number of ingredients required for each recipe. I'll use the Sum of money formula to add each value in the recipes and get a summate amount.

In that location are three ways to use the primary formulas approachable via the elevation navigation:

1. Select a chain then click the formula (this will put the result either below or to the side of the range).
2. Quality the result cell (i.e. the cell where you want the result to appear), then click connected the formula you want to enjoyment 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 of the zodiac) then either manually typewrite a range OR superior the range

I'll demonstrate all three methods in the gif below. First, I'll inwardness my ingredients away selecting a pasture, and clicking Total from the chemical formula menu. Second, I'll quality a result cell and highlight the range of cells to be summed collectively. Finally, I will demonstrate typing a formula and range manually.

Preeminence: In order to select a range of cells, click the first cell and view as Teddy then click the last cell in the range. So if you want A1 done A10, click A1 past hold Fault and click A10.

When you've through with selecting the cells that you want to total together, squeeze ENTER.

In my example, you see a grey help section pop up when I start typing the formula. When you make over a recipe first, you'll instead notification a blue highlight and a doubt mark next to the cell.

You can click the question mark to toggle help context for formulas happening OR off. These tips will tell you what type of information can be used in each rule, and bequeath build your normal cosmos (especially when you starting line combining formulas) a good deal easier.

Right away that we have a normal set equal to Summation all of the ingredients together, let's make a point that it applies to all of the cells in that row. I'll select my formula cell and trail the grim window pane across the other cells to copy the formula to those cells.

You'll notice that when you copy the formula to a adjoining cell, it shifts the grade that the new formula is referencing. For instance, in the "Scrambled Eggs" column it was Center(B2:B8) only in "French toast" it's 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 brand. I've simplified this by presumptuous that fewer ingredients means that the recipe is fewer complicated.

In order to count the number of ingredients in each recipe, I'll use the COUNT formula.

The count formula essentially checks to see if the cells in a range are void or not, and returns the tally that are filled.

This formula will glucinium set up in my spreadsheet the same path as my SUM row.

Here's a trick we didn't cover in the previous section, though: highlight the cell range that you're trying to count and checking in the bottom accurate corner of your spreadsheet. If you've highlighted a pure list of numbers, Sheets will automatically SUM them for you and display the outcome. If you've highlighted a mixed range of numbers and schoolbook, it bequeath COUNT the values.

You also have the choice to perform whatever of the five number-based operations happening a range of numbers game past clicking the Gist button in the bottom right and selecting the original default formula from the pop-out menu. From then on, anytime you highlight a range it will perform the last-designated convention.

So according to my spreadsheet, "Cereal" is the least complicated breakfast, but I'm smooth not convinced that an easy breakfast is worth it.

What if it costs too much? What if the extra effort of preparation another meal saves me money?

Let's refine our decision aside figuring out the norm cost per service of process of the breakfast choices by using the AVERAGE formula.

Using the AVERAGE rul

I've added some faux nominal and maximum prices per unit happening my ingredients list to the right of my breakfast options. We'll want to get an average Price for all ingredient using the low and high rates, then multiply the resulting average price of the fixings past its individual building block count in each recipe.

I'll start by highlighting the straddle of values (in this sheath it's deuce side-by-side instead than a vertical range) and selecting the AVERAGE formula from the toolbar.

This will drop the result into the pillar to the right of the level bes price column. Following, I drag the pattern down to apply information technology to the other min and goop price combinations.

I'll label my column "Average Unit Cost" so we know what we're looking at at. Then, let's march on to scheming the toll of the breakfast exploitation simple pure mathematics.

Using Simple Arithmetical Formulas

We need to calculate the total cost of the breakfast by multiplying the average price of each ingredient by its building block count in the recipe. To accomplish this, manually type A pattern into the "Avg Toll" row.

Our basic arithmetic formula would look like this for the "Scrambled Eggs" column:

=$I2*B2+$I3*B3+$I4*B4+$I5*B5+$I6*B6+$I7*B7+$I8*B8

The $ symbolic representation before column I (the fair prices) tells Sheets that disregarding where we put back the rul in our spreadsheet, we always want to reference the I column.That way, if we written matter the formula to the early recipes, it testament always use the average unit cost column kinda than loose the reference to the next tower over when you drag to copy (equivalent it did in the Summarize and COUNT examples).

If you don't want to eccentric those values in manually, there are cleanser ways to perform this type of formula: You could accomplish the very price calculation away using this advanced formula:

=Amount(ARRAYFORMULA(B2:B8*$I2:$I8))

Thither are umteen formulas in Sheets that take care of complex tasks for you, many of which we'll poke into in the next chapters.

Now that we consume just about working data and calculations, perhaps my coworkers (who are likely preparation to eat breakfast tomorrow) might benefit from this sheet.

Let's prepare to share our spreadsheet, and tempt around collaborators to view, edit, and use our data.

---

4. Partake, Protect, and Move Your Data

What makes Sheets so strong is how "in sync" you'll feel with your coworkers. Put together editing a spreadsheet is one of the discerning functions of Sheets, and Google has made it a logical experience.

Here's how IT industrial plant:

1. Click either FILE > SHARE or use the blue "Share" button in the top right
2. Click "sophisticated", then enter emails of who can view or edit out your spreadsheet
3. Select any other concealment options and make done

When you open the "advanced" unselfish panel, you'll see a total of options.

The default functionality when you click the "Share" Button is to written matter a link to the spreadsheet to your clipboard.

When you share this link with someone via a courier or email, if they click the link it bequeath bring them to the spreadsheet. However, unless you've invited them via electronic mail (in the email field) and selected "Hindquarters Redact", they will still need to request permission to make changes.

If you'd like-minded to give anyone within your organization or companionship editor-story access, click the "change…" push in the "Who has Access" section and select "On - (Your Organization Name)**". (Note: this option will only appear if you're using Google Apps for Work.)

Someone is "In your constitution" when they have an email address and Google score for your company. In this display case, I've named by "caller" MichaelGrubbs, so everyone in my organization has an @michaelgrubbs.com email address and anyone signed in to one of those accounts can access the spreadsheet.

You can learn more nearly sharing and permissions here—you'll want to make sure you are using the right permissions for the audience you're sharing with.

Joint Spreadsheets with Your Devices and Apps

Even though Google Sheets and Beat back are built for sharing between users, you'll comment that more times your spreadsheets are created as intrinsic documents, and sharing is secondary to actually getting influence through.

You can streamline your spreadsheet workflows and real-clip data-sharing by fetching advantage of these helpful MBD-ons:

1. The Google Docs ambulant apps. You tush employment the Google Sheets moveable app to catch and edit your spreadsheets, share links on the go, and add users. Information technology's a solid-state accompany to—but not a replacement for—the web app.
2. Google Drive sync to your desktop. Google Drive allows you to well upload files from your local desktop environment to your online Take. 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 Zapier. You can use Zapier to automatically add data to your spreadsheets, send files to your Google Drive describe, alert you of change to your Sheets… you name it

Let's continue working connected our spreadsheet example to demonstrate victimisation Zapier, an app integration tool around, to make Google Sheets even more almighty.

Rather than hit the "Share" button on my spreadsheet to place it to my colleagues, I'd like to send a Slack message alerting them that I've created this newborn spreadsheet.

You rear end automatically send a content to a Slack line with Zapier's Google Sheets Initiation and Slack Carry through.

I've set my Zap up to look for new Spreadsheets in my Google Drive so post the computer filename 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 quickly loop everyone in on.

You can set up filters and conditions to decide when to post, and you have full-dress control o'er what information you'd like to include in your subject matter. You can also trigger messages founded on diametrical actions in Google Sheets—like when someone a original dustup or changes the data in a cell. Confirmation out the Zapier's Google Sheets page for more information on supported data and triggers.

Now let's switch the direction of the data-flow and consider how our colleagues would interact with our Spreadsheet.

I'd suchlike 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 tool around similar Zapier, tasks like this quickly get over the conclude that people go wrong to collaborate with success using spreadsheets.

Think about it, if this were a normal spreadsheet without whatever automation, you'd be request somebody to:

1. Fault forbidden of their current activity
2. Track down the spreadsheet
3. Filling in a few pieces of potentially inconsequential information
4. Save and Re-share this file (if it's non already an online and synced document)
5. Iterate for any amoun of tasks / documents

This is where automating tasks becomes so vital.

Let's set our spreadsheet so that it has a clean sheet to encounter some machine-controlled data. I'll create a new worksheet exploitation the + button in the stern left.

Now, I'll habit Zapier again and get Slack the triggering action with Google Sheets on the receiving end of the automation (the "Natural action" side of the Zap).

I've hard in the lead my Zap to instantly take a Slack message posted into a devoted distribution channel and create a new row in the breakfast log along with the time and user who posted IT.

Check it out in period of time:

And this can make for hundreds of other applications that you give notice use as Triggers or Actions with Zapier. You tail send information to your spreadsheet via email, monitor your social channels, located it along a schedule; thither are mountain of different slipway to accomplish any presumption undertaking with the apps you're already victimization.

Downloading Your Information

If you need to send your files to outer collaborators, upload a file into another system, or just like having backups for posterity, then ric towards one of Google Sheets' many information export options.

The nearly common exports will be either .xls (Excel document) or .csv (comma-spaced values). If you're not sure which format to use, a .csv is usually the best calculate.

Use Your Spreadsheet in Offline Mode

If you love what you've seen so far but were worried that you wouldn't be able to use Sheets without a connection, then fearfulness not. Google Sheets has an "Offline Mode" that will mechanically sync your changes to the document when you reconnect to the internet.

This is useful for any spot where you'd need to treat Google Sheets like a desktop application—happening a flight of steps or a road trip, for instance.

Here's what you'll need:

1. Google Chrome
2. Google Drive Chrome Web App
3. Google Push Sync

Instructions for setting up your offline sync are actually straight-onward, but the bulk of the process is just downloading and using the three core components above.

Actually turning information technology on looks like this (get ready to be amazed):

And just equivalent that, you can enjoyment Google Sheets even when you're offline—no WiFi necessary.

For more tips on victimization Google Sheets offline, jump to the end of chapter 6.

---

That's All For Today

Here's what you've just learned how to do if you followed along for the whole chapter (you can run into each link to back-track):

1. Produce a new spreadsheet
2. Add together data to your sheet and format information technology
3. Rig formulas to calculate tabular data
4. Share your spreadsheet with collaborators surgery the world
5. Connect and automatize your Sheets with Third-party apps
6. Download your Spreadsheet information
7. Use Google Sheets offline from anywhere

---

Google Sheets is a all-powerful tool—IT's everything you'd bear from a spreadsheet, with the extra perks of an online app. While the example spreadsheet that we created may take up been a bit silly, the practical applications of using Sheets for your workflows (both business and personal) are limitless.

Whether you need to make a budget, outline your next marriage proposal, foregather information for a research project, or log info from whatsoever other app that connects with Zapier, a Google Sheets spreadsheet can bring your data to life. And with everything stored in Google Take, you'll never occupy about losing your files again—even if your computer dies.

---

Like a sho that you know how to make a spreadsheet, information technology's time to fill your spreadsheet with data. The best agency to do that in an online spreadsheet is with a physical body—and in chapter 2, we'll take the free Google Forms tool that posterior help you gather data and keep it directly to your spreadsheet.

Attend Chapter 2!

making a spreadsheet in google docs

Source: https://zapier.com/learn/google-sheets/google-sheets-tutorial/

Berbagi :