How to create and use a pivot table in Google Sheets to summarize and analyze smaller sets of your data

Person in headphones working on spreadsheet on desktop Mac.
Pivot tables in Google Sheets let you organize and group smaller sets of data from a larger dataset.

  • Pivot tables in Google Sheets summarize smaller sets of data from a more extensive table and group sums, averages, or other statistical measurements in a specific and meaningful way.
  • You can create pivot tables in Google Sheets manually or automatically and sort your spreadsheet’s data by rows, columns, values, and filters.
  • Pivot tables also let you group data manually or automatically to give you a more specific and customized window into your more extensive data set.
  • Visit Insider’s Tech Reference library for more stories.

Pivot tables in Google Sheets are a way to get a more focused snapshot of your larger database.

They can be handy for their versatility – pivot tables group and filter information according to variables that you pick – and be as simple or complex as you want.

More than just counting, pivot tables can total up amounts, calculate averages, and even help you find errors in your dataset.

Here’s how to perform some of the most basic functions of a pivot table.

More ways to edit and work with pivot tables

Here are a few more ways to adjust and get the most out of the pivot table you’ve created using other facets of the feature in Google Sheets.

  • Add data: On the right side where you set up the pivot table, you can always add to Rows, Columns, or Values to expand the pivot table with another variable.
  • Change row or column names: To change row or column names, double click inside the label cell and rename it.
  • Change sort order: To see your data in a different order, click the dropdown menu within a variable and sort differently or see a separate order (descending or ascending alphabetically, by amount, etc.)
  • Change the data range: The data range signifies which cells are being used from the original spreadsheet to make the pivot table. Change the range by clicking the grid symbol at the top of the pivot table editor and editing it.
  • Delete data: Any variable you add to your pivot table can be removed using the X feature at the top right of the table.
  • Hide data with filters: Filtering allows you to remove specific data from the pivot table. Under Filters, click Add and choose which category you’d like to filter within. Then, under Status, you can use the dropdown menus to uncheck certain conditions or values. For example, you could select to filter by Account number in the budget and deselect a particular bank account number, which would prevent those entries from being counted in the pivot table.
  • Move data: Make rows columns and vice versa by simply dragging their boxes to the section you’d like them in – for example, you might see that there are more columns than rows and switch them to make the pivot table more vertically readable.
  • Repeat row labels: Not every entry within a group will have a row label. In the box for that group, check Repeat row labels to apply the group name to every row.
  • Show totals: Sum totals for each category or group will typically be visible, and you can check or uncheck Show totals within a variable’s box to change this.
  • Show values as percentages: Change values to percentages by, underneath Values, clicking the dropdown menu under Show as, which will let you convert values into percentages of rows, columns, or totals.
  • Create custom formulas: If you want to create a column that does a certain calculation for each row, click Add underneath Values and click Calculated field, which will add a column with no values. Define the formula (let’s say you want to subtract 100 from each row value) in the text field and customize how the values are shown in the dropdown menus. Under Summarize by change Sum to Custom.
  • Show details of data: Double click any value to show its source data (for example, anything within the “Freelance” category of the budget). This will create a new sheet with a list of the data used to arrive at the value.

How to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheetsHow to use the COUNTIF function in Google Sheets to determine a number of items within a specific conditionHow to use the SUMIF function in Google Sheets to find a specific sum in your spreadsheetHow to use the IMPORTRANGE function in Google Sheets to keep multiple spreadsheets in sync

Read the original article on Business Insider

How to use the COUNTIF function in Google Sheets to determine a number of items within a specific condition

businessman at office on laptop
The COUNTIF function in Google Sheets is useful for finding specific pieces of data.

  • You can use the COUNTIF function in Google Sheets to find the number of items in a range that meet certain criteria.
  • You can use text, numbers, and dates as criteria in the COUNTIF function.
  • COUNTIF is an easy way to find out how many rows in a spreadsheet contain a blank cell, for example.
  • Visit Insider’s Tech Reference library for more stories.

Like Microsoft Excel, Google Sheets includes a simple COUNT function that tells you the number of items in the selected range. But what if you want to know the number of items based on some condition – like only the products in a list that are below a certain price, or only the cities in a specific state? That’s when you would use the COUNTIF function.

As the name implies, COUNTIF combines the abilities of COUNT and IF – it checks to see if the IF argument is true before returning the COUNT value. Here’s what the function looks like:

=COUNTIF(range, criterion)
  • Range: This is the range of cells you want to test against some sort of criterion and then sum.
  • Criterion: This is what you want to use to test against the range. You can use a number, text, or even a date as your criterion.

How to use COUNTIF in Google Sheets with a number condition

Using COUNTIF in your own Google Sheets is pretty straightforward, since you only need to specify two arguments. Suppose you had a spreadsheet like this in which you wanted to know how many items cost less than $500.

How_to_use_COUNTIF_in_Google_Sheets 1
You can use the COUNTIF function to find the number of items in this list that cost less than $500.

1. Type “COUNTIF” and press the Tab key. Google Sheets automatically adds the open parenthesis.

2. Click and drag the mouse to select the column that has the pricing information.

3. Type a comma and then enter the criteria – in this case “<500” (include the quotes).

4. Press the Tab key. Google Sheets will close the parenthesis and display the result in the cell.

How_to_use_COUNTIF_in_Google_Sheets 2
COUNTIF is simple to use in part because it has just two arguments.

How to use COUNTIF with a text condition

The COUNTIF function can also sum items based on text criteria. If you needed to find out how many items in a list have a particular color, you would do this:

1. Type “COUNTIF” and press the Tab key. Google Sheets automatically adds the open parenthesis.

2. Click and drag the mouse to select the column that has the color information.

3. Type a comma and then enter the criteria, such as “blue” (include the quotes).

4. Press the Tab key. You should see the result.

How_to_use_COUNTIF_in_Google_Sheets 3
You can use a text criterion to find how many items have that matching text.

If you’re looking for the sum of all the cells that don’t have this value, that’s easy to do as well – just use the NOT operator, like this:

=COUNTIF(A1:A12,"<>blue")

Likewise, the COUNTIF function is often used to find the number of entries that are blank, or the number that are not blank. This can be handy for cleaning up a messy spreadsheet. Find the blanks with a pair of quotes with nothing between:

=COUNTIF(A1:A12,"")

And the non-blank entries can be found with a NOT operator:

=COUNTIF(A1:A12,"<>")

How to use COUNTIF with a wildcard

Wildcards can come in handy because they help you find partial matches, such as all the descriptions that mention the color red.

1. Type “=COUNTIF” and press the Tab key.

2. Click and drag the mouse to select the column with the product descriptions.

3. Type a comma and then enter “*red*” (include the quotation marks).

4. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.

In this example, we’re looking for cells that contain the word red anywhere. But you could use “*red” to find cells that end with the word red or “red*” for cells that begin with red.

How to use COUNTIF with a date condition

The COUNTIF function can return the number of items that correspond to a particular date. Suppose we want to total all the sales that happened on January 15.

1. Type “=COUNTIF” and press the Tab key.

2. Click and drag the mouse to select the column with the range of dates.

3. Type a comma and then enter “DATE(2021, 1,15)” (do not include the quotation marks).

4. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.

As you can probably guess, you can use variations of this argument with logical operators:

  • =COUNTIF(D1:D8,”>=”&DATE(2021,1,7)). This returns the number of items dated on or after January 7.
  • =COUNTIF(D1:D8,”<>”&DATE(2021,1,7)). This returns the number of items with any date other than January 7.
How_to_use_COUNTIF_in_Google_Sheets 4
By combining logical operators with the date, you can find the number of items that are associated with a specific date range.

How to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheetsHow to use the IMPORTRANGE function in Google Sheets to keep multiple spreadsheets in syncHow to use the SUMIF function in Google Sheets to find a specific sum in your spreadsheetThe 35 best Google Sheets keyboard shortcuts for speeding up your workflow on a Mac or PC

Read the original article on Business Insider

How to use the SUMIF function in Google Sheets to find a specific sum in your spreadsheet

typing on computer with spreadsheets
You can use SUMIF on Google Sheets to calculate a sum based on various conditions.

It’s easy to add a set of numbers together – every spreadsheet user knows how to use the tried-and-true SUM function to find a total. But what if the sum you’re trying to find depends on some sort of condition? Suppose, for example, you have a set of numbers and you only want to add up the ones that are below a certain max value. Or perhaps you have your company’s sales tally and want to know only the sales from a particular region or sales from a certain time period.

That’s where Google Sheets’ SUMIF function comes in. You can use SUMIF to calculate a sum based on a condition. That condition can be built into the set of values themselves, or numbers that are related to a neighboring row or column. If that sounds complicated, the good news is that it’s easy to apply.

How to use the SUMIF function in Google Sheets

As the name of the function implies, SUMIF is conditional and checks for a status using the IF function before totaling your numbers. This is what the function looks like:

=SUMIF(range, criterion, [sum_range])

  • Range: The range is the set of cells that you want to test against some sort of criterion.
  • Criterion: This is what you want to use to test against the range. The SUMIF function is pretty versatile – you can use a number, text, or even a date as the criterion.
  • Sum_range: The sum_range is optional, and is what gives this function so much power. If you omit the sum_range, the function will sum the range. But you have the option of summing a different range depending on the result of the conditional test.

Tips for using SUMIF in Google Sheets

Once you’ve used the SUMIF function a few times, you’ll probably find that it’s pretty straightforward, both with and without the optional argument. But here are a few tips to keep in mind to get the most out of SUMIF:

  • The SUMIF function can only be used to evaluate one condition. If you need to work with several criteria at once, you might need to switch to the SUMIFS function.
  • When you use the optional sum_range, it doesn’t have to be right next to the range, but it does need to include the same number of cells.
  • If you include a text argument in SUMIF, it isn’t case-sensitive – and you can’t make it case-sensitive, so it will treat “apple,” “Apple,” and “APPLE” the same way.
  • Remember to use quotes to enclose elements like text and logical operators, like “apple” and “>=1”
  • If you need to combine two elements in the argument – like a greater than operator and date, for example, use an ampersand to join them.

How to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheetsHow to convert your Google Sheets files into Excel files, and edit them in Microsoft ExcelHow to see the edit history for cells in Google Sheets and spot changes or mistakes in a spreadsheetHow to add cells in Google Sheets on your PC or Mac

Read the original article on Business Insider

How to use the IMPORTRANGE function in Google Sheets to keep multiple spreadsheets in sync

Woman sitting on couch working on laptop
The IMPORTRANGE feature in Google Sheets is useful for managing several spreadsheets at once.

  • You can use the IMPORTRANGE function in Google Sheets to easily copy data from one spreadsheet to another.
  • To import data, you only need to know the URL and name of the original spreadsheet, and the range of cells to import.
  • Once imported, the data is automatically updated when it changes in the original Google Sheets spreadsheet.
  • Visit Insider’s Tech Reference library for more stories.

If you work with Google Sheets often enough, you’ll inevitably need to get data from one spreadsheet into another.

You could always simply copy and paste the cells in question, but if you do that, there’s no live connection between the two sheets – if the original data changes, your second spreadsheet will become outdated.

Instead, you can use the IMPORTRANGE function, which quickly and easily helps you import data from one spreadsheet into another and keeps the two spreadsheets in sync at all times.

How to use IMPORTRANGE in Google Sheets

1. With only two arguments, using the IMPORTRANGE function is usually quite simple. Suppose you have a spreadsheet and you want to import the table into a new spreadsheet.

2. Click the URL in the address bar at the top of the browser and copy it. Alternately, you can copy just the spreadsheet key from within the URL.

How to use IMPORTRANGE in Google Sheets 6
You can import any range of cells from a spreadsheet like this into another by copying the URL or spreadsheet key.

3. In the new spreadsheet, type “=IMPORTRANGE(” – without the quotes.

4. Paste the URL and add a closing quote (“).

5. Type a comma, add a quote (“) and enter the range of cells you want to include. It should look like this: “Sheet1!B1:C6” Here, we’re specifying that we want the spreadsheet named “Sheet1,” and want cells B1 through C6.

6. Add a closing parenthesis and press Enter.

7. The complete function should look something like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Zoq0M0RG-RLYZ9HjOf01ff9eSPIYY3s/edit#gid=1027643093", "Sheet2!A1:C12")
How to use IMPORTRANGE in Google Sheets 2
This example uses the spreadsheet key instead of the full URL.

8. You might have noticed, though, that the data didn’t import – there’s a #REF! error in the cell instead. Click this cell and you’ll see a message that you need to connect these sheets. Click “Allow access” and then, a moment later, the data should appear. You’ll only need to do this once for each spreadsheet you import data from.

How to use IMPORTRANGE in Google Sheets 3
You only need to allow access to a spreadsheet once.

How to use the IMPORTRANGE function with a named range

If you prefer, you can use a named range instead of specifying the range in the manual way.

1. In the original spreadsheet, select the range and then right-click.

2. In the dropdown menu, choose “Define named range.”

How to use IMPORTRANGE in Google Sheets 4
The option to name a range of cells is in the right-click menu.

3. In the Named ranges pane that appears, give the selection a name and then click “Done.”

How to use IMPORTRANGE in Google Sheets 5
Name the range and it will include the name of the sheet, simplifying the task of importing cells.

4. Now when you add your range_string to the IMPORTRANGE function, you can just enter this name, which already includes the name of the sheet. It’s much easier than building the argument by hand.

How to use IMPORTRANGE in Google Sheets 66
Just remember to enclose the name range in quotes, the same as if you specified a range manually.

Using the spreadsheet URL or spreadsheet key

There are a few nuances in the way this function works you should be aware of. Let’s start with the URL.

You have a choice: You can use the entire spreadsheet URL or you can use just the spreadsheet key, which is the part of the URL that follows the “d/.” For example, suppose you had a spreadsheet with this URL:

https://docs.google.com/spreadsheets/d/1K6Jy9BAUsNLYtbEIIxI3LONV9JQ0hTY/edit#gid=25213

You can use the entire URL, or just the part after the d/:

1K6Jy9BAUsNLYtbEIIxI3LONV9JQ0hTY

Both options work exactly the same; the only difference is convenience, so use whichever works best for you. Either way, always enclose this argument in quotes.

How to use IMPORTRANGE in Google Sheets 1
Every spreadsheet has a unique spreadsheet key.

Using the range_string

The range_string also has its own quirks. Specifically, when you enter the range in the IMPORTRANGE function, you need to clarify which sheet the cells are located in. Keep in mind that a spreadsheet might have many tabs, each being its own sheet. As a result, this argument takes this form:

Sheet1!A1:A12

In this example, Sheet1 is the name of the sheet, and the cells are indicated by the range A1:A12. You need to always include an exclamation mark between the sheet name and the range, and like the URL, always enclose it in quotes.

Is Google Drive secure? How Google uses encryption to protect your files and documents, and the risks that remainHow to convert an Excel spreadsheet to a Google Sheets document19 of the best Google Drive tips and tricks for getting the most out of the serviceHow to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheets

Read the original article on Business Insider

What is a CSV file? How to open, use, and save the popular spreadsheet file in 3 different apps

business professional using laptop in cafe
A CSV file is a plain text file that can be opened in a variety of programs.

  • A CSV (comma-separated values) file is a simple text file in which information is separated by commas.
  • CSV files are most commonly encountered in spreadsheets and databases.
  • You can use a CSV file to move data between programs that aren’t ordinarily able to exchange data.
  • Visit Insider’s Tech Reference library for more stories.

If you spend any time with spreadsheet applications like Microsoft Excel or Google Sheets – or even import certain kinds of data into Microsoft Outlook – you will inevitably encounter a comma-separated values file, commonly known as a CSV.

A CSV file is a simple text file that you can open in a wide variety of programs, including any program that works with plain text like the Notepad app; what makes a CSV file unique is the way its content is organized.

What to know about a CSV file

A CSV file, as the name implies, typically separates information using commas. It’s a way to exchange structured information, like the contents of a spreadsheet, among programs that can’t necessarily talk to one another directly.

As long as two programs can both open a CSV file, they can exchange data. For example, you can save contact information from Microsoft Excel as a CSV file, and import it into the address book in Microsoft Outlook.

A typical CSV file looks like this, where each line contains the same sequence of data so any program which needs to read it knows what to expect:

Product, Size, Color, Price

Shirt, Medium, Blue, $14

Shirt, Large, Red, $15

Pants, Medium, Khaki, $23

Despite the name, a CSV doesn’t need to rely on commas as the separator between pieces of information. This separator, called a delimiter, can be a semicolon, space, or some other character, though the comma is most common.

And of course, once you’ve imported your CSV file into any of these programs, you can then edit the data like you would with any other file.

How to split cells into columns in Microsoft Excel using the ‘Text to Columns’ featureHow to convert an Excel spreadsheet to a Google Sheets documentHow to see the edit history for cells in Google Sheets and spot changes or mistakes in a spreadsheetThe 35 best Google Sheets keyboard shortcuts for speeding up your workflow on a Mac or PC

Read the original article on Business Insider

Is Google Drive secure? How Google uses encryption to protect your files and documents, and the risks that remain

Google Drive app on smartphone
Overall, Google Drive is fairly secure, but unlike some end-to-end encrypted apps, it has vulnerabilities.

  • Google Drive is generally very secure, as Google encrypts your files while they’re being transferred and stored.
  • However, Google can undo the encryption with encryption keys, meaning that your files can theoretically be accessed by hackers or government offices.
  • You can make Google Drive more secure by using two-factor authentication and being careful when giving other apps permission to use your Drive.
  • Visit Insider’s Tech Reference library for more stories.

Google Drive is quickly becoming the most popular storage service around. And with more than a billion users and over 2 trillion files saved, it needs to be secure.

But Google users have been victim to hacks before – in 2014, approximately 5 million Gmail usernames and passwords were stolen and leaked online.

So if you use Google Drive, you might be wondering how secure your files really are.

How Google Drive secures your files and data

Regardless of previous hacks, the risk of using Google Drive is low. Google uses the strong 256-bit Advanced Encryption Standard (AES) encryption on all its Google Drive servers (with the exception of a small number of storage devices that date prior to 2015 – those use AES128 encryption instead).

Likewise, when the data is in transit between users and Google Drive servers, Google uses the Transport Layer Security (TLS) protocol to protect the data and prevent interception. 

In short: your data is largely secure.

Is_Google_Drive_secure 1
Google Drive uses enhanced encryption tools for both file transport and storage.

How Google Drive may be vulnerable

Some security experts don’t love that Google keeps encryption keys for all the files on Google Drive. Encryption keys are tools that let Google (or whoever has the keys) decrypt files, bypassing all their security.

“Because they are in control of these encryption keys, it can lead to vulnerabilities for its users,” said Kristen Bolig, founder at SecurityNerd. “They have the power to decrypt files which can make them easier for hackers.” 

This is in contrast to apps like Signal, where not even the company that runs the app can access your data.

Moreover, Google is subject to governments and law enforcement. “If your files are subpoenaed, depending on what Google decides, it might not take a security breach to forfeit your privacy,” said Monica Eaton-Cardone, chief operating officer of Chargebacks911.

And as is often the case with cloud services, the most significant risks aren’t related to the encrypted infrastructure, but with the user, and Google Drive has a number of user-related vulnerabilities. 

Google Drive lacks cohesive organizational permissions, for example. Nick Santora, CEO of Curricula, said, “The way Dropbox uses folders allows us to segment data by department and only give employees in that department access to those folders. Google makes this extremely difficult to do. Everything you do is a one-off. The permissions system is ad hoc, which leads to mistakes.”

Is_Google_Drive_secure 2
Google has no coherent system for file permissions, so every file permission is applied in a one-off, highly error-prone process.

How to protect yourself as a Google Drive user

The biggest risk to your Google Drive data is often you – along with the computers or devices you’ve connected to Google Drive. Remember that in general, any files on Google Drive get synchronized to your computer, so those files are vulnerable. “You can use encryption to further hide and protect your files,” Bolig suggested. 

In addition, you can take advantage of two-factor authentication to prevent hackers from accessing your files from another device, even if they take your username and password. And of course, always make sure you have a strong password.

Security.org editor Gabe Turner said it’s important to “remove any apps or browser extensions that have access to Google Drive unnecessarily.” Every app with permission to access Google Drive is another vector for hackers and a security vulnerability. 

How to share files on Google Drive in 3 different ways, and choose who can view, comment, or editHow to add the Google Drive app to your desktop on a PC and sync all of your files easilyHow to upload files to your Google Drive on desktop or mobile (and back them up for safekeeping)How to create and share a Google Drive folder with customized sharing settings

Read the original article on Business Insider