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  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 .
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 .
• 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  Named ranges. A menu will open on the right.
4. Type the range name you want.
5. To change the range, click Spreadsheet .
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  Named ranges.
3. On the named range you want to edit or delete, click 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 .
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.