I'm trying to create a chart from multiple sheets in an excel file.

Question

I've got multiple sheets that contain data for 2 similar products. Each sheet is also a different date with customers that may be repeated due to various regions. I've tried doing a combined pivot table. That works for combining the customers, but I haven't been able to get the data to separate by date as well. I've tried to work with combining graphs with no luck. Below is an image of what I'm going for. It's a chart from a pivot table for a single date. But I'm having no luck on a combined pivot table.

Here is what happens when I try to add multiple data sources.

Answer 1

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Answer 2

Hi,

Follow the steps in this video to append data from multiple sheets into a single sheet and then create a Pivot Table - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube.

In the individual sheets, please ensure that there is a Date column.

Answer 3

Thanks for the help, but I was able to figure out what I did wrong. I hadn't selected customer name from each data set.

Answer 4

I think this is really going to help me. I was dabbling with Power Querry, but didn't know how to use it.