How to Append Excel Sheets from SharePoint Files While Preserving Formatting
Question details
The user wants to combine all worksheets from multiple Excel files located in a SharePoint document library into a single destination worksheet without losing the original cell formatting.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating multiple Excel reports or datasets hosted on SharePoint into one master sheet while keeping the visual styles intact.
- Observed behavior
- Standard tools like Power Query can successfully extract and append the data structure, but they strip away the original cell formatting (like colors, fonts, and borders).
Ensure all source Excel files in the SharePoint document library have standardized layouts and headers to prevent data misalignment when appending. It is highly recommended to sync your SharePoint folder locally via OneDrive for easier access before running any automation scripts.
Use VBA Macros to Combine Sheets and Preserve Formatting
Since Power Query drops cell formats, a VBA macro is the most robust desktop solution to iterate through synced SharePoint files, copy the used ranges, and paste them with their source formatting intact.
VBA cannot directly interact with web-based SharePoint URLs easily for complex operations. By syncing the SharePoint library to your local PC using OneDrive, you can use a standard VBA script to loop through the local folder path, opening each workbook, and applying the PasteSpecial method.
Open your SharePoint document library in your web browser and click the 'Sync' button to mirror the files to your local file explorer via OneDrive.
Open a new blank Excel workbook that will serve as your destination file. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' from the top menu and select 'Module'. Paste a standard folder-looping VBA script into the blank window.
Ensure your VBA code uses the Range.Copy method from the source files and the Range.PasteSpecial Paste:=xlPasteAll method in your destination workbook to retain all formatting.
Update the script with your local synced SharePoint folder path. Press F5 to execute the macro, which will open, copy, paste, and close each file automatically.

Use Power Query and Apply Standard Formatting Manually
If you are willing to let go of the original source formats, Power Query is the fastest and most reliable way to combine data from SharePoint without writing code.
Easily Merge Multiple Excel Files and Sheets with WPS Office
If you want to avoid writing complex VBA code or struggling with Power Query's formatting limitations, WPS Spreadsheet offers a built-in 'Merge Workbooks' feature. This intuitive tool allows you to consolidate multiple Excel files into a single workbook quickly while preserving basic cell formats and structures, saving you hours of manual work.
- 1. Sync SharePoint Files: Ensure your SharePoint folder containing the target Excel files is synced to your local desktop via OneDrive.
- 2. Launch WPS Spreadsheet: Open WPS Office and start a new blank Spreadsheet file.
- 3. Open the Merge Tool: Navigate to the 'Data' tab on the top ribbon and click on 'Merge' or 'Merge Sheet' (depending on your specific version).
- 4. Add Files to Combine: In the pop-up dialog, select 'Add Files' and browse to your synced SharePoint folder to highlight all the Excel files you want to combine.
- 5. Execute the Merge: Choose the option to merge multiple worksheets into one, and click 'OK' to generate your fully combined and formatted report.

Frequently Asked Questions
Why does Power Query remove my Excel cell formatting?
Power Query is an ETL (Extract, Transform, Load) tool designed strictly for data transformation and shaping. It extracts only the raw values and underlying data structure to ensure maximum performance and data consistency, intentionally discarding visual metadata like cell colors, fonts, and borders.
Can Office Scripts preserve formatting when merging SharePoint files?
Yes. If you use Excel for the Web, you can combine Office Scripts (written in TypeScript) with Power Automate. You can create a flow that opens each file in SharePoint, reads the range, and copies both the data and the formatting to a destination file. However, this requires intermediate coding and API handling skills.
Does syncing a SharePoint library locally affect other users?
No, syncing a SharePoint document library via OneDrive simply creates a local, cached mirror of the files on your computer. As long as you are only reading data from the files for your merge process and not saving over them, other users' workflows will remain completely unaffected.
Is there a way to merge sheets without macros or coding while keeping formats?
Without using macros or scripts, your best option is to manually copy and paste the ranges using 'Paste Special > Keep Source Formatting', or to utilize built-in merge tools found in alternative office suites like WPS Office, which handles basic formatting retention out-of-the-box.




