Pivot Table Issues

Fix Excel PivotTable Values Disappearing When Moving to Columns

Algirdas Jasaitis Sep 30, 2026 869 views

Question details

Values for specific months disappear from the PivotTable when the month field is moved from the Rows area to the Columns area.

Fix Excel PivotTable Values Disappearing When Moving Months to Columns

Product: Excel
Device & OS: not provided
Scenario: Reorganizing a PivotTable layout by shifting the date/month field to columns for a different view.
Observed behavior: Call counts for April, May, and June display correctly in rows, but June values mysteriously disappear when the field is dragged to columns, even though the source data is unchanged.

Before you start

Before troubleshooting, manually refresh your PivotTable and ensure your source data range does not contain completely blank columns that might disrupt the layout.

Solution 1Recommended

Clear Active Filters on Column Labels

Hidden filters applied to the field can carry over when moving it to columns, causing specific data points like certain months to be hidden.

When you pivot a field from Rows to Columns, any manual filters previously applied to that field (or newly inherited ones) remain active. If 'June' was filtered out in a different view, it will remain hidden in the column layout.

Locate Column Labels

Click on the drop-down arrow next to the 'Column Labels' header in your PivotTable.

Check the Filter List

Scroll through the list of months to see if June is unchecked.

Clear the Filter

Select 'Clear Filter From [Month Field]' or manually check the box for '(Select All)' to ensure all months are visible.

Apply Changes

Click 'OK' and verify that the missing values have reappeared in your PivotTable.

Filter Cleared: Clearing the filter forces the PivotTable to display all available data points for that specific dimension.

Solution 2

Unhide Worksheet Columns

If the spreadsheet columns where the new PivotTable data extends are manually hidden, the values will seem to disappear.

Solution 3

Update the PivotTable Data Source Range

The PivotTable might not be capturing the full dataset if new rows were added but the data source range was not updated.

Manage PivotTables effortlessly

Create and Manage PivotTables Seamlessly with WPS Office

WPS Spreadsheet provides a powerful and intuitive PivotTable feature. You can easily drag and drop fields between rows and columns, apply advanced filters, and analyze large datasets without worrying about formatting glitches or hidden data.

1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook containing the dataset.
2. Insert PivotTable: Highlight your data range, navigate to the Insert tab, and click on PivotTable.
3. Adjust Layout: Use the PivotTable pane on the right to easily drag the 'Months' field into the Columns area.
4. Manage Filters: Click the built-in filter icons directly on the table headers to ensure all months are selected and visible.

Fully compatible with Microsoft Excel (.xlsx) files and existing PivotTables.Intuitive drag-and-drop interface for flawless layout adjustments.Built-in advanced filtering tools to prevent accidental hidden data.Free, lightweight, and incredibly fast for large data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why do some PivotTable values vanish when I change the layout?

This most commonly happens because a filter was previously applied to the field. When the field moves to a new area (like from rows to columns), the filter stays active and hides specific data points.

Does moving a field from rows to columns delete my source data?

No. Changing the layout of a PivotTable only alters how the data is summarized and displayed visually. Your original source data remains completely intact and untouched.

How do I ensure my PivotTable includes new data I added?

You must refresh the PivotTable by right-clicking it and selecting 'Refresh'. If it still doesn't appear, go to 'Change Data Source' in the ribbon menu and ensure your newly added rows are included in the selected range.

Excel & Data Problems