Work with data

on

Work with data


Create and use pivot tables

You can use pivot tables to narrow down a large data set or analyze relationships between data points.

Add and edit pivot tables

  1. On your computer, open Google Sheets.
  2. Open the spreadsheet with the data you want to put in a table.
  3. Select the cells with data you want to use.
    • Put the data you want to use into columns. Each column is one data set.
    • Each column needs a header.
  4. In the menu, click Data and then Pivot table. Click the pivot table sheet, if it’s not already open.
  5. Next to each category in the "Report editor" window, click Add field, then click a column to use.
    • Your table has one row or column for each number in the column you use, even if the number is in more than one cell.
    • Data from the columns you use for "Values" is sorted into the matching rows and columns.
    • If you want to add a field to the "Values" category that calculates values based on a formula, click Calculated field and enter the formula.
    • "Filters" hide data that you don't want to show in the table.
  6. You can change how your data is listed, sorted, summarized or filtered. Next to what you want to change, click the Down Arrow Down Arrow.
  7. To format the pivot tables, use the buttons in the toolbar.

Change or remove data

  • To move a data set from one category to another, drag it to the category you want.
  • To remove a data set from a category, click Remove Remove.
  • To expand the range of data used for your pivot table, click Edit range.
  • The pivot table refreshes any time you change the cells it's drawn from.

Name a range of cells

You can name ranges in Google Sheets to keep better track of them and create cleaner formulas.
For example, instead of using "A1:B2" to describe a range of cells, you could name the range "budget_total." This way, a formula like "=SUM(A1:B2, D4:E6)" could be written as "=SUM(budget_total, quarter2)."

Name a range

  1. Open a spreadsheet in Google Sheets.
  2. Select the cells you want to name.
  3. Click Data and then Named ranges. A menu will open on the right.
  4. Type the range name you want.
  5. To change the range, click Spreadsheet Grid.
  6. Select a range in the spreadsheet or type the new range into the text box, then click Ok.
  7. Click Done.
Range names:
  • Can contain only letters, numbers, and underscores.
  • Can't start with a number, or the words "true" or "false."
  • Can't contain any spaces or punctuation.
  • Must be 1–250 characters.
  • Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give your range a name like "A1:B2" or "R1C1:R2C2."

Edit or delete a named range

  1. Open a spreadsheet in Google Sheets.
  2. Click Data and then Named ranges.
  3. On the named range you want to edit or delete, click Edit Edit.
  4. To edit the range, enter a new name or range, then click Done.
  5. To delete the named range, next to the name, click Delete Trash.
  6. On the menu that opens, click Remove.
Note: When you delete a named range, any formulas that reference it will no longer work. Protected ranges that reference a named range will use the cell values and continue to work.

Sort and filter your data

You can sort and filter data in Google Sheets to organize and analyze it.
Note: Filter views are only available on a computer. See the FILTER article for info about the function.

FILTER

Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions.

Sample Usage

FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
FILTER(A2:C5, {TRUE; TRUE; FALSE; TRUE})
FILTER(A2:B10, NOT(ISBLANK(A2:A10)))

Syntax

FILTER(range, condition1, [condition2, ...])
  • range - The data to be filtered.
  • condition1 - A column or row containing true or false values corresponding to the first column or row of range, or an array formula evaluating to true or false.
  • condition2 ... - [ OPTIONAL ] - Additional rows or columns containing boolean values TRUE or FALSE indicating whether the corresponding row or column in range should pass through FILTER. Can also contain array formula expressions which evaluate to such rows or columns. All conditions must be of the same type (row or column). Mixing row conditions and column conditions is not permitted.
    • condition arguments must have exactly the same length as range.

Notes

  • FILTER can only be used to filter rows or columns at one time. In order to filter both rows and columns, use the return value of one FILTER function as range in another.
  • If FILTER finds no values which satisfy the provided conditions, #N/A will be returned.

See Also

UNIQUE: Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.

Sort your data

  1. Open a spreadsheet in Google Sheets.
  2. Highlight the group of cells you'd like to sort. To select the entire sheet, click the top left corner of the sheet.
  3. Click Data and then Sort range.
  4. If your columns have titles, click Data has header row.
  5. Select the column you'd like to be sorted first and whether you would like that column sorted in ascending or descending order. This also sorts numbers.
    • Click +Add another to add another sorting rule. Sorting will be done according to the order of your rules.
    • To delete a rule, click Close Close.
  6. Click Sort. Your range will be sorted.

Filter your data

To see and analyze data in a spreadsheet, use filters. Filters let you hide data that you don’t want to see. You’ll still be able to see all your data when you turn the filter off.

Filters vs. filter views

Both filters and filter views help you analyze a set of data in a spreadsheet.
Filters can be useful if:
  • You want everyone viewing your spreadsheet to see a specific filter when they open it.
  • You want your data to stay sorted after using the filter.
Filter views can be useful if:
  • You want to save multiple views.
  • You want to name your view.
  • You want others to be able to view the data differently. Since filter views need to be turned on by each person viewing a spreadsheet, each person can view a different filter view at the same time.
  • You want to share different filters with people. You can send different filter view links to different people so everyone will see the most relevant information for them.
  • You want to make a copy or create another view with similar rules.
  • You don't have edit access to a spreadsheet and still want to filter or sort. In this case, a temporary filter view will be created.
Note: You can import and export filters, but not filter views.

Use filters in a spreadsheet

To temporarily hide data in a spreadsheet, add a filter.
Note: When you add a filter, anyone with access to your spreadsheet will see the filter too. Anyone with permission to edit your spreadsheet will be able to change the filter.
Filter your data
To filter your data:
  1. Open a spreadsheet in Google Sheets.
  2. Select a range of cells.
  3. Click Data and then Filter.
  4. To see filter options, go to the top of the range and click Filter Filter.
    • Filter by condition: Choose from a list of conditions or write your own. For example, if the cell is empty, if data is less than a certain number, or if the text contains a certain letter or phrase.
    • Filter by values: Uncheck any data points that you want to hide and click OK. If you want to choose all data points, click Select all. You can also uncheck all data points, by clicking Clear.
    • Search: Search for data points by typing in the search box. For example, typing "P" will shorten your list to just the names that start with P.
  5. To turn the filter off, click Data and then Turn off filter.
Sort your data while it’s filtered
  • You can sort data with a filter turned on.
  • When you sort your data, only the data in the filtered range will be sorted.
  • You’ll see a green border around the cells in the filtered range.

Create, name, and save a filter view

Use a filter view when:
  • You want to save your filter and use it later.
  • You don't want to disrupt others' view of the data.
  • You want to share a link to a specific filter with others.
  • You can’t edit a spreadsheet, but you want to filter or sort data.
Create, save, or delete a filter view
  1. Open a spreadsheet in Google Sheets.
  2. Click Data and then Filter views and then Create new filter view.
  3. Sort and filter the data.
  4. To close your filter view, go to the top right and click Close Close.
  5. Your filter view is saved automatically.
To delete or duplicate a filter view go to the top right and click Settings Settings and then Delete or Duplicate.
Rename a filter view
  1. Open a spreadsheet in Google Sheets.
  2. Click Data and then Filter views.
  3. Select a filter view.
  4. Click the filter view name in the top left of the black bar and type the new name.
  5. Press Enter.
See an existing filter view
  1. Open a spreadsheet in Google Sheets.
  2. Click Data and then Filter views.
  3. Select a filter view.
  4. Your filter will be applied to the spreadsheet.
  5. To close your filter view, go to the top right and click Close Close.
Save a filter as a filter view
  1. Open a spreadsheet in Google Sheets.
  2. Apply a filter.
  3. Click Data and then Filter views and then Save as filter view.
Use filter view with "view only" access
If you have permission to view a spreadsheet but not edit it, you can still use filter views:
  • To apply existing filter views, click Data and then Filter views.
  • You can create a temporary filter view that only you can use. Because you don’t have "edit" access to the spreadsheet, the filter view won't be saved.
  • Only users with permission to edit a spreadsheet can create filter views that anyone viewing the spreadsheet can use.

Create an in-cell dropdown list

Create drop-down lists in a cell with Google Sheets.  

Create a drop-down list

  1. Open a spreadsheet in Google Sheets.
  2. Select the cell or cells where you want to create a drop-down list.
  3. Click Data and thenValidation.
  4. Next to "Criteria," choose an option:
    • List from a range: Choose the cells that will be included in the list.
    • List of items: Enter items, separated by commas and no spaces.
  5. The cells will have a Down arrowDown Arrow. To remove the arrow, uncheck "Display in-cell button to show list."
  6. If you enter data in a cell that doesn’t match an item on the list, you’ll see a warning. If you want people to only enter items from the list, choose "Reject input" next to "On invalid data."
  7. Click Save. The cells will show a drop-down list. To change the color of a cell based on the option selected, use conditional formatting.
Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to apply format rules to.
  3. Click Format and then Conditional formatting. A toolbar will open to the right.
  4. Create a rule.
    • Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met.
    • Color scale: Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.
  5. Click Done.
Example
A teacher can highlight test scores to see which students scored less than 80%.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the test scores.
  3. Click Format and then Conditional formatting.
  4. Under "Format cells if," click Less than. If there's already a rule, click it or Add new rule and then Less than.
  5. Click Value or formula and enter 0.8.
  6. To choose a red color, click Fill Fill.
  7. Click Done. The low scores will be highlighted in red.

Use advanced conditional formatting

Use custom formulas with conditional formatting
You can use custom formulas to apply formatting to one or more cells based on the contents of other cells.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to format.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Click Value or formula and add the formula and rules.
  6. Click Done.
Note: Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the INDIRECT function.

INDIRECT

Returns a cell reference specified by a string.

Sample Usage

INDIRECT("Sheet2!"&B10)
INDIRECT("A2")
INDIRECT("R2C3", FALSE)

Syntax

INDIRECT(cell_reference_as_string, [is_A1_notation])
  • cell_reference_as_string - A cell reference, written as a string with surrounding quotation marks.
  • is_A1_notation - [OPTIONAL - TRUE by default] - Indicates if the cell reference is in A1 notation or R1C1 notation.

Examples

Student Info data as a separate sheet in the spreadsheet.

Example 1

To highlight when there's more than one occurrence of the same value in your data:
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the range you want to format. For example, cells A1 to A100.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)>1."
  6. Choose other formatting properties.
  7. Click Done.

Example 2

To format an entire row based on the value of one of the cells in that row:
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the range you want to format, for example, columns A:E.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Write the rule for the first row. For example, if you want to make the whole row green if the value in column B is "Yes", write a formula like "=$B1="Yes"."
  6. Choose other formatting properties.
  7. Click Done.

Absolute vs. relative references

Often, you will need to add dollar signs ($) in front of letters and numbers in formulas so that the formatting is applied using absolute references as opposed to relative references (A1 to B1, A2 to B2).
Use wildcard characters with conditional formatting
You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting.
  • To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc."
  • To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca."
  • To match a question mark or asterisk in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing "a~?c" would format cells with "a?c" but not "abc" or "a~?c."
Notes:
  • To remove a rule, point to the rule and click Remove Remove.
  • Rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To reorder rules, click and drag them.
  • If you copy and paste from a cell or range that has formatting rules, these rules will be applied when you paste the copied data.

Change or delete a drop-down list

  1. Open a spreadsheet in Google Sheets.
  2. Select the cell or cells you’d like to change.
  3. Click Data and thenValidation.
  4. To change the options listed, edit the items next to "Criteria."
  5. To delete a list, click Remove validation.
  6. Click Save. If you change the contents of the range you've selected, the changes will be made in the list automatically.

Automatically create a series or list

You can use autofill to create a series of numbers, letters, or dates in Google Sheets

Use autofill to complete a series

  1. Open a spreadsheet in Google Sheets.
  2. In a column or row, enter text, numbers, or dates in at least two cells next to each other.
  3. Highlight the cells. You’ll see a small blue box in the lower right corner.
  4. Drag the blue box any number of cells down or across.
    • If the cells form a series of dates or numbers, the series will continue across the selected cells.
    • If the cells don’t form a series of dates or numbers, the list of values will repeat across the selected cells.

Use conditional formatting rules in Google Sheets




Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to apply format rules to.
  3. Click Format and then Conditional formatting. A toolbar will open to the right.
  4. Create a rule.
    • Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met.
    • Color scale: Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.
  5. Click Done.
Example
A teacher can highlight test scores to see which students scored less than 80%.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the test scores.
  3. Click Format and then Conditional formatting.
  4. Under "Format cells if," click Less than. If there's already a rule, click it or Add new rule and then Less than.
  5. Click Value or formula and enter 0.8.
  6. To choose a red color, click Fill Fill.
  7. Click Done. The low scores will be highlighted in red.

Use advanced conditional formatting

Use custom formulas with conditional formatting
You can use custom formulas to apply formatting to one or more cells based on the contents of other cells.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to format.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Click Value or formula and add the formula and rules.
  6. Click Done.
Note: Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the INDIRECT function.

INDIRECT

Returns a cell reference specified by a string.

Sample Usage

INDIRECT("Sheet2!"&B10)
INDIRECT("A2")
INDIRECT("R2C3", FALSE)

Syntax

INDIRECT(cell_reference_as_string, [is_A1_notation])
  • cell_reference_as_string - A cell reference, written as a string with surrounding quotation marks.
  • is_A1_notation - [OPTIONAL - TRUE by default] - Indicates if the cell reference is in A1 notation or R1C1 notation.

Examples

Student Info data as a separate sheet in the spreadsheet.

Example 1

To highlight when there's more than one occurrence of the same value in your data:
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the range you want to format. For example, cells A1 to A100.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)>1."
  6. Choose other formatting properties.
  7. Click Done.

Example 2

To format an entire row based on the value of one of the cells in that row:
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the range you want to format, for example, columns A:E.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule and then Custom formula is.
  5. Write the rule for the first row. For example, if you want to make the whole row green if the value in column B is "Yes", write a formula like "=$B1="Yes"."
  6. Choose other formatting properties.
  7. Click Done.

Absolute vs. relative references

Often, you will need to add dollar signs ($) in front of letters and numbers in formulas so that the formatting is applied using absolute references as opposed to relative references (A1 to B1, A2 to B2).
Use wildcard characters with conditional formatting
You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting.
  • To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc."
  • To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca."
  • To match a question mark or asterisk in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing "a~?c" would format cells with "a?c" but not "abc" or "a~?c."
Notes:
  • To remove a rule, point to the rule and click Remove Remove.
  • Rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To reorder rules, click and drag them.
  • If you copy and paste from a cell or range that has formatting rules, these rules will be applied when you paste the copied data.

Using arrays in Google Sheets

An array is a table (consisting of rows and columns) of values. If you want to group the values of your cells together in a particular order, you can use arrays in your spreadsheet.
Some functions return arrays. For example, IMPORTRANGE returns an array of values by importing the specified range from another spreadsheet. When you write a formula using IMPORTRANGE, you'll see its array result spill over to the cells to the right and below.
Any function that takes a range (e.g. A1:B6) as an input parameter will also accept an array in its place. For example, SPARKLINE takes a range as the first parameter to specify the values to plot. You can use the array result of IMPORTRANGEas SPARKLINE's input.
=SPARKLINE(IMPORTRANGE(...))

Create arrays

You can also create your own arrays in a formula in your spreadsheet by using brackets { }. The brackets allow you to group together values, while you use the following punctuation to determine which order the values are displayed in:
  • Commas: Separate columns to help you write a row of data in an array. For example, ={1, 2} would place the number 1 in the first cell and the number 2 in the cell to the right in a new column.
  • Semicolons: Separate rows to help you write a column of data in an array. For example, ={1; 2} would place the number 1 in the first cell and the number 2 in the cell below in a new row.
Note: For countries that use commas as decimal separators (for example €1,00), commas would be replaced by backslashes (\) when creating arrays.
You can join multiple ranges into one continuous range using this same punctuation. For example, to combine values from A1-A10 with the values from D1-D10, you can use the following formula to create a range in a continuous column: ={A1:A10; D1:D10}

Adding arrays to existing formulas

You can also use arrays with other existing formulas using brackets in order to organize the returns from your formulas into rows or columns.
For example, ={SUM(A1:A10), SUM(B1:B10)} will produce two values. The first cell will contain the sum of A1 to A10, the cell to the right will contain the sum of B1 to B10.

Separate cell text into columns

You can split delimited data, such as text separated by a comma, into several columns with Google Sheets. For example, a column with "Last name, First name" can be split into 2 columns: "Last name" and "First name."

Split data into columns

  1. Open a spreadsheet in Google Sheets.
    • If the data’s already in the sheet, select the cells you want to split.
    • If the data isn’t in the sheet yet, paste it.
  2. At the top, click Data and then Split text to columns.
  3. Optional: To change what character Sheets uses to split the data, click Comma.
Note: After pasting data, you can click Paste Paste and then Split text to columns.



Comments

Post a Comment