To create a pivot table in Google Sheets, select your data range, go to Insert > Pivot table, choose where to place it, then drag fields into the Rows, Columns, Values, and Filters areas in the Pivot table editor. This guide walks you through every step, from preparing your data to troubleshooting common issues.
What You’ll Need
- A Google account and access to Google Sheets in a web browser.
- A dataset organized in a table format: each column should have a header row, and each row should represent one record. No blank rows or columns within the data range.
- Basic familiarity with selecting cells and using the mouse or trackpad.
Step-by-Step
- Open your Google Sheets file. Go to sheets.google.com, sign in, and open the spreadsheet containing your data.
- Select your data range. Click and drag to highlight all cells that contain data, including the header row. For example, if your data is in columns A through E and rows 1 through 100, select A1:E100. Alternatively, click any single cell inside the data range—Google Sheets will usually auto-detect the entire range.
- Insert the pivot table. In the top menu, click Insert > Pivot table. A dialog box will appear.
- Choose the data range and placement. In the dialog, confirm the range under “Data range” (it should auto-fill based on your selection). Then choose either “New sheet” to place the pivot table on a fresh tab, or “Existing sheet” and specify a cell location (e.g., G1) to place it in the current sheet. Click Create.
- Open the Pivot table editor. After creation, a new panel will appear on the right side of the screen called the “Pivot table editor.” If it’s not visible, click anywhere on the pivot table to bring it up.
- Add rows. In the editor, find the “Rows” section. Click Add and choose a field (column header) to group your data by. For example, if you have a “Category” column, select it to list each category as a row.
- Add values. In the “Values” section, click Add and choose a numeric field to summarize, like “Sales” or “Quantity.” By default, Google Sheets will sum the values. To change the aggregation (e.g., to average or count), click the dropdown arrow next to the field name under “Values,” then select “Summarize by” and choose an option like SUM, AVERAGE, COUNT, MAX, MIN.
- Add columns (optional). If you want to break down data across columns, click Add in the “Columns” section and select a field. For instance, adding “Year” as a column will show each year as a separate column next to your rows.
- Add filters (optional). To limit the data included, click Add in the “Filters” section, choose a field, then set conditions (e.g., “Sales greater than 1000”). You can also filter by specific values using the “Filter by values” option.
- Reorder and adjust. Drag fields within the editor to reorder them. For example, in Rows, you can drag “Category” above “Subcategory” to create a hierarchy. You can also click the small “x” next to a field to remove it.
- Format the pivot table (optional). Use the toolbar to adjust number formats (e.g., currency), bold headers, or apply conditional formatting. The pivot table updates automatically when you change the underlying data, but you may need to refresh if you add new rows outside the original range.
Common Mistakes
- Blank rows or columns in the data: This can cause Google Sheets to misdetect the range, leading to missing data. Ensure your dataset is contiguous.
- No header row: Without headers, fields will appear as “Column A,” “Column B,” making it hard to understand what each field represents. Always have a header row.
- Mixing data types in a column: If a column contains both numbers and text, the pivot table may treat it as text and not sum correctly. Keep each column consistent.
- Forgetting to update the range when adding new data: If you add rows below your original range, the pivot table won’t include them unless you edit the range. Use a named range or an open-ended range like
A1:Eto avoid this. - Overlooking the “Summarize by” setting: Sometimes you want a count, but the default is sum. Check the aggregation method for each value field.
Tips
- Use named ranges: Select your data, go to Data > Named ranges, and create a named range (e.g., “SalesData”). When creating the pivot table, type the name in the range field. This makes it easier to expand the range later.
- Sort and filter within the pivot table: Click the filter icon in the row or column header of the pivot table to sort ascending/descending or filter specific values without altering the editor.
- Group dates: If you have a date column, you can group by day, month, quarter, or year. In the Rows section, click the dropdown next to the date field, then select “Date grouping” and choose the granularity.
- Create a calculated field: In the Values section, click “Add” > “Calculated field” to create custom formulas. For example, to show profit margin, type
=SUM(Sales)/SUM(Cost)(adjust field names to match your columns). - Refresh data: If you change the source data, the pivot table usually updates automatically. If not, click on the pivot table, then in the editor click the refresh icon (circular arrow) at the bottom.
Troubleshooting
- Pivot table not showing all data: Check the “Data range” in the editor (click the pencil icon next to the range). If you added new rows, update the range to include them. Also ensure there are no blank rows in the middle of your data.
- Values showing as “0” or blank: This often happens when the field selected is not numeric, or when the data contains text. Check the column type and ensure it contains numbers. Also verify the “Summarize by” setting—if it’s set to COUNT, it will count non-empty cells, which might be 0 if the cells are truly empty.
- Error: “Range not found” or “Invalid range”: This usually means the range name or coordinates are incorrect. Double-check that the range exists and is spelled correctly. If using a named range, ensure it’s defined.
- Pivot table editor not appearing: Click anywhere on the pivot table itself. The editor should open on the right. If it doesn’t, refresh the page and try again.
- Changes to source data not reflected: First, make sure you’re editing the correct sheet. If the pivot table is on a different sheet, it still references the original range. If you added rows beyond the original range, update the range as described above. Also, check for any filters that might be hiding new data.
Frequently Asked Questions
- Can I create a pivot table from data on another sheet? Yes. When creating the pivot table, in the “Data range” field, you can either type the sheet name (e.g.,
Sheet2!A1:E100) or use a named range that references another sheet. - How do I change the aggregation from sum to average? In the Pivot table editor, under “Values,” click the dropdown arrow next to the field name, then select “Summarize by” and choose AVERAGE (or COUNT, MAX, etc.).
- Can I add multiple values, like both sum and count? Yes. Under “Values,” click “Add” and select the same field again, then change the “Summarize by” for the second instance to COUNT. You’ll see two columns in the pivot table.
- How do I remove a field from the pivot table? In the Pivot table editor, hover over the field name in any section (Rows, Columns, Values, Filters) and click the “x” that appears on the right. Alternatively, click the field and press Delete on your keyboard.
- Is there a way to make the pivot table update automatically when I add new rows? Yes, if you use a named range that includes extra rows (e.g.,
A1:E1000) or an open-ended range likeA1:E. However, open-ended ranges may slow down performance with very large datasets. Otherwise, you’ll need to manually update the range. - Can I share a pivot table with others who don’t have Google accounts? Yes, you can share the spreadsheet as a viewer, commenter, or editor. If you want them to only see the pivot table, you can publish the sheet or use the “Share” settings to restrict access.
Related
- Excel Template for Expenses & E-Invoice?
- Excel Template for Annual Calendar: How to Make One
- Excel Budget Report Template: How to Build One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart Date Format: Fix the Axis LabelsTo change date labels on an Excel PivotChart, select the chart’s date axis, right-click it, choose Format Axis,…
- Excel Budget Dashboard Template: How to Build OneThe fastest way to get an Excel budget dashboard is to use a pre-built template from Microsoft (File…
- Excel Combo Chart Not Working? Fix the Common CausesTo create a combo chart in Excel for Microsoft 365 on Windows, select your data, choose Insert >…
- Excel IFS in Pivot Tables: How to Add and Summarize ItYes, you can use an IFS formula with an Excel PivotTable, but the dependable method is to put…
- Excel Pivot Table Not Counting Unique Values? FixExcel pivot tables do not have a built-in “count unique values” option, so if you’re trying to count…
- Google Sheets Chart Legend Labels: Edit and FixIn Google Sheets, chart legend labels usually come from the headings in the chart’s source data: edit the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.