Forum Discussion
Pivot table data sources
Hello,
I would like to create a pivot table from an excel workbook containing various worksheets. All the worksheets have the same headers. The data in the worksheets is organized by month - one worksheet August the other September for example. How do I create a pivot table from various worksheets? Is there an online tuturial specific to this?
Thanks
You can create a pivot table from multiple worksheets with the same headers by consolidating data from each separate worksheet range into a PivotTable on a master worksheet. You can summarize and report results from separate worksheet ranges by assembling them so that you can more easily update and aggregate them.
Here’s how you can create a PivotTable from multiple worksheets with the same headers in Excel:
- Click on the “Insert” tab and “PivotTable”.
- A dialog box will appear now, and you will be asked whether we should create the PivotTable in a new or the same sheet. It is good to use a new sheet option in excel.
- Lastly, check the box “Add this data to the Data Model”.
Here’s an online tutorial that might help you with this.
NikolinoDE these tutorials are helpful thank you. the columns in my worksheets are the same the rows are different as the values are unique (patients for example), is there one approach that is better suited for this format? Thanks
You can use either of these two approaches:
- Combine all data into one worksheet before creating a pivot table.
- Create a pivot table for each worksheet and then combine them using a formula.
If I may recommend you, add a file (without sensitive data) and explain your project step by step based on this file. In addition, information about the digital environment would be very helpful. Digital environment means information about the Excel version, operating system, storage medium, etc.
Thank you for your patience and understanding
NikolinoDE Your links are correct, but the numbered list in your post is wrong?
- Thanks for the tip, had overlooked it.
- Why not have all data in one sheet?
JKPieterse yes I could copy and paste into one sheet but there is a lot of data so trying to figure out another way
- Where does your data come from? There is no need to place data you want to summarize in a pivot table on a worksheet. If you import that data from e.g. a CSV file, you can pull that data directly into a query and create a pivot table from the query. You can even very easily import multiple CSV files into the PT. Check out Data, Get Data, From File, From Folder.
