Forum Discussion
Help on Combining Data from Multiple Worksheets into a Single Worksheet in Excel
I'm going to step aside for the moment, since you do have Power Query at your disposal (I don't in my Mac), and let @Sergei Baklan help you with that process. It would seem to me that it should work.
My own experience, without it, is to that do that monthly copy and paste of the new month's data onto the bottom of a cumulative database is to do--in just one step--what you're trying to do in many. I do this with my monthly statements from credit cards and banks, into a single database to track our income and expenses....I let Excel (Pivot Table) do all the calculating. It is work, yes, but I would point out that you've been doing work and aren't satisfied with the results.
So I trust that Power Query will do the trick for you, the trick of combining all those monthly reports neatly and accurately....
Best wishes.
mathetes thank you for your help! I will try to do what you were mentioning as well and see what works better for me. I just wanted to find the easiest way to do this and be able to present it in a dashboard for a better visualization.
SergeiBaklan could you walk me through so that I don't mess up what you were trying to explain to me?
Both of your responses and ideas are great, I just need to try it out and see what works better for me. I do appreciate both of your assistance with this because its been giving me a headache trying to figure it out. I wouldn't say I'm in expert in excel but I just like to always learn more and find better ways to extract big data and provide a better visualization.
To test please put 2-3 of your files in some folder, let say c:\test. Use this connector
Select the folder on next step
Next click Transform Data
Power Query editor will be opened. Click on this button
Here select sample file or keep first one by default, and select the sheet / table with your data - they have to have the same name for all files
Result collects data from all your files in one table. You may do other transformations if necessary. Click here on File->Close and Load to. In your case perhaps it's better to select loading to data model
After that you may insert PivotTable with data model as the source. For the testing you may load result into Excel Table as well.
Okay, I have tried to follow your steps but it did not work so im not sure if I am doing something wrong. I have attached my screenshots for you to look at. Can you aslo let me know if my ribbons has everything I should have?
Thank you!
You skipped few steps. Here
click on Edit, not on Combine (in my version this button is Transform Data). After that approximately as in my previous post, perhaps your user interface is slightly different.
