Excel 2010 Pivot Tables...Preparing Data
I wish to prepare a pivot table so I can prepare a cash-flow of expenditure over each year for different criteria.
The data I have been given shows the expenditure each year. (refer Table 1 in 'Data' sheet on attached Example file)
But Im not sure how I can create a pivot table from the raw data without having to reorganise it as shown in Table 2 in 'Data' sheet on attached Example file.
There is lots of data (not shown in Example) so need to avoid reorganising the raw data.
Any suggestions how to do this.
Example-Pivot-Table-data.xlsx
The data I have been given shows the expenditure each year. (refer Table 1 in 'Data' sheet on attached Example file)
But Im not sure how I can create a pivot table from the raw data without having to reorganise it as shown in Table 2 in 'Data' sheet on attached Example file.
There is lots of data (not shown in Example) so need to avoid reorganising the raw data.
Any suggestions how to do this.
Example-Pivot-Table-data.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That would be good Rory
Is your source data always in that exact format with the merged header cells?
ASKER
Hi Rory
The file iI attached contained simplified information. I can get rid of merged headers,,ie remove 'cost' cell and just have the years as the headers for the columns with costs.
I can see how I can do pivot table straight from raw data.
So all I need is to be able to produce one pivot table for each sheet of data (I have one for each site).
Each sheet will be the same format, but could have extra rows as may be more activities for one site vs another.
Note sure if you can provide code without having the exact spreadsheet and data structure Im using.
The file iI attached contained simplified information. I can get rid of merged headers,,ie remove 'cost' cell and just have the years as the headers for the columns with costs.
I can see how I can do pivot table straight from raw data.
So all I need is to be able to produce one pivot table for each sheet of data (I have one for each site).
Each sheet will be the same format, but could have extra rows as may be more activities for one site vs another.
Note sure if you can provide code without having the exact spreadsheet and data structure Im using.
ASKER
Hi Rory. I ended up getting Power Query which seems like it will create pivot table from multiple sheets. So most likely can get by without code.
The pivot table will structure the data that I wish to show. But I need to make some adjustments to the format headings etc to make it present well. Is there a way to do this?
The pivot table will structure the data that I wish to show. But I need to make some adjustments to the format headings etc to make it present well. Is there a way to do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for offer of coding, and addressing follow up queries
ASKER
The 2nd sheet of the file I uploaded gives an idea of one of the ways I want to present the information using the pivot table.
I use Microsoft Office Home & Business 2010. MS says Power Query requires Microsoft Office 2010 Professional Plus with Software Assurance. Hmm