'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Power Query Percentage Formatting Lost on Refresh
Question details
The user needs to prevent percentage formatting in an Excel table from reverting or disappearing after refreshing data loaded from Power Query.
- Product
- Microsoft Excel / Power Query
- Device & OS
- not provided
- Scenario
- Refreshing external data loaded from Power Query into an Excel worksheet table.
- Observed behavior
- The percentage number formatting manually applied to the column is lost and reset every time the Power Query connection is refreshed.
Ensure your Power Query data is already loaded into an Excel worksheet table and that you have applied your preferred percentage formatting to the column.
Enable 'Preserve Cell Formatting' in External Data Properties
Modifying the external data properties of the loaded table ensures your custom number formatting remains intact after a refresh.
By default, Excel might overwrite worksheet formatting with the raw data types coming from Power Query during a refresh. Adjusting the table's external data properties forces Excel to respect the formatting you applied directly in the worksheet.
Click any cell inside the Excel table that contains the loaded Power Query data.
Navigate to the 'Table Design' (or 'Table Tools') tab on the ribbon and click on 'Properties'. Alternatively, right-click a cell in the table, hover over 'Table', and select 'External Data Properties'.
In the External Data Properties dialog box, locate the section for data formatting and check the box next to 'Preserve cell formatting' or 'Preserve column sort/filter/layout'.
Click 'OK' to save the settings. Refresh your Power Query data to confirm the percentage formatting no longer disappears.
Set Data Type to Percentage inside Power Query Editor
Define the column as a Percentage data type before it loads into Excel to enforce the correct format automatically.
Switch to WPS Office for Hassle-Free Data Formatting
If you are struggling with complex Power Query settings and formatting loss in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment where formatting data is intuitive and seamless.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx files.
- 3. Format Data: Select your columns and apply percentage formatting securely using the Home ribbon.
Frequently Asked Questions
Why does Power Query overwrite my Excel cell formats on refresh?
During a refresh, Power Query pushes its defined data types into the Excel worksheet. If the 'Preserve cell formatting' option is disabled in the table properties, Excel will discard manual formatting in favor of the raw data feed structure.
Can I fix this without changing the query itself?
Yes, adjusting the 'External Data Properties' of the Excel table allows you to retain any custom formatting applied in the worksheet without having to modify the underlying Power Query steps.
Where do I find External Data Properties in newer versions of Excel?
The most reliable way to find this setting in newer versions is to right-click any cell within your loaded data table, hover over 'Table' in the context menu, and click on 'External Data Properties'.
