Data Import & Export

How to Append Excel Sheets from SharePoint Files While Preserving Formatting

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 873 views

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.

How to Append Excel Sheets from SharePoint Files While Preserving 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).
Before you start

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.

Solution 1Recommended

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.

1
Sync SharePoint Locally

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.

2
Open the VBA Editor

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.

3
Insert a New Module

Click 'Insert' from the top menu and select 'Module'. Paste a standard folder-looping VBA script into the blank window.

4
Configure Copy and Paste Methods

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.

5
Run the Macro

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 VBA Macros to Combine Sheets and Preserve Formatting
Syncing is Crucial: VBA interacts much more reliably with local file paths (C:\Users\...) than with HTTPS SharePoint links. Syncing creates the bridge needed for this solution to work smoothly.

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. 1. Sync SharePoint Files: Ensure your SharePoint folder containing the target Excel files is synced to your local desktop via OneDrive.
  2. 2. Launch WPS Spreadsheet: Open WPS Office and start a new blank Spreadsheet file.
  3. 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. 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. 5. Execute the Merge: Choose the option to merge multiple worksheets into one, and click 'OK' to generate your fully combined and formatted report.
Built-in visual tool to merge multiple Excel files without requiring any programming knowledge.Automatically preserves your data layout and standard cell formatting during the merge.Fully compatible with all major Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight, fast, and completely free to use for everyday office data management.
microsoft office alternative - wps office

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.