Excel - Pulling Data From Multiple Files Into One Master Summary

Question

Excel - Pulling Data From Multiple Files Into One Master Summary

Anonymous

Hi,

I have over 20 employees who document their daily production on to their own Excel spreadsheet/file. I have one master production summary for all of the employees production. However, I have to manually enter their production from their spreadsheets into the master production summary. As you can imagine, it's incredibly tedious and time consuming.

Is there a way that I can have my master production summary "pull" all of the data from each of the employee's own production Excel spreadsheets/files when needed i.e. every day or week. Not just one time.

All of the employees own production files/spreadsheets have the same column and row headings. The only difference would be their data entered each day.

I hope this makes sense.

Any help would be greatly appreciated.

My Excel version is Office Professional Plus 2016.

Thank you!

Kimberly

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

Hi, Thank you for reaching out to us today. I’m Kevin and I’m happy to to help you out with your question. You can link the data from your employee’s sheets to your master sheet by referencing the cells. The best way to do this is to open the master sheet and click on the required cell and enter =. Then switch to the employee sheet and select the cell you want to link. This will create a link between the cells that will update whenever you open the master sheet. You can repeat this for all required cells. I hope this information is helpful. If you have any further questions or need additional assistance, please reach back out and I will be more than happy to assist you. Best Regards, Kevin

That is OK for a couple of cells, but if you are pulling in lots of data, PowerQuery is the way to go.

Answer 2

No problem!

The answer is PowerQuery. (In 2016 is hides under misleading label of "Get & Transform ... <sigh>)

.

PowerQuery is the "new & improved" answer to any question of importing data into Excel. (Not so new, it's been around since 2010. MS just forgot to mention it)

.

As long as all of the files use the same format, nothing to it. You can tell PowerQuery to import data from all files in a specific folder.

Hint: after importing the combined data, you can use PivotTables to easily generate the summary reports.

.

Here are some articles to get you started.

.Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)
https://www.youtube.com/watch?v=fHFUh6EhBcw&list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-&index=5

Excel Import data from data sources (Power Query) (44 entries)

https://support.microsoft.com/en-us/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a

.

Combine all Worksheets in a Workbook with Power Query

https://www.youtube.com/watch?v=-787a_89BZk 13min23

Apr 12, 2022 Skillwave Training

Power Query has an awesome 'one-click transform' to combine all files in a folder. While it actually takes more than one-click, it does make it super easy to create a structure where you can clean up a single file, and have that template applied to each file before they are combined. Unfortunately there is no 'one-click' process to do this for all worksheets in a workbook - but you CAN build it yourself. In this video, Ken shows you exactly what you need to do.

Combine all Worksheets in a Workbook with Power Query

https://www.youtube.com/watch?v=-787a_89BZk 13min23

Apr 12, 2022 Skillwave Training

Power Query has an awesome 'one-click transform' to combine all files in a folder. While it actually takes more than one-click, it does make it super easy to create a structure where you can clean up a single file, and have that template applied to each file before they are combined. Unfortunately there is no 'one-click' process to do this for all worksheets in a workbook - but you CAN build it yourself. In this video, Ken shows you exactly what you need to do.

If you want specific help, create 3 or 4 simple example files, upload the folder containing the files to your personal (NOT WORK!) OneDrive folder and share with us.

Upload Example File - Share via “Personal” OneDrive

There is no way to upload example files directly to this forum.
.

Trouble shooting problems using this text only forum can be like a visit to the dentist without anaesthetics: a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we hopefully can eventually come up with a / “the” solution.
.

Often it is faster and easier for everyone if we have a “sample file(s)” so we can get "hands on", to look at, and to “play with” your data. There are a couple of advantages of providing example files:

. * we have exactly the same data you are describing,

. * if your problem requires merging multiple files, we can work with that

. * you will often get different suggestions from users with points of view and experience.
.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:
https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156
.

The above article includes links to a macros to randomize existing text in Word and Excel
.

Share OneDrive files or Folders with a link

https://support.microsoft.com/en-us/office/share-files-and-folders-in-onedrive-personal-3fcefa26-1371-401e-8c04-589de81ed5eb
.

Note: make sure to upload to your Personal (consumer) OneDrive rather than your work OneDrive or SharePoint. Files in the Work cloud can be hard to share due to security restrictions placed by the work admins. Or use any other free storage service (anything other than business OneDrive or SharePoint).
.

Another article explaining need for sharing example files:

Why a sample file is important for troubleshooting. How to do it. https://answers.microsoft.com/en-us/msoffice/forum/all/why-a-sample-file-is-important-for-troubleshooting/9441ae3c-1e92-41c6-9a1f-5b377b08e5a5
.

How Much Cloud Storage Space Will You Really Use?https://www.howtogeek.com/822046/how-much-cloud-storage-space-will-you-really-use/
Fergus O'Sullivan @FergusOSullivan Aug 5, 2022
If you’re shopping for cloud storage, one of the big decisions you’ll make is how much of it to pay for. Starting at free plans for a few gigabytes to expensive subscriptions that offer several terabytes of storage space; you have a lot of choice.
.

2022 03 15- The 5 Best Free Cloud Storage Services
https://www.howtogeek.com/787191/best-free-cloud-storage-services/
There’s no shortage of great options if you’re looking for free cloud storage. Plenty of companies will let you store several gigabytes in the cloud, and all you need to do is create an account. We’ve put together our five favorites.
. * Google Drive      15GB
. * MS OneDrive (free) 5GB
. * Mega                  20GB (first year)
. * IceDrive             10GB
. Sync.COM             5GB
.

Answer 3

Hi,

Thank you for reaching out to us today. I’m Kevin and I’m happy to to help you out with your question.

You can link the data from your employee’s sheets to your master sheet by referencing the cells. The best way to do this is to open the master sheet and click on the required cell and enter =. Then switch to the employee sheet and select the cell you want to link. This will create a link between the cells that will update whenever you open the master sheet. You can repeat this for all required cells.

I hope this information is helpful. If you have any further questions or need additional assistance, please reach back out and I will be more than happy to assist you.

Best Regards, Kevin