Cara menggunakan google sheets formulas multiply

Spreadsheets are one of the best ways to log and organize data. Frequently, I use them to organize projects or take notes on something new I'm learning. It's easy to use a spreadsheet like a blank canvas, and then order the data into a structured format later.


Google Sheets makes it easy to capture and organize that data, right inside of a web browser. It's a free spreadsheet tool you can launch just by opening a new tab.

As your spreadsheet library grows and expands, you may want to pull in data from other files. It helps when you can link up multiple spreadsheets so that you can use data from other workbooks. Google Sheets can help you do just that. Let's learn how to link spreadsheets in Google Sheets.

Watch & Learn

Join me in the screencast below to walk through the technique to link Google Sheets. You'll learn more about linking between separate Sheets workbooks, within single files and grabbing data from external services.

This tutorial is going to cover how to make a Google spreadsheet link to another sheet. Let's walk through the guide to learn several ways to do that.

1. How to Link Within Sheets

Whether you're using a spreadsheet app like Excel, or a browser-based tool like Sheets, you're likely to build out files with many different tabs inside.

Typically, we talk about workbooks as being single "files" for spreadsheets. The individual tabs inside the workbook are called sheets. Workbooks typically have many different sheets inside of them. 

Maybe you use some tabs to capture your inputs, and other tabs to build out charts that analyze them. It's a good idea to keep these sheets somewhat separate for simplicity and organization's sake.

It's common that you need to link data between tabs (sheets) in a Google Sheets file. To do that, let's start writing a formula by simply typing an equals sign ("=") in a cell.

Let's say that you want to simply bring what's in one cell on another sheet over. With the formula still open, I'll switch tabs by clicking on it. Then, let's click on that cell to reference it as you can see below.

Once you press Enter, Sheets will take you back to where you started the formula, and you'll see the data from the linked cell. Now, you're linked to the cell on the second sheet. If the source cell changes, so will the linked cell.

Of course, you can also apply functions to the cell reference. When you're referencing a numeric value, you could multiply it on the fly for example.

One popular setup for workbooks is to use a single sheet for inputs or setting variables on a single tab and have all other tabs do the data transformations and analysis. I think this is a great setup to keep things organized and tidy.

2. How to Link to Other Sheets Files in Google Spreadsheets

What if you want to make two entirely separate files talk to each other, referencing data back and forth between them?

To link Google Sheets, we'll need to learn about the IMPORTRANGE function. This is the best way to pull in data from separate files in Sheets and use that data inside the active sheet.

It helps to have both spreadsheets open while you're working with IMPORTRANGE so that you can easily check what cells need to be brought into your spreadsheet.

To get started with this function, let's type in =IMPORTRANGE. Next, grab the URL for the Sheet that you want to pull data from, and paste it in quotation marks in the first part of the function.

Next, you'll need to add the name of the sheet followed by an exclamation point. Then, specify the cells that you want to pull the data from.

A finished function could look something like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xrGsOD-yXuORqd8cFg21XOo3ZIw9QbSiNDcnSEatlPM/edit#gid=0", "Sheet1!A:A")

The first time that you create a Google Sheet link to another sheet, you might see a warning like this one that requires you to grant access between these two files.

One option I'd recommend is to include entire columns when you're pulling data between Sheets. This will automatically bring over any new data that's added to the source spreadsheet.

This is an easy way to bring data together from multiple spreadsheets. Using the IMPORTRANGE function, there's no reason that you can't create systems of interlinked spreadsheets that work together.

3. How to Get Data from Google Services

One of my favorite things about Sheets is that you can insert data that's not even stored inside of a spreadsheet. Because Google's Sheets app is connected to other services that Google operates, you can get data that's stored online or in Google's services. With this step, you can skip the work of linking a Google Sheet link to another sheet

Basically, these functions are actually a method to skip putting data in a spreadsheet altogether. The idea is the same though; simply use these functions and grab data from other sources right inside of the active sheet. Let's take a look.

1. GOOGLEFINANCE

For finance professionals, the =GOOGLEFINANCE function is a game-changer. You can grab current and historical data from

In Excel, doing analysis on stocks involves finding a data source, downloading the data, cleaning it up so that it's useful, and then writing formulas on it. Sheets skips that work by automatically fetching the data from the Google Finance service.

Here's an example of how you can use the GOOGLEFINANCE function to grab the stock price over a period of time for Amazon's stock:

=GOOGLEFINANCE("AMZN","price","1/1/2018","3/15/2018","Daily")

Here are the results, right inside of a spreadsheet. Sheets generated an array of days and closing prices for the stock:

I just scratched the surface of how the GOOGLEFINANCE function. Check out the full tutorial below for more ideas on how you can apply this function to grab up-to-date financial data.

• 
  
  

  How to Track Stock Data in Google Sheets - With GOOGLEFINANCE Function

  
  
  

  Andrew Childress

  28 Feb 2017

2. GOOGLETRANSLATE

Another amazing function right inside of Sheets is the GOOGLETRANSLATE function, which you can use to translate to and from any language that Google Translate supports. It's far easier to perform those translations inside of a spreadsheet instead of jumping back and forth between tabs.

To use this function, try out my suggested syntax I show below:

=GOOGLETRANSLATE("je ne sais quoi","fr","en")

This syntax will take the text that I've typed into quotes and translate it from French ("fr") to English ("en"). This part of the formula is optional (Sheets will automatically guess at what language to translate from), but makes it easy to remember other options for translation.

You could also use this function to translate data that's already typed into a cell. Instead of typing the text in quotation marks, use a cell reference, such as:

=GOOGLETRANSLATE(A2,"en","es")

This function would translate text in cell A2 from English to Spanish.

Either way that you apply the function, this function is like pulling data from another source because you're feeding data through the Google Translate service.

3. IMAGE

The image function can help you insert an image directly into a spreadsheet, using the IMAGE function.

First, make sure that you grab the URL address to the image. As a reminder, the image has to be hosted online in order to pull it directly into a Google Sheet.

Here's an example of using the function to pull in the current Google doodle that was posted:

=IMAGE("https://www.google.com/logos/doodles/2018/celebrating-george-peabody-4943849993535488-l.png")

One helpful use for this feature is if the image will be updated, but the URL won't change. You could always pull the latest version of an image at the address.

Recap & Keep Learning

Don't stop here! There's so much more to learn when it comes to using Google Sheets to store and work with data. A spreadsheet is one of my favorite ways to log data in a structured way and analyze it to find meaning inside of it.

Here at Envato Tuts+, we've been building out a deep library to help you learn Google Sheets from front to back. Check out the tutorials below to learn more:

• 
  
  

  20 Free Google Sheets Business Templates to Use in 2018

  
  
  

  Andrew Childress

  28 Feb 2018

• 
  
  

  How to Use (Timesaving) Google Sheets Keyboard Shortcuts

  
  
  

  Andrew Childress

  07 Mar 2018

• 
  
  

  How to Edit & Format Cells in Google Sheets Spreadsheets

  
  
  

  Andrew Childress

  22 Feb 2018

• 
  
  

  Google Sheets to Excel: How to Move Back & Forth Between Spreadsheets

  
  
  

  Andrew Childress

  16 Feb 2018

How do you like to interconnect your Google Sheets and share data between them? Let me know in the comments section below.

Bagaimana cara menghitung rata rata nilai menggunakan Google sheet?

Rumus di Google Sheet dapat ditambahkan di mana?