How to Center Power Query Output Across Multiple Columns in Excel
Question details
User needs to format a Power Query output so the first column and its header appear centered across two columns, and the formatting should remain stable when the source table gains or loses rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a Power Query output table and wants a presentation layout in which the first column and its header are centered across two adjacent columns, while keeping the table dynamic.
- Observed behavior
- Power Query output tables do not reliably apply Center Across Selection or merged cells to automatically generated headers; formatting directly inside the output may be lost or misaligned after refresh.
Make sure your Power Query query is loaded to a worksheet as a table and that you can identify the header cell and first output column you want to center. Reserve two adjacent cells outside the table for each centered value.
Create a dynamic presentation area outside the Power Query table and use Center Across Selection
Use this method to keep the Power Query table as a stable data source while formatting separate formula-driven cells that update automatically when the query returns more or fewer rows.
Power Query output tables are dynamic and may not preserve merged or Center Across Selection formatting applied directly to their generated headers and cells. A dedicated presentation area outside the table gives you full control over alignment without interfering with the query refresh.
Open your workbook in WPS Office Spreadsheets and identify the table range created by Power Query, including the header cell and the first output column you want to center.
Click an empty cell outside the table, such as two columns to the right of the first data column, type = followed by the header cell reference, for example =B4, and press Enter.
In the cell below, enter a formula that references the first data cell of the Power Query table column, for example =B5. Fill this formula down to the maximum number of rows you expect after a refresh.
For each row, select the formula cell and the blank cell immediately to its right so the content can be centered across both cells.
Press Ctrl+1 to open Format Cells, go to the Alignment tab, set Horizontal to Center Across Selection, and click OK. Repeat for the header and the data rows that need the same layout.
Format directly after each Power Query refresh
Use this method when the query output is small or the formatting does not need to be permanent; it must be reapplied after every refresh.
Center Power Query Output Across Multiple Columns in WPS Office Spreadsheets
Open your Excel-compatible workbook in WPS Office Spreadsheets and use a separate formula-driven presentation area with Center Across Selection. This keeps the Power Query output dynamic while providing stable, spreadsheet-native formatting that works when rows are added or removed.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open the workbook that contains the Power Query output table, and enable editing if prompted.
- 2. Create a presentation area outside the table: Click an empty cell outside the query table, such as two columns to the right of the first output column, and enter a formula that references the header or data cell, for example =B4.
- 3. Expand formulas for expected rows: Use the fill handle to copy the formula down to cover the maximum number of rows you expect after a refresh, so new rows are included automatically.
- 4. Apply Center Across Selection: Select the formula cell and the adjacent blank cell, press Ctrl+1 to open Format Cells, go to the Alignment tab, set Horizontal to Center Across Selection, and click OK.
- 5. Refresh and verify the layout: Refresh the Power Query connection from the Data or Query tab, then confirm that the presentation cells update, remain centered, and include any newly added rows.
Frequently Asked Questions
Can I merge cells inside a Power Query output table?
Avoid merging cells in the output table. Power Query tables can change size after refresh, and merged cells may block row insertion or cause incorrect layout. Use Center Across Selection in a separate presentation area instead.
Does Center Across Selection survive a Power Query refresh?
When applied directly to the query output, it may be lost or misaligned after refresh. Apply Center Across Selection outside the table or reapply it after each refresh; a formula-driven presentation area is the more stable option.
How do I apply Center Across Selection in WPS Office Spreadsheets?
Select the cells you want to center across, press Ctrl+1 to open Format Cells, go to the Alignment tab, set Horizontal to Center Across Selection, and click OK.
How can I keep the formatting when the source table gains or loses rows?
Keep the Power Query table as the data source. Create formulas in a separate area that reference the table columns, fill the formulas down to the maximum expected number of rows, and apply Center Across Selection to those presentation cells.
Will this method work if the query returns more rows later?
Yes, if the presentation formulas are filled down to or beyond the expected maximum row count. After refresh, the new query rows appear in the table and the corresponding formula cells update and remain centered.
