How to Edit a Pivot Table in Excel

A PivotTable turns a mountain of data into a clean, summarized report, but its true power lies in its dynamic nature. You rarely create a PivotTable and leave it as is, it's a living analysis tool that needs to evolve with your questions. This guide will walk you through the essential ways to edit a PivotTable in Excel, from updating your source data to completely changing its structure and design.

Refresh Your PivotTable and Update Your Data Source

Perhaps the most common edit you'll make is updating the data behind your PivotTable. This happens when you add new rows of information (like a new month of sales data) or new columns (like a new marketing channel). There are two distinct steps here: refreshing and changing the source.

Quick Refresh vs. Changing the Data Source

A simple Refresh updates the PivotTable with any changes made to your existing data range. If you fixed a typo or corrected a number inside the original source data, a refresh is all you need. To do this, right-click anywhere inside your PivotTable and select Refresh, or go to the PivotTable Analyze tab and click the Refresh button.

However, if you've added new rows or columns of data outside the original source range, a refresh won't include them. For that, you need to tell Excel that the size of your dataset has changed.

Follow these steps to change the data source:

1. Click anywhere inside your PivotTable to activate the PivotTable Analyze tab in the ribbon.
2. Click on Change Data Source. A dialog box will appear, showing the current data range with a "marching ants" border around it on your worksheet.
3. In the text box, type in the new range, or simply click and drag to select the entire new data range, including your new rows or columns.
4. Click OK. Your PivotTable will automatically refresh to include the new data.

Pro Tip: The best way to avoid having to manually change your data source every time you add data is to format your source data as an Excel Table (select your data, then go to Insert > Table or press Ctrl+T). When you create a PivotTable from an Excel Table, it automatically expands to include any new rows or columns you add. Simply hit Refresh, and Excel handles the rest.

Add, Remove, or Rearrange Fields

Changing the fields in your PivotTable is how you answer different questions with your data. The PivotTable Fields pane, which usually appears on the right side of the screen when you click on your table, is your control panel for this.

The pane is divided into two sections:

• Field List: A list of all the column headers from your source data.
• Areas: Four boxes (Filters, Columns, Rows, and Values) where you drag and drop fields to build your report.

Here’s how you can modify the fields and layout:

• To add a field: Drag a field from the Field List and drop it into one of the four Areas boxes. Alternatively, you can just check the box next to the field name, and Excel will try to place it in a logical area (text fields usually go to Rows, numbers to Values).
• To remove a field: Drag the field name out of the Areas box, or simply uncheck the box next to its name in the Field List.
• To rearrange fields: Drag fields between the different Areas to change how your data is summarized. For example, moving a field from Rows to Columns will pivot your data, displaying it horizontally instead of vertically.

Example: Rearranging a Sales Report

Imagine your initial PivotTable shows Total Sales (in Values) broken down by Region (in Rows). It looks good, but now you want to see which Product Category is selling best within each region.

Simply find Product Category in your Field List and drag it into the Rows area, right below the Region field. Your PivotTable will instantly update to show a sub-category breakdown, answering your new question in seconds.

Customize Your Calculations and Number Formatting

By default, if you drop a field with numeric data into the Values area, Excel summarizes it with a SUM. If you drop in text, it gives you a COUNT. But you have full control over these summary calculations.

Changing the Summary Calculation

You can quickly switch from Sum to Average, Count, Max, Min, or other calculations.

1. In the Values area of the PivotTable Fields pane, click the arrow next to the field you want to change (e.g., "Sum of Sales").
2. Select Value Field Settings.
3. In the dialog box that appears, look for the "Summarize value by" tab. Here you can choose from different calculation types like Count, Average, Max, Min, Product, etc.
4. Select your desired calculation and click OK.

This is extremely useful. Switching Sum of Sales to Count of Sales can tell you how many individual transactions occurred, while Average of Sales can reveal the average deal size.

Formatting Your Numbers

Numbers in a PivotTable often need formatting to be readable—dollars need dollar signs and commas, for instance. Instead of formatting the cells directly, which can be lost when you refresh or pivot, it's best to set it within the PivotTable itself.

1. Open the Value Field Settings dialog box again (just like in the previous step).
2. Click the Number Format button at the bottom left.
3. This opens the standard Format Cells dialog box where you can choose a format like Currency, Percentage, Number, etc., and customize the decimal places.
4. Choose your format and click OK twice to apply the change. Now, your formatting will stick no matter how you pivot the data.

Sort and Filter Your PivotTable Data

Sorting and filtering allow you to focus on the most important information within your report.

Sorting Your Data

By default, row labels are sorted alphabetically (A to Z). But you can easily change this:

• To sort row or column labels: Click the small filter/sort arrow next to "Row Labels" or "Column Labels" and choose a sort option (Sort A to Z, Sort Z to A).
• To sort by values: If you want to see your best-performing region at the top, right-click on any number in your main values column (e.g., a "Total Sales" number) and select Sort > Sort Largest to Smallest. The entire table will reorder to show your top performers first. You can do the same for Smallest to Largest.

Filtering Your Data

Filters help you pare down a large report into just the data you need. You have a few great options in Excel:

1. The Field Drop-down Menu: The quickest way to filter. Just click the filter arrow for Row or Column Labels, and you can checkbox the items you want to keep or remove.
2. The Filters Area: Drag a field into the Filters box in the PivotTable Fields pane. This creates a master filter that controls the entire report. For example, adding "Year" to the Filters area lets you view your entire sales report for just 2023 or 2024.
3. Using Slicers and Timelines: These are modern, interactive, button-based filters that are much easier to use than dropdown menus, especially for dashboards.

Group Fields for Better Analysis

Grouping is one of the most powerful—and often overlooked—PivotTable features. It allows you to create custom summaries from your raw data without altering the source.

Grouping By Date

If you have a column with a list of daily dates, a PivotTable can feel overwhelming. Excel can easily group these dates into logical time periods.

1. Right-click on any date in the Row or Column area of your PivotTable.
2. Select Group from the menu.
3. A dialog box will appear allowing you to group by Seconds, Minutes, Hours, Days, Months, Quarters, and Years. You can select multiple options (e.g., Years and Months).
4. Excel will instantly create collapsed groupings, which you can expand and collapse as needed.

Grouping Numeric and Text Fields

You can also group numbers or text items. For numeric data like customer ages, you can group them into ranges (e.g., 20-29, 30-39, etc.). For text data, you can manually select several items—like a handful of states—right-click, and select “Group” to create a custom "Sales Territory" right inside your PivotTable without changing your original data source.

Adjusting the Layout and Design

Finally, you can change the cosmetic and structural appearance of your table to make it easier to read.

Select your PivotTable and go to the Design tab in the ribbon. Here you will find several key settings:

• Report Layout: This allows you to switch between default Compact Form (which saves space), Outline Form, or Tabular Form (which looks more like a traditional table and is often easier to read and copy).
• Grand Totals & Subtotals: You can choose to show or hide the grand totals for rows and columns, as well as the subtotals for inner grouped items.
• PivotTable Styles: This gallery offers dozens of pre-formatted designs to quickly give your PivotTable a professional, color-coordinated look.

Final Thoughts

Editing a PivotTable is all about asking new questions and adjusting the view to find a clear answer. By mastering skills like changing the data source, rearranging fields, customizing calculations, and using groups and slicers, you can turn a static report into an endlessly flexible analysis tool that keeps up with your curiosity.

Of course, if you spend a lot of time wrangling spreadsheets and manually updating reports across different platforms like Google Analytics, Shopify, and Facebook Ads, you know this process can consume hours every week. At my company, we created Graphed to eliminate this friction entirely. Instead of struggling with data sources and field lists, you can connect your accounts and simply ask in plain English: “Show me a dashboard of sales by region and product category for last quarter.” We instantly build an interactive, real-time dashboard for you, completely automating the setup and updating so you can focus on insights, not manual table editing.