Power Query Problems

Save Excel Values and Formatting Without Queries or Connections

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to strip all background queries, connections, and the data model from an Excel file, leaving only the static values and original formatting.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Preparing a data-heavy workbook for distribution without exposing the underlying Power Query connections or external data model.
Observed behavior: Manually pasting values and formatting separately causes some PivotTables to lose their structural formatting, making it difficult to preserve the exact look.

Before you start

Always create a secure backup copy of your original workbook before deleting connections or overwriting data, as breaking data connections cannot be easily reversed.

Solution 1Recommended

Paste as Values and Formats on a Duplicated File

The most straightforward method to detach data from queries is to use the Paste Special feature on a copy of your workbook.

While effective for standard cells, be aware that this method may not flawlessly preserve complex PivotTable formatting. If you heavily rely on PivotTables, you may need to reapply specific design elements manually.

Create a copy

Save a complete copy of your original Excel workbook to prevent accidental data loss during the conversion process.

Copy the data

Select all cells in your worksheet by pressing Ctrl + A, then press Ctrl + C to copy the entire selection.

Paste Special Values

Right-click the top-left cell (A1) of a new sheet or the existing one, select 'Paste Special', choose 'Values', and click OK to lock in the numbers.

Paste Special Formats

While the area is still selected, right-click again, select 'Paste Special', choose 'Formats', and click OK to apply the original colors and borders.

PivotTable Limitations: Pasting formats may not retain the structure and style of dynamic PivotTables. Consider flattening PivotTables separately or using a sanitized sample workbook to test formatting retention.

Solution 2

Manually Delete Queries and Connections

If you want to keep the current sheet structure and PivotTables intact without using Paste Special, you can directly delete the connections from the ribbon.

Quickly Save Static Values and Formats with WPS Office

WPS Spreadsheet provides a seamless and lightweight environment to remove external dependencies, convert formulas to static values, and retain your original formatting with excellent Microsoft Excel compatibility.

1. Open your file: Launch WPS Spreadsheet and open your existing Excel workbook.
2. Select and Copy: Highlight the data range you want to freeze and press Ctrl + C to copy it.
3. Paste as Values: Right-click the destination cell, go to 'Paste Special', and select 'Paste Values'.
4. Retain Formatting: Use the 'Format Painter' or 'Paste Special > Formats' to reapply the original look seamlessly without bringing over connections.

100% compatible with Microsoft Excel (.xlsx) formats and stylingEasily convert complex formulas and connections to static valuesLightweight application that handles large datasets smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Why do my PivotTables lose their format when pasting as values?

PivotTables are dynamic objects in Excel. When you paste them as values, Excel converts them into standard static cells. This process strips away the embedded structural formatting associated with the PivotTable design, requiring you to manually reapply colors and borders.

How can I safely share an Excel file without the underlying Power Query data?

You can delete the queries from the Queries & Connections pane before sharing, or simply copy the visible data and paste it as values into a brand-new workbook to ensure absolutely no hidden data or connections are transferred to the recipient.

Does removing a connection delete my original data?

No, deleting a connection in Excel only breaks the link between your workbook and the external data source. The imported data currently sitting in your spreadsheet cells will remain exactly as it is, but it will no longer update automatically.

Excel & Data Problems