Forum Discussion
Create Pareto chart from multiple Excel workbooks
A bit of background - I work as a data analyst for a manufacturing company. We are starting daily safety inspection checklists (things like PPE use, proper chemical storage, etc) for each area of a plant (weld shop, paint, etc).
I have a workbook setup with tabs for Mon-Fri and a sixth tab which totals the daily counts based on different categories and then a Pareto chart is built from that.
Theoretically I would have one Excel file per week from each department at a given plant. How would I create a plant-wide chart which combines the totals from each department workbook? I'd prefer to use just formulas or something like Power Query and stay away from VBA or scripting. Any thoughts or links to a tutorial would be amazing.
4 Replies
- If you're going the PowerQuery route:
https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4
I think vba might be the better solution for this situation, A macro could be written to open selected workbooks in a folder, fetch the needed data and return it to your workbook for the charts. PowerQuery is going to have some overhead with those queries.- Interesting, I'll check out the link.
I've never done much with macros or VBA which is why I'm a little hesitant to go that route (although I do other kinds of programming sometimes). Where would I start with that?- The way I see it, you'd have two subroutines:
1. Open multiple workbooks from a given folder. The code simply opens each workbook, performs the actions in macro #2, then closes the workbook.
2. Locate the data to be extracted from each sheet of each workbook and then return it to your active workbook. This step can be fairly straightforward if there's consistency in where the data resides in each workbook.
