How to Preserve Worksheet Formatting When Loading Power Query Data in WPS Spreadsheets
Question details
The user needs to load or refresh Power Query results into a preformatted worksheet while preserving the existing fonts, colors, borders, and column widths.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user already has a worksheet with specific formatting and wants Power Query to populate that worksheet without replacing the visual design.
- Observed behavior
- Power Query loads data into a default table, and the resulting table does not match the existing worksheet formatting.
Before loading the query, check that the destination range can accommodate the returned rows and columns and decide whether you will load to a new table or an existing preformatted table.
Load Power Query Data to the Target Worksheet and Format the Resulting Table
Use this method when you want to keep a preformatted worksheet and apply the formatting to the table that Power Query creates or refreshes.
Power Query handles extraction and transformation, but worksheet formatting is best applied in WPS Spreadsheets after the data is loaded. When you refresh the query into the same table, the table formatting remains in place as long as the table itself is not deleted.
In WPS Spreadsheets, open your workbook and go to the Data tab, then open Power Query or select the existing query you want to edit.
In the Power Query Editor, click Close & Load To or Load To, select Table, choose Existing worksheet, and then click the cell where the preformatted data should begin.
After the query loads, select the resulting table and apply the desired fonts, fill colors, borders, or number formats from the Home tab and Table Tools Design tab.
Right-click the table and choose Refresh, or go to the Data tab and choose Refresh All, to confirm that the formatting remains after the query updates.
Disable Automatic Column-Width Adjustment for the Loaded Table
Use this method if column widths change after a refresh even though other table formatting remains intact.
Preserve Formatting with Power Query in WPS Spreadsheets
WPS Spreadsheets lets you load Power Query results into an existing worksheet table, apply cell and table formatting, and refresh the query without losing the worksheet appearance.
- 1. Download WPS Office: Go to the official WPS Office website and download WPS Office Free for your device.
- 2. Open WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that contains your Power Query or importable data.
- 3. Load Power Query data to the existing worksheet: Go to the Data tab, open Power Query, and use Close & Load To to load the query result into the preformatted worksheet table.
- 4. Apply and verify formatting: Format the resulting table from the Home and Table Tools Design tabs, then right-click the table and choose Refresh to confirm the formatting stays in place.
Frequently Asked Questions
Why does my Power Query table lose formatting after a refresh in WPS Spreadsheets?
Formatting is usually lost when the query creates a new table or when the table is deleted before refreshing. Load the query into an existing worksheet table, format that table, and keep the table intact so WPS Spreadsheets can preserve the formatting on refresh.
How do I stop Power Query from changing column widths when data refreshes?
Select a cell in the loaded table, open Table Properties or External Data Properties from the right-click menu, turn off the automatic column-width adjustment option, and then refresh the query.
Can I apply custom number formats to a Power Query table in WPS Spreadsheets?
Yes. After the query loads, select the columns or cells in the resulting table and apply number formats from the Home tab. The formatting remains when the same table is refreshed.
Do I need to format data inside the Power Query Editor to keep it formatted?
No. Use the Power Query Editor for extraction and transformation only. Apply fonts, colors, borders, and column widths in the worksheet after loading so those worksheet formats are preserved during refresh.
