Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Adjust a Pivot Table in Excel

Cody Schneider••9 min read

A pivot table in Excel is one of the most powerful tools for making sense of large datasets, but its real value comes from your ability to adjust it. Changing a field, applying a filter, or modifying a calculation can transform a wall of numbers into clear, actionable insights. This guide will walk you through the most common ways to adjust your pivot tables to answer different business questions.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

The Control Center: Understanding the PivotTable Fields Pane

Once you create a pivot table, the PivotTable Fields pane on the right side of your screen becomes your primary workspace. If you don't see it, just click anywhere inside your pivot table, and it should appear. This pane is where all the adjustments happen, and it’s divided into two main sections:

  1. Field List: At the top, you’ll see a list of all the column headers from your source data. These are the building blocks of your report.
  2. Areas Layout: At the bottom, you’ll find four boxes that control how the report is structured: Rows, Columns, Values, and Filters.

Here’s a simple breakdown of what each area does:

  • Rows: Fields you drag here will appear as row labels on the left side of your pivot table. For example, placing a "Product Category" field here would list each category down the side.
  • Columns: Fields placed here will become column labels across the top of your table. For instance, dragging a "Region" field here would create a separate column for each region.
  • Values: This is where you put the numbers you want to calculate. Typically, this is a field like "Sales," "Revenue," or "Quantity." Excel will automatically summarize this data, usually with a SUM.
  • Filters: Dragging a field here adds a filter to the entire pivot table, allowing you to focus on a specific subset of your data (e.g., filtering for a particular year or sales representative).

Mastering how to move fields between these four areas is the foundation of adjusting your pivot table.

Adjusting How Your Data is Organized

The most common pivot table adjustment is simply changing how the data is grouped and viewed. By moving fields between the rows and columns, you can quickly explore different perspectives on your data.

Change Row and Column Labels

Imagine your pivot table shows "Region" in the Rows area and "Product Category" in the Columns area. This setup is great for comparing how different product categories perform across regions.

But what if you want to see which regions are strongest for each product category? Simple. Just drag the "Product Category" field from the Columns area to the Rows area and drag the "Region" field to the Columns area. Your table instantly restructures to show product categories down the side and regions across the top. This simple drag-and-drop action allows you to "pivot" a single table to answer multiple questions without creating a new report from scratch.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Add and Remove Detail with More Fields

Sometimes you need more detail. Let’s say you have "Product Category" in your rows. To get more granular, you can drag the "Product Name" field into the Rows area, placing it underneath "Product Category." Your pivot table will now group the individual product names under their respective categories, creating an expandable hierarchy.

The order of fields matters. The field on top acts as the primary group, and the field below it becomes the subgroup. You can add multiple layers of detail this way. To remove a field and simplify the view, just drag it out of the area box and back into the field list, or simply uncheck its box in the list.

Modifying How Values are Calculated and Presented

The Values area is for more than just summing numbers. You can change the calculation type and adjust the number formatting to provide better context and make your report easier to read.

Change a SUM to a COUNT or AVERAGE

By default, Excel sums numerical data placed in the Values area. But often, another calculation is more useful. For instance, you might want to know the average sale amount or count the number of individual sales transactions.

To change the calculation:

  1. In the Values area, click the field you want to change (e.g., Sum of Sales).
  2. Select Value Field Settings from the menu.
  3. In the dialog box that appears, go to the Summarize Values By tab.
  4. Choose a different calculation, such as Count, Average, Max, or Min.
  5. Click OK.

Your pivot table will instantly update to show the new calculation.

Show Values as a Percentage

Raw numbers are helpful, but percentages often tell a clearer story. For example, seeing that the "Electronics" category accounts for 35% of total sales is more insightful than just seeing its total sales figure. The "Show Values As" feature is perfect for this.

  1. Open the Value Field Settings for your chosen value again.
  2. Click on the Show Values As tab.
  3. In the dropdown menu, you'll find several powerful options. Some of the most useful are:
  4. Select your preferred option and click OK.

Pro Tip: You can show both the raw number AND the percentage. Simply drag the same "Sales" field into the Values area a second time. Then, leave one field as Sum of Sales and configure the other to show % of Grand Total.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Format Numbers for Readability

A wall of unformatted numbers is hard to look at. Clean formatting makes your report professional and easy to understand at a glance. In the Value Field Settings window, there's a Number Format button at the bottom.

Click it, and you'll get the familiar "Format Cells" dialog box. From here, you can format your values as Currency, Percentage (with decimal places), Accounting, and more. This saves you from having to manually format the pivot table cells yourself.

Filtering and Sorting Your Pivot Table

Once your pivot table is structured, filtering and sorting help you zoom in on the most important information.

Filter Data with Slicers

While you can use the dropdown arrows in the pivot table itself or drag a field into the Filters area, Slicers are a much more interactive and user-friendly way to filter your data. A slicer is essentially a set of clickable buttons that filter your pivot table visually.

  1. Click anywhere inside your pivot table.
  2. Go to the PivotTable Analyze tab on the Ribbon.
  3. Click Insert Slicer.
  4. A dialog box will appear listing your fields. Check the boxes for the fields you want to create slicers for (e.g., "Region" or "Year").
  5. Click OK.

Now you have floating filter panes with buttons for each item. Click "North" on the Region slicer, and your pivot table instantly updates to show only data for the North region. Hold Ctrl to select multiple items. Slicers are fantastic for dashboards and reports you share with others, as they make filtering intuitive for everyone.

Sort Your Data

Sorting lets you organize your report to highlight top performers, find outliers, or arrange things alphabetically. To sort, right-click any cell in the column you wish to sort by and choose Sort.

  • Sort Smallest to Largest / Largest to Smallest: If you right-click a cell containing a numeric value (like sales data), you can sort based on those values. This is perfect for quickly finding your best or worst-performing products or regions.
  • Sort A to Z / Z to A: If you right-click a cell with a text label (like a product name), you can sort alphabetically.

Keeping Your Pivot Table Up to Date

A common friction point with pivot tables is ensuring they reflect the latest data. Excel doesn't do this automatically, but updating is simple once you know how.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

How to Refresh Your Data

If you change or correct a value in your original data source, you need to tell your pivot table to re-read that information. Simply right-click anywhere in your pivot table and select Refresh. The pivot table will recalculate everything based on the current state of your source data.

Handling New Rows of Data

Refreshing only recalculates based on the current data range. If you add new rows or columns to your source data, refreshing won't include them. You need to update the source range itself.

To do this, click inside the pivot table, go to the PivotTable Analyze tab, and click Change Data Source. You can then select the new, larger range.

However, an even better way to handle this is to format your source data as an Excel Table before you even create the pivot table. Just select your data range and press Ctrl + T. Excel Tables automatically expand to include new rows and columns you add. If your pivot table is based on an Excel Table, all you need to do is hit Refresh, and the new data will be included automatically. No more resizing your data source range!

Final Thoughts

Learning how to adjust a pivot table unlocks its true potential, transforming it from a static report into a flexible analysis tool. By changing layouts, modifying calculations, filtering with slicers, and applying simple formatting updates, you can explore your data from various angles and discover insights that were previously hidden in the raw numbers.

For many of us, the process of pulling data, cleaning it in Excel, and wrestling with pivot tables is a weekly chore. It’s what you have to do to get answers. At Graphed, we created a tool that automates this entire cycle. Instead of downloading CSVs and building reports, we connect directly to your data sources like Google Analytics, Shopify, or Salesforce in real time. You can simply ask a question like, "Show me a comparison of revenue by product category from Shopify for last quarter" and get an interactive chart or report instantly, without ever opening a spreadsheet.

Related Articles