Power Query Problems

How to Save Excel Values and Formatting Without Queries or Data Model

Khadija Khan Sep 28, 2026 869 views

Question details

The user wants to create a flattened copy of an Excel workbook that retains all visual formatting and data values while securely stripping out Power Query connections and the underlying Data Model.

How to Save Excel Values and Formatting Without Queries or Data Model

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sharing a secure, standalone copy of a complex workbook without exposing backend queries or the Power Pivot Data Model to the recipient.
Observed behavior: Standard saving keeps queries and data models intact, while basic copy-pasting as values often loses complex layout elements and PivotTable formatting.

Before you start

Always create a backup or a dedicated test copy of your original workbook before deleting queries or flattening data to prevent accidental loss of your backend connections.

Solution 1Recommended

Use Paste Special to Keep Values and Formats

The most reliable manual method to remove underlying queries and data models is to copy the data and use Paste Special into a brand-new workbook.

By pasting values and then formatting separately, you sever all ties to the original Power Queries and Data Model. This creates a safe, static version of your data for sharing.

Copy the original data

Open your source workbook, select all the cells you want to keep (or press Ctrl+A), and press Ctrl+C to copy them.

Paste as values in a new workbook

Create a new, blank workbook. Right-click the starting cell (A1), select 'Paste Special', choose 'Values', and click OK. This pastes the raw data without any background queries.

Apply original formatting

With the pasted data still selected, right-click the starting cell again, select 'Paste Special', choose 'Formats', and click OK to restore colors, borders, and layouts.

Use Paste Special to Keep Values and Formats

PivotTable Limitation: This method converts PivotTables to standard cell ranges. Some complex Power Pivot formatting may not carry over perfectly and might require minor manual adjustments.

Solution 2

Manually Delete Queries and Connections

If you want to keep the original file structure and standard formulas but just need to remove the backend data links, you can delete the queries directly.

Free Microsoft Office alternative

Flatten Workbooks and Share Safely with WPS Office

If you find Microsoft Excel's Power Queries and Data Models too complex or difficult to manage when trying to share clean files, try WPS Office. It provides a lightweight, highly compatible spreadsheet tool that makes copying data as flat values and formats incredibly straightforward, ensuring your shared files are secure and visually intact.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Easily copy and paste values and formats to strip out unwanted backend data.Lightweight design that loads large datasets quickly without complex Data Models.Familiar user interface makes transitioning from MS Office completely seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my PivotTable lose its formatting when I pasted it as values?

When you paste a PivotTable as values, it converts from a dynamic data object into static text and numbers. The 'Paste Formats' command sometimes fails to capture the intricate, layered styles of Power Pivot tables, resulting in partial or missing visual formatting.

Can I remove the Data Model without losing my standard formulas?

Yes, if you only delete the queries and connections from the 'Data' tab rather than pasting everything as values, your standard worksheet formulas will remain intact. However, any formulas relying directly on the Data Model (like CUBE functions) will break.

Is there a way to flatten an entire workbook at once without macros?

Excel does not have a native one-click 'flatten workbook' button. You must manually copy and use 'Paste Special > Values' for each sheet, or save the workbook as a CSV (which loses formatting entirely). For complex files, using a VBA macro is the most efficient way to loop through all worksheets.

How do I securely share an Excel file that contains sensitive queries?

To securely share a file without exposing backend queries, create a new blank workbook and paste only the values and formats from the original file. Save this new 'flattened' file, check for any hidden sheets, and share it via a secure cloud service like OneDrive.

Excel & Data Problems