Merge multiple excel files into one (the formatting of the file also)

Copper Contributor

Dear all,

 

Im using excel 2016. me and my colleuages are working on an identical file(we make copy of the file for each one of us every morning) but we are putting data in different row and colums of the file. After we are done , we want to conbine all this information together into a master file and keep the formating of our files.  Does anybody knows how to do it? its a daily job that i have to do.

 

Thank you in advance :)

 

 

 

5 Replies
Hi! Sounds stressful work. Ever thought of a free Microsoft account? You and your pals can store ONE single excel file, stored on a OneDrive. Excel can do consolidations. But now you have no control, who changed, deleted or created something. 1000 copies don’t make sense after a while. In OneDrive and excel online you see every versioning and even can reset to a previous version. Your excel 2016 works better with a having a Microsoft account. To get that just go to live.com to create it, privatly for free. Ask your admin in your company how to get how much licences. Then go to your excel FILE tab and link it to your account. OneDrive will ease your daily work I guess. Greets, Eva

@Eva Vogel The OP is talking about "colleagues" working on the files, so advising them to create personal Microsoft accounts is probably not a good idea. It will be the nightmare of any IT department to see employees farm out company files on personal cloud shares that IT can't control. I'd be very hesitant to give out such advice.

Hello @ioannaterzi ,

 

you may need to revise the data architecture for this. Using Power Query (Get and Transform) in Excel 2016, you can easily combine the data from several Excel workbooks. Each person can work on a separate file and a master workbook can be set up that pulls the data from all workbooks in a folder. 

 

Whether or not this approach works for you will depend on your data structure, though, so it would be good if you could share a few more details. 

thank you for your advices, i tried macro but it crashes... im still struggling!!

Hello @ioannaterzi ! I think @Ingeborg Hawighorst is right to do your issue with power query. Here is a youtube video for your posted question:

 

https://www.youtube.com/watch?v=NHNnnuOF8kU

 

Greets ,Eva.

If you work with data in many worksheets, sometimes it's better to consolidate this data into one worksheet. Especially if they all have the same header fields. You can usually do a copy and paste, but if there are a lot of worksheet or if this is a recurring task, you might want to semi automate