How to Keep PivotChart Column Widths Fixed with a Slicer in Excel
Question details
The user needs a way to maintain consistent column sizing in a PivotChart when applying slicer filters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering data using a slicer connected to a PivotChart.
- Observed behavior
- When slicer filters change the number of displayed categories, the PivotChart automatically adjusts the column widths, causing them to shrink or expand unpredictably.
Because traditional spreadsheet software does not provide a built-in toggle to lock PivotChart column widths during filtering, you will need to utilize a helper range or a VBA workaround.
Use a Helper Range and a Regular Chart
This method prevents automatic resizing by routing your PivotTable data through standard cells and plotting it on a regular chart instead of a dynamic PivotChart.
By linking regular cells to your PivotTable and creating a standard Column Chart, the chart will treat the data points as a fixed series. This ensures the visual gap width and column thickness remain entirely unchanged even when slicer selections leave some categories blank.
Set up a separate range of cells outside your PivotTable. Reference your PivotTable categories and values using the GETPIVOTDATA formula or standard cell references.
Wrap your formulas in an IFERROR function (e.g., =IFERROR(GETPIVOTDATA(...), NA())). Returning NA() ensures that when a slicer removes a category, the regular chart does not plot a zero, leaving an empty space while keeping the other columns the exact same size.
Select your newly created helper range. Navigate to the Insert tab on the top ribbon and select a standard 2D Column Chart. Do not select the PivotTable itself.
Right-click any column in your new regular chart, click Format Data Series, and manually set the Gap Width to your desired percentage. It will now stay locked regardless of slicer interactions.
Apply a VBA Macro to Force Gap Width Updates
Advanced users can automate the resizing process by triggering a macro every time the PivotTable updates.
Try WPS Office for Powerful Data Visualization
If you often encounter missing charting features or frustrating formatting limitations in standard spreadsheet software, consider switching to WPS Office. It provides a lightweight, highly compatible, and completely free alternative for managing complex data sets, PivotTables, and visual reports.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and install it on your Windows, Mac, or Linux device.
- 2. Open your spreadsheet file: Launch WPS Spreadsheets and click 'Open' to load your existing .xlsx workbook containing your PivotTables and slicers.
- 3. Analyze and format effortlessly: Use the highly intuitive charting tools in WPS to format your reports securely and reliably, avoiding complicated workarounds.
Frequently Asked Questions
Why do my PivotChart columns automatically change size?
By default, a PivotChart dynamically links to the source PivotTable. When a slicer filters out data, the number of visible categories decreases. The chart automatically stretches the remaining columns to fill the available chart area, ignoring your previous width settings.
Can I lock the chart dimensions to prevent resizing?
You can lock the physical outer dimensions of the chart area by right-clicking the chart, selecting 'Format Chart Area', navigating to 'Properties', and choosing 'Don't move or size with cells'. However, this only locks the outer border, not the internal column widths when categories disappear.
Will Microsoft add a native feature to lock PivotChart column widths?
Currently, there is no built-in toggle. Users frequently request this feature. You can urge the development team to add it by navigating to File > Feedback in your Office application and submitting a formal feature request.
