combine 2 workseets to create on pivot table

[amy_baker]

hello, i have a spreadsheet that i billed out/what is expected and the other sheet i get back that is what was paid/rejected. how do i combine the spreadsheets to then do a pivot table to see what was not paid?

[#2 · Accepted solution]

@amy_baker

To create a pivot table from data on two different worksheets, you’ll first need to combine the data into a single table. One way to do this is to use a third worksheet to consolidate the data from the two original worksheets.

Here’s an example of how you can do this:

1. Create a new worksheet and give it a name such as “Combined Data”.
2. In the new worksheet, set up column headers that match the headers in your two original worksheets.
3. Copy the data from the first worksheet and paste it into the new worksheet below the headers.
4. Copy the data from the second worksheet and paste it below the data from the first worksheet.
5. Make sure that the data is properly aligned with the column headers.

Once you have combined the data into a single table, you can create a pivot table from it.

Here’s how:

1. Select any cell within the combined data table.
2. Click on the “Insert” tab in the ribbon and select “Pivot Table”.
3. In the “Create PivotTable” dialog box, make sure that “Select a table or range” is selected and that the “Table/Range” field shows the range of cells containing your combined data.
4. Choose where you want to place the pivot table (e.g., in a new worksheet) and click “OK”.
5. Use the PivotTable Fields pane to arrange your data as desired.

This should allow you to create a pivot table that includes data from both of your original worksheets.

Hope this helps you.

[#3]

thank you soooo much!    

one more question, what are you thoughts on consolidate option in excel?@NikolinoDE 

[#4]

@amy_baker 

The Consolidate and Pivot Table options in Excel serve different purposes.

While both can be used to summarize and analyze data, they work in different ways.

The Consolidate option allows you to combine data from multiple worksheets into a single worksheet. You can use it to summarize data by category or by position using functions such as sum, average, count, etc.

On the other hand, a Pivot Table is a tool that allows you to interactively reorganize and summarize large amounts of data. You can use it to group, sort, and filter data, as well as to calculate subtotals and grand totals.

So while both options can be used to summarize and analyze data, they have different capabilities and are best suited for different tasks.

It’s always important to choose the right tool for the job based on your specific needs :).

[#5]

thank you very much for your help!

[#6]

I'm glad that I could help you a little bit.

I also wish you lots of success with Excel!

[#7]

thank you!