How to Edit Pivot Table in Google Sheets

Pivot tables in Google Sheets are incredible for summarizing large datasets, but making changes after you've created one can feel a bit confusing. If you find yourself staring at an existing pivot table unsure of how to tweak, filter, or update it, you're in the right place. This guide will walk you through exactly how to edit any pivot table in Google Sheets, step-by-step.

A Quick Refresher: What's a Pivot Table Again?

Before we dive into editing, let's quickly recap what a pivot table does. Imagine you have a spreadsheet with hundreds or thousands of rows of sales data. It might include columns for Date, Sales Rep, Region, Product, and Sale Amount.

A pivot table lets you reorganize and summarize that information dynamically. Instead of manually filtering and using formulas, you can instantly answer questions like:

• What are the total sales for each sales rep?
• Which region sold the most of a specific product?
• How did our sales break down by month?

It takes your raw, detailed data and "pivots" it into a condensed, meaningful summary report. Now, let's learn how to control that report.

Getting Familiar with the Pivot Table Editor

The key to editing a pivot table is the Pivot table editor pane. If you don't see it, don't worry - it's just hidden. Simply click any cell inside your pivot table, and the editor pane will automatically appear on the right side of your screen.

This editor is your command center. It’s broken down into four main sections, plus a spot for your data range and suggestions:

• Data Range: This defines the source data your pivot table is using (e.g., Sheet1!A1:F500).
• Rows: Determines what data appears down the left side of your table as row headers.
• Columns: Determines what data appears across the top of your table as column headers.
• Values: This is what your table is calculating or measuring (e.g., summing up revenue, counting sales).
• Filters: Allows you to selectively show or hide certain data from your report.

Understanding these four areas is the foundation for making almost any edit you need.

How to Modify Rows, Columns, and Values

Most of your edits will involve adding, removing, or reordering the fields in the Rows, Columns, and Values sections.

Adding, Removing, and Reordering Fields

Let's say you have a pivot table showing total sales by Sales Repr (as rows). Here’s how you could modify it:

• Add a Field: Want to break down the sales by Region as well? In the editor pane, click the "Add" button next to Rows and select "Region." Now you'll see each sales rep's results broken down by region. You can also drag fields from the "Suggestions" area directly into the Rows, Columns, or Values sections.
• Remove a Field: Decided the regional breakdown is too much detail? Simply click the "X" button next to the "Region" field in the Rows section to remove it.
• Reorder Fields: The order of fields matters. If you have "Sales Repr" and then "Region" in the Rows section, you'll see each rep's regional performance. If you drag "Region" to be above "Sales Repr," the table will pivot to show each region's results broken down by rep. Just click and drag the fields within a section to change their hierarchy.

This same logic applies to the Columns section. You could, for instance, drag the "Product Category" field to columns to see sales for each rep organized by the type of product they sold.

Changing How Values are Calculated

The Values section is where you control the numbers inside your table. By default, Google Sheets often uses SUM for numeric data and COUNTA for text data, but you can change this.

Imagine your pivot table is showing the SUM of Sale Amount. You can easily change it:

1. In the Values section, click on the field (e.g., "SUM of Sale Amount").
2. A dropdown menu labeled "Summarize by" will appear.
3. Here, you can switch from SUM to another function, such as:

This is a quick way to switch from "How much did we sell?" (SUM) to "How many deals did we close?" (COUNT).

Changing How Values are Displayed

You can also change how a value is displayed in relation to other data. In the same Values settings, look for the "Show as" dropdown. This allows you to view data as a percentage.

For example, instead of seeing raw sales numbers, you could select:

• % of grand total: Shows each value as a percentage of the total for the entire table.
• % of row: Shows each cell's value as a percentage of its row's total.
• % of column: Shows each cell's value as a percentage of its column's total.

This is extremely useful for quickly seeing which sales reps contributed the most to the overall revenue without you needing to write a single formula.

Filtering Data in Your Pivot Table

Filters are one of the most powerful pivot table features. They let you narrow down your data without altering the original structure. In the editor pane, click the "Add" button in the Filters section and choose the field you want to filter by - for example, "Region."

You now have two ways to filter:

1. Filter by Values

This is the most common filtering method. After adding a filter (like "Region"), you'll see a dropdown that says "Showing all items." Click on it. A list of all unique values in that column will appear (e.g., "North," "South," "East," "West").

You can then uncheck the values you want to hide. For instance, unchecking "West" will remove all data related to the West region from your pivot table summary. It's a quick way to focus on specific segments of your data.

2. Filter by Condition

Filtering by condition allows for more advanced rules. Instead of selecting values manually, click "Filter by condition." You'll see a dropdown menu that lets you set rules like:

• Text contains: Show only rows where the text includes a certain word (e.g., filter product names containing "Pro").
• Date is after: Show data only after a specific date (e.g., show sales from the second half of the year).
• Greater than: Show only values above a certain number (e.g., filter to only see sales greater than $1,000).

Conditions are great when your list of values is too long to select manually or when you need a more dynamic rule.

How to Update the Source Data Range

This is a classic pivot table problem. You've added new rows of sales data to your original source sheet, but your pivot table isn’t showing the new numbers. Why?

Your pivot table is looking at a specific range (e.g., A1:F500). If you add data in row 501, the pivot table doesn't know it's there. You need to update the data range.

Here's how to fix it:

1. Click on your pivot table to open the editor.
2. At the top of the editor, click inside the Data range box.
3. A window will pop up allowing you to either type a new range or highlight the correct one.
4. To avoid this problem in the future, set your range to include the entire columns, like A:F. This tells Google Sheets to always include all data in columns A through F, so any new rows you add will automatically be included the next time the pivot table refreshes.

Formatting Your Pivot Table for Readability

A functional pivot table is great, but a clean, well-formatted one is even better. You can format a pivot table just like any other range in Google Sheets.

• Apply Alternating Colors: Select your entire pivot table, go to Format > Alternating colors to make the rows much easier to read.
• Format Numbers: Use the toolbar options to format your values as currency ($), percentages (%), or to adjust the number of decimal places. This makes your report look professional and polished.
• Align Text: Adjust the horizontal and vertical alignment of your headers and values to improve layout and readability.

Final Thoughts

Mastering how to edit pivot tables in Google Sheets boils down to getting comfortable with the editor pane. By understanding how to manipulate rows, columns, values, and filters, you gain complete control over your data, allowing you to quickly pull insights from thousands of rows without writing a single formula.

While pivot tables are powerful for analyzing data inside a spreadsheet, the process of exporting data and wrangling it manually can still be a major time sink. We built Graphed to automate this entire process. Instead of manually building and editing reports, you can connect your data sources (like Google Analytics, Shopify, or your CRM) once and then just ask for a dashboard in plain English. Graphed builds interactive, real-time dashboards for you in seconds, saving you from the hassle of spreadsheets altogether.