To edit a PivotTable in Excel for Microsoft 365 on Windows, click any cell inside it, then use the PivotTable Fields pane or the PivotTable Analyze and Design tabs to change its fields, calculations, filters, and layout. To change the underlying numbers, edit the source data and refresh the PivotTable; typing over a summarized value does not change its source records.
What You Can Edit in a PivotTable
A PivotTable is a summary view of records from a source range, Excel Table, or supported external source. Its displayed results are shaped by the fields and settings you choose, rather than being a separate set of editable source values. You can revise the summary without rebuilding it: move a field between areas, change how values are calculated, apply filters, sort items, group dates, or adjust the report’s appearance.
The PivotTable Fields pane has four areas that control the arrangement:
- Filters: Adds a report-level filter that can limit the entire PivotTable.
- Columns: Displays field items across the top as column labels.
- Rows: Displays field items down the side as row labels.
- Values: Summarizes numeric or other supported fields, such as by sum, count, or average.
For example, a sales report with Region in Rows, Month in Columns, and Revenue in Values shows revenue by region and month. Moving Month to Rows changes the arrangement, but it does not change the underlying sales records.
Open the Field List and Rearrange Fields
Click inside the PivotTable to show its contextual ribbon tabs. On Windows, select PivotTable Analyze > Field List if the pane is hidden. In some Excel updates or window sizes, the contextual tab may be labeled Analyze. The PivotTable Fields pane lists source columns above the four layout areas.
- In the field list, select the checkbox beside a field to add it to the layout. Excel usually places text fields in Rows and numeric fields in Values, but the automatic choice can depend on the field and report.
- Drag a field from the list into Filters, Columns, Rows, or Values to place it exactly where you want it.
- Drag a field from one area to another to change its role, or clear its checkbox to remove it from the report.
- To change the order of row or column fields, drag them up or down within their area.
For instance, if a PivotTable currently shows sales by salesperson, drag Product from the field list into Rows above or below Salesperson. The nested order changes which grouping appears first. To compare products across regions, put Product in Rows and Region in Columns. The same field can sometimes be used more than once, such as placing a date field in both Rows and Filters, if that arrangement serves the report.
On Excel for Mac, click the PivotTable and use the contextual PivotTable Analyze tab and field list as well; the ribbon layout and exact labels can vary by release. Excel for the web offers PivotTable interaction and editing features, but some advanced options may be unavailable compared with the desktop app. If a command described here is missing, open the workbook in desktop Excel.
Change a Value Calculation or Number Format
A numeric field placed in Values is often summarized by Sum, but Excel may use Count when it interprets the source values as text or when the field contains nonnumeric entries. To change the calculation, open the drop-down beside the field name in the Values area and choose Value Field Settings. You can also right-click a value in the PivotTable and select Summarize Values By for common calculation choices.
Depending on the field and PivotTable type, choices can include Sum, Count, Average, Max, Min, and other summaries. If the report should show each department’s average order value rather than its total, change the Order Value field from Sum to Average. Check the resulting totals and labels: an average of averages is not necessarily the same as an average calculated from all underlying records, especially when groups contain different numbers of records.
The Show Values As tab in Value Field Settings changes how a summary is displayed, for example as a percentage of a grand total, a percentage of a row total, or a difference from another item. These options change the presentation of the summarized values, not the source records. To adjust currency, decimal places, or percentage formatting consistently, use Number Format within Value Field Settings rather than formatting individual cells. Direct cell formatting can be lost or behave unexpectedly when the PivotTable refreshes or changes size.
To rename a value heading, select its label and type a clearer name, provided it does not duplicate a source field name in a way Excel rejects. For example, change Sum of Revenue to Total Revenue. This changes the displayed heading, not the source column’s name or calculation.
Filter, Sort, Group, and Expand the Report
Use the drop-down arrows beside Row Labels or Column Labels to select specific items, apply label or value filters, and sort. A value filter can show, for example, the products with sales above a threshold. A report filter in the Filters area applies a selection to the whole PivotTable and is useful when readers need to switch between regions or years without rearranging the report.
For an interactive filter control, select a PivotTable cell and choose PivotTable Analyze > Insert Slicer, then choose the field to filter. A slicer presents clickable buttons and can make a report easier to use than a small drop-down. Date-based PivotTables may also support PivotTable Analyze > Insert Timeline. If several PivotTables share a compatible source, use the slicer’s Report Connections option to connect it to the relevant reports.
Dates can often be grouped into months, quarters, or years. Right-click a date item and choose Group, then select the periods needed. Grouping is useful when the source contains individual transaction dates but the report should summarize by month. If Group is unavailable or Excel reports that it cannot group the selection, inspect the source date column for blanks, text values, or invalid dates; one inconsistent entry can prevent grouping.
Use the plus and minus controls, or the context menu’s expand and collapse options, to show or hide detail within a hierarchy. This changes the visible level of detail rather than deleting records. If you need to inspect records behind a particular summary number, double-click that value; Excel can create a new worksheet containing the contributing source rows, subject to the source and workbook settings.
Change the PivotTable Layout and Appearance
The Design tab controls report layout and style. Report Layout offers Compact, Outline, and Tabular forms. Compact form places multiple row fields in one column and is space-efficient; Tabular form gives each row field its own column, which is often easier to read or copy into another analysis. Repeat All Item Labels can make repeated row labels visible in tabular reports.
Subtotals and grand totals are also controlled from Design. Turn subtotals on or off, choose where they appear, and decide whether grand totals should be shown for rows, columns, or both. These settings affect the report’s structure and displayed totals, so confirm that the resulting layout still communicates the intended comparison. The PivotTable Styles gallery can apply banded rows, header formatting, and other visual styles; choosing a style does not alter the calculation.
If you want the report to preserve custom formatting after a refresh, open PivotTable Analyze > Options (the dialog may be called PivotTable Options) and check the formatting-related settings available there, such as preserving cell formatting on update. Options and labels can differ slightly among Excel releases. Avoid relying on color alone to communicate a category, especially if the report will be printed or shared with people using different display settings.
Edit the Source Data or Change Its Range
To correct a wrong sales amount, add a missing transaction, or change a customer name, edit the source records, not the summarized PivotTable cells. Locate the source worksheet or external data system, make the correction there, and refresh the PivotTable. If you type directly into a PivotTable result cell, Excel will not treat that entry as a correction to the underlying records.
For a PivotTable based on a worksheet range or Excel Table, select a cell in the PivotTable and choose PivotTable Analyze > Change Data Source. Check the selected range or source table, then confirm the change. This is useful when a PivotTable was built from a fixed range and new records fall outside it. If the PivotTable uses the Data Model, an external connection, or another managed source, the source may need to be changed through its connection or query rather than by selecting a worksheet range.
For recurring lists, convert the source range into an Excel Table before creating the PivotTable: select a cell in the records and choose Insert > Table, confirming whether the data has headers. A PivotTable based on that Table can include rows added to the Table when refreshed, avoiding a manually extended fixed range. Keep each source column consistently typed and give each column a unique header; blank headers and mixed types can make fields confusing or interfere with grouping and summaries.
Changing the PivotTable source range does not repair the contents of the source itself. If an imported amount is stored as text, convert or correct it in the source data, then refresh. Also make sure the source has a single header row and no fully blank rows interrupting the records. These basic data checks help the field list and summary calculations behave predictably.
Refresh and Troubleshoot Common Problems
A PivotTable may continue showing old results after its source changes because its displayed summary has not been refreshed. Right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh. To update multiple workbook connections and PivotTables where supported, use Data > Refresh All. A refresh updates the PivotTable from its current source; it does not automatically correct a wrong source range or fix bad source values.
- New rows do not appear: Check whether they fall inside the PivotTable’s source. Expand a fixed range through Change Data Source, or use an Excel Table as the source for a growing list, then refresh.
- Numbers show as Count instead of Sum: Inspect the source column for numbers stored as text, blanks, or nonnumeric entries. Correct the source consistently and refresh.
- An old item remains in a filter: A PivotTable can retain items no longer present in the source. Open PivotTable Options, find the Data settings, set the number of items to retain per field to None if that setting is available, and refresh.
- Grouping is disabled: Check for blanks, text, or invalid values in the field being grouped. For dates, ensure every relevant source entry is a valid date.
- The Field List is missing: Click inside the PivotTable and choose PivotTable Analyze > Field List. If no contextual tab appears, verify that the selected cell belongs to the PivotTable.
When a change seems to have no effect, check whether the PivotTable is connected to the source you edited. Workbooks can contain several similar tables, queries, or PivotTables with different sources. Confirm the source and refresh status before rebuilding the report, since recreating it may discard useful layout choices or formatting.
When to Use a Different Approach
PivotTables are useful for interactive summaries, but they are not always the best tool for every task. If you need to edit individual records, use the source table. If you need a fixed report that will not change when the source is updated, copy the PivotTable and paste values into a separate area, understanding that the pasted copy will no longer refresh as a PivotTable. Keep the original PivotTable if you still need an interactive version.
For a formula-driven summary that updates cell by cell, worksheet functions may be more suitable, though they require maintaining formulas and ranges. For cleaning, combining, or reshaping larger recurring datasets, Power Query can prepare the source before a PivotTable summarizes it. These approaches are alternatives or companions, not edits to the PivotTable’s source records. Choose based on whether the priority is interactive exploration, record-level changes, a static snapshot, or repeatable data preparation.
Frequently Asked Questions
Can I edit the numbers directly inside a PivotTable?
No. PivotTable values are calculated from source records, so change the source data and refresh the report. You can change the calculation or display format, but that does not rewrite the records.
How do I add a field to an existing PivotTable?
Click inside the report, show the Field List using PivotTable Analyze > Field List, and drag the field into Rows, Columns, Values, or Filters. Check the result to ensure Excel placed it in the intended area.
Why is my PivotTable not updating?
Refresh it, then verify that the source includes the edited or newly added records. If the source is a fixed range, update that range; if it is an external connection, confirm that the connection refresh completed.
Can I change Sum to Average in a PivotTable?
Yes. Open Value Field Settings for the field in Values and choose Average under Summarize Values By. The option may be unavailable or behave differently for some nonnumeric fields and PivotTable sources.
How do I edit a PivotTable in Excel for Mac?
Click inside the PivotTable and use its contextual PivotTable Analyze tab and field list to change fields, then use the available value and design controls. Ribbon placement and labels can vary by Excel for Mac version; some advanced options may require desktop Windows Excel.
Related
- Excel Conditional Formatting Rules: How to Use Them
- Conditional Formatting Scatter Plots in Excel
- Conditional Formatting Percentages in Excel: A Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Auto Wrap Text in Google Sheets: A Quick GuideTo automatically show long text on multiple lines in Google Sheets, select the cells, then choose Format >…
- Outlook Share Calendar With a Distribution List?Outlook can send a calendar-sharing invitation to a distribution list, but that does not reliably give every member…
- Create a Page Template in Word and Save It for ReuseTo create a page template in Word, set up a blank document with the page size, margins, headers,…
- Excel Dropdown Lists: How They Work and How to Create ThemAn Excel dropdown list is a cell control that lets someone choose from a set of permitted entries…
- Find and Replace in Word: Open and Use the Dialog BoxIn Microsoft Word for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace; in current versions,…
- Excel Not Responding and Won’t Close? Try These FixesIf Excel says Not Responding and will not close, first give it a minute if it may be…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.