Pivot Table Issues

How to Stop Excel PivotTable Formatting Changes When Using Slicers

Amos Gikunda Sep 30, 2026 869 views

Question details

The user wants to prevent PivotTable formatting, such as borders and text alignment, from changing or resetting when selecting filters via a slicer.

How to Stop PivotTable Formatting Changes When Using Slicers

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering PivotTable data using a slicer and preparing the worksheet for printing or exporting to PDF.
Observed behavior: Applied cell formatting, alignments, and borders are lost or altered upon clicking a slicer, despite having automatic column sizing disabled and formatting preservation enabled.

Before you start

Before proceeding, ensure you have applied your formatting to the actual PivotTable structure rather than formatting the underlying worksheet cells behind the table.

Solution 1Recommended

Toggle Format Preservation and Reconnect Slicers

Resetting the formatting preservation settings while the slicer is temporarily disconnected can force Excel to correctly lock in your applied styles.

Excel can sometimes exhibit inconsistent behavior when slicers are actively connected to a PivotTable during formatting adjustments. Temporarily removing the connection allows the formatting rules to properly register.

Access PivotTable Options

Right-click any cell inside your PivotTable and select PivotTable Options from the context menu.

Disable preserve formatting

Navigate to the Layout & Format tab, uncheck the box for 'Preserve cell formatting on update', and click OK.

Disconnect the slicer

Temporarily disconnect your slicer by right-clicking the slicer, selecting 'Report Connections', and unchecking the current PivotTable.

Re-enable formatting preservation

Open PivotTable Options again, check the 'Preserve cell formatting on update' box, and click OK. Now manually reapply your desired cell borders and alignments to the PivotTable.

Reconnect and test

Right-click the slicer, return to Report Connections, and reconnect it to your PivotTable. Test the slicer to confirm the formatting remains stable.

Toggle Format Preservation and Reconnect Slicers

Exporting to PDF: Once the formatting successfully locks, your PivotTable borders will remain intact when using the 'Print to PDF' or 'Export' features.

Solution 2

Apply a Custom PivotTable Style

Creating and applying a custom PivotTable style ensures that your borders, fills, and fonts are natively integrated into the table's design, preventing slicers from overriding them.

Free Microsoft Office alternative

Try WPS Office for Stable Data Analysis and PivotTables

If formatting glitches and slicer conflicts continue to disrupt your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for complex data analysis, fully supporting Microsoft Excel file formats and advanced PivotTable functionalities without the formatting headaches.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the PivotTables and slicers.
3. Filter data flawlessly: Use your slicers and apply formatting seamlessly, taking advantage of the built-in PDF export tools to generate perfect reports.

Highly compatible with Microsoft Excel (.xlsx, .xls) formats and existing PivotTable structures.Retains complex table formatting, borders, and slicer connections during PDF exports.Free and lightweight, ensuring fast performance even with extremely large datasets.Familiar user interface makes migrating from Microsoft Office completely seamless.

Frequently Asked Questions

Why does my PivotTable lose its column widths when I click a slicer?

This happens because Excel defaults to auto-sizing columns whenever PivotTable data is refreshed or filtered. To stop this, right-click the PivotTable, choose PivotTable Options, and under the Layout & Format tab, uncheck the 'Autofit column widths on update' box.

Can I lock specific cell borders in a PivotTable so they never change?

The most reliable method to permanently lock borders in a PivotTable is to define them within a Custom PivotTable Style from the Design tab. Applying manual borders from the Home tab makes them vulnerable to being wiped out when the data range shrinks or expands.

Does preserving cell formatting apply to conditional formatting?

Yes, but you must ensure the conditional formatting rule is applied to the PivotTable structure rather than fixed cell references. When setting up the rule, choose the option to apply it to 'All cells showing [Field] values' so it expands and contracts correctly with slicer filters.

Excel & Data Problems